Snowflake – Data Types That Matter

Snowflake's type list is short, which is a relief after Postgres. Most of it needs no thought. But three areas — numbers, timestamps and VARIANT — produce real bugs, and they are the kind that pass every test and then go wrong in production against real data. This lesson is mostly about those three.

The list

Note what is absent. There is no separate SERIAL, no enum, no array-of-typed-values in the Postgres sense — an ARRAY holds VARIANT elements — and no unsigned integers.

Numbers: use NUMBER for money, always

NUMBER(precision, scale) is exact. FLOAT is IEEE 754 binary floating point and is not. The difference shows up the first time somebody sums a large column of currency and reconciles it against another system:

SELECT 0.1::FLOAT + 0.2::FLOAT       AS float_sum,     -- 0.30000000000000004
       0.1::NUMBER(10,2) + 0.2::NUMBER(10,2) AS exact_sum;   -- 0.30

So: NUMBER(19,4) or NUMBER(12,2) for money, always. Use FLOAT for measurements and scientific values where a fractional error is meaningless and speed matters.

Two details save trouble later. INT is exactly NUMBER(38,0) — there is no separate small-integer type and no storage saving from pretending there is, because Snowflake compresses by the values actually present. And declaring a precision does not reserve space; a NUMBER(38,0) column holding only small values costs the same as NUMBER(5,0). Declare precision for correctness, not for size.

Watch the scale in division, though. It is the one arithmetic surprise:

-- Integer division keeps a limited scale by default.
SELECT 10 / 3;                          -- 3.333333
SELECT (10 / 3)::NUMBER(20,10);         -- 3.3333330000  — precision already lost
SELECT 10::NUMBER(20,10) / 3;           -- 3.3333333333  — cast BEFORE dividing

Strings: VARCHAR(n) buys you validation, not space

VARCHAR, STRING and TEXT are one type. The maximum length is 16,777,216 characters, and VARCHAR with no length is exactly that maximum.

Crucially, the declared length does not affect storage or performance. VARCHAR(10) and VARCHAR(16777216) holding "hello" occupy the same space. The only thing a length gives you is a rejection when something longer arrives:

CREATE TABLE t (code VARCHAR(3));
INSERT INTO t VALUES ('ABCD');
-- String 'ABCD' is too long and would be truncated in column 'CODE'

Which is a feature when the column really is a three-letter code, and an obstacle when a supplier starts sending four. Constrain what is genuinely constrained; leave the rest as STRING.

Timestamps: pick one and standardise

This is the type decision that causes the most confusion, because the three variants look interchangeable and behave differently.

TIMESTAMP on its own is an alias controlled by the TIMESTAMP_TYPE_MAPPING parameter, which defaults to TIMESTAMP_NTZ. That default is fine, but relying on a parameter someone can change is not — write the variant you mean.

CREATE TABLE events (
  event_id     INT,
  occurred_at  TIMESTAMP_NTZ,   -- explicitly stored in UTC by convention
  loaded_at    TIMESTAMP_LTZ    -- an instant; display follows the reader
);

-- The session zone changes what LTZ shows, and nothing about what is stored.
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
SELECT loaded_at FROM events;

ALTER SESSION SET TIMEZONE = 'UTC';
SELECT loaded_at FROM events;   -- same row, different text

The recommendation, for a warehouse anyone else will query: store UTC in TIMESTAMP_NTZ and convert at the edges. It makes every value mean the same thing regardless of who is querying, and it makes date arithmetic reproducible. Use TIMESTAMP_LTZ deliberately, when you want the display to follow the reader; use TIMESTAMP_TZ when the originating offset is data you must not lose.

The conversion functions worth knowing:

SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', occurred_at) AS local_time,
       DATE_TRUNC('month', occurred_at)                          AS month,
       DATEADD('day', -7, occurred_at)                           AS a_week_earlier,
       DATEDIFF('hour', occurred_at, CURRENT_TIMESTAMP())        AS hours_ago
FROM   events;

VARIANT: the type that holds anything

A VARIANT column stores any of the other types, and in practice stores JSON. It is what makes Snowflake pleasant with semi-structured data — you can load a JSON document without designing a schema for it and still query it in SQL.

CREATE TABLE raw_events (payload VARIANT);

INSERT INTO raw_events
SELECT PARSE_JSON('{"user": {"id": 42, "email": "a@example.com"},
                    "tags": ["beta", "eu"], "score": 9.5}');

SELECT payload:user.id::INT       AS user_id,
       payload:user.email::STRING AS email,
       payload:tags[0]::STRING    AS first_tag
FROM   raw_events;

The cast is not optional. Without ::STRING, a text value comes back as a VARIANT still carrying its JSON quotes — "a@example.com", with the quote marks part of the value — and every comparison and join against it silently fails to match. This is the single most common semi-structured bug, and lesson 9 goes through it properly along with FLATTEN.

A VARIANT holds up to 16 MB compressed per row, so it is not a place to put a large document.

What a type does not control

Coming from a row-store, it is natural to assume the type declaration drives storage layout and therefore performance. In Snowflake it largely does not, and knowing that removes a whole category of premature optimisation.

Data is stored columnar and compressed per micro-partition, and the compression is chosen from the values actually present. A NUMBER(38,0) column holding order ids under a million compresses to about what NUMBER(20,0) would. A STRING column holding two-letter country codes compresses to almost nothing regardless of the declared maximum.

So the reasons to declare a narrow type are correctness reasons, not storage ones:

• A length or precision limit rejects bad data at load time, which is usually where you want to find out.
• The declared type is documentation that survives the person who wrote the pipeline.
• Downstream tools — BI, dbt, an ORM — read the type and generate from it.

The one place type choice does affect performance is the column you filter on. Comparing a DATE against a DATE lets partition pruning work; storing dates as strings and comparing them against string literals mostly works too, until somebody writes '2024-1-5' and the ordering silently stops being chronological.

Casting and NULLs

SELECT '42'::INT,                     -- 42
       CAST('42' AS INT),             -- the same thing, ANSI spelling
       TRY_CAST('banana' AS INT),     -- NULL, no error
       '42'::INT;                     -- errors on 'banana'

TRY_CAST and its siblings — TRY_TO_NUMBER, TRY_TO_DATE, TRY_TO_TIMESTAMP — return NULL instead of failing. In a loading pipeline that is usually what you want: land the row, flag the bad value, and keep going rather than aborting a batch of a million because one field was empty.

One Snowflake-specific null trap. Semi-structured data has two kinds of nothing: SQL NULL, meaning absent, and JSON null, which is a value. They are not equal to each other, and IS NULL does not catch the second:

SELECT PARSE_JSON('null')            IS NULL AS json_null_is_sql_null,  -- FALSE
       PARSE_JSON('{"a": 1}'):b      IS NULL AS missing_key_is_null,    -- TRUE
       IS_NULL_VALUE(PARSE_JSON('null'))     AS is_json_null;           -- TRUE

If a filter on JSON data is dropping rows you expected, this is usually why.

Next: loading data — stages, COPY INTO, and why file size matters more than you would guess.