ARA-C01 Question 234
Single answerTime TravelA financial services company uses Snowflake Enterprise Edition and stores trade records in a permanent table named TRADES in the PROD_DB.MARKET schema. The company retains 7 days of Time Travel for this database. At 10:05 AM, an engineer accidentally runs a DELETE statement without a WHERE clause against TRADES. The mistake is discovered at 10:20 AM, and new valid trades have been inserted into TRADES since 10:05 AM. The architect must restore only the deleted rows while preserving the new inserts made after the accidental DELETE. Which approach should the architect use?
- A
Use CREATE OR REPLACE TABLE TRADES AS SELECT * FROM TRADES AT (TIMESTAMP => '10:04:59');
- B
Use a query against TRADES AT (TIMESTAMP => '10:04:59') to identify the missing rows, then INSERT those rows back into the current TRADES table.
- C
Use UNDROP TABLE TRADES to restore the table to its state before the DELETE, then re-run the valid inserts that occurred after 10:05 AM.
- D
Clone the entire PROD_DB database at TIMESTAMP '10:04:59' and swap the cloned TRADES table with the current TRADES table.
Show answer and explanation
Correct answer: B
Explanation
Snowflake Time Travel allows querying, cloning, and restoring historical data for a defined retention period, but the recovery method must match the failure type. Because this incident involved an accidental DELETE on an existing table, the architect should use historical querying with AT or BEFORE to access the table state immediately prior to the delete. Since valid inserts occurred afterward, replacing or swapping the table would lose those newer rows. The correct pattern is to query the historical version, identify rows missing from the current table, and insert them back. UNDROP is only for dropped objects, not deleted rows. Snowflake documentation on Time Travel and historical data queries supports using AT | BEFORE for point-in-time access and distinguishes this from UNDROP behavior for dropped tables, schemas, and databases.
- A. Incorrect.
Incorrect. CREATE OR REPLACE TABLE from a historical query would rebuild TRADES as it existed at 10:04:59, but it would also remove any valid rows inserted after 10:05 AM. The scenario explicitly requires preserving new inserts. Replacing the table is a common mistake when Time Travel is available, but it is too destructive for selective recovery.
- B. Correct.
Correct. Querying the table using Time Travel at a point just before the accidental DELETE allows the architect to reconstruct the missing data set. By comparing the historical version with the current table and inserting only the deleted rows back, the architect preserves the valid post-incident inserts. This is the safest targeted recovery pattern when only part of the data needs restoration.
- C. Incorrect.
Incorrect. UNDROP TABLE applies only to dropped objects, not to data removed by DML such as DELETE. Since the table still exists and only rows were deleted, UNDROP TABLE cannot help here. This distractor reflects a common confusion between object recovery and historical data access.
- D. Incorrect.
Incorrect. Cloning the database or table at a prior timestamp would create a historical copy, but swapping or replacing the current TRADES table would again discard valid inserts made after 10:05 AM unless additional reconciliation steps are performed. While cloning can be useful for investigation or comparison, it is not the best direct method for restoring only deleted rows in this scenario.