DEA-C02 Question #114: Real Exam Question with Answer & Explanation

Question

A Data Engineer is debugging a SQL stored procedure that contains a transaction with a 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 will the code write into table A1?

Options

• A0 rows - the ROLLBACK command at the end of the query will remove all inserted data.
• B2 rows - the CREATE statement acts as an implicit ROLLBACK, so all prior inserted rows are
• C4 rows - the ROLLBACK command at the end of the query will result in values 5 and 6 not being
• D6 rows - the statement acts as an implicit to the transaction. All values are

Explanation

In Snowflake, a DDL statement (CREATE OR REPLACE TABLE) encountered inside an explicit transaction triggers an implicit ROLLBACK of all preceding uncommitted DML - meaning the inserts of (1,2) and (3,4) are wiped out and the transaction ends. The subsequent INSERT INTO A1 VALUES (5), (6) then runs in auto-commit mode (no active transaction), permanently committing those 2 rows. The final ROLLBACK finds no active transaction and is effectively a no-op, so only 2 rows remain in A1.

Why the distractors fail:

• A (0 rows) - Assumes the final ROLLBACK removes everything, but it can't roll back auto-committed rows; it's a no-op.
• C (4 rows) - Assumes DDL acts as an implicit commit (saving rows 1–4) and ROLLBACK only removes 5–6; DDL actually triggers a rollback, not a commit.
• D (6 rows) - Assumes DDL is transaction-neutral and all inserts are eventually committed; ignores both the DDL-triggered rollback and the explicit ROLLBACK.

Memory tip: "DDL Destroys Live transactions" - in Snowflake, a DDL inside a transaction rolls it back rather than committing it, so any DML after the DDL auto-commits while anything before it is lost.

No community discussion yet for this question.