ARA-C01 Question 189
Single answerRollback processA retail company deployed a release script that accidentally updated every row in the SALES_FACT table with an incorrect DISCOUNT_RATE value. The script was executed as a single explicit transaction by an ETL role, and the engineer discovered the mistake 20 minutes later. Other reporting workloads have continued to run against the table since the bad commit. The architect needs the fastest way to restore the table to its pre-change state without affecting unrelated objects in the schema. Which action should be taken?
- A
Execute ROLLBACK in a new session using the ETL role to undo the committed transaction.
- B
Use CREATE OR REPLACE TABLE SALES_FACT AS SELECT * FROM SALES_FACT AT (OFFSET => -1200);
- C
Run UNDROP TABLE SALES_FACT to restore the previous version of the table before the update.
- D
Use a clone of the database from before the release and swap the entire schema back into production.
Show answer and explanation
Correct answer: B
Explanation
When a bad data change has already been committed in Snowflake, the transaction rollback mechanism is no longer available. ROLLBACK applies only to an active, uncommitted transaction in the current session. After commit, architects should use Snowflake Time Travel to access historical data and restore the object or data set to a prior state. For a single affected table, a targeted restore such as recreating the table from a historical query is typically faster and less disruptive than broader database or schema recovery approaches. UNDROP is only for dropped objects, not for reversing committed DML. Snowflake documentation distinguishes clearly between transaction control statements like COMMIT/ROLLBACK and historical recovery features like Time Travel and UNDROP, and exam questions often test the ability to choose the least disruptive recovery path for a real operational incident.
- A. Incorrect.
Incorrect. In Snowflake, ROLLBACK only reverses changes in the current open transaction in the current session before COMMIT. Once the transaction has been committed, a later session cannot issue ROLLBACK to undo it. This is a common misconception for teams coming from operational recovery patterns that rely on transaction logs.
- B. Correct.
Correct. Because the change was already committed, the appropriate recovery mechanism is Time Travel. Re-creating the table from a point before the bad update restores the data to its earlier state while limiting the scope to the affected table. The statement uses a timestamp offset of 1,200 seconds (20 minutes) to query the historical version and rebuild the table quickly.
- C. Incorrect.
Incorrect. UNDROP TABLE is used only after a table has been dropped. It does not revert DML changes such as UPDATE, DELETE, or MERGE operations. A candidate might choose this if they confuse dropped-object recovery with row-level data recovery.
- D. Incorrect.
Incorrect. Although cloning and swapping can be useful in some deployment and rollback strategies, this option is broader than required and would affect more than the single impacted table. The scenario specifically calls for the fastest targeted restoration without affecting unrelated objects, so table-level Time Travel is the better fit.