DEA-C02 Storage and Data Protection Practice Question
A data engineer has a permanent table ORDERS_ARCHIVE with DATA_RETENTION_TIME_IN_DAYS set to 30. An analyst accidentally runs DELETE FROM ORDERS_ARCHIVE WHERE order_date < '2022-01-01' and commits the transaction. Twenty minutes later, the engineer wants to recover only the deleted rows without disturbing the current state of the table. Which approach is correct?
⚠ Common exam trap
The trap here is reaching for UNDROP TABLE to reverse a DELETE, when UNDROP only recovers objects that were dropped and cannot restore rows removed by DML.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Query the table with SELECT * FROM ORDERS_ARCHIVE BEFORE (STATEMENT => '<query_id>') and insert the missing rows back.
Recovering deleted rows from a table that still exists requires reading its historical state with Time Travel and re-inserting the missing data. The BEFORE (STATEMENT => ...) clause targets the exact snapshot preceding the delete, so only the deleted rows are restored and post-delete changes are preserved. UNDROP applies to dropped tables, retention changes do not restore data, and cloning at an arbitrary timestamp does not precisely target the delete.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Run UNDROP TABLE ORDERS_ARCHIVE to restore the table to its pre-delete state.
Why it's wrong here
UNDROP TABLE reverses a DROP TABLE operation, not a DELETE. The table was never dropped, so UNDROP returns an error stating the table already exists. Even if the engineer dropped the table first, that would discard all rows added after the delete, which is not the goal. Recovery of deleted rows requires a Time Travel query against the table's history, not a DDL recovery command.
- ✗
Run ALTER TABLE ORDERS_ARCHIVE SET DATA_RETENTION_TIME_IN_DAYS = 30 to refresh the recovery window and then query the deleted rows.
Why it's wrong here
The retention is already 30 days, so this ALTER is a no-op and does not expose additional history. More importantly, changing the retention parameter does not restore deleted rows or create a new snapshot; it only governs how long existing history is kept. The engineer still needs a Time Travel query to read the pre-delete state, so this statement alone accomplishes nothing toward recovery.
- ✓
Query the table with SELECT * FROM ORDERS_ARCHIVE BEFORE (STATEMENT => '<query_id>') and insert the missing rows back.
Why this is correct
The BEFORE clause with a STATEMENT identifier reads the table as it existed immediately before the specified statement executed, which is exactly the pre-delete snapshot. Selecting from that snapshot and inserting the missing rows re-establishes the deleted data while preserving rows written after the delete. This works because the table's 30-day retention window still covers the twenty-minute-old delete, and the query ID can be retrieved from the Query History view.
- ✗
Run CREATE TABLE ORDERS_RESTORE CLONE ORDERS_ARCHIVE AT (TIMESTAMP => CURRENT_TIMESTAMP - INTERVAL '30 minutes') and swap the tables.
Why it's wrong here
A clone with AT creates a new table reflecting the source at that time, but the 30-minute offset does not match the actual delete time and may include or exclude the wrong rows. Cloning also copies the current table metadata and grants but produces a separate object, so it does not restore the original table in place. The engineer would then need to reconcile two tables, which is riskier than a targeted insert from a BEFORE snapshot.
About these practice questions
One of 229 original DEA-C02 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This DEA-C02 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C02 exam.