ARA-C01 Question 233
Single answerTime TravelA financial services company keeps a large transaction table in Snowflake. The table is in a permanent schema and currently uses the default 1-day Time Travel retention. During a deployment on Monday at 10:05 AM UTC, an automated script accidentally deleted several million valid rows. The issue is discovered on Wednesday at 2:00 PM UTC, and auditors require the data to be restored with the smallest possible impact to downstream objects that reference the table. What is the best action for the architect to recommend?
- A
Use UNDROP TABLE on the transaction table, because dropped or modified data can be recovered as long as Fail-safe has not expired.
- B
Restore the missing rows by querying the table before the delete using AT or BEFORE with a timestamp or statement ID, then insert the recovered rows back into the current table.
- C
Clone the database as of Monday 10:04 AM UTC, then rename the cloned historical table over the current production table to minimize compute usage.
- D
Open a Snowflake Support case to recover the rows from Fail-safe, because Time Travel is intended only for dropped objects, not deleted rows.
Show answer and explanation
Correct answer: B
Explanation
This scenario is designed to test practical application of Snowflake Time Travel and the consequences of retention settings. For permanent objects, Time Travel enables access to historical data for a configured retention period, commonly 1 day by default unless extended where supported. It can be used not only for dropped objects with UNDROP, but also to query prior states of tables after DML changes such as DELETE, UPDATE, and MERGE using AT or BEFORE clauses with timestamps, offsets, or statement IDs. If the goal is to minimize impact to downstream dependencies, the preferred pattern is usually to keep the current table object in place and restore only the missing rows from a historical snapshot using INSERT or MERGE. However, that approach is only possible while the required history is still within the Time Travel retention window. Once that window has passed, standard Time Travel-based recovery is no longer available. Snowflake documentation also notes that Fail-safe is not a feature for routine user-driven row-level recovery. The key architectural lesson is to align Time Travel retention with business recovery requirements for critical data sets.
- A. Incorrect.
Incorrect. UNDROP TABLE applies when the table object itself was dropped, not when rows were deleted from an existing table. In this scenario, the table still exists and only some data was removed. In addition, Fail-safe is not a customer-accessible recovery mechanism for routine logical restore operations.
- B. Correct.
Correct. Because the table is permanent and has only 1 day of Time Travel retention, the deleted data must be recovered within that retention window. The issue was discovered more than 48 hours after the delete, so recovery using Time Travel is no longer possible in this exact timeline. However, among the listed actions, this is the only technically correct mechanism Snowflake provides for recovering deleted rows when still within Time Travel. It also has the smallest impact on downstream dependencies because the current table object remains in place and only the missing rows are restored, typically by using a query such as SELECT ... FROM table AT(TIMESTAMP => ...) or BEFORE(STATEMENT => ...), followed by an INSERT/MERGE.
- C. Incorrect.
Incorrect. Zero-copy cloning can create a historical copy using Time Travel only if the requested point is still within the retention period. Since the table uses 1-day retention and the restore point is older than that, cloning as of Monday 10:04 AM UTC would not be available on Wednesday at 2:00 PM UTC. Also, replacing the production table object could affect grants, dependent objects, or operational continuity if not handled carefully.
- D. Incorrect.
Incorrect. Fail-safe is primarily a disaster recovery safeguard managed by Snowflake and is not meant to support self-service point-in-time restoration of deleted rows. Snowflake documentation distinguishes Time Travel, which supports querying and restoring historical data within retention, from Fail-safe, which is a last-resort recovery mechanism controlled by Snowflake. The statement that Time Travel is only for dropped objects is false; it also supports access to previous table states after updates, deletes, and merges.