DAA-C01 Question 139
Single answerUse Time Travel and cloning featuresA data analyst accidentally ran a DELETE statement against the PROD.SALES_DAILY table at 10:05 AM, removing one day of valid records. At 10:20 AM, the analyst needs to quickly create an isolated copy of the table as it existed just before the DELETE so the team can validate the data before restoring anything in production. The table is permanent, Time Travel is enabled, and the deleted data is still within the retention period. Which action should the analyst take?
- A
Create a zero-copy clone of PROD.SALES_DAILY using AT (TIMESTAMP => '2024-...') to the time just before 10:05 AM, and validate the cloned table.
- B
Undrop the PROD.SALES_DAILY table, because UNDROP can restore the table to its state before the DELETE statement.
- C
Use FAIL-SAFE to access the version of PROD.SALES_DAILY from before 10:05 AM, then clone that version for validation.
- D
Create a clone of the current PROD.SALES_DAILY table, then use Time Travel on the clone to automatically recover the deleted rows into the clone.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of Time Travel and zero-copy cloning together. In Snowflake, Time Travel allows access to historical data for supported retention periods, and cloning can be combined with AT or BEFORE to create a new object from a prior version of an existing object. This is a common operational pattern when analysts need to inspect or validate data before performing a recovery in production. UNDROP applies only to dropped objects, not to rows removed by DML such as DELETE. Fail-safe is not a queryable historical feature for end users and should not be considered a routine recovery tool. Snowflake documentation on Time Travel, cloning, and UNDROP behavior makes these distinctions clear and reflects recommended best practice: use Time Travel-based cloning for fast, low-risk validation of prior states.
- A. Correct.
Correct. Snowflake supports creating a clone of an object at a specific historical point using Time Travel, such as with AT or BEFORE. This is the fastest and safest way to create an isolated copy of the table as it existed before the accidental DELETE. Because cloning is zero-copy initially, it is efficient, and the team can validate the historical snapshot without modifying production.
- B. Incorrect.
Incorrect. UNDROP is used to recover dropped objects, such as tables, schemas, or databases that were dropped and are still within Time Travel retention. It does not reverse a DELETE statement on an existing table. A common misconception is to treat DML mistakes and DROP operations the same, but they are handled differently in Snowflake.
- C. Incorrect.
Incorrect. Fail-safe is not intended for user-driven querying or self-service recovery. It is a disaster recovery mechanism managed by Snowflake and is only relevant after Time Travel retention has expired. Since the deleted data is still within Time Travel retention, Fail-safe is neither necessary nor directly usable by the analyst for this scenario.
- D. Incorrect.
Incorrect. Cloning the current table would preserve its current state, which already reflects the DELETE. Time Travel does not work by 'repairing' a clone after the fact unless the clone itself contains or references the needed historical version through an explicit time-travel-based clone or query. The correct approach is to clone the source table at the desired historical point in time.