ARA-C01 Question 230
Single answer2.5 Determine the appropriate data recovery solution in Snowflake and how data can be restored.A financial services company stores critical transaction history in a permanent Snowflake table named PROD_DB.FINANCE.TRANSACTIONS. At 10:00 AM on Tuesday, an engineer accidentally executed a DELETE statement without a WHERE clause, removing all rows. The error was discovered at 2:00 PM the same day. The company must restore the table to its state immediately before the DELETE as quickly as possible while preserving any grants on the object. The account uses the Standard Edition of Snowflake, and no database replication or failover groups are configured. Which action should the architect recommend?
- A
Use UNDROP TABLE PROD_DB.FINANCE.TRANSACTIONS to recover the deleted rows because Time Travel automatically recreates the table contents after DML errors.
- B
Create a clone of PROD_DB.FINANCE.TRANSACTIONS using AT(TIMESTAMP => 'just before 10:00 AM Tuesday') and then swap or rename objects as needed to restore the data.
- C
Restore the table from Fail-safe because Fail-safe supports self-service recovery of accidental DELETE operations within 7 days.
- D
Use Snowflake replication to recover the table to a prior point in time because Standard Edition includes cross-region point-in-time recovery for tables.
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the ability to choose the correct Snowflake recovery mechanism for an accidental DML change. Since the table was not dropped, UNDROP does not apply. The correct recovery method is Time Travel, which allows access to historical table data within the retention window. In Standard Edition, permanent objects typically have 1 day of Time Travel retention, which covers a same-day mistake discovered four hours later. A practical approach is to create a clone of the table from a point just before the DELETE, then restore the data from that clone. This is often preferred because cloning is fast and storage-efficient. Fail-safe is not designed for self-service restoration of user errors, and replication/failover is both unnecessary here and not available as described in Standard Edition. Snowflake documentation on Time Travel, UNDROP, cloning, and Fail-safe supports these distinctions and aligns with best practice: use Time Travel first for logical recovery, reserve Fail-safe for last-resort Snowflake-assisted recovery scenarios.
- A. Incorrect.
Incorrect. UNDROP is used to restore dropped objects, such as tables, schemas, and databases, within the Time Travel retention period. In this scenario, the table still exists; only its rows were deleted by a DML statement. UNDROP cannot reverse an accidental DELETE on an existing table.
- B. Correct.
Correct. For accidental DML changes such as DELETE without a WHERE clause, Snowflake Time Travel can be used to access historical data from a point before the error. Creating a zero-copy clone of the table at a timestamp immediately before 10:00 AM is an appropriate recovery approach. The data can then be copied back, or the clone can be renamed/swapped into place depending on operational constraints. Because the original table remains in place, existing grants on that object can be preserved more easily than dropping and recreating it.
- C. Incorrect.
Incorrect. Fail-safe is not a self-service recovery feature and is intended primarily for disaster recovery by Snowflake, not for routine correction of user errors. Customers cannot directly query or restore data from Fail-safe, and it is not the recommended solution for same-day recovery from an accidental DELETE.
- D. Incorrect.
Incorrect. Replication and failover capabilities are not available in Standard Edition in the way described, and they are not the primary mechanism for correcting an accidental DML statement on a single table within the retention window. Even where replication exists, Time Travel is the appropriate first choice for point-in-time logical recovery of table data.