ADA-C01 Question 226
Single answer3.2 Given a scenario, manage databases, tables, and views.A Snowflake administrator manages a production database named SALES_DB that contains a large permanent table SALES_FACT. An analyst accidentally runs a DELETE statement without a WHERE clause and removes all rows from SALES_FACT at 10:05 AM. The mistake is discovered at 10:20 AM. The business wants the table restored to its state immediately before the delete, with minimal impact to downstream objects and without recreating grants manually. Which action should the administrator take?
- A
Use CREATE OR REPLACE TABLE SALES_FACT AS SELECT * FROM SALES_FACT AT (TIMESTAMP => '10:04:59')
- B
Use UNDROP TABLE SALES_FACT to recover the table, because Snowflake keeps dropped and modified data in Time Travel
- C
Use CREATE OR REPLACE TABLE SALES_FACT CLONE SALES_FACT AT (TIMESTAMP => '10:04:59')
- D
Use INSERT INTO SALES_FACT SELECT * FROM SALES_FACT AT (TIMESTAMP => '10:04:59') to repopulate the deleted rows
Show answer and explanation
Correct answer: C
Explanation
For accidental DML changes on an existing table, Snowflake Time Travel allows administrators to access historical versions of the table and restore data. UNDROP is only for dropped objects, not for reversing DELETE, UPDATE, or TRUNCATE on an existing table. In real-world administration, the preferred recovery pattern is to use zero-copy cloning from a point in time, such as CREATE OR REPLACE TABLE
CLONE- A. Incorrect.
This is incorrect. Although querying historical data with AT is valid, CREATE OR REPLACE TABLE drops and recreates the table object. That can disrupt dependencies and object metadata, and grants are not automatically preserved unless COPY GRANTS is explicitly supported and used in the statement pattern. The requirement is to restore the table with minimal impact and without manually recreating grants. Replacing the table object is less suitable than restoring the existing object in place via cloning and replacement semantics specifically designed for this use case.
- B. Incorrect.
This is incorrect. UNDROP TABLE only applies when the table object itself was dropped. In this scenario, the table still exists; only its rows were deleted. Time Travel supports recovery of historical data versions, but UNDROP is not the mechanism for undoing DML changes on an existing table.
- C. Correct.
This is correct. Snowflake supports cloning a table from a historical point using Time Travel, and CREATE OR REPLACE TABLE ... CLONE ... AT(...) is the recommended recovery pattern for restoring a table to a prior state after accidental DML. This approach efficiently uses metadata, restores the table contents to the selected point in time, and preserves the object name so downstream references continue to work. It is the most appropriate method for reverting the table to its pre-delete state.
- D. Incorrect.
This is incorrect. Querying the table at an earlier timestamp and inserting the rows back is technically possible in some scenarios, but here the DELETE removed all rows and the goal is to restore the entire table state immediately before the mistake. An INSERT-based recovery may duplicate rows if any rows remain, does not revert other possible table-state changes, and is less reliable than replacing the table from a Time Travel clone. It is also more operationally error-prone for a large production table.