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.