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 tells you what changed in a table since you last looked. It is how incremental pipelines avoid reprocessing everything.

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

    CREATE STREAM orders_stream ON TABLE orders;

It stores no data. A stream is a bookmark — an offset into the table's version history. When you query it, Snowflake computes the difference between the table now and the table at that offset, using the same retained micro-partitions that power Time Travel.

So creating a stream costs nothing and takes no storage.

Three metadata columns come back with each row.

    METADATA$ACTION      INSERT or DELETE
    METADATA$ISUPDATE    TRUE if this is half of an update
    METADATA$ROW_ID      a stable id for the row

An update appears as a DELETE and an INSERT pair, both with ISUPDATE = TRUE. That is how you tell a real delete from the removal half of an update — a distinction that quietly breaks merge logic when missed.

ISUPDATE = TRUE

Reading does not consume it. This is the property that makes streams usable:

    SELECT * FROM orders_stream;       -- inspect freely, offset unchanged
    INSERT INTO target
      SELECT * FROM orders_stream;     -- offset advances

The offset only moves when the stream is used inside a DML statement, and only if that statement commits. So you can look at what is pending as often as you like, and a failed job leaves the changes waiting rather than losing them.

The usual pattern is a stream plus a task plus a merge.

    MERGE INTO target t USING orders_stream s ON t.id = s.id
      WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' THEN DELETE
      WHEN MATCHED THEN UPDATE SET ...
      WHEN NOT MATCHED THEN INSERT ...

Staleness is the trap. A stream can only compute its delta while the retained versions it needs still exist. That window is the source table's data retention period.

    retention 1 day, stream not consumed for 2 days
    -> stream is stale, and its pending changes are gone

A stale stream cannot be repaired. It must be recreated, and whatever it had not yet delivered must be recovered another way — usually a full reload.

So a pipeline that pauses over a long weekend can lose data silently. Check SYSTEM$STREAM_HAS_DATA and the stream's STALE_AFTER in monitoring, and give tables feeding critical streams a retention period comfortably longer than your worst outage.

SYSTEM$STREAM_HAS_DATASTALE_AFTER

Several streams on one table are independent. Each has its own offset, so two consumers can read the same changes without interfering — which is how one source feeds several downstream pipelines.

What the interviewer is probing.1. "What does a stream actually store?" Probing: the mechanism. Stalls: "The changed rows." Moves up: an offset — the changes are derived from the table's versions, so the stream itself is metadata, not a copy.

2. "When does the offset advance?" Probing: the critical detail. Stalls: "When you read it." Moves up: when the stream is consumed inside a DML statement that commits — a plain SELECT does not advance it.

3. "What happens if a stream is not consumed?" Probing: the hazard. Stalls: "Nothing." Moves up: it becomes stale once the offset falls outside the retention window, and then it must be recreated with the changes lost.

4. "What are the three stream types?" Probing: the breadth. Stalls: "One type." Moves up: standard showing net changes, append-only which is cheaper for inserts, and insert-only for external tables.