Snowflake Query Cookbook

Cloud analytical warehouse separating computing virtual warehouses from storage. 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Filter Window Results with QUALIFY

Filter window function outputs directly without CTE subqueries.

SELECT employee_id, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

#qualify#window#snowflake

2. Zero-Copy Clone Production Database

Create instant zero-storage metadata database snapshot.

CREATE DATABASE prod_dev_sandbox CLONE production_db;

#clone#database#utility

3. Time Travel Query Data State

Query table data as it existed prior to an accidental drop or update.

SELECT * FROM orders AT(OFFSET => -60*5); -- 5 mins ago
-- Or by Query ID:
-- SELECT * FROM orders BEFORE(STATEMENT => '8e5d0b9a-0001-0000-0000-000000000000');

#time_travel#recovery#history

4. Parse JSON VARIANT Fields

Extract values directly from JSON variant columns.

SELECT raw_payload:user.id::INT AS user_id,
       raw_payload:user.email::STRING AS email
FROM events_stage
WHERE raw_payload:event_type = 'signup';

#variant#json#semi-structured

5. Load Stage Parquet Files with COPY INTO

Bulk load external S3 cloud files into table.

COPY INTO target_table
FROM @my_s3_stage/data/
FILE_FORMAT = (TYPE = 'PARQUET')
ON_ERROR = 'CONTINUE';

#copy_into#stage#ingestion

6. Pivot Rows to Columns

Transform query row values into dynamic reporting columns.

SELECT * FROM sales_summary
PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
ORDER BY year;

#pivot#analytics#transform

7. Resize & Suspend Virtual Warehouse

Dynamically alter warehouse compute capacity.

ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 300;

#warehouse#dba#compute

8. Undrop Accidental Deleted Table

Restore dropped table instantly with zero data loss.

UNDROP TABLE orders;

#undrop#recovery#safety

9. Unpack JSON Arrays with FLATTEN

Deconstruct JSON arrays into individual output rows.

SELECT id, f.value::STRING AS tag
FROM articles,
LATERAL FLATTEN(input => metadata:tags) f;

#flatten#array#variant

10. Query Cached Result Set

Reuse query result cache without warehouse compute costs.

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

#cache#result_scan#performance

Test your Snowflake architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study Snowflake Flashcards →