Explain Streams: Change Data Capture on Tables in Databases

51 / 66

Snowflake

Streams: Change Data Capture on Tables

Track row-level changes (inserts, updates, deletes) on a table for incremental processing.

A stream is a Snowflake object that records the delta of changes (inserts, updates, deletes) made to a table since the stream was last consumed, enabling efficient change data capture (CDC) without scanning the entire source table. Streams don't store the changed data themselves; instead, they use the table's underlying versioning (the same mechanism behind Time Travel) to compute the difference between the current table state and the last-consumed offset.

A stream is like a bookmark in a constantly-updated document — querying it shows you everything that changed since your bookmark, and only moving the bookmark forward (by actually processing those changes) marks them as handled.

Key Concepts

Each row returned by querying a stream includes metadata columns — METADATA$ACTION (INSERT or DELETE), METADATA$ISUPDATE (TRUE if part of an update), and METADATA$ROW_ID — that let downstream logic distinguish between true inserts, deletes, and updates (which streams represent as a paired delete+insert).

METADATA$ACTIONMETADATA$ISUPDATEMETADATA$ROW_ID

A stream's offset only advances when it is consumed inside a DML transaction (e.g., an INSERT ... SELECT * FROM stream, or within a task). Simply querying a stream with SELECT does not advance its offset, allowing safe, repeated inspection without losing unconsumed changes. This offset-based design makes streams ideal building blocks for incremental ETL, feeding only new/changed rows into downstream tables.

DML transactionINSERT ... SELECT * FROM streamSELECT

Streams have a staleness limit tied to the source table's data retention period — if a stream isn't consumed before that window elapses, it becomes stale and must be recreated, since the historical versions needed to compute the delta are no longer available.

staleness