Problem

You dropped and recreated a new Delta table, resulting in the loss of table versions and history. You want to recover the lost data with its history. However since there is another table with the same name, the UNDROP method does not work and gives you an error message stating that the table already exists.

UnityCatalogServiceException: [RequestId=*******-****-****-***-*********** ErrorClass=TABLE_ALREADY_EXISTS. UNDROP_TABLE_ALREADY_EXISTS] Cannot undrop table because a table with the name catalogname.schemaname.tablename already exists.

Cause

The UNDROP (AWS | Azure | GCP) SQL command does not work since a workspace cannot have two tables with the same name.

Solution

To recover the table history (within a 7 day retention period):

1. Rename the newly created table to a different name to avoid name conflicts.

ALTER TABLE <catalog-name>.<schema-name>.<deleted-table-name> RENAME TO <catalog-name>.<schema-name>.<new-table-name>;

2. Use the UNDROP TABLE command to recover the original table.

UNDROP TABLE <catalog-name>.<schema-name>.<deleted-table-name>;

3. Use DESCRIBE HISTORY to check the history of the recovered table.

DESCRIBE HISTORY <catalog-name>.<schema-name>.<deleted-table-name>;

4. If the new table is already in use, copy the latest data from the new table to the recovered table.

INSERT INTO <catalog-name>.<schema-name>.<deleted-table-name> SELECT * FROM <catalog-name>.<schema-name>.<new-table-name>;