What are Snowflake Streams and how do they implement Change Data Capture (CDC)?

A Snowflake Stream is a named object that records row-level DML changes (INSERT, UPDATE, DELETE) made to a source table since the stream was last consumed. It works like a bookmark: each time you read the stream inside a DML transaction, the bookmark advances, and those changes are removed from the stream. Streams implement Change Data Capture without any external tooling or log-tailing.

Every row returned by a stream includes three metadata columns:

• METADATA$ACTION: either 'INSERT' or 'DELETE'. An UPDATE to a row appears as a DELETE of the old version plus an INSERT of the new version — a before/after pair.
• METADATA$ISUPDATE: TRUE if this row is the INSERT half of an UPDATE pair (not a net-new insert).
• METADATA$ROW_ID: a unique, stable identifier for the underlying row, allowing matching of DELETE/INSERT UPDATE pairs.

Stream types: Standard captures all DML (INSERT + UPDATE + DELETE). Append-only captures only INSERTs, ignoring updates and deletes — more efficient for append-heavy event tables. Insert-only is for External Tables.

-- Create a standard stream on the orders table
CREATE STREAM orders_stream ON TABLE orders;

-- Inspect changes since last consumption
SELECT * FROM orders_stream;

-- Consume the stream: copy inserts into a staging table
INSERT INTO orders_staging
SELECT order_id, customer_id, amount, order_ts
FROM orders_stream
WHERE METADATA$ACTION = 'INSERT'
  AND METADATA$ISUPDATE = FALSE;

-- After the INSERT above commits, the stream advances its offset
SELECT SYSTEM$STREAM_HAS_DATA('orders_stream');