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.