Managed vs External Tables in Unity Catalog: Storage, Formats, Create and Drop

1.Governance vs storage: what "managed" actually controls

Every table you register in Unity Catalog is centrally governed. Unity Catalog holds its metadata and controls access, auditing and lineage. That is true for every registered object. The managed/external split comes from a second question: who controls the data files themselves?

For a managed table, Unity Catalog decides where the files go and handles their lifecycle: how they are organized, how they are optimized and when they are deleted. It stores them in the managed storage location defined on the containing schema, catalog or metastore. For an external table, Unity Catalog still governs access, but you set the storage path and you control the file lifecycle. In both cases the files stay in your own cloud account. A managed table does not hand your data over to Databricks.

The managed/external distinction applies only to tables and volumes. Views, models and functions have no managed or external variants.

Volumes follow the same rule as tables. A managed volume has its storage location and file lifecycle controlled by Unity Catalog, and when it is dropped its data files are deleted after a retention period. An external volume is one whose location you set, for example a subpath of an external location, and when it is dropped its data files remain in place.

Sources1

2.Formats, optimizations and when to choose each type

Managed tables are the default and recommended type. They use Delta Lake or Apache Iceberg. They get automatic optimization and maintenance, and external clients can reach them through open APIs. The Unity REST API serves Delta clients, and the Iceberg REST Catalog serves Iceberg clients. Both use credential vending, which hands out temporary, scoped credentials that carry the requesting principal's privileges. All reads and writes must use the table name. Path-based access to a managed table is not supported (except in Compatibility Mode), because it would bypass Unity Catalog access controls.

External tables accept a wider range of formats: Delta Lake, CSV, JSON, AVRO, PARQUET, ORC and TEXT. Databricks still recommends Delta for them because it has transactional guarantees. Databricks recommends external tables in two cases. The first is registering existing data that a managed table can't hold, such as JSON or Avro. The second is giving direct access to non-Databricks clients that can't use the other access patterns. That second case has a governance cost: Unity Catalog privileges are not enforced when someone reads the files directly from an external system.

Sources21345

3.Creating managed and external tables

The SQL for each type differs by one clause. A managed table needs only a three-level name and a column list. Unity Catalog chooses the storage path. To create one, you need USE CATALOG on the parent catalog, plus USE SCHEMA and CREATE TABLE on the parent schema.

-- Create a managed Delta table
CREATE TABLE <catalog-name>.<schema-name>.<table-name>
(
  <column-specification>
);

-- Create a managed table using Iceberg
CREATE TABLE <catalog-name>.<schema-name>.<table-name>
(
  <column-specification>
)
USING iceberg;

An external table must name its storage location, and that path has to sit inside an external location already registered in Unity Catalog. So you configure the external location first. On top of the three privileges a managed table needs, you also need CREATE EXTERNAL TABLE on the external location that covers the path. Use a separate directory for each table.

CREATE TABLE <catalog>.<schema>.<table-name>
(
  <column-name> <data-type>
)
LOCATION 's3://<bucket-path>/<table-directory>';

The DataFrame API follows the same pattern. Calling saveAsTable on its own creates a managed table. Adding a path option first makes the table external:

df.write \
  .option("path", "s3://<bucket-path>/<table-directory>") \
  .saveAsTable("<catalog>.<schema>.<table-name>")

CREATE TABLE <catalog>.<schema>.<table-name>
 ?  's3://<bucket-path>/<table-directory>'
AS SELECT * FROM <source-table>;

Sources34

4.Dropping a table: where the two types differ most

The command is the same for both types. To run it, you must own the table or hold the MANAGE privilege on it.

DROP TABLE IF EXISTS catalog_name.schema_name.table_name;

What happens next depends on the table type. Dropping a managed Unity Catalog table removes it from the metastore and marks its data for deletion. During the recovery period (7 days by default) you can bring it back with UNDROP. After the recovery period ends, an asynchronous purge process deletes the underlying files from your cloud storage. You can set the recovery period at catalog or schema level with RETAIN DROPPED TO: the value is either 0 hours, which disables recovery, or 7 to 30 days. A schema-level setting takes precedence over the catalog's, and a new setting applies only to tables dropped afterwards. Dropping an external table removes only the metadata, and from then on access to that path is governed by the external location that contains it. Deleting the files is your job. The legacy Hive metastore behaves differently: a managed Hive table's data is deleted straight away.

ALTER CATALOG my_catalog RETAIN DROPPED TO 30 DAYS;

To recover a dropped table, run UNDROP TABLE with its name. By default it restores the most recently dropped table of that name that you own. The parent schema and catalog must still exist, and table privileges, column spec and properties are recovered. Primary and foreign key constraints are not, so recreate them with ALTER TABLE ADD CONSTRAINT. If several dropped tables share a name, list them with SHOW TABLES DROPPED to find each table ID, then restore a specific one with UNDROP TABLE WITH ID. If a table with that name exists again, rename it first with ALTER TABLE ... RENAME TO.

UNDROP TABLE my_catalog.my_schema.my_table;

SHOW TABLES DROPPED IN my_schema;

UNDROP TABLE WITH ID '6ca7be55-8f58-47a7-85ee-7a59082fd17a';

Sources3546

5.Modifying tables: ALTER TABLE, converting to managed and rolling back

You modify a Unity Catalog table with ALTER TABLE. Two examples are changing the owner ([ SET ] OWNER TO principal) and applying tags (SET TAGS, which needs the APPLY TAG permission). The biggest modification that touches the managed/external split is converting an external table into a managed one.

To convert, run ALTER TABLE ... SET MANAGED. It works on Unity Catalog external or foreign Delta Lake tables, and the conversion keeps the table's name, settings, permissions, views and history. For an external table you omit MOVE and COPY, which are only for foreign tables. Before you convert an external table, check the prerequisites: the table must use the Delta Lake format, you must run Databricks Runtime 17.3 LTS or above (or Serverless compute), and you must own the table.

ALTER TABLE main.default.my_external_table SET MANAGED;

The conversion can be undone. UNSET MANAGED rolls a converted table back to an external table by pointing its metadata at the original external location. For a converted external table you can roll back within 14 days of conversion.

One more modification matters for external tables. If you change an external table's metadata with a non-Databricks client, or through path-based access from within Databricks, Unity Catalog does not sync automatically. Databricks recommends against such updates. If you do make one, run MSCK REPAIR TABLE <table-name> SYNC METADATA to bring the schema in Unity Catalog up to date.

ALTER TABLE main.default.my_managed_table UNSET MANAGED;

Sources783

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Dropping an external table also deletes its files from cloud storage.Why is that wrong?

   DROP TABLE on an external table removes only the Unity Catalog metadata. To remove the data, you delete the files yourself.

   Covered in Dropping a table: where the two types differ most

2. 2.Dropping a managed Unity Catalog table permanently deletes its files the moment the command completes.Why is that wrong?

   The data is only marked for deletion. You can UNDROP the table during the recovery period (7 days by default), and the files are purged only after it ends.

   Covered in Dropping a table: where the two types differ most

3. 3.A table described as "managed by Unity Catalog" must be a managed table.Why is that wrong?

   "Managed by Unity Catalog" means governed, and that covers external tables too. Only a managed table also has its storage location and lifecycle controlled by Unity Catalog.

   Covered in Governance vs storage: what "managed" actually controls

4. 4.You need an external table whenever a non-Databricks engine must read the data, because managed tables lock you in.Why is that wrong?

   External engines can read, write and create managed tables through open APIs such as the Unity REST API and the Iceberg REST Catalog.

   Covered in Formats, optimizations and when to choose each type

5. 5.To convert an external table to managed, you must add MOVE or COPY to SET MANAGED.Why is that wrong?

   MOVE and COPY are for foreign tables only. Adding either one to an external table conversion makes the command fail, so you run plain SET MANAGED.

   Covered in Modifying tables: ALTER TABLE, converting to managed and rolling back