COF-C03 Question 98
Single answerPermanentA data engineering team stores curated sales data in a permanent table in a production schema. During a deployment, an engineer accidentally runs a DROP TABLE statement against that table. The team needs the fastest way to recover the table with its data while keeping the same table name in the same schema. Which action should they take?
- A
Use UNDROP TABLE on the dropped table, provided the table is still within its Time Travel retention period
- B
Query the table using AT or BEFORE because permanent tables can still be queried after they are dropped
- C
Recreate the table from Fail-safe because Fail-safe is intended for self-service recovery by account users
- D
Clone the schema to a point before the drop, because permanent tables cannot be restored individually once dropped
Show answer and explanation
Correct answer: A
Explanation
Permanent tables in Snowflake are designed for long-term storage and support both Time Travel and Fail-safe. If a permanent table is accidentally dropped, the preferred recovery path is to use UNDROP TABLE while the object is still within its Time Travel retention period. This is the quickest and most direct self-service recovery method. After the Time Travel period ends, the data moves into Fail-safe for an additional 7 days, but Fail-safe recovery is not performed by users and requires Snowflake Support. This aligns with Snowflake documentation on table types, Time Travel, and object recovery best practices.
- A. Correct.
Correct. Permanent tables support Time Travel, and if the table was dropped recently enough to still be within its data retention period, the table can be restored with UNDROP TABLE. This is the fastest self-service recovery method and preserves the original object name in the same schema.
- B. Incorrect.
Incorrect. Time Travel queries with AT or BEFORE can be used to access historical data from existing objects, but once a table has been dropped, the appropriate recovery action is UNDROP TABLE during the retention period. Simply querying it after the drop does not restore the object for ongoing use.
- C. Incorrect.
Incorrect. Fail-safe is not a self-service recovery mechanism. It is a Snowflake-managed recovery option available only after Time Travel has expired, and recovery requires contacting Snowflake Support. It is also intended for disaster recovery, not routine user-driven restores.
- D. Incorrect.
Incorrect. Cloning can be useful in some recovery workflows, but it is not required here and is not the best answer for restoring a single dropped permanent table. Permanent tables can be restored individually with UNDROP TABLE as long as they remain within Time Travel retention.