SnowPro Associate: Platform Question 341
Single answer● Time TravelA data engineer accidentally runs an UPDATE statement on the SALES.PUBLIC.ORDERS table at 2:00 PM, overwriting the STATUS column for every row. The mistake is discovered at 2:20 PM. The table has a standard Time Travel retention period enabled, and no Fail-safe-related limitations apply. The engineer wants to restore the table to its exact state just before the UPDATE while minimizing additional manual work. Which action should the engineer take?
- A
Run an UNDROP TABLE command on SALES.PUBLIC.ORDERS.
- B
Create a clone of SALES.PUBLIC.ORDERS using Time Travel with an AT or BEFORE clause that references a point in time before 2:00 PM, then replace or swap as needed.
- C
Query the table with AT(OFFSET => -1200) to permanently revert the table contents in place.
- D
Wait until the table enters Fail-safe, then recover the previous version from there.
Show answer and explanation
Correct answer: B
Explanation
This scenario tests practical use of Snowflake Time Travel for correcting accidental data modifications. Time Travel supports accessing historical data for tables, schemas, and databases using a timestamp, offset, or statement ID. For an accidental UPDATE, the table was not dropped, so UNDROP is irrelevant. The most effective recovery method is to use Time Travel to access the table state from before the change, commonly by creating a clone at that earlier point and then restoring data from the clone. Snowflake documentation distinguishes Time Travel from Fail-safe: Time Travel is customer-accessible for recovery within the retention period, while Fail-safe is a last-resort recovery mechanism controlled by Snowflake and not designed for routine user restoration. Also, querying historical data with AT or BEFORE is read access to past state, not an automatic rollback of the current object.
- A. Incorrect.
Incorrect. UNDROP TABLE is used to recover dropped objects, not to reverse data changes made by DML such as UPDATE. In this scenario, the table still exists, so UNDROP TABLE does not address the overwritten rows.
- B. Correct.
Correct. Snowflake Time Travel allows access to historical data at a specific point or offset in time. A practical recovery approach is to create a clone of the table as it existed before the accidental UPDATE using AT or BEFORE, validate the data, and then use replacement steps such as swapping or inserting back as appropriate. This minimizes manual reconstruction and uses Time Travel as intended for object/data recovery.
- C. Incorrect.
Incorrect. The AT or BEFORE clause can be used to query historical data, but querying historical data does not itself revert the current table in place. The misconception is assuming Time Travel queries automatically restore data. Recovery requires an explicit action such as cloning historical state or recreating data from the historical result.
- D. Incorrect.
Incorrect. Fail-safe is not intended for user-driven point-in-time recovery operations and is not a substitute for Time Travel. Fail-safe is primarily a disaster recovery mechanism managed by Snowflake after Time Travel has expired. It also would not be the fastest or most appropriate option for correcting an accidental UPDATE discovered within the Time Travel retention period.