Streams for change data capture in Snowflake - Step-by-Step Execution

Process Flow - Streams for change data capture

Create Stream on Table

↓

Table Data Changes

↓

Stream Captures Changes

↓

Query Stream for Changes

↓

Process or Apply Changes

↓

Stream Advances to Next Offset

This flow shows how a Snowflake stream tracks changes on a table, captures them, and allows querying for incremental data updates.

Execution Sample

Snowflake

CREATE OR REPLACE TABLE customers (id INT, name STRING);
CREATE OR REPLACE STREAM customers_stream ON TABLE customers;
INSERT INTO customers VALUES (1, 'Alice');
UPDATE customers SET name = 'Alicia' WHERE id = 1;
SELECT * FROM customers_stream;

This code creates a table and a stream on it, inserts and updates data, then queries the stream to see captured changes.

Process Table

Step	Action	Table State	Stream Captured Change	Stream Query Result
1	Create table customers	Empty table	Stream not created yet	No rows
2	Create stream customers_stream	Empty table	Stream ready to capture changes	No rows
3	Insert (1, 'Alice')	Table has 1 row: (1, 'Alice')	Insert record captured	1 row: INSERT (1, 'Alice')
4	Update name to 'Alicia' where id=1	Table row updated: (1, 'Alicia')	Update record captured	1 row: UPDATE (1, 'Alicia')
5	Select from customers_stream	Table unchanged	Changes shown once	2 rows: Insert and Update
6	Select from customers_stream again	Table unchanged	No new changes	No rows
7	Insert (2, 'Bob')	Table has 2 rows	Insert record captured	1 row: INSERT (2, 'Bob')
8	Select from customers_stream	Table unchanged	New insert shown	1 row: INSERT (2, 'Bob')

💡 Stream shows changes only once; after querying, it advances and waits for new changes.

Status Tracker

Variable	Start	After Step 3	After Step 4	After Step 7	Final
Table customers	Empty	(1, 'Alice')	(1, 'Alicia')	(1, 'Alicia'), (2, 'Bob')	(1, 'Alicia'), (2, 'Bob')
Stream customers_stream	Empty	Insert (1, 'Alice')	Insert + Update (1, 'Alicia')	Insert (2, 'Bob')	Insert (2, 'Bob')

Key Moments - 3 Insights

Why does the stream return no rows when queried a second time without new changes?

Because streams in Snowflake show each change only once. After you query the stream, it advances its offset and does not return the same changes again until new changes happen. See execution_table rows 5 and 6.

Does the stream store the full table data?

No, the stream only stores the changes (inserts, updates, deletes) since the last time it was queried. The full table data remains in the table itself. See execution_table steps 3 and 4.

What happens if multiple changes happen before querying the stream?

The stream accumulates all changes since last query and returns them all at once. For example, after insert and update, the stream shows both changes in one query (step 5).

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What change does the stream capture?

A An update changing name to 'Alicia' for id 1

B An insert of (1, 'Alice')

C A delete of id 1

D No change captured

💡 Hint

Check the 'Stream Captured Change' column at step 4 in execution_table.

At which step does the stream return no rows because there are no new changes?

A Step 5

B Step 6

C Step 7

D Step 8

💡 Hint

Look at the 'Stream Query Result' column in execution_table for step 6.

If we insert a new row (3, 'Carol') after step 8, what will the stream show on next query?

A Two rows: Insert (2, 'Bob') and Insert (3, 'Carol')

B No rows, because stream only shows changes once

C One row: Insert (3, 'Carol')

D All rows in the table

💡 Hint

Refer to variable_tracker and execution_table steps 7 and 8 for how new inserts appear.

Concept Snapshot

Snowflake Streams track changes on a table.
They capture inserts, updates, deletes since last query.
Querying the stream returns these changes once.
After query, stream advances and waits for new changes.
Use streams for incremental data processing and CDC.

Full Transcript

This visual execution shows how Snowflake Streams work for change data capture. First, a table is created, then a stream is created on that table. When data is inserted or updated in the table, the stream captures those changes. Querying the stream returns the changes since the last query. After querying, the stream advances its offset and will not return the same changes again until new changes occur. This allows incremental processing of data changes efficiently. The execution table traces each step, showing the table state, what changes the stream captures, and what results the stream query returns. The variable tracker shows how the table and stream states evolve. Key moments clarify common confusions about streams showing changes only once and not storing full table data. The quiz tests understanding of stream behavior at specific steps. The snapshot summarizes the core concept for quick reference.