SnowPro Advanced: Security Engineer Question 369
Single answerUse Time Travel and Fail-safe to access historical states of dataA security engineer discovers that a privileged user accidentally executed a DELETE statement against a sensitive audit table 36 hours ago. The table was configured with a 2-day Time Travel retention period and is a permanent table. The engineer needs to retrieve the deleted data as quickly as possible while preserving the current production table for forensic review. Which action should the engineer take?
- A
Use Time Travel to create a clone of the table at a point before the DELETE, then query the clone to recover the deleted rows.
- B
Open a Snowflake Support case to recover the table using Fail-safe, because Fail-safe is intended for restoring deleted data within 7 days.
- C
Undrop the table, because UNDROP can restore previous row versions that were deleted by DML statements.
- D
Use a query against the table with AT or BEFORE to access the historical version from before the DELETE, then reinsert the required rows into the current table if needed.
Show answer and explanation
Correct answer: D
Explanation
Snowflake Time Travel allows access to historical data for a defined retention period, including data changed by DML such as DELETE, UPDATE, and MERGE. Because the incident occurred within the table's 2-day retention period, the security engineer should use Time Travel rather than Fail-safe. The most direct method is to query the table using AT or BEFORE with a timestamp, offset, or statement ID corresponding to a point before the DELETE. If restoration is required, the engineer can insert the recovered rows back into the current table or create a separate recovery table from the historical query results. Fail-safe is only relevant after Time Travel retention has expired and is not intended for routine operational restores. Also, UNDROP restores dropped objects, not deleted rows. These behaviors are documented in Snowflake's Time Travel and Fail-safe documentation, including historical data access methods and object recovery limitations.
- A. Incorrect.
This is plausible but not the best answer for the stated requirement. Cloning a historical version using Time Travel is valid in Snowflake, but the scenario asks to retrieve deleted data as quickly as possible while preserving the current production table. Querying the historical version directly with AT or BEFORE is more immediate and does not require creating an additional object first. A clone could also work operationally, but it adds an extra step when direct historical querying is sufficient.
- B. Incorrect.
This is incorrect because Fail-safe is not designed for self-service data recovery by customers. Fail-safe is primarily a Snowflake disaster recovery mechanism available after Time Travel has expired, and access requires Snowflake Support. In this scenario, the DELETE occurred 36 hours ago and the table has a 2-day Time Travel retention period, so the data is still within Time Travel and should be recovered using Time Travel features instead of Fail-safe.
- C. Incorrect.
This is incorrect because UNDROP applies to dropped objects such as tables, schemas, and databases, not to rows deleted by DML operations like DELETE. A common misconception is that UNDROP can reverse any data loss, but for row-level historical recovery Snowflake uses Time Travel queries, cloning, or CREATE ... AS SELECT from a historical state.
- D. Correct.
This is correct. Since the DELETE happened 36 hours ago and the table has a 2-day Time Travel retention period, the historical state is still accessible through Time Travel. Using SELECT ... AT or BEFORE allows the engineer to query the table as it existed prior to the DELETE without modifying or replacing the current production object. This aligns with the requirement to preserve the current table for forensic analysis while recovering data quickly.