ARA-C01 Question 232
Single answerBackup/recoveryA financial services company runs a critical reporting database in Snowflake. An ETL job accidentally executed a CREATE OR REPLACE TABLE statement at 09:05, replacing the PROD_DB.FINANCE.POSITIONS table with an empty table. The mistake is discovered at 10:00 the same day. The company needs to restore the original table as quickly as possible while minimizing impact to downstream objects that reference it. Which action should the Snowflake architect recommend?
- A
Use UNDROP TABLE PROD_DB.FINANCE.POSITIONS to restore the original table in place.
- B
Use Time Travel to clone or recreate PROD_DB.FINANCE.POSITIONS from a point before 09:05, then validate and swap or rename as needed.
- C
Recover the table from Fail-safe, because CREATE OR REPLACE bypasses Time Travel for the replaced object.
- D
Restore the entire database to a previous point in time so that dependent views and grants are recovered automatically.
Show answer and explanation
Correct answer: B
Explanation
Snowflake backup and recovery for user mistakes primarily relies on Time Travel and, after Time Travel expires, Fail-safe. In this scenario, the issue was detected within the same day, so the original table version is still within the Time Travel retention window. The best practice is to recover only the affected object using Time Travel, often by cloning the table from a timestamp before the accidental replacement and then validating before renaming or swapping into place. This minimizes blast radius and avoids rolling back unrelated database changes. UNDROP is appropriate for dropped objects, but CREATE OR REPLACE introduces a new object, so object-level Time Travel recovery is the more appropriate mechanism. Fail-safe is a last-resort recovery mechanism managed through Snowflake Support and is not designed for immediate, routine operational restores. These behaviors align with Snowflake documentation on Time Travel, Fail-safe, and object recovery semantics.
- A. Incorrect.
Incorrect. UNDROP is used for objects that were dropped. In this scenario, the table was replaced using CREATE OR REPLACE TABLE, which drops the old table and creates a new one with the same name. Although the old version is retained for Time Travel, UNDROP cannot restore it in place while a new object with the same name already exists. This is a common misconception because CREATE OR REPLACE internally performs a drop-and-create pattern, but operationally the recommended recovery path is through Time Travel on the prior version rather than a simple UNDROP of the current object name.
- B. Correct.
Correct. When a table is replaced, Snowflake retains the previous version for the configured Time Travel retention period. The architect can use Time Travel to recover the table as it existed before 09:05, typically by creating a clone or using CREATE TABLE ... CLONE or related recovery syntax referencing a timestamp before the error. This approach is fast, avoids reverting the entire database, and allows validation before swapping or renaming. It also minimizes disruption to downstream dependencies compared with broader recovery actions.
- C. Incorrect.
Incorrect. Fail-safe is not intended for self-service operational recovery and is only available after the Time Travel retention period has expired. It requires Snowflake Support involvement and is designed for disaster recovery of historical data, not immediate correction of a same-day user error. Also, CREATE OR REPLACE does not bypass Time Travel for the replaced object's recoverable history during the retention window.
- D. Incorrect.
Incorrect. Restoring the entire database is unnecessarily broad for a single-table replacement and may affect unrelated objects changed after 09:05. A point-in-time database restore or clone can be useful in some situations, but here the requirement is to restore one table quickly with minimal impact to downstream objects. Recovering just the affected table through Time Travel is the more precise and operationally sound choice.