COF-C03 Question 30
Single answerTablesA retail company loads point-of-sale transactions into a Snowflake table named SALES_TXN. During nightly processing, a developer accidentally runs a DELETE statement without a WHERE clause, removing all rows from the table. The mistake is discovered 45 minutes later. Business users need the table restored to its exact state from just before the DELETE as quickly as possible, and the table definition should remain the same. Which action should the Snowflake administrator take?
- A
Use Time Travel to restore the table, for example by executing CREATE OR REPLACE TABLE SALES_TXN AS SELECT * FROM SALES_TXN AT(OFFSET => -2700)
- B
Use Fail-safe to immediately recover the deleted rows because Fail-safe supports direct self-service restoration within 7 days
- C
Query the dropped data from the INFORMATION_SCHEMA and reinsert the rows into SALES_TXN
- D
Clone the current empty SALES_TXN table and then load the missing rows from the clone
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical recovery of table data after accidental DML. In Snowflake, Time Travel is the primary feature for restoring or querying historical table data within the configured data retention period. Since the DELETE occurred only 45 minutes ago, the table's earlier state can be accessed with AT or BEFORE clauses and used to restore the table contents. Fail-safe is a separate mechanism that begins only after Time Travel ends and is not designed for immediate administrator-driven recovery. INFORMATION_SCHEMA stores metadata, not deleted data, and cloning the current object state would only reproduce the empty table. Snowflake documentation on Time Travel and cloning emphasizes that historical recovery should use Time Travel when the desired point-in-time version is still within retention.
- A. Correct.
Correct. Because the DELETE happened 45 minutes ago, the prior version of the table data is still available through Time Travel, assuming the table's retention period covers that interval. The administrator can restore the data to the state before the DELETE by using a Time Travel query against the historical version of the same table and recreating or reloading the table contents. This preserves the logical table object while restoring the data to an earlier point in time. Snowflake supports querying historical data using AT or BEFORE clauses for tables within the retention period.
- B. Incorrect.
Incorrect. Fail-safe is not intended for immediate, self-service recovery by users or administrators. It is a Snowflake-managed disaster recovery mechanism used after Time Travel has expired, and recovery requires Snowflake Support. It is not the fastest or appropriate method when the data loss occurred only 45 minutes ago and Time Travel is available.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA provides metadata about database objects, not historical row-level data that was deleted from a table. A common misconception is that metadata views can be used to reconstruct deleted table contents, but they cannot restore transactional data removed by a DELETE statement.
- D. Incorrect.
Incorrect. Cloning the current SALES_TXN table would create a zero-copy clone of its current state, which is already empty after the accidental DELETE. Cloning does not automatically recover prior data versions unless the clone itself is created from a historical point using Time Travel semantics. Cloning the current table state would therefore not restore the missing rows.