COF-C03 Question 318
Single answerTime TravelA data engineering team accidentally ran a DELETE statement against the PROD.SALES_DAILY table at 10:05 AM, removing thousands of rows. The table has a Time Travel retention period of 1 day, and the mistake is discovered at 10:20 AM the same day. The team wants to restore the table to its state immediately before the DELETE with minimal impact to downstream processes that already reference PROD.SALES_DAILY by name. Which action should the engineer take?
- A
Run CREATE TABLE PROD.SALES_DAILY_RESTORE AS SELECT * FROM PROD.SALES_DAILY AT (TIMESTAMP => '2024-01-01 10:04:59');
- B
Run UNDROP TABLE PROD.SALES_DAILY;
- C
Run CREATE OR REPLACE TABLE PROD.SALES_DAILY AS SELECT * FROM PROD.SALES_DAILY BEFORE (STATEMENT => '<delete_query_id>');
- D
Run ALTER TABLE PROD.SALES_DAILY SET DATA_RETENTION_TIME_IN_DAYS = 0 and then UNDROP TABLE PROD.SALES_DAILY;
Show answer and explanation
Correct answer: C
Explanation
Snowflake Time Travel allows querying, cloning, or restoring historical data from a defined retention period. For accidental DML changes on an existing object, a common recovery pattern is to recreate the table from a point before the change, often using AT or BEFORE clauses. When the exact offending query is known, BEFORE (STATEMENT => query_id) is preferred because it restores the data state immediately before that statement and avoids timestamp precision issues. UNDROP is only for recovering dropped objects, not for reversing row-level changes in an existing table. In Snowflake documentation, Time Travel supports accessing historical data using timestamps, offsets, and statement IDs, while object recovery after DROP uses UNDROP within the retention window.
- A. Incorrect.
This would create a separate restored copy of the data using Time Travel, which can be useful for investigation or comparison. However, it does not restore the original PROD.SALES_DAILY object in place. Downstream processes referencing PROD.SALES_DAILY would still point to the modified table unless additional cutover steps are taken. Because the question asks for minimal impact to existing dependencies on the table name, this is not the best choice.
- B. Incorrect.
UNDROP TABLE is used to recover a dropped table, not to reverse DML changes such as DELETE, UPDATE, or MERGE on an existing table. Since the table still exists and only rows were deleted, UNDROP is not applicable here. This is a common misconception because both UNDROP and Time Travel are recovery features, but they solve different problems.
- C. Correct.
This is the best answer. Using Time Travel with BEFORE (STATEMENT => '<delete_query_id>') reconstructs the table from the point immediately before the DELETE statement executed. CREATE OR REPLACE TABLE replaces the existing table under the same name, which minimizes impact to downstream processes that already reference PROD.SALES_DAILY. Referencing the statement ID is also more precise than relying on a timestamp when the exact query that caused the issue is known.
- D. Incorrect.
Setting DATA_RETENTION_TIME_IN_DAYS to 0 would reduce or eliminate the ability to use Time Travel going forward, not help restore deleted rows. In addition, UNDROP still would not apply because the table was not dropped. This option combines two incorrect ideas: changing retention does not recover data already deleted from an existing table, and UNDROP does not reverse a DELETE.