Tables and data types

Types and table kinds affect correctness, time-zone behaviour and how much storage protection you pay for.

Snowflake's types are mostly familiar, with a few specifics:

NUMBER(p, s) is the core numeric type; INT and BIGINT are aliases for NUMBER(38, 0). FLOAT is 64-bit.
VARCHAR with no length is fine. The declared length doesn't change storage, so there's no reason to write VARCHAR(255).
Timestamps come in three flavours. TIMESTAMP_NTZ is wall-clock time with no zone, TIMESTAMP_LTZ is an instant shown in the session's time zone, and TIMESTAMP_TZ stores an offset with each value. Plain TIMESTAMP maps to NTZ by default. Pick deliberately; for event times, LTZ or TZ avoids ambiguity.
VARIANT, OBJECT and ARRAY hold semi-structured data such as JSON.
BOOLEAN, DATE, TIME, BINARY, GEOGRAPHY and VECTOR also exist.

Constraints: PRIMARY KEY, UNIQUE and FOREIGN KEY can be declared on standard tables but are not enforced; only NOT NULL is. They serve as documentation and hints for tools.

Table kinds trade safety for cost. Permanent tables (the default) have Time Travel and a 7-day Fail-safe. Transient tables have at most 1 day of Time Travel and no Fail-safe, which suits staging data you can reload. Temporary tables live only for the session. CREATE TABLE ... AS SELECT (CTAS) and CREATE OR REPLACE TABLE are everyday idioms.

Example

CREATE OR REPLACE TABLE customers (
  id         INT PRIMARY KEY,          -- declared, NOT enforced
  email      VARCHAR NOT NULL,         -- NOT NULL is enforced
  balance    NUMBER(12, 2),
  created_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
  profile    VARIANT
);

INSERT INTO customers (id, email, balance) VALUES (1, 'a@x.io', 10), (1, 'b@x.io', 5);
SELECT COUNT(*) FROM customers;          -- 2  (duplicate id accepted!)

-- Cheaper table for reloadable staging data
CREATE TRANSIENT TABLE stg_customers AS SELECT * FROM customers;

-- Session-only scratch table
CREATE TEMPORARY TABLE tmp_ids AS SELECT DISTINCT id FROM customers;

Remember

INT is NUMBER(38,0); VARCHAR length doesn't affect storage
Choose TIMESTAMP_NTZ, LTZ or TZ deliberately
Only NOT NULL is enforced; PK/UNIQUE/FK are informational
Transient tables skip Fail-safe and cost less to keep

Watch out for

Relying on a PRIMARY KEY to prevent duplicate rows; deduplicate in your pipeline instead.

Check yourself

You insert two rows with the same id into a table declared with id INT PRIMARY KEY. What happens?
Which table type is best for a staging table rebuilt every night from files?

Official docs: docs.snowflake.com/en/sql-reference/data-types

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks