Managed versus external tables in Unity Catalog, and converting between them

Unity Catalog supports managed and external tables, differing in who owns the underlying data lifecycle. Managed tables store data in the Unity Catalog managed storage location and are dropped along with their data, while external tables reference data at an existing cloud storage path via a location, and dropping them only removes the metadata.

1 · Learn the must-know

• 1Managed tables store data under the metastore/catalog/schema managed storage location; DROP TABLE deletes both metadata and underlying data files.
• 2External tables require an external location and storage credential registered in Unity Catalog, and DROP TABLE removes only the table metadata, leaving data files intact.
• 3CREATE TABLE without a LOCATION clause creates a managed table; specifying LOCATION 'path' creates an external table.
• 4You cannot directly convert a managed table to external (or vice versa) in place; the common approach is CREATE TABLE ... AS SELECT (CTAS) or CREATE TABLE ... LOCATION to copy/rebuild data into the desired table type.
• 5ALTER TABLE can rename a table or change properties/comments and column definitions, but ownership, location type, and storage credential associations are set at creation and not simply toggled via ALTER.
• 6Managed tables benefit fully from Unity Catalog governance features like Predictive Optimization and automatic file layout optimization, whereas external tables are typically used for data shared across platforms or requiring specific storage locations/lifecycle control outside Databricks.

2 · Check your understanding