learn.fttgsolutions.com · Cheat Sheets
Load. Query. Scale. Share.
Virtual Warehouses
| CREATE WAREHOUSE | CREATE WAREHOUSE my_wh
WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE; | Create a virtual warehouse (compute cluster). AUTO_SUSPEND pauses it after inactivity; AUTO_RESUME starts it automatically on query. |
| ALTER WAREHOUSE | ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE = 'LARGE'; | Resize a warehouse on the fly with no downtime. Billing switches to the new size immediately. |
| SUSPEND / RESUME | ALTER WAREHOUSE my_wh SUSPEND;
ALTER WAREHOUSE my_wh RESUME; | Manually pause or start a warehouse to control costs. |
| USE WAREHOUSE | USE WAREHOUSE my_wh; | Set the active warehouse for the current session. |
| SHOW WAREHOUSES | SHOW WAREHOUSES; | List all warehouses and their current state, size, and credit usage. |
Databases & Schemas
| CREATE DATABASE | CREATE DATABASE my_db; | Create a top-level namespace that contains schemas and all their objects. |
| CREATE SCHEMA | CREATE SCHEMA my_db.my_schema; | Create a logical grouping inside a database for tables, views, and other objects. |
| USE DATABASE / SCHEMA | USE DATABASE my_db;
USE SCHEMA my_schema; | Set the active database and schema for the session so you can omit them from object names. |
| SHOW DATABASES | SHOW DATABASES;
SHOW SCHEMAS;
SHOW TABLES; | List all objects at each level. Add LIKE 'pattern%' to filter results. |
Querying
| Basic SELECT | SELECT col1, col2
FROM my_schema.my_table
WHERE col1 = 'value'
ORDER BY col2 DESC
LIMIT 100; | Standard SQL SELECT with filtering, ordering, and row limiting. |
| QUALIFY | SELECT *
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sale_date DESC) = 1; | Snowflake extension to filter window function results inline — no subquery needed. |
| SAMPLE | SELECT * FROM orders SAMPLE (10); | Return an approximate random sample — 10% of rows. Use SAMPLE (100 ROWS) for a fixed row count. |
| PIVOT | SELECT *
FROM monthly_sales
PIVOT (SUM(amount) FOR month IN ('Jan','Feb','Mar')); | Rotate rows into columns — turn category values into column headers with aggregated values. |
| UNPIVOT | SELECT *
FROM quarterly_totals
UNPIVOT (revenue FOR quarter IN (q1, q2, q3, q4)); | Rotate columns back into rows — the reverse of PIVOT. |
| Result Cache | -- Automatic — same query within 24h returns cached results instantly
ALTER SESSION SET USE_CACHED_RESULT = FALSE; -- disable | Snowflake caches query results for 24 hours. Identical queries cost zero compute credits. |
Data Loading
| CREATE STAGE | -- Internal stage
CREATE STAGE my_stage;
-- External (S3)
CREATE STAGE s3_stage
URL = 's3://my-bucket/data/'
CREDENTIALS = (AWS_KEY_ID='...' AWS_SECRET_KEY='...'); | A stage is a landing area for files before they are loaded into tables. Internal stages live inside Snowflake; external stages point to cloud storage. |
| PUT (upload file) | PUT file:///local/path/data.csv @my_stage; | Upload a local file to an internal Snowflake stage using SnowSQL CLI. |
| COPY INTO (load) | COPY INTO my_table
FROM @my_stage/data.csv
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'); | Bulk-load staged files into a table. Snowflake tracks loaded files and skips duplicates by default. |
| COPY INTO (unload) | COPY INTO @my_stage/export/
FROM my_table
FILE_FORMAT = (TYPE = 'PARQUET'); | Export query results or table data out to a stage as files. |
| Snowpipe | CREATE PIPE my_pipe AS
COPY INTO my_table FROM @my_stage
FILE_FORMAT = (TYPE = 'JSON'); | Continuous micro-batch loading. Snowpipe ingests new files automatically as they land in a stage. |
Semi-Structured Data
| VARIANT type | CREATE TABLE events (
id INT,
payload VARIANT
); | VARIANT stores any JSON, Avro, Parquet, or XML data natively. Snowflake auto-optimises the internal encoding. |
| Parse JSON | SELECT PARSE_JSON('{"name":"Alex"}'):name::STRING; | PARSE_JSON converts a string to VARIANT. Use colon notation to extract fields, then cast with ::TYPE. |
| Dot / colon notation | SELECT payload:user.email::STRING AS email
FROM events; | Navigate nested JSON using colon (:) for top-level keys and dot (.) for nested keys. |
| FLATTEN | SELECT f.value::STRING AS tag
FROM events,
LATERAL FLATTEN(INPUT => payload:tags) f; | Explode a JSON array into individual rows — one row per array element. |
| OBJECT_CONSTRUCT | SELECT OBJECT_CONSTRUCT('name', first_name, 'age', age)
FROM users; | Build a JSON object from key-value pairs on the fly. |
| ARRAY_AGG | SELECT user_id, ARRAY_AGG(order_id) AS orders
FROM orders GROUP BY user_id; | Aggregate multiple rows into a JSON array — the inverse of FLATTEN. |
Time Travel
| Query past data | -- By timestamp
SELECT * FROM orders
AT (TIMESTAMP => '2024-01-01 12:00:00'::TIMESTAMP);
-- By offset (seconds ago)
SELECT * FROM orders
BEFORE (OFFSET => -3600); | Query historical table state up to 90 days back (Enterprise tier). Default retention is 1 day. |
| UNDROP | UNDROP TABLE orders;
UNDROP SCHEMA my_schema;
UNDROP DATABASE my_db; | Restore a dropped table, schema, or database within the Time Travel retention window. |
| Set retention period | ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 30; | Override the default retention period per object. Max 90 days on Enterprise tier. |
Cloning
| CLONE table | CREATE TABLE orders_backup
CLONE orders; | Zero-copy clone — creates an independent copy that shares storage with the original until data diverges. Instant and free. |
| CLONE database | CREATE DATABASE prod_clone
CLONE production; | Clone an entire database including all schemas, tables, and views. Great for staging environments. |
| Clone at point in time | CREATE TABLE orders_jan
CLONE orders
BEFORE (TIMESTAMP => '2024-02-01'::TIMESTAMP); | Clone a historical snapshot by combining cloning with Time Travel. |
Streams & Tasks
| CREATE STREAM | CREATE STREAM orders_stream ON TABLE orders; | A stream tracks INSERT, UPDATE, and DELETE changes to a table (CDC). Query it like a table — consumed rows are cleared after a DML transaction. |
| Query a stream | SELECT *,
METADATA$ACTION,
METADATA$ISUPDATE
FROM orders_stream
WHERE METADATA$ACTION = 'INSERT'; | Streams expose METADATA$ACTION (INSERT/DELETE) and METADATA$ISUPDATE to distinguish updates from deletes. |
| CREATE TASK | CREATE TASK sync_task
WAREHOUSE = my_wh
SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
INSERT INTO summary
SELECT * FROM orders_stream WHERE METADATA$ACTION = 'INSERT'; | Tasks run SQL on a schedule or when triggered. Chain tasks together with AFTER to build pipelines. |
| EXECUTE TASK | EXECUTE TASK sync_task; | Manually trigger a task immediately, outside its schedule. |
Access Control
| CREATE ROLE | CREATE ROLE analyst;
GRANT ROLE analyst TO USER alice; | Snowflake uses role-based access control (RBAC). Users are granted roles; roles are granted privileges. |
| GRANT privilege | GRANT USAGE ON DATABASE my_db TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO ROLE analyst; | Grant specific privileges on objects to a role. Always grant USAGE on parent objects before object-level access. |
| REVOKE | REVOKE SELECT ON TABLE orders FROM ROLE analyst; | Remove a previously granted privilege from a role. |
| USE ROLE | USE ROLE analyst; | Switch the active role for the current session. |
| Row-level security | CREATE ROW ACCESS POLICY region_policy AS
(region STRING) RETURNS BOOLEAN ->
region = CURRENT_ROLE();
ALTER TABLE sales ADD ROW ACCESS POLICY region_policy ON (region); | Filter rows at query time based on the current user's role or attributes — enforced automatically. |
Performance & Optimisation
| Clustering keys | ALTER TABLE orders CLUSTER BY (order_date, region); | Define a clustering key to co-locate similar data in micro-partitions. Improves pruning for range and equality filters on large tables. |
| EXPLAIN | EXPLAIN SELECT * FROM orders WHERE region = 'EU'; | Show the logical query plan without executing it. Use Query Profile in the UI for full execution details. |
| Query Profile | -- Run query, then: History → select query → Query Profile | Visual breakdown of query execution — shows partition pruning, spill to disk, and bottleneck operators. |
| Materialized view | CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date, SUM(amount) AS total
FROM orders GROUP BY date; | Pre-compute and cache expensive aggregations. Snowflake auto-refreshes the view as base data changes. |
| Search optimisation | ALTER TABLE events ADD SEARCH OPTIMIZATION; | Accelerates point lookup queries on large tables — useful for selective filters on non-clustered columns. |