COF-C03 Question 296
Single answerUsing dataA retail company loads daily sales files into a Snowflake table named SALES_RAW. Analysts frequently need to compare the current data to what the table looked like earlier in the day after each load. An engineer accidentally runs a DELETE statement at 3:00 PM that removes several thousand rows. The team confirms the correct data existed at 2:45 PM and wants to quickly query the table as it existed then, without restoring the table or disrupting ongoing workloads. What is the best approach?
- A
Use Time Travel with an AT or BEFORE clause to query SALES_RAW as of 2:45 PM
- B
Use Fail-safe to query the table contents from 2:45 PM and compare the deleted rows
- C
Clone SALES_RAW at 3:00 PM and query the clone to retrieve the rows that existed at 2:45 PM
- D
Use a materialized view on SALES_RAW to automatically expose the table's prior versions
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowflake Time Travel. Time Travel is designed specifically for accessing historical data for a defined retention period and supports point-in-time queries using AT or BEFORE clauses. This makes it ideal when a team needs to inspect or compare a table's prior contents without performing a restore operation. Fail-safe is often confused with Time Travel, but it is a last-resort recovery feature managed by Snowflake and is not intended for direct querying. Cloning is useful for creating independent copies of data, but to capture an earlier state it must be used together with Time Travel rather than performed only on the current table. Materialized views are for performance optimization, not data versioning. This aligns with Snowflake documentation on Time Travel, Fail-safe, and zero-copy cloning best practices.
- A. Correct.
Correct. Snowflake Time Travel allows users to query, clone, or restore historical data from a table, schema, or database within the configured retention period. In this scenario, the requirement is to query the table as it existed at 2:45 PM without restoring the object or interrupting current use. A statement such as SELECT * FROM SALES_RAW AT (TIMESTAMP => '2025-...') is the appropriate solution. This is a common operational use case for Time Travel when users need point-in-time access to prior table states.
- B. Incorrect.
Incorrect. Fail-safe is not intended for user-driven querying or self-service recovery. It is a Snowflake-managed recovery mechanism available only after the Time Travel retention period has expired, and it requires Snowflake Support involvement. It also does not provide direct point-in-time query capability for customers.
- C. Incorrect.
Incorrect. Cloning at 3:00 PM would create a zero-copy clone of the table as it existed at the moment of cloning, which is after the DELETE occurred. While cloning can be combined with Time Travel in some workflows, cloning the current table alone would not recover the 2:45 PM state. The option misses the key requirement of using historical point-in-time access.
- D. Incorrect.
Incorrect. Materialized views improve query performance for certain repeated query patterns, but they do not provide historical versions of base tables for arbitrary point-in-time analysis. A materialized view reflects maintained query results over current underlying data, not a full history of prior table states.