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');

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.