IntermediateLast updated: 2026-04-09 • 5 sections
Essential Snowflake SQL commands, functions, and syntax for data engineers. Covers DDL, DML, querying, and Snowflake-specific features.
| Command | Syntax | Example |
|---|---|---|
| Create Database | CREATE DATABASE [IF NOT EXISTS] db_name; | CREATE DATABASE analytics_db; |
| Create Schema | CREATE SCHEMA [IF NOT EXISTS] schema_name; | CREATE SCHEMA raw_data; |
| Use Database | USE DATABASE db_name; | USE DATABASE analytics_db; |
| Use Schema | USE SCHEMA schema_name; | USE SCHEMA raw_data; |
| Show Databases | SHOW DATABASES; | SHOW DATABASES LIKE '%analytics%'; |
| Drop Database | DROP DATABASE [IF EXISTS] db_name; | DROP DATABASE old_db; |
| Command | Syntax | Example |
|---|---|---|
| Create Table | CREATE TABLE t (col TYPE, ...); | CREATE TABLE users (id INT, name VARCHAR, created_at TIMESTAMP_NTZ); |
| Create Table As | CREATE TABLE t AS SELECT ...; | CREATE TABLE summary AS SELECT dept, COUNT(*) cnt FROM employees GROUP BY dept; |
| Clone Table | CREATE TABLE t CLONE source; | CREATE TABLE users_backup CLONE users; |
| Transient Table | CREATE TRANSIENT TABLE t (...); | CREATE TRANSIENT TABLE temp_staging (id INT, data VARIANT); |
| Alter Table | ALTER TABLE t ADD COLUMN col TYPE; | ALTER TABLE users ADD COLUMN email VARCHAR; |
| Drop Table | DROP TABLE [IF EXISTS] t; | DROP TABLE old_staging; |
| Feature | Syntax | Use Case |
|---|---|---|
| Time Travel | SELECT * FROM t AT(OFFSET => -60*5); | Query data as it was 5 minutes ago |
| Time Travel (Timestamp) | SELECT * FROM t AT(TIMESTAMP => '2026-01-01'::TIMESTAMP); | Query data at a specific point in time |
| Undrop | UNDROP TABLE t; | Recover a dropped table within retention period |
| FLATTEN | SELECT f.value FROM t, LATERAL FLATTEN(input => t.json_col) f; | Expand semi-structured arrays into rows |
| PARSE_JSON | SELECT PARSE_JSON('{"key":"val"}'); | Parse a JSON string into VARIANT |
| OBJECT_CONSTRUCT | SELECT OBJECT_CONSTRUCT('k1',v1,'k2',v2); | Build a JSON object from key-value pairs |
| QUALIFY | SELECT * FROM t QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY ts DESC) = 1; | Filter window function results without subquery |
| RESULT_SCAN | SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())); | Query results of the last executed query |
| Command | Syntax | Notes |
|---|---|---|
| Create Warehouse | CREATE WAREHOUSE wh WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; | Always set AUTO_SUSPEND to save credits |
| Resize | ALTER WAREHOUSE wh SET WAREHOUSE_SIZE = 'MEDIUM'; | Takes effect on next query |
| Suspend | ALTER WAREHOUSE wh SUSPEND; | Immediately stops credit consumption |
| Resume | ALTER WAREHOUSE wh RESUME; | Manual resume if AUTO_RESUME is off |
| Multi-cluster | ALTER WAREHOUSE wh SET MIN_CLUSTER_COUNT=1 MAX_CLUSTER_COUNT=3; | Enterprise edition feature for concurrency |
| Query Tag | ALTER SESSION SET QUERY_TAG = 'etl_pipeline_v2'; | Tag queries for cost attribution |
QUALIFY is a Snowflake-specific clause that filters the results of window functions directly, eliminating the need for subqueries. For example, QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY ts DESC) = 1 keeps only the latest row per id.
Time Travel lets you query historical data using AT or BEFORE clauses. You can go back up to 90 days (Enterprise edition) or 1 day (Standard). Syntax: SELECT * FROM table AT(OFFSET => -300) queries data from 5 minutes ago.
VARIANT is a semi-structured data type that can store JSON, Avro, ORC, Parquet, or XML data natively. VARCHAR stores plain text strings. Use VARIANT when working with nested/hierarchical data and VARCHAR for flat string values.