Streams for change data capture in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Stream in Snowflake?

A Stream in Snowflake is a database object that tracks changes (inserts, updates, deletes) made to a table, allowing you to capture and process these changes incrementally.

beginner

How does a Snowflake Stream help with Change Data Capture (CDC)?

It records the changes made to a table since the last time the stream was read, enabling efficient incremental data processing without scanning the entire table.

beginner

What types of changes does a Snowflake Stream capture?

It captures INSERT, UPDATE, and DELETE operations performed on the source table.

intermediate

What happens to the data in a Snowflake Stream after it is consumed?

After the stream data is consumed (read), the stream marks those changes as processed, so the next read only returns new changes.

intermediate

Can a Snowflake Stream be used on a table with no primary key?

Yes, but without a primary key, it may be harder to uniquely identify rows for updates or deletes, so best practice is to have a primary key for accurate CDC.

What does a Snowflake Stream track?

A User login history

B Query execution plans

C Table schema changes

D Changes made to a table since last read

Streams track data changes (inserts, updates, deletes) made to a table since the last time the stream was read.

Which operations are captured by a Snowflake Stream?

A Only INSERTs

B Only DELETEs

C INSERT, UPDATE, DELETE

D Only UPDATEs

Snowflake Streams capture INSERT, UPDATE, and DELETE operations on the source table.

What happens when you read data from a Snowflake Stream?

A The stream resets and loses all data

B The changes are marked as consumed and won't appear again

C The source table is deleted

D The stream duplicates the data

Reading from a stream marks the changes as consumed, so the next read only returns new changes.

Why is having a primary key recommended when using Streams for CDC?

A To uniquely identify rows for accurate change tracking

B To speed up queries unrelated to CDC

C To prevent data from being deleted

D To encrypt the data

A primary key helps uniquely identify rows, which is important for tracking updates and deletes accurately.

Can a Snowflake Stream be created on a view?

A No, streams only work on tables

B Only on external tables

C Only on materialized views

D Yes, streams work on views

Streams can only be created on tables, not on views.

Explain how Snowflake Streams enable change data capture and why they are useful.

Think about how you would keep track of changes in a spreadsheet without copying the whole sheet every time.

Describe the lifecycle of data in a Snowflake Stream from creation to consumption.

Imagine a mailbox that collects letters until you check it, then it empties for new letters.