ADA-C01 Question 556
Single answer6.2 Given a scenario, manage Snowflake Time Travel and Fail-safe.A Snowflake administrator manages a production database that contains several permanent tables used for financial reporting. The database is configured with a 7-day Time Travel retention period. On Monday at 09:00, a developer accidentally executes a DROP TABLE statement against a critical permanent table. The mistake is discovered on Thursday at 11:00, and the business needs the fastest self-service recovery method that preserves data without opening a Snowflake Support case. Which action should the administrator take?
- A
Use UNDROP TABLE on the dropped table because it is still within the Time Travel retention period.
- B
Wait until the table enters Fail-safe, then restore it using a SQL command available to ACCOUNTADMIN.
- C
Use ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 0 and then clone the table from Fail-safe.
- D
Recreate the table manually and query historical data using AT(OFFSET => -726060) because dropped objects remain directly queryable until Fail-safe ends.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical understanding of the boundary between Time Travel and Fail-safe. For permanent tables, Time Travel provides a configurable retention window during which administrators can perform self-service recovery operations such as UNDROP TABLE and query historical data. After Time Travel ends, the object may enter Fail-safe for 7 days, but Fail-safe is managed by Snowflake and is not intended for routine operational recovery; recovery from Fail-safe requires contacting Snowflake Support. Best practice is to use Time Travel for rapid operational recovery and to understand that Fail-safe is a last-resort protection layer, not an administrative restore feature. Relevant Snowflake documentation includes the sections on Time Travel, UNDROP, data retention periods, and Fail-safe behavior for permanent objects.
- A. Correct.
Correct. A permanent table dropped within its Time Travel retention period can be restored with UNDROP TABLE. Because the table was dropped on Monday at 09:00 and discovered on Thursday at 11:00, it is still within the 7-day Time Travel window. Time Travel supports self-service recovery of dropped objects, including restoring dropped tables, without involving Snowflake Support.
- B. Incorrect.
Incorrect. Fail-safe is not a self-service recovery mechanism. Once Time Travel expires, the object may enter the 7-day Fail-safe period for permanent objects, but recovery from Fail-safe requires Snowflake Support and is intended for disaster recovery scenarios, not routine operational restores. There is no SQL command that an ACCOUNTADMIN can use to restore from Fail-safe.
- C. Incorrect.
Incorrect. Changing DATA_RETENTION_TIME_IN_DAYS to 0 after the table has already been dropped would not help recover it, and objects cannot be cloned from Fail-safe. Cloning relies on existing object metadata and Time Travel-compatible states, not Fail-safe. This option mixes valid Snowflake features in an invalid recovery sequence.
- D. Incorrect.
Incorrect. Historical query syntax such as AT or BEFORE can be used to query prior states of existing objects within the retention window, but once a table has been dropped, the appropriate recovery method is UNDROP TABLE if still in Time Travel. Also, dropped objects are not directly queryable all the way through Fail-safe by using AT/BEOFRE syntax.