Streams for change data capture in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Stream Behavior on Table Updates

You have a Snowflake table users and a stream users_stream defined on it. After performing an UPDATE on some rows in users, what will SELECT * FROM users_stream return?

Snowflake

UPDATE users SET email = '[e-mail]' WHERE user_id = 123;

A It returns the changed rows with both old and new values for the updated columns.

B It returns only the new values of the updated rows without old values.

C It returns all rows from the users table regardless of changes.

D It returns an empty result because streams do not capture updates.

💡 Hint

Think about what a stream captures when rows are updated.

Snowflake streams capture change data including both old and new values for updated rows, allowing you to see what changed.

❓ Architecture

intermediate

Choosing Stream Type for CDC

You want to capture all changes (inserts, updates, deletes) on a Snowflake table for downstream processing. Which stream type should you create?

A A standard stream on the table.

B A materialized view on the table.

C A change tracking stream on the table.

D A transient table with triggers.

💡 Hint

Consider which stream type captures all DML changes.

Standard streams capture inserts, updates, and deletes for CDC. Change tracking streams only track row-level changes without old values.

❓ security

advanced

Stream Access Control

Which Snowflake privilege is required for a user to read data from a stream?

A SELECT privilege on the underlying table only.

B CREATE STREAM privilege on the database.

C USAGE privilege on the stream and SELECT privilege on the underlying table.

D OWNERSHIP privilege on the stream only.

💡 Hint

Think about what permissions are needed to access both the stream and its data source.

To read from a stream, a user needs USAGE privilege on the stream itself and SELECT privilege on the underlying table.

❓ Configuration

advanced

Stream Behavior After Consuming Changes

What happens to the data in a Snowflake stream after you run SELECT * FROM stream_name without using DELETE or MERGE to consume the changes?

A The stream data is cleared automatically after the SELECT.

B The stream resets and starts capturing changes from scratch.

C The stream data is lost and cannot be recovered.

D The stream continues to show the same changes until they are consumed by DML statements.

💡 Hint

Consider how streams track consumption of changes.

Streams retain change data until it is consumed by DML operations like DELETE or MERGE. A simple SELECT does not consume changes.

✅ Best Practice

expert

Optimizing Stream Usage for Large Tables

You have a very large table with frequent changes and a stream defined on it. To minimize storage costs and improve performance, which practice is best when consuming stream data?

A Run <code>SELECT * FROM stream</code> multiple times before consuming changes.

B Consume stream data frequently using MERGE statements to clear changes promptly.

C Drop and recreate the stream daily to reset change tracking.

D Disable the stream when not in use to save storage.

💡 Hint

Think about how to keep stream data size manageable.

Frequent consumption of stream data using MERGE or DELETE clears the change data, reducing storage and improving performance.