Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 38 ยท Streams, Tasks and Pipelines

Streams and Change Data Capture

Learn Snowflake streams: how they track inserts, updates and deletes with METADATA columns, standard versus append-only streams, offsets and consuming changes.

A pipeline that reloads a whole table every hour wastes compute. Better to process only what changed since the last run. That is change data capture (CDC), and in Snowflake it is the job of a stream: a small object that records the position (offset) up to which you have already seen changes on a table, and shows you the rows that changed since.

Streams exist only inside Snowflake. The syntax is shown read-only. To make the behavior concrete, the runnable examples rebuild the same idea in SQLite using triggers and a change table.

Creating a stream

SQL
CREATE TABLE orders (id NUMBER, customer STRING, amount NUMBER, status STRING);

CREATE STREAM orders_stream ON TABLE orders;

INSERT INTO orders VALUES (1, 'ana', 120, 'new'), (2, 'ben', 80, 'new');

SELECT * FROM orders_stream;

A stream stores no data of its own. It is a view over the table's change history, plus an offset. Querying it returns the table's columns plus three metadata columns:

Column Meaning
METADATA$ACTION INSERT or DELETE
METADATA$ISUPDATE TRUE if this row is half of an update
METADATA$ROW_ID A stable ID for the row, to match old and new versions

An update is represented as a pair: a DELETE row with the old values and an INSERT row with the new values, both flagged ISUPDATE = TRUE.

Consuming a stream

Reading with SELECT does not move the offset. The offset advances only when a stream is used as the source in a DML statement that commits (INSERT, MERGE, UPDATE, DELETE, COPY INTO location). After that, the consumed rows disappear from the stream.

SQL
MERGE INTO orders_clean t
USING orders_stream s ON t.id = s.id
WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' AND s.METADATA$ISUPDATE = FALSE
  THEN DELETE
WHEN MATCHED AND s.METADATA$ACTION = 'INSERT'
  THEN UPDATE SET customer = s.customer, amount = s.amount, status = s.status
WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT'
  THEN INSERT (id, customer, amount, status) VALUES (s.id, s.customer, s.amount, s.status);

Because the offset moves transactionally, if the statement fails nothing is lost: the stream still holds the changes. Within one transaction several statements see the same stream contents, so you can fan changes out to many targets.

Stream types

  • Standard (the default): tracks inserts, updates and deletes.
  • Append-only (APPEND_ONLY = TRUE): tracks inserts only; cheaper for log and event tables where rows are never changed.
  • Insert-only (INSERT_ONLY = TRUE): for external tables and Iceberg tables.

Streams can be created on tables, views and directory tables. A stream has a staleness limit: if you do not consume it within the table's data retention (extended up to 14 days for streams), the offset falls outside the history and the stream becomes stale, so unconsumed changes cannot be recovered. Check with SHOW STREAMS (column STALE) and consume regularly.

SYSTEM$STREAM_HAS_DATA('orders_stream') returns TRUE when there is something to process, and is the standard way to let a task skip empty runs.

Rebuilding the idea with triggers

SQL A change log like a standard stream (stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You should see three rows: an INSERT for the new customer, then a DELETE and an INSERT (both is_update = 1) describing the city change. Exactly the shape of a Snowflake stream.

Consuming and moving the offset

SQL Consume changes into a target (stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

After processing, city_dim holds just customer 2 in Cusco, and the change table shows nothing left to consume. In Snowflake you would never mark rows yourself: the committed DML moves the offset.

Design tips

  • Use one stream per consumer: each stream has its own offset.
  • Prefer append-only streams for event data.
  • Consume within the retention window; monitor staleness.
  • Use MERGE for dimension tables so retries are idempotent.
  • For simple transformations consider dynamic tables, which manage the incremental logic for you.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does a stream store?

    A stream holds no row data; it records a position and exposes changes since that position.
  2. 2. How is an UPDATE represented in a standard stream?

    The old and new versions appear as a delete and insert pair flagged as an update.
  3. 3. What moves a stream's offset?

    Only DML that consumes the stream and commits advances the offset; plain queries do not.
  4. 4. Which stream type suits an append-only event log?

    An append-only stream tracks inserts only, which is cheaper when rows never change.
  5. 5. A stream becomes stale when...

    If changes are not consumed within the retention window, they can no longer be reconstructed.

Summary

  • Streams provide CDC: they expose rows changed since an offset.
  • METADATA$ACTION, ISUPDATE and ROW_ID describe each change.
  • Committed DML on the stream consumes it; SYSTEM$STREAM_HAS_DATA tests for work.
  • Standard, append-only and insert-only streams; consume before they go stale.

Next: Tasks and task graphs