DAA-C01 Question 193
Single answerStored proceduresA data analyst team uses a JavaScript stored procedure to refresh a curated reporting table each morning. The procedure truncates and reloads the target table, then writes a row to an audit table indicating success or failure. Recently, analysts noticed that on failure days the reporting table is sometimes left empty, while the audit table still shows an error entry. The team wants to make the procedure more reliable so that either the full refresh succeeds or the target table remains unchanged, while still preserving failure logging. Which approach best meets this requirement in Snowflake?
- A
Wrap the TRUNCATE and INSERT/MERGE statements for the target table in an explicit transaction inside the stored procedure, COMMIT only after the load succeeds, and in the CATCH block ROLLBACK the data changes before writing the error record to the audit table.
- B
Replace the stored procedure with a user-defined function so the refresh logic runs atomically and can still write audit rows when an error occurs.
- C
Keep the current procedure logic, but add AUTOCOMMIT = FALSE at the session level; Snowflake will automatically preserve the original target table contents and still commit the audit insert if any statement fails.
- D
Use a temporary table for the target refresh because temporary tables automatically protect permanent tables from partial updates when a stored procedure fails.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use explicit transaction handling inside the stored procedure so the refresh of the reporting table is atomic. In Snowflake, stored procedures are commonly used to orchestrate multi-statement workflows, including DML and error handling. When a procedure performs destructive steps such as TRUNCATE followed by reload, explicit transaction control is the safest way to ensure that a failure does not leave the target table empty or partially updated. After rolling back the failed refresh, the procedure can then record the failure in an audit table as a separate action. This aligns with Snowflake best practices for procedural workflows that need all-or-nothing behavior for data changes. Relevant Snowflake documentation includes guidance on stored procedures, transaction semantics, and JavaScript stored procedure error handling.
- A. Correct.
Correct. In Snowflake stored procedures, transaction control can be used to group the target-table refresh steps so they either succeed together or are rolled back together. By explicitly beginning a transaction, performing the TRUNCATE and reload logic, and committing only after success, the procedure avoids leaving the reporting table in a partially refreshed state. In the error handler, rolling back the failed refresh before inserting an error row into the audit table preserves the original target data while still allowing separate failure logging. This is the practical pattern for atomic refresh behavior with procedural error handling.
- B. Incorrect.
Incorrect. A user-defined function (UDF) is not an appropriate replacement for this workload. UDFs are intended to return values and are not designed for procedural data-loading workflows that perform DML/DDL side effects such as truncating tables, inserting large result sets, or writing audit rows. This option reflects a common misconception that UDFs and stored procedures are interchangeable in Snowflake.
- C. Incorrect.
Incorrect. Setting AUTOCOMMIT = FALSE does not by itself guarantee the desired behavior. Snowflake does not automatically preserve the original target table contents and separately commit the audit record after a failure without explicit transaction handling logic in the procedure. If the procedure truncates the table and then fails before the reload completes, the session still needs explicit ROLLBACK/COMMIT management to control what persists.
- D. Incorrect.
Incorrect. Temporary tables can be useful as staging objects, but simply using a temporary table for the refresh does not ensure the permanent reporting table remains unchanged if the procedure truncates or modifies it directly. The key requirement is transactional control over the permanent target-table changes. This option confuses object type selection with transaction design.