Unity Catalog managed vs external tables

The exam’s “managed and unmanaged tables” are, in Unity Catalog terms, managed tables and external tables. Both are fully governed by Unity Catalog (access control, auditing, lineage); the difference is who controls the data files’ location and lifecycle.

Learn recommends managed tables for most use cases and all new tables. External tables fit when you need:

• to register existing data in a format managed tables don’t support (such as JSON or Avro), or any non-Delta, non-Iceberg format;
• direct access from non-Databricks clients that can’t use open APIs (Unity Catalog privileges aren’t enforced on such direct file access);
• a quick Hive metastore upgrade without moving data (with eventual migration to managed);
• disaster recovery requirements that managed tables can’t meet.

Managed tables aren’t a lock-in: external engines can read and write them through the Unity REST API and Iceberg REST Catalog with credential vending.

-- Managed Delta table (the default format)
CREATE TABLE prod.sales.orders (order_id BIGINT, amount DECIMAL(10,2));

-- External table at an explicit path
CREATE TABLE prod.sales.orders_ext (order_id BIGINT, amount DECIMAL(10,2))
LOCATION 'abfss://ext@<storage-account>.dfs.core.windows.net/sales/orders';

Creating a managed table needs USE CATALOG, USE SCHEMA and CREATE TABLE on the schema. An external table also needs CREATE EXTERNAL TABLE on the external location covering its path. Add USING iceberg for a managed Iceberg table; without it you get Delta. From Databricks Runtime 17.3 LTS, ALTER TABLE ... SET MANAGED converts an external Delta table to a managed table, keeping its name, permissions, views and history, with UNSET MANAGED available to roll back within 14 days. Ingestion-oriented DDL such as CTAS and CREATE OR REPLACE TABLE is covered in Ingesting data into Unity Catalog.

Common trap: Choosing an external table because “managed tables delete my data and lock it into Databricks” - managed table files live in your own cloud account and are open to external engines through open APIs; choose external only for the specific needs Learn lists, remembering that dropping it leaves orphaned files you must clean up yourself.

Get the whole book

This note is one section of Ultra Transcenders DP-750: Implementing Data Engineering Solutions Using Azure Databricks, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · DP-750 terms in the glossary · All DP-750 study notes