SnowPro Advanced Data Engineer

By Snowflake · Question Mode

QuestionQ58

Implement Data Pipelines

A Data Engineer is troubleshooting a SQL stored procedure that includes a transaction and rollback, using this code:

DROP TABLE A1;  
CREATE OR REPLACE TABLE A1(i int);  
BEGIN TRANSACTION;  
INSERT INTO A1 VALUES (1), (2);  
INSERT INTO A1 VALUES (3), (4);  
CREATE OR REPLACE TABLE table2 (i VARCHAR);  
INSERT INTO A1 VALUES (5), (6);  
ROLLBACK;

How many rows does this code write to table A1?

Explanation

In Snowflake, a DDL statement implicitly commits an active transaction before the DDL executes. CREATE OR REPLACE TABLE table2 therefore commits the first four inserted rows. The subsequent insert of values 5 and 6 is committed outside that earlier explicit transaction, and the final ROLLBACK does not undo those rows.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!