ARA-C01 Question 190
Single answerRollback processA Snowflake architect is overseeing a deployment that replaces a production table named SALES_FACT with a new version created by a CREATE OR REPLACE TABLE statement as part of a release script. Thirty minutes later, users report that several expected columns are missing because the new table definition was incorrect. The release must be rolled back quickly with minimal impact and without restoring the entire database. What is the BEST approach to recover the prior version of the table?
- A
Use Time Travel to create a clone of SALES_FACT from a point before the CREATE OR REPLACE operation, then validate it and swap or rename it back into place.
- B
Run UNDROP TABLE SALES_FACT because CREATE OR REPLACE only hides the old table and UNDROP will automatically reinstate it under the same name.
- C
Use Fail-safe to immediately restore the previous version of SALES_FACT because Fail-safe is intended for operational rollback within the first 24 hours.
- D
Query the table's historical data using AT or BEFORE clauses and the result will automatically restore the missing columns in the live table definition.
- E
Execute ROLLBACK on the current session because Snowflake can reverse any DDL change for up to 24 hours after the statement completes.
Show answer and explanation
Correct answer: A
Explanation
The best rollback strategy is to use Snowflake Time Travel to recover the prior version of the object from before the CREATE OR REPLACE TABLE statement. In real-world release failures, architects often recover the prior table into a temporary or recovery name, validate it, and then use a rename or table swap approach to minimize downtime. This is more precise and less disruptive than restoring an entire database. Key Snowflake concepts involved are: (1) CREATE OR REPLACE performs an atomic drop-and-create operation; (2) Time Travel supports querying and restoring prior object states within the data retention period; (3) UNDROP has limitations when an object with the same name already exists; and (4) Fail-safe is not for immediate self-service rollback. These behaviors are consistent with Snowflake documentation on Time Travel, UNDROP, CREATE OR REPLACE semantics, and Fail-safe best practices.
- A. Correct.
Correct. In Snowflake, CREATE OR REPLACE drops the old object and creates a new one in a single transaction. The prior version can still be recoverable through Time Travel for the retention period, assuming the table is permanent or transient and retention settings allow it. A practical rollback approach is to recreate or clone the table from a point before the replacement, validate it, and then use renaming or ALTER TABLE ... SWAP WITH to put the recovered table back into production with minimal disruption. This targets only the affected object rather than restoring the full database.
- B. Incorrect.
Incorrect. UNDROP can recover a dropped table, but after CREATE OR REPLACE, the replacement table now occupies the original name. UNDROP does not simply overwrite the existing object under that same name. In practice, recovery usually requires restoring the prior dropped version under a different name or using Time Travel-based reconstruction, then renaming or swapping. This option reflects a common misconception that UNDROP alone is sufficient after a replace operation.
- C. Incorrect.
Incorrect. Fail-safe is not a customer-managed operational rollback tool. It is a Snowflake-managed disaster recovery mechanism available after the Time Travel period ends for permanent objects, and it requires Snowflake Support involvement. It is not intended for rapid self-service rollback of a deployment issue within minutes or hours.
- D. Incorrect.
Incorrect. AT and BEFORE clauses allow querying historical data from Time Travel, but they do not automatically restore schema changes or revert the live object definition. They are useful for inspecting prior states or copying data out, but additional DDL is required to recover the prior table structure and place it back into service.
- E. Incorrect.
Incorrect. ROLLBACK only reverses statements within the current open transaction. Most DDL in Snowflake auto-commits, and once a CREATE OR REPLACE TABLE statement has completed, a later session-level ROLLBACK cannot undo it. This option represents a transactional misconception carried over from other database platforms.