SnowPro Advanced: Data Engineer free question 2 / 10 · Domain 1: Data Movement
An audit task reads a stream with SELECT and sends the result to an external audit system. The auditor says that the changes sent last time are sent again every time. Which fix matches the official documentation?
- Recreate the stream as APPEND_ONLY so that insert rows that were read once are not returned by the next SELECT
- Call SYSTEM$STREAM_HAS_DATA on every SELECT until it returns FALSE, so that the offset advances
- Consume the stream by moving its rows into a staging table with INSERT … SELECT, and send the rows read from that table
- Wrap the SELECT in BEGIN and COMMIT so that the offset of the stream advances when the transaction commits
Show the answer and explanation
Answer: C
Why this is correct
A stream does not hold the table's data itself; it holds only an offset into the source object. Multiple queries can read the same changes without changing the offset. The offset advances only when the stream is used in a DML statement and that transaction commits; merely querying it does not advance it, even inside an explicit transaction. A task that only runs SELECT therefore returns the same changes every time, so you consume the stream by moving the rows into a table with INSERT … SELECT and send from that table.
Why the other options are wrong
"Wrap the SELECT in BEGIN and COMMIT so that the offset of the stream advances when the transaction commits" … Merely querying a stream does not advance the offset, even inside an explicit transaction. To advance the offset you have to consume the stream with DML.
"Recreate the stream as APPEND_ONLY so that insert rows that were read once are not returned by the next SELECT" … An append-only stream is a type that records only inserts; it does not advance its offset just because rows were read. As long as you read with SELECT alone, the same inserts are returned every time.
"Call SYSTEM$STREAM_HAS_DATA on every SELECT until it returns FALSE, so that the offset advances" … This function returns whether there are changes, and calling it on an empty stream keeps the stream from going stale. It does nothing to advance the offset.
Related setting
To advance the offset to the current version without using the changes, recreate the stream with CREATE OR REPLACE, or run an INSERT into a temporary table with a condition that excludes every row, such as WHERE 0 = 1.