Who owns the files? Managed vs external Delta tables, Hive metastore and Unity Catalog on Azure Databricks

Every table in Databricks has two parts:

• Data files: Parquet files plus the Delta transaction log (_delta_log/) in cloud storage.
• Metadata: the table name, schema, location and permissions in a metastore.

Whether a table is managed or external decides who owns the files. Whether it lives in the Hive metastore or Unity Catalog decides how it is governed.

#Delta Lake in one paragraph

Delta adds a transaction log to Parquet. That gives ACID writes, MERGE/UPDATE/DELETE, schema enforcement, time travel (VERSION AS OF) and data skipping statistics. It is the default table format in Databricks: CREATE TABLE t (...) creates a Delta table.

DESCRIBE DETAIL main.sales.orders;          -- format, location, numFiles, sizeInBytes, properties
DESCRIBE HISTORY main.sales.orders;         -- every commit: who, when, operation
DESCRIBE EXTENDED main.sales.orders;        -- Type: MANAGED or EXTERNAL
SELECT * FROM main.sales.orders VERSION AS OF 42;

#Hive metastore vs Unity Catalog

#Managed vs external tables

-- managed (Unity Catalog decides the path)
CREATE TABLE main.sales.orders (order_id BIGINT, amount DECIMAL(18,2), order_ts TIMESTAMP);

-- external (you own the path; needs CREATE EXTERNAL TABLE on the external location)
CREATE TABLE main.sales.orders_ext (order_id BIGINT, amount DECIMAL(18,2), order_ts TIMESTAMP)
LOCATION 'abfss://curated@stlakeprod.dfs.core.windows.net/sales/orders';

#Where managed tables are stored

Unity Catalog uses the most specific managed location that is set:

1. Schema managed location, if set.
2. Otherwise the catalog managed location.
3. Otherwise the metastore root (avoid relying on this).

CREATE CATALOG sales MANAGED LOCATION 'abfss://sales@stlakeprod.dfs.core.windows.net/managed';
CREATE SCHEMA sales.curated MANAGED LOCATION 'abfss://sales@stlakeprod.dfs.core.windows.net/curated';

Give each domain or environment its own managed location (container or storage account) for isolation and cost tracking.

#What DROP really does

• UC managed table: the table disappears at once. UNDROP TABLE main.sales.orders can bring it back within a retention window (7 days by default). After that, the files are deleted.
• UC external table: only the metadata goes. Re-create it with CREATE TABLE ... LOCATION on the same path to get it back.
• Hive metastore managed table: data is deleted immediately, with no UNDROP. Rely on storage soft delete.

Warning

Never point two tables at the same path, and never put an external table inside a managed location. Unity Catalog blocks overlapping paths for good reason: one DROP or VACUUM corrupts the other table.

#Converting to Delta

-- in place: Parquet directory to Delta (writes a _delta_log next to the files)
CONVERT TO DELTA parquet.`abfss://raw@stlakeprod.dfs.core.windows.net/legacy/events`;

-- or copy into a new managed Delta table
CREATE TABLE main.raw.events AS SELECT * FROM parquet.`abfss://raw@stlakeprod.dfs.core.windows.net/legacy/events`;

CSV and JSON have no in-place conversion. Read them and write a Delta table.

#Upgrading Hive metastore tables to Unity Catalog

-- preview, then run
SYNC SCHEMA main.legacy_sales FROM hive_metastore.sales DRY RUN;
SYNC SCHEMA main.legacy_sales FROM hive_metastore.sales;

-- managed HMS table: copy to a UC managed table
CREATE TABLE main.sales.customers DEEP CLONE hive_metastore.sales.customers;

After upgrading:

• Repoint jobs and notebooks to three-level names.
• Set the workspace's default catalog so two-level names resolve to UC.
• Make the HMS tables read-only, then retire them.

Newer runtimes can also convert a UC external table to managed in place (ALTER TABLE ... SET MANAGED). Check that your runtime supports it.

#Quick reference

SHOW CATALOGS;  SHOW SCHEMAS IN main;  SHOW TABLES IN main.sales;
SELECT table_name, table_type, data_source_format, storage_path
FROM main.information_schema.tables WHERE table_schema = 'sales';
ALTER TABLE main.sales.orders SET OWNER TO `sales-data-owners`;
ALTER TABLE main.sales.orders SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 14 days');

Tip

Rule of thumb: managed tables in Unity Catalog unless you have a concrete reason. External tables are for files you must keep at a known path, or share with tools outside Databricks.