ADA-C01 Question 562
Single answerRestore dropped objectsA Snowflake administrator discovers that a developer accidentally executed DROP TABLE sales.prod.orders at 09:10. At 09:20, another team recreated a new table with the same fully qualified name and loaded test data into it. Management now wants the original dropped table restored as quickly as possible without losing the newly created table. Which action should the administrator take?
- A
Run UNDROP TABLE sales.prod.orders; Snowflake will merge the original data back into the current table with the same name.
- B
Rename the newly created sales.prod.orders table to a different name, then run UNDROP TABLE sales.prod.orders.
- C
Use Time Travel with SELECT ... AT(OFFSET => -600) to recreate the original table in place; UNDROP cannot restore objects when the name has been reused.
- D
Clone the current sales.prod.orders table to a backup name, then run UNDROP TABLE sales.prod.orders; Snowflake will automatically version both tables under the same name.
Show answer and explanation
Correct answer: B
Explanation
This scenario tests practical recovery of dropped objects using UNDROP and awareness of object name conflicts. In Snowflake, dropped tables can be restored with UNDROP TABLE during the Time Travel retention period. However, restoration requires that no active object with the same name already exists in the schema. If the name has been reused, the current object must first be renamed or dropped. In this case, renaming the newly created table is the safest option because it preserves the replacement table and frees the original name for UNDROP. This aligns with Snowflake documentation and best practices for restoring dropped objects: use UNDROP for object-level recovery when possible, and remember that object names must be available for restoration. Time Travel is useful for querying or recreating historical data, but it is not the most direct solution when full object restoration via UNDROP is available.
- A. Incorrect.
Incorrect. UNDROP restores a dropped object only if no current object with the same name exists in the same schema. Snowflake does not merge the dropped table with an existing table of the same name. A common misconception is that Time Travel and UNDROP behave like record-level recovery, but UNDROP restores the dropped object definition and data as an object, not by merging contents.
- B. Correct.
Correct. Because the table name sales.prod.orders is already in use by the newly created replacement table, the administrator must first free that name, typically by renaming the current table. After that, UNDROP TABLE sales.prod.orders can restore the original dropped table, provided it is still within the retention period and has not been purged from Fail-safe. This approach preserves the new table while allowing the original object to be recovered quickly.
- C. Incorrect.
Incorrect. Time Travel can be used to query historical data or recreate data from an earlier point, but the statement is wrong because UNDROP can restore the original object as long as the conflicting current object name is removed first. Saying UNDROP cannot restore objects when the name has been reused is too broad and factually incorrect. The practical issue is the current naming conflict, not a permanent inability to restore.
- D. Incorrect.
Incorrect. Cloning the current table to a backup name may preserve the new test table's contents, but the rest of the option is incorrect because Snowflake does not allow two tables with the same name in the same schema, nor does it automatically version both tables under the same name. The administrator must explicitly rename or otherwise remove the naming conflict before using UNDROP.