Which SQL commands, when committed, will consume a stream and advance the stream offset? (Choose two.)
Which SQL commands, when committed, will consume a stream and advance the stream offset? (Choose two.)
The SQL commands that consume a stream and advance the stream offset when committed are those that perform DML (Data Manipulation Language) operations, specifically INSERT and UPDATE operations. 'UPDATE TABLE FROM STREAM' and 'INSERT INTO TABLE SELECT FROM STREAM' fit this criterion. These commands read from the stream and make changes to a table, thereby advancing the stream offset when the transaction is committed.
AC The stream position (i.e. offset) is advanced when the stream is used in a DML statement. The position is updated at the end of the transaction to the beginning timestamp of the transaction. The stream describes change records starting from the current position of the stream and ending at the current transactional timestamp. To ensure multiple statements access the same change records in the stream, surround them with an explicit transaction statement (BEGIN .. COMMIT). An explicit transaction locks the stream, so that DML updates to the source object are not reported to the stream until the transaction is committed.
The stream position is advanced in DML statements i.e (Update,Insert) Hence A&C is the correct answer
A&C DML
https://docs.snowflake.com/en/sql-reference/sql/create-stream.html
A and C
The stream position (i.e. offset) is advanced when the stream is used in a DML statement. Insert, Update
A and C are correct
correct answer:AC
A and C are correct
The following SQL commands, when committed, will consume a stream and advance the stream offset: A: SELECT FROM STREAM B: UPDATE TABLE FROM STREAM
https://docs.snowflake.com/en/user-guide/streams-intro To advance the offset of a stream to the current table version without consuming the change data in a DML operation, complete either of the following actions: Recreate the stream (using the CREATE OR REPLACE STREAM syntax). Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).
choose CD
Could be ACE: A, C - The stream position (i.e. offset) is advanced when the stream is used in a DML statement. E - To ensure multiple statements access the same change records in the stream, surround them with an explicit transaction statement (BEGIN .. COMMIT).
DMLs advance Stream, so it is A and C.
If committed after update it will move the offset position and same for insert
Insert, Delete, Update, so its AC