Undrop for recovering dropped objects in Snowflake - Step-by-Step Execution

Process Flow - Undrop for recovering dropped objects

Object Dropped

↓

Check Time Travel Retention

Yes↓

Use UNDROP Command

↓

Object Restored

↓

Verify Object Exists

↓

END

This flow shows how a dropped object can be recovered using the UNDROP command if it is within the time travel retention period.

Execution Sample

Snowflake

DROP TABLE my_table;
UNDROP TABLE my_table;

Drops a table and then recovers it using UNDROP within the retention period.

Process Table

Step	Action	Object State	Result
1	DROP TABLE my_table;	my_table dropped	Table is marked as dropped, available in Time Travel
2	Check retention period	my_table in Time Travel	Object can be recovered
3	UNDROP TABLE my_table;	my_table restored	Table is restored and accessible again
4	Verify table existence	my_table exists	Table is available for queries
5	Time Travel retention expired	my_table permanently deleted	Cannot recover after retention period

💡 Execution stops when the object is restored or retention period expires making recovery impossible.

Status Tracker

Variable	Start	After Drop	After Undrop	Final
my_table state	exists	dropped (in Time Travel)	restored	exists

Key Moments - 2 Insights

Why can't I undrop an object after a long time?

Because the Time Travel retention period has expired, as shown in execution_table row 5, the object is permanently deleted and cannot be recovered.

Does UNDROP create a new object or restore the original?

UNDROP restores the original object with its data and metadata intact, as shown in execution_table row 3.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of 'my_table' immediately after the DROP command?

A my_table is permanently deleted

B my_table is dropped but available in Time Travel

C my_table exists and is accessible

D my_table is restored

💡 Hint

Refer to execution_table row 1 for the state after DROP.

At which step does the table become accessible again after being dropped?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Check execution_table rows 3 and 4 to see when the table is restored and verified.

If the retention period expires, what happens to the dropped object?

A It can still be undropped

B It is archived indefinitely

C It is permanently deleted and cannot be recovered

D It is automatically restored

💡 Hint

See execution_table row 5 for the effect of retention expiration.

Concept Snapshot

UNDROP recovers dropped objects within Time Travel retention.
Syntax: UNDROP <object_type> <object_name>;
Dropped objects remain recoverable until retention expires.
After retention, objects are permanently deleted.
Use UNDROP to restore original object state and data.

Full Transcript

When you drop an object like a table in Snowflake, it is not immediately deleted. Instead, it is kept in a special state called Time Travel for a set retention period. During this time, you can recover the object using the UNDROP command. The process starts by dropping the object, which marks it as dropped but still recoverable. You then check if the retention period is still valid. If yes, you run UNDROP to restore the object to its original state. Finally, you verify that the object exists again and is accessible. If the retention period expires, the object is permanently deleted and cannot be recovered. This flow helps prevent accidental data loss by allowing recovery within a limited time.

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