Your DROP TABLE deleted the data, not just the metadata

DROP TABLE does not mean the same thing twice

On a Unity Catalog MANAGED table, DROP TABLE marks the underlying data files for deletion: the default UNDROP recovery window is 7 days, followed by an asynchronous purge. On an EXTERNAL table, it only unregisters catalog metadata — the files remain in your storage and become your cleanup problem.

Check the type before you drop with DESCRIBE EXTENDED and its Type row, or query table_type from system.information_schema.tables.

The incident pattern

Illustrative scenario: a routine cleanup job drops a managed table assuming drop means unregister. The table vanishes from the catalog and its files are marked for deletion. An external table dropped in the same job leaves every file in place — re-registering over the same LOCATION brings all rows back; recreating the managed table brings back zero rows.

The recovery runbook

1. Find the original drop: SHOW TABLES DROPPED IN <schema>; and note its table_id.
2. Recover it: if the name is free, UNDROP TABLE <catalog>.<schema>.<table>; recovers the most recent drop of that name. If the name is taken because you recreated it, rename or drop the imposter first, or target precisely with UNDROP TABLE WITH ID '<table_id>';
3. Validate and repair metadata: verify the rows, then recreate PRIMARY KEY / FOREIGN KEY constraints manually — UNDROP does not restore them.
4. Know the window: 7 days by default, configurable per catalog or schema; 0 disables recovery and the supported range is 7–30 days in Public Preview. After it, the asynchronous purge deletes the files.

Reproducible example

Setup — 0. Sandbox schema

CREATE SCHEMA IF NOT EXISTS drop_lab;

1. Create one managed table and one external table

The managed table has no LOCATION, so Unity Catalog assigns its storage. For the external table, replace s3://<bucket-path> with a path under your registered external location.

CREATE OR REPLACE TABLE drop_lab.managed_demo (id INT, v STRING) USING DELTA;
INSERT OVERWRITE drop_lab.managed_demo VALUES (1, 'a'), (2, 'b');

CREATE OR REPLACE TABLE drop_lab.external_demo (id INT, v STRING)
USING DELTA
LOCATION 's3://<bucket-path>/drop_lab/external_demo';
INSERT OVERWRITE drop_lab.external_demo VALUES (1, 'a'), (2, 'b');

Note: INSERT OVERWRITE is safe here because each table was just created empty by CREATE OR REPLACE; it keeps the setup idempotent on rerun. See Issue 012 for why INSERT OVERWRITE without a PARTITION clause is dangerous on tables that already hold data.

2. Gate — check the type before you drop

Script this into every cleanup job.

SELECT table_name, table_type
FROM system.information_schema.tables
WHERE table_schema = 'drop_lab'
  AND table_name IN ('managed_demo', 'external_demo');

3. The incident — drop both tables

DROP TABLE drop_lab.managed_demo;
DROP TABLE drop_lab.external_demo;

4a. External recovery — re-register the same location

The files never moved.

CREATE TABLE drop_lab.external_demo (id INT, v STRING)
USING DELTA
LOCATION 's3://<bucket-path>/drop_lab/external_demo';
SELECT * FROM drop_lab.external_demo ORDER BY id;

4b. Managed non-recovery — a fresh table is empty

A new table has no link to the dropped files.

CREATE TABLE drop_lab.managed_demo (id INT, v STRING) USING DELTA;
SELECT * FROM drop_lab.managed_demo;

5. The UNDROP runbook — inside the default 7-day period

DROP TABLE drop_lab.managed_demo;  -- remove the empty shell so the name is free

SHOW TABLES DROPPED IN drop_lab;   -- note the table_id of the ORIGINAL

UNDROP TABLE WITH ID '<table_id_of_original_managed_demo>';
SELECT * FROM drop_lab.managed_demo ORDER BY id;

Validation gates that catch the regression

Gate 1 — no managed table may be dropped by automation

SELECT COUNT(*) AS managed_drops_blocked
FROM system.information_schema.tables
WHERE table_schema = 'drop_lab'
  AND table_type = 'MANAGED';

The gate passes only when every table the job may drop is EXTERNAL (count = 0), or the drop list is an explicit allowlist reviewed by a human.

Gate 2 — the recovery window is actually on

DESCRIBE SCHEMA EXTENDED drop_lab;

Check the Recovery Period Hours row: 0 means UNDROP is disabled for this schema.

1. Gate every DROP: query table_type from information_schema; block MANAGED drops or require an explicit allowlist.
2. Rewrite atomically: pipelines use CREATE OR REPLACE TABLE, never DROP + CREATE. It keeps identity and history, remains RESTORE-able, and is the documentation's explicit recommendation.
3. Default to managed: Databricks recommends managed tables for automatic optimization, lower cost, and the UNDROP safety net. Use external only for existing data, non-Databricks readers, or compliance-pinned paths — and own the orphaned-file cleanup.
4. Set retention deliberately: configure RETAIN DROPPED TO per catalog or schema. Never treat UNDROP as backup: the window is finite, purge follows, and constraints are lost.

Use this when

1. You run cleanup or teardown automation that issues DROP TABLE — gate it on table_type first.
2. Someone already dropped a managed table and you are inside the recovery window — SHOW TABLES DROPPED, then UNDROP.
3. You are choosing table types for new pipelines — managed by default; external for existing data, non-Databricks readers, or compliance-pinned paths.
4. You are setting data-protection policy per catalog or schema — configure RETAIN DROPPED deliberately; 0 disables recovery.

Avoid this when

1. You are tempted to treat UNDROP as backup — the window is finite, configurable down to 0, the asynchronous purge follows, and constraints are lost.
2. The table lives in the Hive metastore — no UNDROP exists there; a managed drop deletes data immediately.
3. You need the pre-drop PK/FK constraints back automatically — they are not restored; plan the manual recreation.
4. You would rather drop and recreate a table name inside a running pipeline — use CREATE OR REPLACE TABLE; the drop/recreate pattern disrupts concurrent readers.