ADA-C01 Question 561
Single answerRestore dropped objectsA production schema named FINANCE.REPORTING was accidentally dropped 2 hours ago by a role with sufficient privileges. The schema contained several permanent tables, one transient table, and one external table. No objects with the same names have been recreated since the drop. The Snowflake administrator needs to recover as much of the schema as possible as quickly as possible, while preserving the original object structure. Which action should the administrator take?
- A
Run UNDROP SCHEMA FINANCE.REPORTING; to restore the dropped schema and the recoverable child objects within the Time Travel retention period.
- B
Run CREATE SCHEMA FINANCE.REPORTING CLONE FINANCE.REPORTING AT(OFFSET => -7200); to recreate the schema exactly as it existed 2 hours ago, including all external table metadata.
- C
Run UNDROP DATABASE FINANCE; because a dropped schema can only be recovered by restoring its parent database.
- D
Run ALTER SCHEMA FINANCE.REPORTING SET DATA_RETENTION_TIME_IN_DAYS = 1; and then run UNDROP SCHEMA FINANCE.REPORTING; so that the dropped schema becomes eligible for recovery.
Show answer and explanation
Correct answer: A
Explanation
The best action is to use UNDROP SCHEMA on the dropped schema while it is still within the applicable Time Travel retention period. UNDROP is designed specifically for restoring dropped databases, schemas, tables, and certain other supported objects without having to rebuild them manually. In this scenario, it is the quickest way to recover the original object hierarchy because the schema name has not been reused.
Candidates should also understand the limits of restore operations. Permanent and transient tables can generally be recovered with UNDROP if they are still within retention rules applicable to those object types, but external tables are not recovered through Time Travel/UNDROP in the same way because their metadata handling differs from native Snowflake table storage. This makes option 1 the only accurate and practical choice.
Relevant Snowflake documentation topics include Time Travel, UNDROP
- A. Correct.
Correct. If a schema was dropped and is still within its Time Travel retention period, UNDROP SCHEMA can restore the schema itself and the recoverable dropped child objects it contained, provided no conflicting object with the same name has been created. This is the fastest way to restore the original structure. However, only supported/recoverable objects return; external tables are not restored by UNDROP because their metadata is not protected by Time Travel in the same way as standard Snowflake-managed objects.
- B. Incorrect.
Incorrect. Cloning at a historical point is a valid recovery technique in some scenarios, but this statement is inaccurate for two reasons. First, once the schema has been dropped, referencing it directly as a clone source in this way is not the standard recovery method; UNDROP is the intended mechanism for restoring a dropped schema. Second, external table metadata is not restored via Time Travel/clone as implied here. This option overstates what clone can recover.
- C. Incorrect.
Incorrect. A dropped schema does not require the parent database to be restored if the database itself still exists. UNDROP SCHEMA is supported independently when the schema is within retention and the parent database is present. This distractor reflects a common misconception that restore operations must occur only at the highest container level.
- D. Incorrect.
Incorrect. Changing DATA_RETENTION_TIME_IN_DAYS after the schema has already been dropped does not retroactively make that dropped object recoverable. Retention must already have been in effect before the drop. Also, because the schema is dropped, ALTER SCHEMA cannot be used on it in the normal way to enable recovery.