DAA-C01 Question 140
Single answerUse Time Travel and cloning featuresA data analyst accidentally ran an UPDATE statement at 10:05 AM that corrupted several columns in the PROD_DB.SALES.FACT_ORDERS table. The team discovered the issue at 10:20 AM. They need to quickly validate the pre-change data and compare it with the current table without interrupting downstream workloads that are still reading from FACT_ORDERS. Which approach should the analyst use?
- A
Create a zero-copy clone of FACT_ORDERS using AT (TIMESTAMP => '10:04:59') into a temporary validation table, then compare that clone to the current FACT_ORDERS table.
- B
Use UNDROP TABLE FACT_ORDERS to restore the table to its state before 10:05 AM, then compare the restored table with the current table.
- C
Run ALTER TABLE FACT_ORDERS SET DATA_RETENTION_TIME_IN_DAYS = 0, then query historical data from before 10:05 AM to reduce storage impact during validation.
- D
Create a full copy of FACT_ORDERS with CREATE TABLE AS SELECT from FACT_ORDERS BEFORE (STATEMENT => '<update_query_id>'), then replace the production table after the comparison.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use Time Travel together with zero-copy cloning to create a separate validation object representing the table state immediately before the accidental UPDATE. This approach preserves the current production table for downstream consumers while giving the analyst an isolated object to compare against. In Snowflake, cloning can be performed at a specific timestamp or before a statement, provided the data is still within the object's Time Travel retention period. Zero-copy clones are metadata-based at creation time, making them fast and storage-efficient compared with creating a physical copy via CTAS. By contrast, UNDROP applies only to dropped objects, not modified rows, and changing retention settings does not restore prior table versions. This aligns with Snowflake documentation and best practices for using Time Travel and cloning for rapid recovery validation, auditing, and safe testing in production-adjacent scenarios.
- A. Correct.
Correct. A zero-copy clone created from a Time Travel point just before the bad UPDATE allows the analyst to inspect the prior state without changing the production table that downstream workloads depend on. This is the safest and fastest approach for validation and comparison. Snowflake supports cloning objects at a specific time or before a statement within the retention period, and zero-copy cloning avoids immediate data duplication.
- B. Incorrect.
Incorrect. UNDROP only restores a dropped object; it does not revert a table to a prior version after an UPDATE. This option reflects a common confusion between object recovery after DROP and Time Travel access to previous table versions.
- C. Incorrect.
Incorrect. Setting DATA_RETENTION_TIME_IN_DAYS to 0 would not help recover or validate past data and may actually reduce or eliminate Time Travel availability for future changes, depending on edition and object settings. It also does not retroactively create a historical snapshot for comparison. This is a misconception about retention settings and storage management.
- D. Incorrect.
Incorrect. Querying historical data with Time Travel in a CTAS statement can create a point-in-time copy, so part of this option sounds plausible. However, it is not the best answer because it creates a physical copy rather than a zero-copy clone, which is less efficient for quick validation. The instruction to replace the production table is also unnecessary and risky because the scenario only requires validation and comparison without interrupting downstream readers.