Snowflake SQL Dialect and Data Types

Identifier case, QUALIFY, timestamp variants, NUMBER precision, GEOGRAPHY, PIVOT, and the syntax details that prevent subtle portability bugs.

Snowflake SQL is close enough to standard SQL to feel familiar and different enough to draw blood. You’ll write a JOIN and a GROUP BY and everything works exactly as you’d expect from Postgres or SQL Server — and then a column you know exists returns “invalid identifier,” a decimal you loaded comes back as a float with a rounding error, or a timestamp shifts three hours between two accounts. None of that is a bug. It’s the dialect, and it’s the data types, and once you’ve seen where the sharp edges are you stop cutting yourself on them.

This is the reference chapter. It’s the one you come back to when a query behaves differently than the same SQL did somewhere else. We’ll go through the type system, the identifier-casing rule that causes more support tickets than anything else in Snowflake, the dialect extensions worth reaching for, and how transactions actually work — all of it worked against SNOWFLAKE_SAMPLE_DATA.TPCH_SF1 so you can run it as you read.

USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;

The type system, and its aliases

Snowflake has fewer types than it looks like it has, because most of the names you know are aliases for a small set of physical types. Knowing the real type under the alias is what keeps you out of trouble.

Numbers are all one type. There is exactly one exact-numeric type in Snowflake — NUMBER(precision, scale) — and INT, INTEGER, BIGINT, SMALLINT, TINYINT, BYTEINT, DECIMAL, and NUMERIC are all aliases for it. Write INTEGER and you get NUMBER(38, 0). Write BIGINT and you get the identical NUMBER(38, 0) — Snowflake does not have a narrower 8-byte integer to switch to. Precision (total digits) can go up to 38; scale (digits after the decimal point) up to 37. The declared precision doesn’t change storage — Snowflake stores the value in as few bytes as it needs regardless — but scale is real, because it decides how much of your fraction survives.

SELECT
  l_extendedprice::NUMBER(12, 2)      AS money_ok,   -- keeps the cents
  l_extendedprice::NUMBER(12, 0)      AS money_lost, -- rounds to whole dollars
  l_discount::NUMBER(3, 2)            AS discount    -- 0.06, exact
FROM lineitem
LIMIT 5;

The point of NUMBER(12, 2) for money isn’t storage, it’s exactness. Which brings us to the type you should be suspicious of.

FLOAT is a trap for money. FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, and REAL are all the same type: a 64-bit IEEE-754 double. It’s binary floating point, so it can’t represent 0.1 exactly, and sums of many small floats accumulate error. That’s fine for a scientific measurement and wrong for a financial ledger. If a loader infers FLOAT for a price column because the CSV had a decimal point in it, you now have a rounding bug waiting to be discovered by an accountant. The fix is to declare the column NUMBER(p, s) on purpose rather than letting inference guess — this is the “financial decimal becoming a float-shaped problem” the stub warned about, and it’s real.

Strings: length is a ceiling, not a shape. VARCHAR(N) in Snowflake means “at most N characters.” It is not padded — a one-character value in a VARCHAR(255) column takes the storage of one character, not 255. CHAR(N) does not pad either; CHAR, CHARACTER, STRING, TEXT, NVARCHAR, and friends are all aliases for VARCHAR, and the default with no length is the maximum, VARCHAR(16777216) — 16 MB. Because length is a ceiling and costs nothing to leave large, the common Snowflake style is to declare VARCHAR (or STRING) with no length at all and only add one when you genuinely want the database to reject longer input. Coming from a row-store where CHAR(1) saved bytes over VARCHAR, unlearn that instinct — here it saves nothing.

BOOLEAN holds TRUE, FALSE, or NULL, and it’s usefully liberal about conversion: string 'yes', 't', 'on', '1' and numeric non-zero coerce to TRUE; 'no', 'f', '0', and 0 to FALSE.

Dates and — the important part — three timestamps. DATE is a calendar day, no time. TIME is a time of day, no date. And then there are three timestamp types, and choosing the wrong one is how a value silently shifts hours between environments:

  • TIMESTAMP_NTZ — “no time zone.” A wall-clock reading stored as-is, with no zone attached and no conversion ever applied. 2026-07-04 09:00:00 stays 2026-07-04 09:00:00 no matter who reads it. Use it for things that are a wall-clock fact — a business date-time, a scheduled local time — where you never want it re-interpreted.
  • TIMESTAMP_LTZ — “local time zone.” Stored internally as an absolute UTC instant, but displayed converted into the reader’s session time zone (TIMEZONE session parameter). The same stored row shows 09:00 to a session in New York and 14:00 to one in London. Use it for a real moment in time that different people should see in their own zone — an event timestamp, a load time.
  • TIMESTAMP_TZ — “time zone.” Stores the instant and an explicit UTC offset captured at write time. Use it when the originating offset itself is data you need to keep — a client in -05:00 and one in +05:30 writing to the same column, where “which offset did this come from” matters.

The word TIMESTAMP on its own is an alias, and which of the three it points at is controlled by the session/account parameter TIMESTAMP_TYPE_MAPPING, whose default is TIMESTAMP_NTZ. So CREATE TABLE t (ts TIMESTAMP) gives you an NTZ column by default — but an admin can change the mapping account-wide, which means the same DDL can produce different column types in two accounts. When behavior matters, name the concrete type (TIMESTAMP_NTZ) rather than the alias. You can watch all three diverge in one query:

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT
  '2026-07-04 09:00:00'::TIMESTAMP_NTZ AS ntz,   -- 09:00, always
  '2026-07-04 09:00:00'::TIMESTAMP_LTZ AS ltz,   -- 09:00 shown in NY session
  '2026-07-04 09:00:00 +00:00'::TIMESTAMP_TZ AS tz;  -- keeps +00:00

Change the session TIMEZONE to Asia/Kolkata, rerun, and only the LTZ value moves. That’s the whole distinction, visible in one statement.

The semi-structured trio — VARIANT, OBJECT, ARRAY — hold JSON-shaped data inside SQL, each capped at 16 MB. They get their own full treatment in post 11; here just note that they’re first-class column types, not a bolt-on.

GEOGRAPHY and GEOMETRY are the spatial pair. GEOGRAPHY treats coordinates as points on a sphere (longitude/latitude, great-circle distances) — the right choice for anything Earth-scale. GEOMETRY treats them as points on a flat Cartesian plane — the right choice for projected or planar coordinate systems. They accept WKT, WKB, and GeoJSON, and come with a large ST_* function family. TPCH has no geospatial columns, so this is the one type you won’t exercise here, but knowing which is spherical and which is planar saves a confusing afternoon later.

BINARY (alias VARBINARY) stores raw bytes up to 8 MB, read and written as hex or base64. You’ll meet it mostly as the output of hashing and encryption functions.

Casting: ::, CAST, and the TRY_ family

Snowflake will implicitly coerce types when it’s unambiguous — comparing a VARCHAR that looks like a number to a NUMBER, or passing an integer where a float is expected — but it’s conservative, and relying on implicit casts is how you get surprised. Prefer explicit. The idiomatic explicit cast is the :: operator; CAST(expr AS type) is the ANSI-standard spelling of exactly the same thing:

SELECT
  '1998-01-01'::DATE            AS d,
  CAST('42.5' AS NUMBER(4, 1))  AS n,
  o_totalprice::VARCHAR         AS price_text
FROM orders LIMIT 3;

The crucial safety valve is TRY_CAST (and the typed TRY_TO_NUMBER, TRY_TO_DATE, TRY_TO_TIMESTAMP, …). A plain cast on bad input fails the whole statement — one unparseable string and your million-row query errors out. TRY_CAST returns NULL for the values it can’t convert and lets everything else through, which is exactly what you want when cleaning raw or semi-structured data:

SELECT
  TRY_CAST('not a number' AS NUMBER)  AS bad,   -- NULL, no error
  TRY_TO_DATE('1998-13-40')           AS bad_dt, -- NULL, no error
  TRY_TO_NUMBER('42.5')               AS good;   -- 42.5

Reach for TRY_CAST on any column whose cleanliness you don’t control; save the plain cast for values you’ve already trusted.

Identifier case-folding: the bug factory

This is the single most important behavioral rule in Snowflake SQL, and it catches nearly everyone once.

Unquoted identifiers fold to uppercase. When you write SELECT o_totalprice FROM orders, Snowflake stores and resolves those names as O_TOTALPRICE and ORDERS. It doesn’t matter whether you typed them lower, upper, or mixed — unquoted, they all fold to uppercase before anything else happens. This is why every object in a fresh Snowflake account has a SHOUTING name in the metadata even though nobody typed it that way.

Double-quoted identifiers are exact and case-sensitive, forever. The moment you wrap a name in double quotes, Snowflake takes it literally — exact case, spaces and punctuation allowed, no folding. "MyColumn" is stored as MyColumn and can only ever be referenced as "MyColumn". And here’s the collision: an unquoted MyColumn folds to MYCOLUMN, which is a different name from the quoted "MyColumn". Likewise a column created as "id" (lowercase, quoted) cannot be found by writing id unquoted — because unquoted id resolves to ID, and ID ≠ id.

-- Unquoted: these three all resolve to the SAME column, O_ORDERKEY
SELECT o_orderkey, O_ORDERKEY, O_OrderKey FROM orders LIMIT 1;

-- Quoted must match the stored case exactly:
SELECT "O_ORDERKEY" FROM orders LIMIT 1;   -- works: TPCH stores uppercase
SELECT "o_orderkey" FROM orders LIMIT 1;   -- ERROR: invalid identifier

Because TPCH’s columns were created unquoted, they’re stored uppercase, so "O_ORDERKEY" works and "o_orderkey" fails. Now imagine the opposite. A loading tool — some ELT connectors, some Spark writers, some scripts translating a lowercase Postgres schema — creates columns quoted and lowercase: "id", "order_date". Every hand-written query against that table must now quote every column forever, because the natural unquoted order_date folds to ORDER_DATE and won’t match "order_date". That’s the endless "quoted" bug: a table you can’t query without quotes, and analysts who keep getting “invalid identifier” on names they can plainly see in the column list.

Two defenses. First, when you create objects, create them unquoted and let them fold uppercase — then everyone can query them in any case without quotes, which is the frictionless path. Second, if you’re stuck consuming lowercase-quoted columns and it’s driving you mad, the session parameter QUOTED_IDENTIFIERS_IGNORE_CASE makes quoted identifiers resolve case-insensitively too — but it’s account-affecting and blunt, so understand it before flipping it. The everyday rule is simpler: don’t quote identifiers unless you truly need a special character or a preserved case, and you’ll mostly never think about this again.

The dialect: extensions worth learning on purpose

Standard SQL runs unchanged on Snowflake. On top of it sits a set of extensions that turn multi-query problems into one clean statement. Several of these — QUALIFY, PIVOT/UNPIVOT, LISTAGG — got worked examples back in post 4; here’s the rest of the toolkit, plus the why behind the ones you’ll lean on hardest.

QUALIFY — the WHERE for window functions. You can’t filter on a window function in a WHERE clause, because window functions are computed after WHERE. The classic workaround is to wrap the query in a subquery and filter outside it. QUALIFY removes the subquery: it filters on window-function results the same way HAVING filters on aggregates. It’s the Snowflake feature you’ll miss most when you go back to a database without it:

-- The 3 most valuable orders per customer, no subquery
SELECT o_custkey, o_orderkey, o_totalprice
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY o_custkey ORDER BY o_totalprice DESC
) <= 3;

ILIKE and RLIKE. ILIKE is LIKE with case folding — pattern matching that ignores case, so you don’t have to UPPER() both sides. RLIKE (alias REGEXP) matches a POSIX regular expression:

SELECT DISTINCT c_name
FROM customer
WHERE c_name ILIKE '%customer#0000012%'      -- case-insensitive
   OR c_comment RLIKE '.*(express|urgent).*'; -- regex

IFF and DECODE. IFF(cond, then, else) is a two-branch CASE compressed to one function — cleaner for simple conditionals. DECODE(expr, match1, result1, match2, result2, …, default) maps a value against a list of cases, and it treats NULL as matchable (unlike a chain of = comparisons, which never match NULL):

SELECT
  o_orderkey,
  IFF(o_totalprice > 200000, 'large', 'normal') AS size_bucket,
  DECODE(o_orderstatus,
         'O', 'Open',
         'F', 'Fulfilled',
         'P', 'Partial',
         'Unknown')                             AS status_label
FROM orders
LIMIT 10;

GROUP BY ALL and SELECT * EXCLUDE / RENAME / REPLACE. These are quality-of-life extensions that cut repetition. GROUP BY ALL groups by every non-aggregated column in the SELECT list, so you don’t restate them (or count positions):

SELECT
  n.n_name AS nation,
  o.o_orderpriority,
  SUM(o.o_totalprice) AS total
FROM orders o
JOIN customer c ON c.c_custkey = o.o_custkey
JOIN nation n   ON n.n_nationkey = c.c_nationkey
GROUP BY ALL;   -- groups by nation, o_orderpriority

SELECT * gets modifiers so you rarely have to enumerate a wide table just to drop or rename one column: * EXCLUDE (col) selects everything but a column, * RENAME (old AS new) renames in place, and * REPLACE (expr AS col) swaps a column’s expression while keeping its position:

SELECT * EXCLUDE (n_comment) RENAME (n_name AS nation)
FROM nation;

MERGE and multi-table INSERT deserve a mention here as dialect too, but they’re about writing data, so they land in the transactions section below.

MATCH_RECOGNIZE, briefly. For row-pattern matching — finding sequences across ordered rows, like “three consecutive months of rising revenue” or a funnel of events in order — Snowflake implements the SQL-standard MATCH_RECOGNIZE. It defines pattern variables with DEFINE, an ordering with ORDER BY, and a regex-like PATTERN over those variables. It’s a genuine power tool for sessionization and sequence analysis, well beyond what a newcomer needs on day one; file it under “things Snowflake can do that most warehouses can’t” and return to it when you have an ordered-sequence problem that window functions make awkward.

Semi-structured operators. The colon path accessor (payload:customer.id) and FLATTEN are part of the dialect too, covered in post 11.

Constraints: declared, recorded, and mostly ignored

Here is a fact that belongs in the first hour of anyone’s Snowflake career and somehow never gets said out loud: Snowflake does not enforce your constraints. Except one.

CREATE TABLE orders (
    o_orderkey  NUMBER PRIMARY KEY,      -- recorded. not enforced.
    o_custkey   NUMBER NOT NULL,         -- ENFORCED.
    o_status    VARCHAR,
    CONSTRAINT fk_cust FOREIGN KEY (o_custkey)
        REFERENCES customer (c_custkey)  -- recorded. not enforced.
);

INSERT INTO orders VALUES (1, 100, 'O');
INSERT INTO orders VALUES (1, 100, 'O');   -- succeeds. two rows, same "primary key".

NOT NULL is real: try to insert a null and Snowflake rejects it. PRIMARY KEY, UNIQUE, and FOREIGN KEY are metadata — Snowflake stores them, shows them in DESC TABLE, hands them to the query optimizer as hints, and lets BI tools read them to infer joins. It will not check them. You can insert a thousand duplicate primary keys and a foreign key pointing at a customer who does not exist, and every insert will succeed.

This is not a bug or an oversight; it’s the analytics-warehouse bargain. Enforcing uniqueness on every insert means checking every insert against the whole table, which is exactly the kind of per-row work a columnar, immutable-micro-partition engine is built to avoid. The transactional database that enforces your keys is doing it because it has to — it’s serving your application. Snowflake assumes something upstream already did.

The consequence is the part that matters, and it reshapes how you work:

  • Declare them anyway. They document intent, the optimizer genuinely uses them, and BI tools draw better models from them. Free, and useful.
  • Never trust them. A PRIMARY KEY in a Snowflake DDL is a claim, not a guarantee. If you have not tested it, you do not know it.
  • Test them. This is why every serious Snowflake project ends up running assertions over its own tables — a unique test on the key, a relationships test on the foreign key — and it is why the tooling that does that (dbt, most obviously) became standard in this ecosystem rather than merely popular. The constraint machinery your OLTP database gave you for free has to be rebuilt as tests, and running them is not optional hygiene; it is the only thing standing between you and a silently duplicated key.

Every duplicate-row bug you will ever debug in Snowflake starts here.

Transactions: how writes actually behave

Everything so far has been reads. When you start writing, Snowflake’s transaction model is worth understanding precisely, because it’s different from an OLTP database in ways that mostly help you.

Autocommit is on by default. Each statement you run commits on its own the moment it succeeds. There’s no open transaction quietly accumulating changes unless you start one. To group statements into a unit, wrap them explicitly:

BEGIN;                              -- or BEGIN TRANSACTION
  UPDATE my_orders SET status = 'F' WHERE status = 'O';
  INSERT INTO audit_log VALUES ('closed open orders', CURRENT_TIMESTAMP());
COMMIT;                             -- both land, or ROLLBACK to discard both

ROLLBACK instead of COMMIT discards everything since BEGIN. Inside a transaction the statements succeed or fail together; that’s the multi-statement atomicity you’d expect.

Every statement is atomic on its own. Even outside an explicit transaction, a single DML statement is all-or-nothing. An UPDATE that touches five million rows and hits an error on row four million leaves the table exactly as it was — there is no half-applied statement. You never have to clean up a partially-completed statement.

No long-running locks; readers never block. Snowflake is multi-version. A query reads a consistent snapshot of the data as of the moment it started, and it is never blocked by a concurrent writer — and a writer is never blocked by readers. There are no row-level locks held across your session the way an OLTP database would hold them. Concurrent writes to the same table do serialize (Snowflake takes a brief lock at commit so two UPDATEs can’t corrupt each other), but this is a short commit-time affair, not a long-held lock that makes other sessions wait on your open transaction. For an analytics workload — many readers, periodic batch writers — this means you essentially never think about lock contention, and a long-running dashboard query and a table refresh coexist without blocking each other.

Because the sample database is a read-only share, you can’t run DML against TPCH itself. So build a small writable copy in your own schema first — this also shows CREATE TABLE AS, the fastest way to materialize a query result:

CREATE OR REPLACE TABLE my_orders AS
SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.orders
WHERE o_orderdate >= '1998-01-01';

MERGE — upsert in one statement. MERGE reconciles a target table against a source, applying inserts, updates, and deletes by whether rows match — the standard “load new, update changed” pattern in one atomic statement instead of a delete-then-insert dance:

MERGE INTO my_orders t
USING (
  SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.orders
  WHERE o_orderdate = '1998-08-01'
) s
ON t.o_orderkey = s.o_orderkey
WHEN MATCHED THEN UPDATE SET
  t.o_orderstatus = s.o_orderstatus,
  t.o_totalprice  = s.o_totalprice
WHEN NOT MATCHED THEN INSERT
  (o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate)
  VALUES (s.o_orderkey, s.o_custkey, s.o_orderstatus, s.o_totalprice, s.o_orderdate);

Multi-table insert: INSERT ALL / INSERT FIRST. Snowflake can route one query’s rows into several tables in a single statement — an unconditional INSERT ALL … SELECT, or a conditional fan-out that sends each row wherever its WHEN clause says. It’s a neat way to split a stream into buckets in one pass:

-- Route each order into a small- or large-value table by threshold
INSERT ALL
  WHEN o_totalprice >= 200000 THEN INTO big_orders
  WHEN o_totalprice <  200000 THEN INTO small_orders
SELECT o_orderkey, o_totalprice
FROM my_orders;

INSERT FIRST stops at the first matching WHEN per row (like an if/else if), while INSERT ALL evaluates every branch (a row can land in more than one table). Both run as a single atomic statement.

A worked example, several features at once

Let’s close by stacking the chapter’s pieces into one query against TPCH. The task: for each nation, find its single highest-value order in 1997, label the order’s size, and format the revenue as an exact two-decimal number — casting, IFF, QUALIFY, and case-aware column handling together.

SELECT
  n.n_name                                    AS nation,
  o.o_orderkey,
  o.o_orderdate,
  o.o_totalprice::NUMBER(12, 2)               AS order_value,
  IFF(o.o_totalprice > 300000, 'flagship', 'standard') AS tier
FROM orders o
JOIN customer c ON c.c_custkey   = o.o_custkey
JOIN nation   n ON n.n_nationkey = c.c_nationkey
WHERE o.o_orderdate >= '1997-01-01'
  AND o.o_orderdate <  '1998-01-01'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY n.n_name ORDER BY o.o_totalprice DESC
) = 1
ORDER BY order_value DESC;

One statement returns exactly 25 rows — one champion order per nation — with an exact-decimal value and a computed tier label, no subquery and no self-join. Every element is something we covered: the ::NUMBER(12, 2) cast for exactness, IFF for the branch, QUALIFY for the per-partition top-1, and unquoted identifiers throughout so nothing has to be quoted. That’s the dialect earning its keep.

Examples in this chapter are docs-checked against the series’ stated versions, but they were not executed in this repository unless a companion project explicitly says so.

Final thoughts

The through-line of this chapter is that Snowflake’s differences are mostly defaults you can name. The identifier that “doesn’t exist” is a case-folding rule you can see coming. The timestamp that shifts is a type you chose implicitly when you could have chosen TIMESTAMP_NTZ explicitly. The money that lost its cents is a FLOAT the loader inferred where you could have written NUMBER(12, 2). None of it is mysterious once you know the type under the alias and the rule under the behavior.

So make the choices on purpose. Declare concrete types instead of leaning on inference and aliases. Pick the timestamp variant that matches what the value means — wall clock, instant, or instant-with-offset — rather than accepting whatever TIMESTAMP_TYPE_MAPPING happens to be set to. Create objects unquoted so no one has to quote them later. And when you reach for a dialect extension like QUALIFY or MERGE because it genuinely simplifies real work, that’s a good trade — just know you’ve written something a plain-SQL migration won’t carry over, and name the dependency so a future move discovers it in a comment, not in a failure. Portability is a choice; the goal is to make it a deliberate one.

Next: Account Administration and Metadata

Comments