Databricks DE · Topic 2.3

Managed vs External Tables (& properties)

The difference is who owns the data files. Managed = Unity Catalog owns storage, lifecycle & optimization (DROP deletes data). External = you own the files (DROP keeps them). Plus the table/column properties that make a table validated and discoverable.

Managed = defaultDROP differsPredictive OptimizationTBLPROPERTIESconstraints

The one-liner

Managed tables let Unity Catalog own storage, optimization, and lifecycle — dropping one deletes the data. External tables store files at a location you specify — UC governs access only, and dropping one keeps the files. Managed is the default & recommended.

🏨 Analogy: managed = a hotel room (cleaned & cleared at checkout); external = a rented apartment with your own lock — the landlord (UC) controls who enters, but your furniture (data) stays when the lease ends.

Compare — click a type

▸ toggle managed / external

Managed

External

Managed table — CREATE TABLE x (...), no location.

UC picks storage + runs Predictive Optimization (auto OPTIMIZE/VACUUM/ANALYZE)
UC controls the file lifecycle
Best for almost everything you build in Databricks

CREATE TABLE main.sales.orders (order_id BIGINT, amount DECIMAL(10,2));
DROP TABLE main.sales.orders;   -- ⚠️ data files DELETED

DROP TABLE x → ⚠️ data files DELETED (UNDROP within 7 days)

External table — CREATE TABLE x (...) LOCATION 's3://...'.

You own file layout & cleanup; UC governs access only
Path must sit under a UC external location; formats: Delta, Parquet, CSV, JSON, Avro, ORC
No Predictive Optimization — you run maintenance

CREATE TABLE main.sales.orders_ext (order_id BIGINT)
LOCATION 's3://acme-lake/sales/orders/';
DROP TABLE main.sales.orders_ext;  -- ✅ metadata only; files REMAIN

DROP TABLE x → ✅ metadata removed, data files REMAIN

DROP simulator — see what survives

▸ click "Run DROP" on each

MANAGED table

metadata: present

data files: present

EXTERNAL table

metadata: present

data files: present

1 · Creating managed & external tables

The presence of LOCATION (SQL) or .option("path", …) (PySpark) is the only switch between managed and external. UC recovers a dropped managed table for 7 days via UNDROP (privileges & properties restored; PK/FK not).

Managed — SQL & PySpark

CREATE TABLE main.sales.orders (order_id BIGINT, amount DECIMAL(10,2), order_ts TIMESTAMP);
UNDROP TABLE main.sales.orders;   -- recover within the 7-day window

(df.write.format("delta").saveAsTable("main.sales.orders"))   # no path → managed

External — SQL & PySpark

CREATE TABLE main.sales.orders_ext (order_id BIGINT, amount DECIMAL(10,2))
LOCATION 's3://acme-lake/sales/orders/';   -- under a UC external location

(df.write.format("delta").option("path", "s3://acme-lake/sales/orders/")
   .saveAsTable("main.sales.orders_ext"))   # path → external

2 · Why managed wins by default — Predictive Optimization

On managed UC tables, Databricks automatically identifies and runs OPTIMIZE, VACUUM, and ANALYZE — no schedules to maintain. It's enabled by default for newer accounts and does not run on external tables, which is the single biggest reason to prefer managed.

🗂️ Real-world use case: use managed for everything built inside Databricks; reach for external only when files are shared with other engines/tools or already live in a fixed cloud location you must keep.

3 · Table & column properties — click each

▸ expand each property type

TBLPROPERTIES (Delta flags)▸

Key-value settings: Delta feature flags + retention horizons + free-form business tags.

ALTER TABLE main.sales.orders SET TBLPROPERTIES (
  'delta.enableDeletionVectors' = 'true',        -- merge-on-read
  'delta.deletedFileRetentionDuration' = 'interval 7 days',
  'quality' = 'gold');

Constraints (NOT NULL / CHECK)▸

Reject bad rows at write time. NOT NULL inline; CHECK via ALTER TABLE.

amount DECIMAL(10,2) NOT NULL
-- and:
ALTER TABLE main.sales.orders ADD CONSTRAINT amount_positive CHECK (amount >= 0);

Generated & identity columns▸

Auto-computed values and surrogate keys.

order_id   BIGINT GENERATED ALWAYS AS IDENTITY,
order_date DATE   GENERATED ALWAYS AS (CAST(order_ts AS DATE))

Comments & tags (discovery)▸

Power Catalog search & governance. (Column masking/row filters → Topic 8.)

COMMENT ON TABLE main.sales.orders IS 'Curated order facts (gold).';
ALTER TABLE main.sales.orders SET TAGS ('domain' = 'sales', 'pii' = 'false');

Managed vs external — the decision

	Managed	External
Storage location	UC-managed	You specify (LOCATION)
Lifecycle / optimization	Unity Catalog (Predictive Optimization)	You / external system
DROP deletes data?	Yes ⚠️	No (metadata only)
Governance (access, lineage)	UC	UC
Best for	Default — most tables	Shared / fixed-location files

Decision rule: default to managed; choose external only when another engine reads the same files or the data must live at a fixed external path.

Uses, edge cases & limitations

Uses: managed = default for new tables; external = shared/fixed-location files; properties (TBLPROPERTIES, constraints, generated/identity columns, comments, tags) = Delta features + column quality + documentation/discovery.
Edge cases: dropping a managed table is destructive (recoverable only via UNDROP within ~7 days; PK/FK not restored). Two external tables on the same path → overlapping governance. Adding NOT NULL/CHECK validates existing data and fails if rows violate it.
Limitations: external tables miss Predictive Optimization — you run OPTIMIZE/VACUUM yourself; some newer features assume managed tables; you can't INSERT arbitrary values into a GENERATED ALWAYS identity column.

Common gotchas

❌ Dropping a managed table expecting files to survive — they don't.
❌ Defaulting to external "to be safe" — you lose Predictive Optimization & add ops burden. Prefer managed.
❌ Hand-deleting files under an external table — the metastore still expects them; queries break.
❌ Expecting an inline CHECK in CREATE TABLE — add it with ALTER TABLE ... ADD CONSTRAINT.