Snowflake SQL Patterns
Worked SQL recipes and the footguns official reference pages leave out.
This is a small, independent cookbook of SQL patterns that come up constantly in warehouse work: latest-per-key filters, nested JSON, MERGE upserts, clustering, Time Travel, timestamps, and file loads.
It is not a second copy of the Snowflake function reference. Official docs are linked from every page and remain the authority. What each page adds is a concrete problem, a working example, the gotcha that usually bites, and when not to use the pattern.
Where a query could be run in DuckDB (which implements QUALIFY, UNNEST, and MERGE), it was executed in DuckDB 2.0.0 while this site was built. Snowflake-only syntax is labeled as verified against official documentation and not executed on Snowflake.
Latest-per-key with QUALIFY
Keep the current row per key with QUALIFY and ROW_NUMBER — and why DISTINCT is the wrong tool.
WHERE, HAVING, and QUALIFY — filter order
Why a filter in the wrong clause silently drops the wrong rows, using official clause order.
Flatten nested JSON without exploding the grain
Two LATERAL FLATTEN calls multiply rows. Totals quietly go wrong, and empty arrays drop parents.
VARIANT path access and casts
Colon paths return VARIANT, not VARCHAR. Cast explicitly or comparisons and output will surprise you.
MERGE upserts — and when MERGE is the wrong tool
Upsert and flagged deletes with MERGE, why duplicate source keys error, and the fact that Snowflake has no WHEN NOT MATCHED BY SOURCE.
Clustering keys — when they help, when they cost
Official guidance on when to add a clustering key, what it is for, and why most tables should not have one.
Time Travel and zero-copy clones
Query or clone a past state, restore a dropped table, and avoid treating AT(TIMESTAMP) as a session-local wall clock.
TIMESTAMP_NTZ, LTZ, and TZ
The three Snowflake timestamp types, what TIMESTAMP alone means, and the DST offset trap on TIMESTAMP_TZ.
COPY INTO — errors, reloads, and silent skips
Default ON_ERROR aborts the load, FORCE can duplicate rows, and already-loaded files are skipped unless you ask otherwise.
SAMPLE vs LIMIT
LIMIT is the cheapest handful of rows. SAMPLE is a method-based subset. They answer different questions.