SnowPro Associate: Platform Question 342
Single answer● Time TravelA data engineer accidentally runs a DELETE statement against the PROD.SALES.ORDERS table at 2:00 PM, removing thousands of rows. At 2:20 PM, the engineer confirms the mistake and needs to recover the deleted data with minimal disruption to other users who are still querying the current table. Which action should the engineer take?
- A
Use Time Travel to query the ORDERS table as it existed before 2:00 PM and reinsert the missing rows into the current table.
- B
Use Fail-safe to immediately restore the deleted rows because Fail-safe is intended for user-driven recovery scenarios.
- C
Clone the current ORDERS table without specifying a timestamp, then copy the missing rows back from the clone.
- D
Run an UNDROP TABLE command on ORDERS to restore the deleted rows while keeping all later changes.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of Snowflake Time Travel for data recovery after accidental DML changes. Time Travel supports accessing historical data for objects within the retention period by using statements such as SELECT ... AT(TIMESTAMP => ...), SELECT ... BEFORE(STATEMENT => ...), or by creating a clone at a prior point in time. For accidental row deletion where the table still exists, querying historical data and reinserting only the missing rows is often the best solution because it minimizes impact on ongoing workloads. In contrast, UNDROP applies to dropped objects, not deleted rows. Fail-safe is not a user-operated feature for quick operational recovery; it is a last-resort recovery layer managed by Snowflake after Time Travel expires. Snowflake documentation on Time Travel and Fail-safe distinguishes clearly between these recovery methods and their intended use cases.
- A. Correct.
Correct. Time Travel allows querying, cloning, or restoring data from an earlier point within the configured retention period. In this scenario, the table still exists, but rows were deleted. The least disruptive approach is to query the table at a point before the DELETE, such as using AT or BEFORE in a SELECT statement, identify the missing rows, and insert them back into the current table. This lets current users continue accessing the active table while the engineer recovers only the affected data.
- B. Incorrect.
Incorrect. Fail-safe is not designed for immediate, self-service recovery by users. It is a Snowflake-managed recovery mechanism available only after the Time Travel retention period ends, primarily for disaster recovery and not for routine operational fixes. It also does not provide direct, instant row-level recovery for a user mistake detected within minutes.
- C. Incorrect.
Incorrect. Cloning the current table without a timestamp would create a copy of the table in its already-deleted state, so the missing rows would still be absent. To use cloning effectively for recovery, the engineer would need to clone the table at a point in time before the DELETE occurred.
- D. Incorrect.
Incorrect. UNDROP TABLE is used when a table itself has been dropped, not when rows inside an existing table were deleted. Since the ORDERS table still exists and only data was removed, UNDROP TABLE does not address this situation.