How to Recover a Dropped Table in Snowflake Quickly
To recover a dropped table in Snowflake, use the
UNDROP TABLE <table_name> command within the Time Travel retention period. This command restores the table to its state before it was dropped.Syntax
The UNDROP TABLE command restores a dropped table if it is within the Time Travel retention period. You need to specify the exact table name that was dropped.
UNDROP TABLE <table_name>: Recovers the dropped table.
sql
UNDROP TABLE my_table;Example
This example shows how to drop a table and then recover it using UNDROP TABLE. It demonstrates the recovery of the table within the allowed Time Travel period.
sql
CREATE OR REPLACE TABLE my_table (id INT, name STRING); INSERT INTO my_table VALUES (1, 'Alice'), (2, 'Bob'); -- Drop the table DROP TABLE my_table; -- Recover the dropped table UNDROP TABLE my_table; -- Query the recovered table SELECT * FROM my_table;
Output
ID | NAME
---|-------
1 | Alice
2 | Bob
Common Pitfalls
Common mistakes when recovering dropped tables include:
- Trying to undrop a table after the Time Travel retention period has expired (default 1 day).
- Using incorrect table names or schema references.
- Not having sufficient privileges to undrop the table.
Always check your Time Travel retention settings and confirm the table name before running UNDROP TABLE.
sql
/* Wrong: Trying to undrop a table after retention period */ UNDROP TABLE old_table; -- Fails if retention expired /* Right: Undrop within retention period */ UNDROP TABLE recent_table;
Quick Reference
| Command | Description |
|---|---|
| UNDROP TABLE | Restores a dropped table within Time Travel period |
| DROP TABLE | Deletes a table but allows recovery within retention |
| SHOW TABLES | Lists tables to verify existence or recovery |
| SELECT * FROM | Query data from the recovered table |
Key Takeaways
Use UNDROP TABLE to recover dropped tables within Snowflake's Time Travel retention period.
The default Time Travel retention period is 1 day but can be configured per table or account.
Ensure you have the correct table name and privileges before running UNDROP TABLE.
Dropped tables cannot be recovered after the Time Travel retention period expires.
Verify table recovery by querying the table after undropping.
