Managed and External Tables in Delta Lake

Every Delta table in Databricks is either managed, meaning the platform owns both the metadata and the files, or external, meaning the metastore only records metadata while the files live at a storage path you control. The distinction decides what happens on DROP TABLE, who can write to the files, which optimisations Databricks applies automatically, and how easily other engines can read the data. The sections below show where each kind of table lives under Unity Catalog, how to create and convert them, and how to choose.

1. Managed Table

Storage Location: Delta Lake automatically manages both the data and metadata. The data is stored in a location controlled by the Delta Lake system.
Lifecycle Management: When you create a managed table, Delta Lake determines the storage location, typically inside the database directory. When the table is dropped, both the data and metadata are deleted automatically.
Use Case: Suitable for cases where you want Delta Lake to manage the lifecycle of the data, including cleaning up when the table is no longer needed.

2. External Table

Storage Location: The data resides in a location outside of Delta Lake's control, often specified by the user (e.g., an S3 or HDFS path). Delta Lake only manages the metadata, not the physical storage of the data.
Lifecycle Management: When an unmanaged table is dropped, only the metadata is removed, but the data remains intact at its specified storage location.
Use Case: Ideal when you want to retain control over the data storage location, or the data is shared with other systems.

3. Summary

Managed Table: Delta Lake controls both the data and metadata, and automatically cleans up data when the table is dropped.

External Table: Delta Lake only controls the metadata, and the user manages the data location. Dropping the table doesn't remove the data itself.

4. Where the files live

In Unity Catalog, a managed table's files are written under a managed storage location configured at the metastore, catalog or schema level, in a directory whose name is generated by Unity Catalog. Users are not expected to access those paths directly; all reads and writes go through the table name, so permissions are enforced by Unity Catalog grants. An external table is created with an explicit LOCATION that must fall inside a registered external location, which pairs a cloud path with a storage credential (an IAM role, managed identity or service account). In the legacy Hive metastore the same split exists, with managed tables placed under the database's warehouse directory.

5. Lifecycle: what DROP does

Dropping a managed table removes the table from the catalog and deletes its data. In Unity Catalog the underlying files are cleaned up after a retention period, and a recently dropped managed table can be recovered with UNDROP TABLE within that window. Dropping an external table removes only the metadata; the Delta files and their _delta_log stay where they were and can be re-registered with another CREATE TABLE ... LOCATION. This is the main safety argument for external tables over shared or regulated data, and the main hygiene argument for managed tables, which do not leave orphaned files behind.

6. Worked example: DDL

-- managed table: no LOCATION
CREATE TABLE main.sales.orders (
    order_id   BIGINT,
    customer   STRING,
    amount     DECIMAL(10,2),
    order_date DATE
);

-- external table: LOCATION inside a registered external location
CREATE TABLE main.sales.orders_ext (
    order_id   BIGINT,
    customer   STRING,
    amount     DECIMAL(10,2),
    order_date DATE
)
LOCATION 's3://acme-lake/sales/orders';

-- which kind is it?
DESCRIBE EXTENDED main.sales.orders_ext;      -- Type: EXTERNAL, Location: s3://...

-- register existing Delta files as an external table
CREATE TABLE main.sales.legacy USING DELTA LOCATION 's3://acme-lake/legacy/orders';

-- copy an external table into a managed one
CREATE TABLE main.sales.orders_managed AS SELECT * FROM main.sales.orders_ext;

# PySpark equivalents
df.write.saveAsTable("main.sales.orders")                              # managed
df.write.option("path", "s3://acme-lake/sales/orders").saveAsTable("main.sales.orders_ext")  # external

Databricks has also added an in-place conversion (ALTER TABLE ... SET MANAGED) for moving Unity Catalog external tables to managed; check availability in your workspace before relying on it, otherwise use CTAS or DEEP CLONE.

7. When to use which

Concern	Managed	External
Who owns the files	Databricks / Unity Catalog	You (bucket, path, lifecycle rules)
DROP TABLE	Deletes data (recoverable for a period in UC)	Keeps data
Automatic optimisation	Eligible for predictive optimisation and automatic liquid clustering features	Run OPTIMIZE / VACUUM yourself
Access from other engines	Through Unity Catalog APIs and credential vending	Directly from the path, if the engine reads Delta
Typical use	Default for new tables built in Databricks	Data produced or consumed outside Databricks, migrations, shared landing zones

Databricks recommends managed tables as the default for anything created inside the platform. External tables remain the right tool when files must stay at a specific path for other systems, when data predates the workspace, or when organisational policy requires that dropping a catalog object never deletes data.

8. Pitfalls

Writing to an external path behind the catalog's back. Another tool rewriting files without the Delta protocol corrupts the table; external writers must use a Delta-aware library.
Two tables on one path. Registering the same LOCATION twice, or nesting one table's path under another's, is rejected in Unity Catalog and causes confusing behaviour in the Hive metastore.
VACUUM on external tables deletes unreferenced files permanently; a short retention can break time travel and concurrent readers.
Assuming DROP is safe. On managed tables it is not a metadata-only operation; confirm the table type with DESCRIBE EXTENDED before dropping.