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, 83 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
A jump-to index of every table row in this cheat sheet.
An interactive map of every table and concept in this topic.
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. | ||
CREATE STREAM s ON EXTERNAL TABLE ext INSERT_ONLY = TRUE; | • Only insert-only type is supported • Tracks new metadata files added to the external stage since last consumption | ||
CREATE STREAM s ON STAGE my_stage; | • Append-only stream on a directory table • Detects new or removed files in a stage • used for file-driven pipelines | ||
ALTER TABLE orders SET CHANGE_TRACKING = TRUE; | • Required on source tables before standard or append-only streams can be created • For views, must be enabled on every base table • External tables do not require it | ||
CREATE STREAM s ON TABLE t SHOW_INITIAL_ROWS = TRUE; | • On creation, the stream's first consumption includes existing table rows as INSERT records • After that initial read, offset advances normally • Useful for full-load + CDC patterns |