COF-C03 Question 317
Single answerTime TravelA data engineering team accidentally ran a DELETE statement at 2:05 PM that removed valid rows from the PROD_DB.SALES.ORDERS table. The table has Time Travel retention enabled, and the team wants to restore the table to the exact state it was in at 2:00 PM without rebuilding it manually. Which Snowflake approach should be used?
- A
Use CLONE on PROD_DB.SALES.ORDERS with an AT(TIMESTAMP => '...') clause for 2:00 PM, then replace the original table after validation
- B
Use UNDROP TABLE PROD_DB.SALES.ORDERS to recover the table to its 2:00 PM state
- C
Query the table with BEFORE(STATEMENT => '<delete_query_id>') and the changes will be permanently restored automatically
- D
Use Fail-safe to retrieve the table as it existed at 2:00 PM because Fail-safe supports self-service point-in-time recovery
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Time Travel with a point-in-time clone. Snowflake Time Travel allows access to historical data using AT or BEFORE with timestamps, offsets, or statement IDs. In practice, cloning the table as it existed at the required time is safer than immediately overwriting the damaged table because the recovered data can be validated first. UNDROP only applies to dropped objects, not accidental DELETE or UPDATE operations. Querying historical data with BEFORE helps inspect prior states but does not itself restore current data. Fail-safe is not a self-service feature and is not designed for routine operational recovery. This aligns with Snowflake documentation and best practices for Time Travel and object recovery.
- A. Correct.
Correct. Time Travel supports creating a clone of a table at a specific point in the past using AT or BEFORE. In a real recovery scenario, creating a point-in-time clone is a safe approach because the team can validate the restored data before swapping or replacing the damaged table. This directly addresses the requirement to restore the table to its prior state without manual rebuild.
- B. Incorrect.
Incorrect. UNDROP is used to recover a dropped object, not to reverse DML changes such as an accidental DELETE on a table that still exists. Since the table was not dropped, UNDROP would not solve this scenario.
- C. Incorrect.
Incorrect. BEFORE(STATEMENT => ...) can be used to query or create objects from a prior point in time, but simply querying historical data does not automatically restore the current table. A candidate might choose this because BEFORE is related to Time Travel, but additional recovery steps such as cloning or recreating data are required.
- D. Incorrect.
Incorrect. Fail-safe is not intended for self-service operational recovery or point-in-time restore by users. It is a Snowflake-managed disaster recovery mechanism used after the Time Travel retention period expires. It does not provide direct user-driven restoration of a table to a specific timestamp.