SnowPro Core Streams and Tasks 1 — Questions and Answers
Question 1: What is a Snowflake Stream primarily used for?
- Storing large files in cloud object storage
- Tracking DML changes (INSERT, UPDATE, DELETE) on a table for CDC workflows (Correct answer)
- Scheduling periodic SQL queries on a defined interval
- Compressing and archiving historical data for cost savings
Correct answer: Tracking DML changes (INSERT, UPDATE, DELETE) on a table for CDC workflows
Snowflake Streams track DML changes (inserts, updates, deletes) made to a source table, enabling change data capture (CDC) workflows downstream.
Question 2: Which metadata columns are automatically included when querying a Snowflake Stream?
- STREAM_ID, STREAM_TIME, STREAM_ACTION
- METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID (Correct answer)
- CHANGE_TYPE, CHANGE_TIME, ROW_HASH
- CDC_ACTION, CDC_TIMESTAMP, CDC_KEY
Correct answer: METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID
Snowflake streams expose three metadata columns: METADATA$ACTION (INSERT or DELETE), METADATA$ISUPDATE (TRUE if the record is part of an update), and METADATA$ROW_ID (unique row identifier).
Question 3: What happens to records in a Snowflake Stream after they are successfully consumed in a DML transaction?
- They are moved to a backup retention table for 90 days
- They are replicated to all secondary accounts automatically
- The stream offset advances and those records are no longer visible in the stream (Correct answer)
- They are deleted from the source table immediately
Correct answer: The stream offset advances and those records are no longer visible in the stream
When a DML transaction that reads stream data commits successfully, the stream's offset pointer advances and the consumed change records are no longer visible — they have been 'consumed'.
Question 4: When does a Snowflake Stream become stale and unusable?
- When its offset falls outside the source table's DATA_RETENTION_TIME_IN_DAYS window (Correct answer)
- After exactly 7 days regardless of account edition or table settings
- When the stream has not been queried for more than 24 hours
- After the stream accumulates more than 1 million change records
Correct answer: When its offset falls outside the source table's DATA_RETENTION_TIME_IN_DAYS window
A stream becomes stale when its offset falls outside the source table's Time Travel retention period, because Snowflake can no longer reconstruct the historical data the stream depends on.
Question 5: Which of the following Snowflake object types can have a stream created on it?
- Only standard (permanent) tables
- Tables, external tables, views (with limitations), and directory tables (Correct answer)
- Tables and materialized views only
- Stages and pipes only
Correct answer: Tables, external tables, views (with limitations), and directory tables
Snowflake supports streams on standard tables, external tables, views (append-only), and directory tables for tracking file metadata changes on a stage.
Question 6: In a Snowflake Stream, what does a row with METADATA$ACTION = 'DELETE' and METADATA$ISUPDATE = TRUE represent?
- A row that was permanently deleted using a DELETE statement
- The pre-image (old values) of a row that was updated (Correct answer)
- A DELETE statement that was rolled back in a transaction
- A stream reset operation performed by the account administrator
Correct answer: The pre-image (old values) of a row that was updated
An UPDATE produces two stream records: a DELETE with METADATA$ISUPDATE=TRUE (the old row values) and an INSERT with METADATA$ISUPDATE=TRUE (the new row values).
Question 7: Which stream type in Snowflake captures ALL DML changes including inserts, updates, and deletes?
- Append-only stream
- Standard (delta) stream (Correct answer)
- Insert-only stream
- External stream
Correct answer: Standard (delta) stream
A standard stream (the default type) captures all DML operations — inserts, updates, and deletes — providing a complete changelog since the last offset advancement.
SnowPro Core Streams and Tasks 1
What is a Snowflake Stream primarily used for?