Streams for change data capture in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a stream on the table 'orders' to capture changes.

Snowflake

CREATE OR REPLACE STREAM orders_stream ON TABLE orders [1] = FALSE;

Drag options to blanks, or click blank then click option'

AAPPEND_ONLY

BCHANGE_TRACKING

CALL

DINSERT_ONLY

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT_ONLY captures only inserts, missing updates and deletes.

CHANGE_TRACKING is not a valid stream type in Snowflake.

The APPEND_ONLY = FALSE option captures all changes (inserts, updates, deletes) on the table.

2fill in blank

medium

Complete the code to query the stream 'orders_stream' for inserted rows only.

Snowflake

SELECT * FROM orders_stream WHERE [1] = 'INSERT';

Drag options to blanks, or click blank then click option'

ACHANGE_TYPE

BMETADATA$ACTION

CACTION_TYPE

DROW_CHANGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-existent column like CHANGE_TYPE.

Confusing the metadata column name.

The METADATA$ACTION column indicates the type of change: 'INSERT', 'UPDATE', or 'DELETE'.

3fill in blank

hard

Fix the error in the stream creation by choosing the correct option for the stream type.

Snowflake

CREATE STREAM customer_stream ON TABLE customers [1] = TRUE;

Drag options to blanks, or click blank then click option'

AAPPEND_ONLY

BALL

CCHANGE_TRACKING

DINSERT_ONLY

Attempts:

3 left

💡 Hint

Common Mistakes

Using CHANGE_TRACKING which is not supported.

Using ALL when only inserts are needed.

The APPEND_ONLY = TRUE stream type is valid and captures only inserts. Other options like CHANGE_TRACKING are invalid in Snowflake.

4fill in blank

hard

Fill both blanks to create a stream showing initial rows on the 'products' table.

Snowflake

CREATE OR REPLACE STREAM products_stream ON TABLE products [1] = [2];

Drag options to blanks, or click blank then click option'

ASHOW_INITIAL_ROWS

BAPPEND_ONLY

CTRUE

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using APPEND_ONLY which captures only inserts.

Setting SHOW_INITIAL_ROWS to FALSE excludes initial rows.

The SHOW_INITIAL_ROWS = TRUE option allows the stream to include initial rows of the table in addition to capturing changes.

5fill in blank

hard

Fill all three blanks to query the stream 'inventory_stream' for deleted rows and select the metadata column, the product_id, and quantity.

Snowflake

SELECT [1], product_id, quantity FROM inventory_stream WHERE [2] = 'DELETE' ORDER BY [3];

Drag options to blanks, or click blank then click option'

AMETADATA$ACTION

Cproduct_id

Dquantity

Attempts:

3 left

💡 Hint

Common Mistakes

Using different column names for metadata action.

Ordering by quantity instead of product_id.

The METADATA$ACTION column is used to filter for deletes and also selected. Ordering by product_id organizes the output.