COF-C03 Practice Question: Performance Optimization, Querying, and Transformation
A developer is using a Stream on a table to capture changes. If the developer executes a DML statement that consumes the data from the Stream within a transaction, what happens to the Stream's offset after the transaction commits?
⚠ Common exam trap
Many candidates think a stream offset advances immediately when a query reads it, forgetting that transactional commit boundaries dictate offset advancement.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
The offset is advanced only if the transaction commits successfully.
Snowflake Streams use an offset to track the point in time from which they are reading changes. When a Stream is used as a source in a DML operation (like INSERT INTO... SELECT FROM stream), the offset is advanced only when the transaction successfully commits, ensuring 'exactly-once' processing of the change data.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The offset is advanced immediately when the SELECT is executed.
Why it's wrong here
Advancing the offset immediately would be risky; if the transaction fails after the SELECT but before the commit, the change data would be lost. Snowflake waits for a successful commit to ensure that the data has been safely processed before moving the stream's position forward.
- ✓
The offset is advanced only if the transaction commits successfully.
Why this is correct
The Stream's offset is managed transactionally. If the DML statement using the stream is part of a transaction that commits, the stream moves its pointer to the next set of changes. If the transaction rolls back, the offset remains at its original position.
- ✗
The offset must be manually updated using the ALTER STREAM command.
Why it's wrong here
Snowflake automatically manages the offset of a stream as it is consumed by DML statements. Manual intervention is not required and there is no command to 'set' a stream to a specific point in time, as the stream is designed for automated incremental processing.
- ✗
The Stream is dropped and must be recreated for the next batch.
Why it's wrong here
Streams are persistent objects designed for ongoing change data capture. They are not one-time-use objects. Once a batch of changes is consumed and the offset advances, the stream remains active and begins capturing any new changes that occur in the source table immediately.
About these practice questions
This COF-C03 question is part of Courseiva's 280-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This COF-C03 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the COF-C03 exam.