DAA-C01 Question 194
Single answerStored proceduresA data analyst team uses a JavaScript stored procedure to refresh a reporting table each morning. The procedure truncates and reloads REPORTING.SALES_DAILY from several source tables, then writes a row to an audit table indicating success or failure. Recently, the team discovered that when one INSERT statement fails midway through execution, the target table is sometimes left partially refreshed, but the audit row still shows the procedure was called. The team wants the refresh to behave as one atomic unit so that either all data changes succeed or none of them are committed. Which change should the analyst recommend?
- A
Wrap the DML statements in an explicit transaction inside the stored procedure by issuing BEGIN, COMMIT, and ROLLBACK through SQL statements, and only write the success audit row after COMMIT succeeds.
- B
Convert the stored procedure to a SQL UDF so Snowflake automatically rolls back all statements if any statement fails.
- C
Add an EXCEPTION handler that catches all errors and continues executing the remaining statements so the procedure completes and Snowflake resolves transaction consistency automatically.
- D
Run each DML statement with AUTOCOMMIT disabled at the session level from the caller; stored procedures cannot control transactions internally.
Show answer and explanation
Correct answer: A
Explanation
The key issue is transactional control across multiple statements in a stored procedure. In Snowflake, a stored procedure that executes several DML statements can leave partial results if an error occurs and the procedure does not explicitly manage the transaction boundary. For ETL-like refresh logic, best practice is to define the unit of work clearly: begin a transaction, perform all refresh steps, commit only after all steps succeed, and rollback if any step fails. Audit logging should also be aligned with this design so that success is logged only after the transaction commits. This approach is consistent with Snowflake documentation on transactions and stored procedures, which emphasizes explicit transaction management when multiple statements must succeed or fail together.
- A. Correct.
Correct. In Snowflake stored procedures, multiple SQL statements are not automatically treated as a single atomic transaction unless transaction control is used appropriately. A practical fix is to execute BEGIN, then run the TRUNCATE/INSERT steps, COMMIT only after all steps succeed, and issue ROLLBACK in error handling if any step fails. Writing the success audit row only after COMMIT helps ensure the audit state reflects the actual refresh outcome. This is the standard approach when a stored procedure must guarantee all-or-nothing behavior across multiple DML statements.
- B. Incorrect.
Incorrect. A SQL UDF is not a replacement for a stored procedure in this scenario. UDFs are intended to return a value and are not used to orchestrate multi-step DML workflows such as TRUNCATE, INSERT, and audit logging. They do not provide a mechanism to manage a transactional ETL-style refresh process.
- C. Incorrect.
Incorrect. Catching errors and continuing execution would make the inconsistency worse, not better. If a statement fails after earlier DML has already run, continuing without an explicit rollback can leave partially applied changes in place. Error handling in a stored procedure should typically trigger compensating logic or a rollback, not suppress the failure and proceed as though the process succeeded.
- D. Incorrect.
Incorrect. This reflects a common misconception. While session transaction settings matter, Snowflake stored procedures can execute transaction control statements as part of their logic for multi-statement workflows. Relying only on the caller's AUTOCOMMIT setting does not by itself guarantee that the procedure's refresh and audit steps behave as one atomic unit.