What is a characteristic of the operations of streams in Snowflake?

Question

Options

• AWhenever a stream is queried, the offset is automatically advanced.
• BWhen a stream is used to update a target table, the offset is advanced to the current time.
• CQuerying a stream returns all change records and table rows from the current offset to the current
• DEach committed and uncommitted transaction on the source table automatically puts a change

• B
  94% (46)
• C
  2% (1)
• D
  4% (2)

Explanation

Option B is correct because Snowflake streams use an offset - a pointer to a position in the source table's change history. The offset only advances when the stream is successfully consumed within a committed DML transaction that updates a target table (e.g., INSERT INTO target SELECT ... FROM my_stream). This ensures that change records are not lost if a transaction fails or rolls back.

Why the distractors are wrong:

• A is wrong because querying a stream alone never advances the offset - only a committed transaction that consumes the stream does.
• C is wrong because a stream returns only change records (inserts/updates/deletes) from the offset forward, not a mix of change records and full table rows.
• D is wrong because uncommitted transactions do not affect the stream - only committed DML operations that consume the stream move the offset.

Memory tip: Think of a stream offset like a bookmark that only moves after you've finished reading the chapter and saved your progress (committed transaction). Just opening the book (querying) or writing in the margins (uncommitted work) doesn't move the bookmark.

No community discussion yet for this question.