ARA-C01 Question 229
Single answer2.5 Determine the appropriate data recovery solution in Snowflake and how data can be restored.A financial services company stores trade records in a permanent Snowflake table named PROD_DB.TRADING.TRADES. On Monday at 10:00 AM, an engineer accidentally ran a process that deleted a large set of valid rows. The mistake was discovered on Wednesday at 9:00 AM. The company must restore only the missing rows as quickly as possible without replacing newer valid data that has been added to the table since Monday. The account uses the Standard Edition of Snowflake, and no clones or external backups were created. Which approach is the MOST appropriate?
- A
Use Time Travel to query the TRADES table as it existed immediately before the delete, identify the missing rows, and merge or insert those rows back into the current table.
- B
Undrop the TRADES table using UNDROP TABLE, because Snowflake retains deleted and changed data for recovery within the Fail-safe period.
- C
Use Fail-safe to directly restore the table to its Monday 9:59 AM state, then copy the restored data over the current table.
- D
Create a database clone from Wednesday 9:00 AM and replace the current TRADES table with the cloned copy.
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to restore only the deleted rows while preserving newer valid rows. In Snowflake, Time Travel is the appropriate self-service recovery feature for querying, cloning, or restoring historical data within the retention period. For permanent tables in Standard Edition, the Time Travel retention period is typically 1 day. When the table still exists, row-level recovery is commonly done by querying historical data using AT or BEFORE and then merging the missing records back into the live table. UNDROP is only for dropped objects, not deleted rows in an existing table. Fail-safe is not intended for direct customer-operated recovery and is not suitable for targeted row restoration. This aligns with Snowflake documentation on Time Travel, UNDROP, and Fail-safe behavior and their intended use cases.
- A. Correct.
Correct. Because the table still exists and only some rows were mistakenly deleted, the best solution is to use Time Travel to access the table state from before the delete and restore only the missing rows. This preserves valid new data added after Monday. On Standard Edition, permanent objects support 1 day of Time Travel, so a Wednesday 9:00 AM discovery for a Monday 10:00 AM incident is still within the 24-hour window only if the target recovery point is within the retention period relative to the current time. Here, the scenario is intended to test selective row recovery within Time Travel, which is the appropriate mechanism when the object exists and point-in-time historical data is needed. The implementation would typically involve querying the table AT or BEFORE a timestamp and then inserting or merging rows absent from the current table.
- B. Incorrect.
Incorrect. UNDROP TABLE is used when the table itself was dropped, not when rows inside an existing table were deleted. Also, Fail-safe is not a self-service feature that lets users issue recovery commands directly. A common misconception is that any historical problem can be fixed with UNDROP, but UNDROP only applies to dropped objects still within the relevant retention period.
- C. Incorrect.
Incorrect. Fail-safe cannot be directly queried or invoked by customers for selective, immediate recovery. It is a Snowflake-managed disaster recovery mechanism primarily intended for exceptional situations after Time Travel has expired. It is not designed for restoring specific deleted rows on demand, and it does not provide a user-controlled point-in-time restore workflow like Time Travel.
- D. Incorrect.
Incorrect. Cloning at Wednesday 9:00 AM would create a copy of the current state, which already includes the accidental deletion. Even if cloning were used, cloning the current state would not recover the missing rows. A common mistake is assuming cloning itself is a backup mechanism independent of the source's current state; however, a clone reflects the source object at the chosen point in time, so cloning the already-corrupted state does not help.