Snowflake's change data capture and pipeline automation stack rests on three primitives: Streams (CDC bookmarks that track DML changes against tables and views), Tasks (SQL schedulers that consume those changes in user-defined graphs), and Dynamic Tables (declarative, automatically-refreshed materializations). Together they cover the full spectrum from fine-grained procedural ETL to declarative, lag-driven pipelines — with cost, complexity, and operational trade-offs at each level.
What This Cheat Sheet Covers
This topic spans 17 focused tables and 203 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
1. Stream Types and Supported Source Objects2. Stream Metadata Columns3. Stream Offset and Consumption Mechanics4. Stream Staleness and Management5. Task Basics: CREATE, Compute Models, and Scheduling6. Task Triggered Mode and SYSTEM$STREAMHASDATA7. Task DAGs: Structure and Control8. Task Error Handling, Retries, and Monitoring9. Dynamic Tables: Core Concepts and CREATE Syntax10. Dynamic Table Refresh Modes11. Dynamic Table Monitoring and Cost12. Decision Guide: Streams + Tasks vs Dynamic Tables vs Materialized ViewsOfficial Snowflake DocumentationOfficial Snowflake DocumentationOfficial Snowflake DocumentationOfficial Snowflake DocumentationCommunity and Third-Party Resources
1. Stream Types and Supported Source Objects
| Concept | Example | Description |
|---|---|---|
CREATE STREAM s ON TABLE orders; | • Captures all DML (INSERT, UPDATE, DELETE) on standard tables, directory tables, and views • Default type • Updates appear as a DELETE + INSERT pair in the stream | |
CREATE STREAM s ON TABLE logs APPEND_ONLY = TRUE; | • Captures insert operations only • skips update/delete tracking overhead • Best for ELT pipelines consuming immutable log or event tables • Lower overhead than standard | |
CREATE STREAM s ON EXTERNAL TABLE ext_t INSERT_ONLY = TRUE; | • Exclusively for external tables, directory tables, and Iceberg tables • Only tracks row inserts • no update/delete semantics • Required when source is external/Iceberg | |
CREATE STREAM s ON VIEW v_orders; | Standard streams can be created on views (including joins), provided all underlying tables have change tracking enabled and view is not SECURE or contain certain constructs. |