SnowPro Associate: Platform Question 343
Single answer● Time TravelA data engineer accidentally runs a DELETE statement against the SALES table at 2:05 PM, removing thousands of rows. The team discovers the mistake at 2:20 PM and needs to restore the deleted data quickly without affecting rows that were inserted correctly after 2:05 PM. The table is permanent, and its Time Travel retention period covers the time of the mistake. Which approach should the engineer use?
- A
Use a query such as INSERT INTO SALES SELECT * FROM SALES AT (TIMESTAMP => '2025-01-15 14:04:59') to reinsert the missing rows from the table version before the DELETE.
- B
Run UNDROP TABLE SALES to recover the table as it existed before 2:05 PM.
- C
Use FAIL-SAFE to restore the deleted rows because Time Travel cannot recover deleted data from a DELETE statement.
- D
Clone the SALES table at the current time and copy the missing rows back from the clone.
Show answer and explanation
Correct answer: A
Explanation
Snowflake Time Travel enables access to historical versions of data within the configured retention period for permanent and transient objects. For accidental DML changes such as DELETE, a common recovery method is to query the table using AT or BEFORE to access its state before the change, then restore the affected rows. This is preferable here because the business wants to preserve valid data inserted after the accidental DELETE. UNDROP applies to dropped objects, not deleted rows in an existing table. Fail-safe is not a self-service recovery mechanism for routine operational mistakes. Snowflake documentation on Time Travel and cloning explains that historical data can be queried or cloned from a prior point in time, while Fail-safe is only for additional recovery protection after Time Travel is no longer available.
- A. Correct.
Correct. Time Travel allows querying historical data from a table at a specific point in time or before a statement. In this scenario, the engineer can query the SALES table as it existed just before the DELETE and reinsert only the missing rows. This preserves valid rows inserted after 2:05 PM, which is often the safest recovery approach when only part of the data needs to be restored.
- B. Incorrect.
Incorrect. UNDROP TABLE is used when a table itself has been dropped. Here, the table still exists; only rows were deleted. UNDROP TABLE would not apply to recovering data lost from a DELETE statement on an existing table.
- C. Incorrect.
Incorrect. This reflects a common misconception. Time Travel is specifically designed to access historical data for tables, schemas, and databases within the retention period, including data changed by DML such as DELETE. Fail-safe is not intended for user-driven, immediate query-based recovery and is primarily a last-resort disaster recovery feature managed by Snowflake.
- D. Incorrect.
Incorrect. Cloning the table at the current time would reproduce the table in its already-deleted state, so the missing rows would still be absent. To use cloning for recovery, the clone would need to be created from a historical point in time, not from the current version.