Streams for change data capture in Snowflake - Commands & Configuration

Introduction

When you want to track changes like inserts, updates, and deletes in a table automatically, streams help by capturing these changes so you can use them later without changing your original table.

When you want to keep a log of all changes made to a sales data table for auditing.

When you need to update a reporting table only with new or changed records from a source table.

When you want to build a data pipeline that reacts to changes in your main data without scanning the entire table.

When you want to synchronize changes from one table to another in near real-time.

When you want to implement incremental data processing to save time and resources.

Config File - create_stream.sql

create_stream.sql

CREATE OR REPLACE TABLE sales_data (
  id INT,
  product STRING,
  quantity INT,
  price FLOAT,
  updated_at TIMESTAMP
);

CREATE OR REPLACE STREAM sales_data_stream ON TABLE sales_data;

This SQL file first creates a table named sales_data with columns for id, product, quantity, price, and update time.

Then it creates a stream called sales_data_stream on the sales_data table. This stream will track all changes made to the table after the stream is created.

Commands

This command inserts a new record into the sales_data table to create a change that the stream will capture.

Terminal

snowsql -q "INSERT INTO sales_data (id, product, quantity, price, updated_at) VALUES (1, 'Widget', 10, 9.99, CURRENT_TIMESTAMP)"

Expected Output

1 row inserted.

This command queries the stream to see the changes captured since the stream was created or last consumed.

Terminal

snowsql -q "SELECT * FROM sales_data_stream"

Expected Output

ID | PRODUCT | QUANTITY | PRICE | UPDATED_AT | METADATA$ACTION

1  | Widget  | 10       | 9.99  | 2024-06-01 12:00:00 | INSERT

This command deletes the record from the sales_data table, creating another change for the stream to capture.

Terminal

snowsql -q "DELETE FROM sales_data WHERE id = 1"

Expected Output

1 row deleted.

Query the stream again to see the new delete action captured by the stream.

Terminal

snowsql -q "SELECT * FROM sales_data_stream"

Expected Output

ID | PRODUCT | QUANTITY | PRICE | UPDATED_AT | METADATA$ACTION

1  | Widget  | 10       | 9.99  | 2024-06-01 12:00:00 | DELETE

Key Concept

If you remember nothing else from this pattern, remember: a stream in Snowflake automatically tracks all changes to a table so you can process only what changed.

Common Mistakes

Querying the stream without making any changes to the base table first.

The stream will be empty because it only shows changes made after its creation.

Make sure to insert, update, or delete rows in the base table before querying the stream.

Not consuming or querying the stream regularly.

Streams keep track of changes until consumed; if ignored, they can grow large and cause confusion.

Query the stream regularly to process and clear the captured changes.

Creating a stream on a table that does not exist or has no data.

The stream will not capture any meaningful changes if the table is missing or empty.

Create the base table first and ensure it has data or will receive data changes.

Summary

Create a stream on a table to automatically capture inserts, updates, and deletes.

Make changes to the base table to generate change data the stream can capture.

Query the stream to see and process only the changed rows since the last query.

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