Recovering dropped tables in Azure Databricks with UNDROP TABLE

Oops, Dropped the Wrong Table? What now?

We’ve all been there: you’re cleaning up some old stuff in Databricks, run a quick DROP TABLE… and suddenly realize it was the production sales table. Heart stops for a second, right?

Good news: if you’re using Unity Catalog in Azure Databricks, the UNDROP command is basically an “undo” button that works for 7 days after a drop. It’s a soft-delete safety net that saves a lot of headaches.

What Exactly Can You Bring Back?

Regular tables (managed or external) that live in Unity Catalog.
Materialized views – but only if they were created through an ETL pipeline (like Lakeflow or DLT). Ones you made directly in Databricks SQL can’t come back this way.

Temporary tables? Sorry, no recovery there. Streaming tables follow the same rules as materialized views: the pipeline that created them still needs to exist.

When you undrop something, you get back the metadata: columns, properties, privileges, and the fact that it’s registered in the catalog/schema. What doesn’t come back automatically:

Primary/foreign key constraints (you’ll have to add them again with ALTER TABLE … ADD CONSTRAINT).
Ownership (it goes back to whoever owned it before; change it if needed with ALTER … OWNER TO).

For external tables, make sure the storage location and credential are still around, and you have the right CREATE EXTERNAL TABLE permissions.

Super Simple Ways to Use It

Let’s prepare some sample data:

%sql
USE CATALOG raw_dev; 
USE SCHEMA default;

CREATE TABLE IF NOT EXISTS customer_orders (
    order_id          BIGINT,
    customer_id       STRING,
    order_date        DATE,
    order_status      STRING,
    total_amount      DECIMAL(12,2),
    shipping_country  STRING,
    is_expedited      BOOLEAN,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP
);

INSERT INTO customer_orders
    (order_id, customer_id, order_date, order_status, total_amount, shipping_country, is_expedited, created_at, updated_at)
VALUES
    (10024581, 'CUST-8372', DATE '2026-02-03', 'COMPLETED',  342.50, 'Germany',     TRUE,  TIMESTAMP '2026-02-03 14:22:19', TIMESTAMP '2026-02-03 14:35:40'),
    (10024582, 'CUST-1249', DATE '2026-02-04', 'PROCESSING', 189.99, 'France',      FALSE, TIMESTAMP '2026-02-04 09:15:00', TIMESTAMP '2026-02-04 09:15:00'),
    (10024583, 'CUST-5610', DATE '2026-02-07', 'COMPLETED', 1249.00, 'Netherlands', TRUE,  TIMESTAMP '2026-02-07 16:40:12', TIMESTAMP '2026-02-08 08:10:55'),
    (10024584, 'CUST-2991', DATE '2026-02-08', 'SHIPPED',     78.25, 'Belgium',     FALSE, TIMESTAMP '2026-02-08 11:05:33', TIMESTAMP '2026-02-08 14:20:01'),
    (10024585, 'CUST-8372', DATE '2026-02-09', 'COMPLETED',  549.95, 'Germany',     TRUE,  TIMESTAMP '2026-02-09 13:50:27', TIMESTAMP '2026-02-09 13:50:27');

Check if data is there:

SELECT 
    order_id,
    customer_id,
    order_date,
    order_status,
    total_amount,
    shipping_country,
    is_expedited,
    created_at,
    updated_at
FROM customer_orders

Now let’s drop the table:

DROP TABLE customer_orders

Now we can list all the tables that have been dropped using SHOW TABLES DROPPED:

To recover it most of the time you can just do this:

UNDROP TABLE raw_dev.default.customer_order;

It grabs the most recent drop of that table that you owned. Easy.

Table is restored and ready to use. But if there’s already a table with the same name (maybe you recreated it in panic mode), rename the current one first:

ALTER TABLE my_catalog.my_schema.important_sales_data RENAME TO important_sales_data_backup;
UNDROP TABLE my_catalog.my_schema.important_sales_data;

Same trick works great for tables when you have name collisions.

How Do You Find What’s Still Recoverable?

As I showed you previously – just run this handy command:

SHOW TABLES DROPPED IN my_schema;

Or leave out the schema to see everything. You’ll get a nice list showing:

table name
unique table ID (UUID)
type (TABLE or MATERIALIZED VIEW)
when it was dropped
who owned it

Grab the table ID from there whenever you need to be specific (especially for materialized views).

Who Can Actually Run UNDROP?

You need decent permissions-usually one of these:

You owned the table + have CREATE TABLE on the schema + USE on schema and catalog.
You own the schema (with catalog USE).
You own the catalog or even the whole metastore.
You have MANAGE on the table + the usual schema/catalog access.

For external tables there are a couple extra permission checks, but that’s about it.

Why This Little Feature Is Actually Pretty Great

Accidental drops happen more often than anyone likes to admit-especially during migrations, experiments, or late-night cleanups. That 7-day window means you can breathe, check SHOW TABLES DROPPED, and fix things without rebuilding everything from scratch or digging through backups.

A few quick tips from folks who’ve used it:

Check the dropped list right away when something feels off.
Keep a note of really important table IDs/names somewhere safe.
Remind the team that DROP isn’t always forever in Unity Catalog (but don’t rely on it instead of good change controls!).
For materialized views, watch out for pipeline deletions-they block recovery.

It’s not magic, and it won’t save you from a CASCADE drop gone wrong, but UNDROP is one of those quiet Databricks features (available since Runtime 12.2 LTS, with better materialized view support later) that makes daily life noticeably less stressful.

Next time you (or someone on your team) has that “I just dropped the wrong thing” moment, point them here. It usually turns a potential risky situation into a quick undo and a coffee break. 😊