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.

Practice

1. What is the main purpose of a stream in Snowflake for change data capture?

easy

A. To automatically delete old data from a table

B. To create a full backup of the table data every hour

C. To track only the changes (inserts, updates, deletes) on a table without copying all data

D. To replicate data to another database in real-time

Solution

1. Step 1: Understand the role of streams

   Streams in Snowflake track changes like inserts, updates, and deletes on a table without copying the entire data.

2. Step 2: Compare options with stream purpose

   Options A, B, and D describe deletion, backup, and replication, which are not the main function of streams.

3. Final Answer:

   To track only the changes (inserts, updates, deletes) on a table without copying all data -> Option C

4. Quick Check:

   Streams track changes = C [OK]

Hint: Streams track only changes, not full data copies [OK]

Common Mistakes:

• Thinking streams copy full table data
• Confusing streams with backups
• Assuming streams delete data automatically

2. Which of the following is the correct syntax to create a stream on a table named orders in Snowflake?

easy

A. CREATE STREAM orders_stream ON TABLE orders;

B. CREATE STREAM orders_stream FOR TABLE orders;

C. CREATE STREAM orders_stream ON orders;

D. CREATE STREAM orders_stream FROM orders;

Solution

1. Step 1: Recall Snowflake stream creation syntax

   The correct syntax uses CREATE STREAM <name> ON TABLE <table_name>.

2. Step 2: Match options with syntax

   CREATE STREAM orders_stream ON TABLE orders; matches the correct syntax exactly. Options B, C, and D use incorrect keywords or omit TABLE.

3. Final Answer:

   CREATE STREAM orders_stream ON TABLE orders; -> Option A

4. Quick Check:

   CREATE STREAM ... ON TABLE ... = A [OK]

Hint: Use ON TABLE keyword when creating streams [OK]

Common Mistakes:

• Omitting ON TABLE keywords
• Using FOR TABLE instead of ON TABLE
• Using FROM instead of ON TABLE

3. Given a stream orders_stream on table orders, what will the following query return?

SELECT * FROM orders_stream;

Assume some rows were inserted and updated since the last query.

medium

A. Only the rows that were inserted or updated since the last query

B. An error because streams cannot be queried directly

C. Only the rows that were deleted from the orders table

D. All rows from the orders table

Solution

1. Step 1: Understand what querying a stream returns

   Querying a stream returns the changed rows (inserts, updates, deletes) since the last time it was queried or consumed.

2. Step 2: Analyze the query behavior

   The query selects all changes from orders_stream, so it returns only inserted or updated rows since last read, not all rows or errors.

3. Final Answer:

   Only the rows that were inserted or updated since the last query -> Option A

4. Quick Check:

   Streams return changed rows = D [OK]

Hint: Streams return only changed rows since last read [OK]

Common Mistakes:

• Thinking streams return full table data
• Assuming streams return deleted rows only
• Believing streams cannot be queried

4. You created a stream on a table but notice it does not capture existing rows as inserts initially. What is the likely cause?

medium

A. The stream was created with the wrong table name

B. Streams only capture changes made to the table after the stream was created

C. Streams do not capture inserts, only updates and deletes

D. The table is empty, so no rows to capture

Solution

1. Step 1: Recall stream behavior for existing rows

   Streams in Snowflake track only DML changes made after the stream is created. Existing rows are not captured as inserts.

2. Step 2: Evaluate other options

   The stream was created with the wrong table name would cause errors, not missing initial rows. Streams do not capture inserts, only updates and deletes is false; streams capture inserts too. The table is empty, so no rows to capture is possible but less likely if table has data.

3. Final Answer:

   Streams only capture changes made to the table after the stream was created -> Option B

4. Quick Check:

   Streams capture post-creation changes only [OK]

Hint: Streams track post-creation changes only [OK]

Common Mistakes:

• Not realizing streams only track post-creation changes
• Assuming streams capture only updates and deletes
• Confusing stream creation errors with missing data

5. You want to process only new and changed rows from a large sales table daily. Which approach using streams is best to minimize resource use and ensure no data is missed?

hard

A. Create a stream and query the full sales table daily

B. Create multiple streams on sales for inserts, updates, and deletes separately

C. Create a stream and never consume it, just query the sales table directly

D. Create a stream on sales and consume the stream daily

Solution

1. Step 1: Understand efficient change capture

   Create a stream to track changes since creation, then consume it daily to process only new changes efficiently.

2. Step 2: Evaluate resource efficiency and correctness

   Consuming the stream daily processes only changes, saving resources. A queries full table daily, wasting resources. C never consumes but queries full table, missing stream benefits. D is unnecessary complexity.

3. Final Answer:

   Create a stream on sales and consume the stream daily -> Option D

4. Quick Check:

   Stream + daily consume = efficient change capture [OK]

Hint: Create stream and consume daily for efficiency [OK]

Common Mistakes:

• Querying full table instead of consuming stream
• Not consuming the stream regularly
• Creating multiple unnecessary streams