Data Engineer is performing below steps in sequence while working on Stream s1 created on table t1.

Data Engineer is performing below steps in sequence while working on Stream s1 created on table t1.

Step 1: Begin transaction.

Step 2: Query stream s1 on table t1.

Step 3: Update rows in table t1.

Step 4: Query stream s1.

Step 5: Commit transaction.

Step 6: Begin transaction.

Step 7: Query stream s1.

Mark the Incorrect Operational statements:

A. For Step 2, The stream returns the change data capture records between the current position to the Transaction 1 start time. If the stream is used in a DML statement, the stream is then locked to avoid changes by concurrent transactions.

B. For Step 4, Returns the CDC data records by streams with updated rows happened in the Step 3 because Streams works in Repeated committed mode in which statements see any changes made by previous statements executed within the same transaction, even though those changes are not yet committed.

C. For Step 5, If the stream was consumed in DML statements within the transaction, the stream position advances to the transaction start time.

D. For Step 7, Results do include table changes committed by Transaction 1.

E. if Transaction 2 had begun before Transaction 1 was committed, queries to the stream would have returned a snapshot of the stream from the position of the stream to the be-ginning time of Transaction 2 and would not see any changes committed by Transaction 1.

View Answer

Answer: C

Explanation:

Let’s analyze each statement based on the given sequence of operations and Snowflake’s behavior regarding streams:

A. Correct Statement – When querying a stream within a transaction, Snowflake returns the CDC records since the last time the stream was consumed up to the start of the current transaction. If used in a DML, the stream does lock to ensure consistency and avoid changes from concurrent transactions.

B. Correct Statement – Within a transaction, Snowflake operates in a "Read Committed" isolation level, allowing subsequent queries within the same transaction to see changes made by earlier statements in the transaction, even if those changes aren’t committed yet. Thus, in Step 4, you’ll see the changes made in Step 3.

C. Incorrect Statement – The position of the stream would advance to the transaction’s end time (the time of commit), not the start time. The position reflects the last change that’s visible in the stream, so it has to account for all changes made during the transaction.

D. Correct Statement – Once Transaction 1 is committed, its changes become visible to subsequent transactions. So, queries in Transaction 2 (Step 7) would indeed see the changes from Transaction 1.

E. Correct Statement – Snowflake’s stream behavior is such that if Transaction 2 had started before Transaction 1 was committed, it would take a snapshot based on its start time, and hence wouldn’t see changes made in Transaction 1. This behavior is consistent with Snowflake’s "Read Committed" isolation level.

Given the options, the incorrect operational statement is:

C. For Step 5, If the stream was consumed in DML statements within the transaction, the stream position advances to the transaction start time.