Undrop for recovering dropped objects in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Recovering a dropped table using UNDROP

You dropped a table named sales_data in Snowflake. You want to recover it using the UNDROP command. Which of the following commands will successfully restore the table?

A UNDROP TABLE sales_data;

B UNDROP TABLE sales_data COPY;

C UNDROP TABLE sales_data REPLACE;

D UNDROP TABLE IF EXISTS sales_data;

💡 Hint

Think about the basic syntax to restore a dropped table without additional clauses.

The correct syntax to recover a dropped table is UNDROP TABLE <table_name>;. Options with IF EXISTS, REPLACE, or COPY are invalid in Snowflake's UNDROP command.

🧠 Conceptual

intermediate

Understanding the retention period for undropping objects

After dropping a table in Snowflake, how long is the dropped table available for recovery using UNDROP by default?

A 90 days

B 7 days

C 1 day

D 30 days

💡 Hint

Think about the default data retention period Snowflake provides for dropped objects.

By default, Snowflake retains dropped tables for 7 days, allowing recovery with UNDROP within this period.

❓ security

advanced

Permissions required to undrop a dropped object

Which role or permission is required to successfully execute UNDROP on a dropped table in Snowflake?

A Any role with SELECT privilege on the database

B Only the ACCOUNTADMIN role

C Ownership on the dropped object or the schema

D Only the SYSADMIN role

💡 Hint

Consider who controls the object or schema ownership in Snowflake.

To undrop an object, the user must have ownership on the dropped object or the schema it belonged to. Other roles or privileges alone are insufficient.

❓ Configuration

advanced

Recovering a dropped schema with multiple objects

You dropped a schema named analytics that contained multiple tables and views. Which command will restore the entire schema and all its objects?

A RESTORE SCHEMA analytics;

B UNDROP DATABASE analytics;

C UNDROP SCHEMA analytics REPLACE;

D UNDROP SCHEMA analytics;

💡 Hint

Think about the correct object type to undrop when recovering a schema.

The UNDROP SCHEMA <schema_name>; command restores the dropped schema and all its contained objects. The other options are invalid or refer to different object types.

✅ Best Practice

expert

Handling undrop failures due to name conflicts

You try to undrop a dropped table named customers, but a new table with the same name already exists in the schema. What is the best way to recover the dropped table without losing data?

A Rename or drop the existing table, then run <code>UNDROP TABLE customers;</code>

B Use <code>UNDROP TABLE customers REPLACE;</code> to overwrite the existing table

C Run <code>UNDROP TABLE customers COPY;</code> to create a copy with a new name

D Undrop is not possible if a table with the same name exists; restore from backup instead

💡 Hint

Consider how Snowflake handles object name conflicts during undrop.

Snowflake does not support undropping an object if another object with the same name exists. The best practice is to rename or drop the existing object first, then undrop the dropped object.

Practice

1. What is the primary purpose of the UNDROP command in Snowflake?

easy

A. To recover a dropped table, view, or schema within the retention period

B. To permanently delete a table or schema from Snowflake

C. To create a new table with the same structure as an existing one

D. To rename an existing table or schema

Solution

1. Step 1: Understand the function of UNDROP

   The UNDROP command is designed to restore objects that were accidentally dropped, such as tables, views, or schemas.

2. Step 2: Identify the correct use case

   Since UNDROP recovers dropped objects within the retention period, it is not used for deletion, creation, or renaming.

3. Final Answer:

   To recover a dropped table, view, or schema within the retention period -> Option A

4. Quick Check:

   UNDROP = Recover dropped objects [OK]

Hint: UNDROP means undo drop to restore deleted objects [OK]

Common Mistakes:

• Confusing UNDROP with DELETE or DROP commands
• Thinking UNDROP creates new objects
• Assuming UNDROP renames objects

2. Which of the following is the correct syntax to undrop a table named sales_data in Snowflake?

easy

A. UNDROP TABLE sales_data;

B. RECOVER TABLE sales_data;

C. UNDROP TABLE IF EXISTS sales_data;

D. RESTORE TABLE sales_data;

Solution

1. Step 1: Recall the correct UNDROP syntax

   The correct syntax to recover a dropped table is UNDROP TABLE <table_name>;

2. Step 2: Eliminate incorrect options

   Adding IF EXISTS is invalid with UNDROP. RECOVER and RESTORE are not supported commands for recovering dropped tables in Snowflake.

3. Final Answer:

   UNDROP TABLE sales_data; -> Option A

4. Quick Check:

   UNDROP TABLE = correct syntax [OK]

Hint: Use UNDROP TABLE followed by the table name [OK]

Common Mistakes:

• Using RECOVER or RESTORE instead of UNDROP
• Adding IF EXISTS which is invalid with UNDROP
• Missing the TABLE keyword

3. Consider the following sequence of commands in Snowflake:

DROP TABLE IF EXISTS customers;
UNDROP TABLE customers;

What will be the result of the UNDROP TABLE customers; command assuming the drop happened within the retention period?

medium

A. An error will occur because the table does not exist

B. The command will be ignored silently

C. The table will be restored but without any data

D. The table 'customers' will be restored with its data intact

Solution

1. Step 1: Understand the effect of DROP and UNDROP

   The DROP TABLE command deletes the table but keeps it recoverable within the retention period. UNDROP restores the dropped table with its data.

2. Step 2: Confirm retention period and command behavior

   Since the drop is recent and within retention, UNDROP will successfully restore the table and its data.

3. Final Answer:

   The table 'customers' will be restored with its data intact -> Option D

4. Quick Check:

   UNDROP restores dropped table and data [OK]

Hint: UNDROP restores table and data if within retention [OK]

Common Mistakes:

• Assuming UNDROP only restores structure, not data
• Thinking UNDROP fails if table was dropped
• Believing UNDROP silently ignores missing tables

4. You tried to undrop a dropped schema using UNDROP SCHEMA sales_schema; but received an error. Which of the following is the most likely cause?

medium

A. You used the wrong command; UNDROP does not support schemas

B. The schema was dropped outside the retention period

C. The schema name is case-sensitive and was typed incorrectly

D. You need to run UNDROP with the database name specified

Solution

1. Step 1: Check retention period requirement

   UNDROP only works if the object was dropped within the retention period. If dropped too long ago, recovery fails.

2. Step 2: Verify command support and syntax

   UNDROP supports schemas, and schema names are case-insensitive by default. Database name is not required if context is set.

3. Final Answer:

   The schema was dropped outside the retention period -> Option B

4. Quick Check:

   Retention period exceeded causes UNDROP error [OK]

Hint: Check retention period before undropping schemas [OK]

Common Mistakes:

• Assuming UNDROP can't recover schemas
• Ignoring retention period limits
• Mistyping schema name without checking case sensitivity

5. You accidentally dropped a table orders in your production database. You want to recover it using UNDROP, but you also want to ensure no data loss and minimal downtime. Which of the following steps is the best approach?

hard

A. Wait for the retention period to expire and then restore from a full database backup

B. Recreate the table manually and import data from backups

C. Immediately run UNDROP TABLE orders; within the retention period and verify data integrity

D. Use UNDROP TABLE orders; only after recreating the table structure

Solution

1. Step 1: Use UNDROP immediately after accidental drop

   UNDROP recovers the dropped table with data if done within the retention period, minimizing downtime and data loss.

2. Step 2: Avoid manual recreation or waiting unnecessarily

   Manual recreation or waiting for backup restore increases downtime and risks data loss.

3. Final Answer:

   Immediately run UNDROP TABLE orders; within the retention period and verify data integrity -> Option C

4. Quick Check:

   UNDROP quickly recovers dropped tables with data [OK]

Hint: Run UNDROP immediately after drop to avoid data loss [OK]

Common Mistakes:

• Delaying UNDROP beyond retention period
• Recreating table manually before undropping
• Ignoring data verification after recovery