ADA-C01 Question 555
Single answer6.2 Given a scenario, manage Snowflake Time Travel and Fail-safe.A financial services company stores regulatory reporting data in a permanent Snowflake table named FIN_DB.REPORTING.MONTH_END_POSITIONS. The table has a DATA_RETENTION_TIME_IN_DAYS value of 1. On Tuesday at 09:00, an analyst accidentally runs a DELETE statement that removes all rows. The mistake is discovered on Thursday at 15:00. The table has not been dropped, and no clone was created before the incident. The company needs the data back as quickly as possible. Which action should the Snowflake administrator take?
- A
Use UNDROP TABLE FIN_DB.REPORTING.MONTH_END_POSITIONS to recover the deleted rows because the table still exists in Fail-safe.
- B
Use AT | BEFORE with a query or CREATE TABLE ... CLONE to restore the table to a point before Tuesday 09:00 because Time Travel is still available for 7 days on permanent tables.
- C
Open a Snowflake Support case to request Fail-safe recovery, because the table is beyond its 1-day Time Travel retention period and Fail-safe recovery is the remaining recovery option.
- D
Increase DATA_RETENTION_TIME_IN_DAYS to 7 and then use Time Travel to restore the table to a point before the DELETE.
Show answer and explanation
Correct answer: C
Explanation
This scenario tests the distinction between Snowflake Time Travel and Fail-safe, and when each can be used operationally. Time Travel provides self-service access to historical data for querying, cloning, or restoring objects within the configured retention period. For permanent tables, that retention can vary based on account edition and object/account settings, but administrators must rely on the effective configured value when determining recoverability. In this case, the table's DATA_RETENTION_TIME_IN_DAYS is explicitly 1, and the error was discovered more than 1 day later, so Time Travel can no longer be used.
After the Time Travel period ends for permanent objects, historical data moves into Fail-safe for 7 days. Fail-safe is not a self-service feature: administrators cannot query it, clone from it, or use UNDROP against it. Instead, Snowflake Support must perform the recovery. Best practice for operational resilience is to set retention appropriately for critical data and use additional protections such as zero-copy clones or backup/recovery procedures for business-critical tables, rather than relying on Fail-safe for routine recovery.
Relevant Snowflake documentation topics include Time Travel, Fail-safe, DATA_RETENTION_TIME_IN_DAYS, and UNDROP behavior for dropped objects.
- A. Incorrect.
Incorrect. UNDROP is used to restore dropped objects, such as a dropped table, schema, or database, during the Time Travel retention period. In this scenario, the table was not dropped; rows were deleted from an existing table. In addition, Fail-safe is not directly user-accessible through SQL commands like UNDROP. A common misconception is that Fail-safe works like an extended self-service recovery window, but it does not.
- B. Incorrect.
Incorrect. Time Travel can be used to query, clone, or restore data to an earlier state only within the configured retention period for the object. Here, DATA_RETENTION_TIME_IN_DAYS is set to 1, and the issue was discovered more than 1 day after the DELETE. The assumption that all permanent tables automatically provide 7 days of self-service Time Travel is incorrect; the actual retention depends on the edition and the configured setting for the object/account, but if the object is set to 1 day and that window has elapsed, Time Travel is no longer available for this recovery.
- C. Correct.
Correct. Because the table is a permanent table, after the Time Travel retention period ends, the historical data enters Fail-safe for 7 days. Fail-safe is intended for disaster recovery and is not accessible through SQL by customers. Recovery from Fail-safe requires engaging Snowflake Support, and recovery targets the data as of a point before the destructive operation. Given that the DELETE occurred on Tuesday at 09:00 and was discovered on Thursday at 15:00, the 1-day Time Travel window has expired, so Fail-safe recovery via Support is the appropriate next step.
- D. Incorrect.
Incorrect. Increasing DATA_RETENTION_TIME_IN_DAYS after the fact does not retroactively extend or recreate historical data that has already aged out of the previous Time Travel window. Once the deleted data is no longer available in Time Travel, changing the parameter cannot bring that history back into self-service recovery. This option reflects a common misunderstanding about retention settings.