liminfoSnowflake SQL Reference
Free reference guide: Snowflake SQL Reference
About Snowflake SQL Reference
This Snowflake SQL Reference is a searchable cheat sheet for Snowflake-specific SQL syntax that goes beyond standard ANSI SQL. Organized into five categories — DDL, DML, Functions, Streaming, and Sharing — it focuses on the features that differentiate Snowflake from traditional data warehouses: external stages, COPY INTO for bulk loading, semi-structured data handling with VARIANT and FLATTEN, Time Travel, zero-copy cloning, Snowpipe continuous ingestion, and secure data sharing between accounts.
Snowflake's cloud-native architecture separates storage from compute, allowing independent scaling and near-zero maintenance. This architectural advantage surfaces in SQL features that have no equivalent in on-premises databases. Time Travel lets you query historical data at any point within the retention period using AT(TIMESTAMP => ...). CLONE creates instant, storage-efficient copies of databases, schemas, or tables without duplicating underlying data. QUALIFY filters window function results directly, eliminating the need for a wrapping subquery — a pattern that simplifies common deduplication and ranking queries.
The streaming and sharing categories cover Snowflake's real-time and collaboration features. CREATE STREAM captures change data capture (CDC) events on tables, while CREATE TASK schedules recurring SQL statements using cron syntax. CREATE PIPE with AUTO_INGEST enables Snowpipe, which automatically loads files as they arrive in cloud storage. Secure Data Sharing with CREATE SHARE allows you to grant read access to live data across Snowflake accounts without copying or moving data, enabling real-time data marketplace and partner collaboration scenarios.
Key Features
- DDL for external stages (CREATE STAGE with S3/GCS/Azure URLs), Time Travel queries with AT/BEFORE, UNDROP, and zero-copy CLONE
- COPY INTO for bulk data loading from stages with file format specifications (CSV, Parquet, JSON, Avro)
- MERGE statement for UPSERT operations with WHEN MATCHED / WHEN NOT MATCHED clauses
- Semi-structured data handling — VARIANT, OBJECT, ARRAY types with colon-path notation and type casting
- FLATTEN for exploding nested arrays and objects with LATERAL join syntax
- Snowpipe (CREATE PIPE with AUTO_INGEST), CREATE TASK for scheduled SQL, and CREATE STREAM for CDC
- QUALIFY clause for filtering window function results without subqueries — ideal for deduplication
- Secure Data Sharing (CREATE SHARE) and MATCH_RECOGNIZE for row pattern recognition in event sequences
Frequently Asked Questions
What makes Snowflake SQL different from standard SQL?
Snowflake extends ANSI SQL with cloud-native features. Key additions include VARIANT type for semi-structured data, FLATTEN for nested structures, QUALIFY for window function filtering, Time Travel for historical queries, zero-copy CLONE, Snowpipe for continuous ingestion, and CREATE SHARE for cross-account data sharing. Standard SELECT/INSERT/UPDATE/DELETE syntax remains the same.
How does Time Travel work in Snowflake?
Time Travel lets you access historical data using AT(TIMESTAMP => timestamp) or BEFORE(STATEMENT => query_id) clauses in SELECT statements. The retention period is configurable (up to 90 days on Enterprise edition). You can also use UNDROP to restore accidentally dropped tables, schemas, or databases within the retention window.
What is zero-copy cloning and when should I use it?
CREATE TABLE clone CLONE source creates an instant copy that shares the underlying storage with the original. No data is physically copied, so it completes in seconds regardless of table size and incurs no additional storage cost until modifications diverge the data. Use it for creating development/test environments, safe experimentation, or point-in-time snapshots.
How do I load semi-structured data like JSON into Snowflake?
Load JSON files into a VARIANT column using COPY INTO with FILE_FORMAT=(TYPE=JSON). Query nested fields using colon-path notation: data:customer:name::STRING. For arrays, use FLATTEN with LATERAL to explode nested elements into rows: SELECT f.value:id FROM table, LATERAL FLATTEN(input => data:items) f.
What is the difference between a Task and a Pipe?
A Task (CREATE TASK) schedules SQL statements to run at defined intervals using cron expressions — similar to a database-level cron job. A Pipe (CREATE PIPE with AUTO_INGEST) is specifically designed for continuous data loading: it monitors a cloud storage location and automatically runs COPY INTO when new files arrive.
How does QUALIFY simplify window function queries?
QUALIFY filters rows based on window function results directly, without requiring a wrapping subquery or CTE. For example, SELECT * FROM events QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ts DESC) = 1 returns the latest event per user in a single query — a pattern that would require a subquery in standard SQL.
What is Secure Data Sharing and does it copy my data?
Secure Data Sharing (CREATE SHARE) grants read-only access to live data in your account to other Snowflake accounts. No data is copied, moved, or transferred — the consumer queries your data in place using their own compute resources. This enables real-time data collaboration, data marketplace publishing, and partner access without ETL pipelines or file exports.
How do Streams enable change data capture (CDC)?
CREATE STREAM on a table tracks INSERT, UPDATE, and DELETE operations. Each row in the stream includes METADATA$ACTION (INSERT or DELETE) and METADATA$ISUPDATE (true for updates) columns. You consume stream data in DML statements or Tasks, and once consumed, the stream advances its offset. This enables incremental data processing without full table scans.