ADA-C01 Question 227
Single answer3.2 Given a scenario, manage databases, tables, and views.A data engineering team maintains a database named PROD_DB that contains a large transactional table SALES_FACT. An analyst accidentally runs a DELETE statement without a WHERE clause against SALES_FACT at 10:05 AM. The mistake is discovered at 10:20 AM, but the team wants to avoid disrupting downstream BI queries that are actively using PROD_DB. The account uses permanent tables and the data retention period is sufficient to recover the deleted rows. Which action should the Snowflake administrator take to restore the table contents with the least impact to current workloads?
- A
Run UNDROP TABLE SALES_FACT to recover the deleted data in place.
- B
Create a clone of SALES_FACT using Time Travel at a point just before 10:05 AM, validate it, and then swap or rename it into place during a controlled cutover.
- C
Use Fail-safe to immediately restore SALES_FACT to its state before the DELETE statement.
- D
Recreate SALES_FACT from the most recent snapshot by creating a materialized view on the table's historical data.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Time Travel to create a clone of SALES_FACT from a timestamp immediately before the accidental DELETE, then validate and cut over with minimal disruption. In Snowflake, Time Travel supports querying, cloning, and restoring historical data for databases, schemas, and tables within the configured retention period. For accidental DML changes, creating a point-in-time clone is often preferable to directly overwriting the production object because it allows validation before cutover and avoids interrupting active workloads. UNDROP applies only to dropped objects, not row-level changes. Fail-safe is not a primary operational recovery method for administrators and is used only after Time Travel is no longer available. These behaviors align with Snowflake documentation and common administrative best practices for managing databases, tables, and recovery scenarios.
- A. Incorrect.
Incorrect. UNDROP TABLE is used to recover dropped objects such as databases, schemas, tables, and views that are still within the retention window. In this scenario, the table was not dropped; rows were deleted by DML. Because the object still exists, UNDROP TABLE does not address the problem.
- B. Correct.
Correct. Time Travel can be used to access historical data for a table at a specific point before the accidental DELETE. Creating a clone of the table from just before 10:05 AM allows the administrator to validate the recovered data without immediately affecting current BI workloads. After validation, the recovered object can be swapped or renamed into place during a planned cutover, minimizing disruption. This is a practical recovery pattern for accidental DML changes when the original object must remain available.
- C. Incorrect.
Incorrect. Fail-safe is not intended for immediate, self-service recovery by administrators after routine user errors such as accidental DELETE statements. Fail-safe is a Snowflake-managed recovery mechanism used only after Time Travel has expired, and it requires Snowflake Support. It is not the least-impact or fastest option here, especially since the retention period still allows Time Travel recovery.
- D. Incorrect.
Incorrect. Snowflake does not provide recovery of deleted table rows by creating a materialized view on historical table data. Materialized views are used for query performance optimization on current base table data, not as a recovery mechanism for accidental DML changes. This option reflects a misunderstanding of the purpose of materialized views.