UNDROP TABLE

Using the UNDROP TABLE statement, you can restore deleted tables, dynamic tables, and materialized views. Whether a deleted object can be restored depends on the table's data retention period.

Data Retention Period

Whether an object can be restored depends on the data retention period (data_retention_days). The default retention period in Lakehouse is 1 day (24 hours). You can adjust the retention period using the ALTER TABLE command; the valid range is 0–90 days.

-- View the current retention period SHOW CREATE TABLE my_table; -- Set the retention period to 7 days ALTER TABLE my_table SET PROPERTIES ('data_retention_days'='7');

⚠️ Note: Extending the retention period increases storage costs. Lakehouse charges separately for storage generated by Time Travel.

Supported Object Types

UNDROP TABLE can restore the following types of objects:

Deleted Object	Drop Command	Restore Command
Regular table (TABLE)	`DROP TABLE`	`UNDROP TABLE`
Dynamic table (DYNAMIC TABLE)	`DROP DYNAMIC TABLE`	`UNDROP TABLE`
Materialized view (MATERIALIZED VIEW)	`DROP MATERIALIZED VIEW`	`UNDROP TABLE`
Table Stream	`DROP TABLE STREAM`	`UNDROP TABLE`

⚠️ Note: Regardless of the type of table object that was deleted, the UNDROP TABLE syntax is always used to restore it.

Syntax

UNDROP TABLE [schema_name.]<table_name>

Parameter Description

Parameter	Description
`schema_name`	Optional. Specifies the schema name.
`table_name`	The name of the deleted table, dynamic table, or materialized view.

Examples

Example 1: Restore a Table Immediately After Deletion

-- Create table CREATE TABLE mytable(id INT, name STRING); INSERT INTO mytable VALUES(1, 'aaa'); -- Drop table DROP TABLE mytable; -- View table deletion records SHOW TABLES HISTORY; +---------------+------------+-------------------------+----------+------+-------+---------+----------------+-------------------------+ | schema_name | table_name | create_time | creator | rows | bytes | comment | retention_time | delete_time | +---------------+------------+-------------------------+----------+------+-------+---------+----------------+-------------------------+ | my_schema | mytable | 2023-06-06 12:22:57.642 | UAT_TEST | 1 | 1348 | | 1 | 2023-07-18 17:49:26.374 | +---------------+------------+-------------------------+----------+------+-------+---------+----------------+-------------------------+ -- Restore table UNDROP TABLE mytable; -- Verify the restore SELECT * FROM mytable; +----+------+ | id | name | +----+------+ | 1 | aaa | +----+------+

Example 2: Restore a Table After a Same-Name Table Was Created

If a table with the same name was created after the original was dropped, rename the new table first before restoring the old one:

-- Create and drop the table CREATE TABLE mytable(id INT, name STRING); INSERT INTO mytable VALUES(1, 'aaa'); DROP TABLE mytable; -- Create a new table with the same name CREATE TABLE mytable(col1 INT, col2 STRING); -- Rename the new table ALTER TABLE mytable RENAME TO mytable_backup; -- Restore the old table UNDROP TABLE mytable;

Example 3: Restore a Dynamic Table

-- Create base table CREATE TABLE dy_base_a (i INT, j INT); INSERT INTO dy_base_a VALUES (1, 10), (2, 20), (3, 30), (4, 40); -- Create dynamic table CREATE DYNAMIC TABLE change_table (i, j) AS SELECT * FROM dy_base_a; REFRESH DYNAMIC TABLE change_table; -- Query data SELECT * FROM change_table; +---+----+ | i | j | +---+----+ | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 40 | +---+----+ -- Drop and restore the dynamic table DROP DYNAMIC TABLE change_table; UNDROP TABLE change_table; -- Verify the restore (dynamic table properties are preserved) SELECT * FROM change_table;

Example 4: Restore a Materialized View

-- Create base table CREATE TABLE mv_base_a (i INT, j INT); INSERT INTO mv_base_a VALUES (1, 10), (2, 20), (3, 30), (4, 40); -- Create materialized view CREATE MATERIALIZED VIEW mv1 (i, j) AS SELECT * FROM mv_base_a; REFRESH MATERIALIZED VIEW mv1; -- Drop the materialized view DROP MATERIALIZED VIEW mv1; -- View deletion records SHOW TABLES HISTORY WHERE table_name = 'mv1'; -- Restore the materialized view UNDROP TABLE mv1; -- Verify the restore SELECT * FROM mv1;

Notes

Restore within the retention period: The restore operation must be performed within the data_retention_days retention period. Once the period expires, data is physically deleted and cannot be recovered.
Same-name object conflict: Ensure no object with the same name exists before restoring. If one does, rename or drop it first.
Properties preserved after restore:
- Regular tables retain their original schema, data, and properties (including data_retention_days, data_lifecycle, etc.)
- Dynamic tables retain their original refresh configuration (REFRESH INTERVAL, VCLUSTER, etc.)
- Materialized views retain their original query definition
Objects that cannot be restored: Regular views (VIEW), external tables (EXTERNAL TABLE), indexes, functions, connections, and similar objects cannot be restored after deletion.