SnowPro Advanced: Security Engineer Question 361
Single answerRestore data from backups, Time Travel, or Fail-safeA security engineer discovers that a privileged user accidentally executed DROP TABLE PROD_SEC.AUDIT.LOGIN_EVENTS; 2 hours ago. The table stored critical audit records needed for an ongoing investigation. The database and schema still exist, and the account has Enterprise Edition with a 7-day Time Travel retention period configured for permanent objects. The security engineer must restore the table as quickly as possible while preserving the current production environment. Which action should the engineer take?
- A
Use
UNDROP TABLE PROD_SEC.AUDIT.LOGIN_EVENTS;to recover the dropped table within the Time Travel retention period. - B
Open a Snowflake Support case and request Fail-safe recovery of the table because dropped tables cannot be restored by customers.
- C
Restore the table by cloning the entire database from a point before the drop, then replacing the production database with the clone.
- D
Query the table using
AT (TIMESTAMP => ...)and recreate it manually withCREATE TABLE AS SELECT, becauseUNDROPonly works for schemas and databases.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use UNDROP TABLE because the table was dropped only 2 hours ago and the account has a 7-day Time Travel retention period for permanent objects. Time Travel allows restoring dropped data and objects to a historical state within the retention window. Since the parent database and schema still exist, UNDROP TABLE PROD_SEC.AUDIT.LOGIN_EVENTS; is the most direct, least disruptive, and operationally correct recovery method. Fail-safe is only applicable after Time Travel is no longer available and generally requires Snowflake Support involvement. Historical queries using AT or BEFORE are useful for inspecting or reconstructing prior data, but they are less efficient than UNDROP for this specific scenario. This aligns with Snowflake documentation on Time Travel, UNDROP, and Fail-safe behavior for permanent objects.
- A. Correct.
Correct.
UNDROP TABLEis the fastest and most appropriate recovery method when a table was dropped and is still within the configured Time Travel retention period. Because the database and schema still exist, the table can be restored directly in place without disrupting other production objects. This is a standard Time Travel recovery workflow for dropped objects. - B. Incorrect.
Incorrect. Fail-safe is not intended as the first-line recovery option for recent accidental deletions. For permanent tables, Fail-safe becomes relevant only after the Time Travel retention period has expired. In addition, Fail-safe recovery requires Snowflake assistance and is designed for disaster recovery scenarios, not routine operational restores when Time Travel is still available.
- C. Incorrect.
Incorrect. Cloning from a prior point in time can be useful in some scenarios, especially for comparison or selective recovery, but replacing the entire production database is unnecessarily disruptive here. Since only one table was dropped and
UNDROP TABLEis available, cloning the whole database adds risk and operational complexity without benefit. - D. Incorrect.
Incorrect. Time Travel queries with
ATorBEFOREcan be used to access historical data and reconstruct objects in some cases, but this is not the best option for a dropped table whenUNDROP TABLEis supported. The statement thatUNDROPonly works for schemas and databases is false; Snowflake supportsUNDROPfor tables, schemas, and databases within retention limits.