SnowPro Associate: Platform Question 335
Single answer4.0 Data Protection and Data SharingA data engineering team accidentally ran a DELETE statement against a production Snowflake table at 2:00 PM. At 2:20 PM, the team confirms that valid rows were removed and needs to restore the table contents as quickly as possible without affecting other objects in the schema. The table is permanent, and the account uses standard Time Travel retention. Which action should the Snowflake administrator take?
- A
Use UNDROP TABLE to recover the table to its state before the DELETE statement.
- B
Create or replace the table using a query with AT (TIMESTAMP => '2024-...') or BEFORE (STATEMENT => '<query_id>') to restore the data from Time Travel.
- C
Wait for Fail-safe to begin, then recover the deleted rows from Fail-safe because it is intended for user error recovery.
- D
Clone the entire schema at a point before 2:00 PM, because Time Travel can only restore dropped objects and not changed data.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Time Travel to restore the affected table from a point before the accidental DELETE. In Snowflake, Time Travel supports access to historical data for tables, schemas, and databases, including data changed by DML operations such as UPDATE, DELETE, and MERGE, not just dropped objects. If the object still exists, UNDROP is not applicable. Fail-safe is not intended for immediate operational recovery from user errors and is Snowflake-managed rather than a standard self-service mechanism. Best practice is to use Time Travel with a precise point-in-time or statement-based reference, such as BEFORE(STATEMENT => '<query_id>'), to minimize impact and recover only what is needed. This aligns with Snowflake documentation on Time Travel, UNDROP, cloning, and Fail-safe behavior.
- A. Incorrect.
Incorrect. UNDROP TABLE is used to restore dropped objects, such as a table that was dropped. In this scenario, the table still exists; only rows were deleted by mistake. Because the object was not dropped, UNDROP TABLE does not address the problem.
- B. Correct.
Correct. Snowflake Time Travel supports querying, cloning, or recreating data from a previous point in time, including before a DML operation such as DELETE. A common recovery approach is to create or replace the table from a historical version using AT or BEFORE, including BEFORE(STATEMENT => '<query_id>') when the offending statement is known. This restores the table contents without requiring recovery of the whole schema.
- C. Incorrect.
Incorrect. Fail-safe is not designed for routine self-service recovery from user mistakes. It is a Snowflake-managed recovery mechanism used after Time Travel has expired, primarily for disaster recovery support scenarios. It is slower and not the appropriate first response for a recent accidental DELETE.
- D. Incorrect.
Incorrect. Time Travel is not limited to dropped objects. It also supports accessing historical data before updates, deletes, and other changes. Cloning the entire schema would be broader than necessary and does not best meet the requirement to restore only the affected table as quickly as possible.