07.3 Unity Catalog Tables — Managed vs External

A Unity Catalog table has two separate dimensions:

1. Governance — Unity Catalog controls metadata, permissions, auditing and lineage.
2. Storage lifecycle — either Unity Catalog controls the table's storage location and lifecycle, or you do.

Architect rule: For new workloads, prefer Unity Catalog managed tables unless you have a concrete requirement for externally controlled storage, interoperability, migration, or disaster recovery.

Databricks currently recommends managed tables for new tables and uses external tables for specific requirements such as existing data, external readers/writers, unsupported formats, or certain DR scenarios. See the Unity Catalog best practices.

1. Fundamental distinction

Managed table

Unity Catalog determines:

• where the data is stored
• the managed storage location
• file organization and lifecycle
• what happens to data when the table is dropped

The data still remains in your cloud account.

External table

You specify the cloud storage location.

Unity Catalog manages:

• table metadata
• governance
• permissions
• lineage and auditing

But the underlying data lifecycle, storage location and file layout remain under your external storage/platform management.

Dimension	Managed	External
Metadata	Unity Catalog	Unity Catalog
Governance	Unity Catalog	Unity Catalog
Storage path	UC determines	You specify
Data lifecycle	UC-managed	Externally managed
Drop table	Data follows UC lifecycle	Files remain
Existing data registration	Less suitable	Excellent
New enterprise tables	Preferred	Requirement-driven

See Managed versus external assets.

2. Managed does not mean Databricks owns your data

For a managed table:

Your AWS account
└── S3
    └── Unity Catalog managed storage
        └── Delta table files

Databricks determines the managed storage location and manages the lifecycle, but the underlying files remain in your cloud account.

Managed by Unity Catalog ≠ owned by Databricks.

This distinction matters for security, compliance and enterprise architecture.

3. Managed storage hierarchy

Managed table storage can be defined at:

Metastore
   |
   +-- Catalog
         |
         +-- Schema
               |
               +-- Managed Table

Effective location precedence:

Schema managed location
        ↓
Catalog managed location
        ↓
Metastore managed location

If the schema has a managed location, it wins. If not, the catalog location is considered. If neither exists, the metastore managed location is used.

See Managed storage locations.

4. How Unity Catalog reaches S3

On AWS, storage access is built from two governance objects:

Storage Credential
       |
       | IAM role
       v
External Location
       |
       | S3 URI
       v
S3 bucket / prefix

A storage credential represents the authentication mechanism, such as an AWS IAM role.

An external location combines a cloud storage path with a storage credential and becomes a governed securable object.

See Connect to cloud object storage.

5. Managed storage and external locations

A managed storage location is a path in your cloud account where Unity Catalog stores managed tables and volumes. That location can be backed by an external location.

Conceptually:

Unity Catalog
   |
   +-- Managed Storage
         |
         +-- External Location
                |
                +-- Storage Credential
                       |
                       +-- AWS IAM Role
                              |
                              +-- S3

This gives Unity Catalog controlled cloud access without putting long-lived credentials into notebooks.

Databricks recommends restricting storage-credential privileges and granting only the capabilities required to create external locations. See Manage storage credentials.

6. Creating a managed table

The simplest pattern is:

CREATE TABLE procurement_prod.silver.purchase_order (
  purchase_order_id STRING,
  supplier_id STRING,
  order_date DATE,
  amount DECIMAL(18,2)
)
USING DELTA;

No storage path is specified. Unity Catalog chooses the effective managed storage location.

This is normally the preferred enterprise pattern for new Delta tables.

7. Creating an external table

For an external table, specify a location covered by an appropriate Unity Catalog external location.

CREATE TABLE procurement_prod.bronze.s4_po_raw
USING DELTA
LOCATION 's3://company-raw-prod/procurement/s4/po/';

The exact path must satisfy your external-location and privilege configuration.

The table metadata is governed by Unity Catalog, but the underlying files remain under external storage lifecycle management.

See Work with external tables.

8. What happens when you DROP TABLE?

Managed

DROP TABLE procurement_prod.silver.purchase_order;

Unity Catalog removes the table and the underlying managed data follows the applicable managed-table deletion/recovery lifecycle.

External

DROP TABLE procurement_prod.bronze.s4_po_raw;

Unity Catalog removes the table registration. The underlying S3 data remains.

Therefore:

DROP managed table
    → metadata removed
    → data lifecycle handled by UC

DROP external table
    → metadata removed
    → files remain

Document this behavior explicitly in production runbooks.

9. External table is not "less governed"

An external table can still be governed through Unity Catalog:

• catalog/schema privileges
• table permissions
• lineage
• auditing
• ownership
• metadata and tags
• applicable row/column controls

The difference is who controls the underlying file lifecycle.

Therefore:

Governance ≠ Storage Ownership

10. When to use managed tables

Managed tables are generally the default for:

• new lakehouse tables
• Delta Lake workloads
• Bronze/Silver/Gold pipelines
• curated data products
• platform-managed analytics

Managed tables simplify lifecycle and operational management.

Databricks currently recommends managed tables for new tables. See Unity Catalog best practices.

11. When to use external tables

External tables are justified when:

Existing data already lives in S3

You can register existing data without immediately moving it.

Non-Databricks systems must access the files

For example:

Databricks
   ↕
S3
   ↕
Other analytics platform

You require a specific storage layout

For regulatory, operational or interoperability reasons.

You need supported non-Delta formats

External tables support formats such as CSV, JSON, Avro, Parquet and ORC where supported.

A specific DR strategy requires independently controlled storage

External tables can be useful when storage lifecycle must remain outside Databricks.

See Work with external tables.

12. The dangerous pattern: unmanaged direct S3 access

Avoid:

User
 |
 +--> Unity Catalog
 |
 +--> direct S3 credentials

The second path can bypass centralized Unity Catalog authorization, auditing and lineage.

For managed tables, do not grant non-Unity-Catalog identities direct storage access.

See Unity Catalog cloud storage guidance.

13. Lifecycle responsibility

Think about the full lifecycle:

CREATE
  ↓
WRITE
  ↓
OPTIMIZE
  ↓
UPDATE / DELETE / MERGE
  ↓
RETENTION
  ↓
DROP

For managed tables, Unity Catalog and Databricks manage the table storage lifecycle.

For external tables, Unity Catalog governs the table while your storage/platform processes remain responsible for the underlying files.

This affects:

• backups
• retention
• deletion
• cost management
• archival
• disaster recovery
• compliance

14. Path governance

Unity Catalog prevents unsafe overlaps between governed storage paths.

For example, avoid overlapping external locations such as:

external-location-A → s3://company-data/procurement/
external-location-B → s3://company-data/procurement/silver/

Managed storage should not overlap external tables or external volumes.

This prevents ambiguous ownership of the same files.

See Path rules and access.

15. External-location design

A useful enterprise pattern is:

S3
└── company-data-prod/
    ├── finance/
    ├── procurement/
    └── customer/

Then define external locations around meaningful governance boundaries, for example:

extloc_procurement_prod
    → s3://company-data-prod/procurement/

Databricks recommends useful common prefixes and alignment with catalog/schema boundaries where appropriate. See Unity Catalog architecture guidance.

Do not create hundreds of external locations for every table unless a real security or operational boundary requires it.

16. External readers: do not jump straight to external tables

A common requirement is:

"Our other platform needs to read the same S3 files."

First ask:

1. Can the consumer use governed data sharing?
2. Can it use a supported open API?
3. Can the data product be shared without exposing storage credentials?
4. Is direct file access genuinely required?

Modern lakehouse architecture should prefer governed sharing over bypassing Unity Catalog with raw storage access where possible.

17. Migration from Hive metastore

A common migration looks like:

Hive Metastore
      |
      v
Existing S3 data
      |
      v
Unity Catalog external table
      |
      v
Governed migration
      |
      v
Unity Catalog managed table

External tables can provide a migration bridge because existing files do not need to move immediately.

Over time, important workloads can migrate to managed tables where the operating model benefits from UC-managed storage.

18. SAP / BW migration perspective

Suppose SAP BW/4HANA currently has an ADSO-like curated dataset exported to S3.

Migration phase

SAP / BW
   |
   v
S3 existing files
   |
   v
UC External Table

This avoids an immediate data-copy project.

Target architecture

SAP / source
   |
   v
Bronze managed
   |
   v
Silver managed
   |
   v
Gold managed
   |
   v
BI / Analytics

Useful migration principle:

External for onboarding legacy data; managed for the target lakehouse operating model.

19. Performance considerations

Managed versus external does not automatically determine query performance.

Performance is primarily influenced by:

• file sizes
• data layout
• partitioning/clustering
• statistics
• data skipping
• query predicates
• Delta optimization
• compute
• execution engine

Managed tables simplify the platform's ability to apply storage optimization and lifecycle practices consistently.

So:

Managed ≠ automatically faster
External ≠ automatically slower

The architectural benefit of managed tables is primarily operational control and integration, not a magic performance multiplier.

20. Security model

Separate the layers:

Identity
   ↓
Unity Catalog privileges
   ↓
Table / schema / catalog
   ↓
External location / managed storage
   ↓
Cloud IAM
   ↓
S3

Use least privilege at every layer.

For external data:

• restrict who can use the external location
• restrict who can create external tables
• restrict storage credentials
• avoid broad S3 permissions
• document ownership
• audit direct cloud access

21. Decision matrix

Requirement	Managed	External
New Delta table	Preferred	Possible
Existing S3 data	Possible	Strong fit
UC controls lifecycle	Strong fit	No
External file consumers	Limited fit	Strong fit
Direct cloud-path access required	No	Yes
Migration bridge	Possible	Strong fit
Non-Delta formats	Limited	Strong fit
Simplified operations	Strong fit	Lower
Independent storage lifecycle	No	Strong fit
Standard enterprise lakehouse target	Usually preferred	Exception

22. Hands-on lab

Lab A — Create a managed table

CREATE TABLE procurement_prod.gold.lab_managed_orders (
  order_id STRING,
  supplier_id STRING,
  order_date DATE,
  amount DECIMAL(18,2)
)
USING DELTA;

Inspect:

DESCRIBE DETAIL procurement_prod.gold.lab_managed_orders;

Observe that no LOCATION was supplied.

Lab B — Register an external table

Use a test S3 path covered by an approved external location:

CREATE TABLE procurement_prod.bronze.lab_external_orders
USING DELTA
LOCATION 's3://approved-bucket/procurement/lab/orders/';

Inspect:

DESCRIBE DETAIL procurement_prod.bronze.lab_external_orders;

Lab C — Compare DROP behavior

Use disposable test data:

1. Create one managed table.
2. Create one external table.
3. Drop both.
4. Inspect the storage locations.

Record the difference:

Managed:
metadata → removed
files → UC-controlled lifecycle

External:
metadata → removed
files → remain

23. Troubleshooting

"LOCATION overlaps with another table"

Check:

• external locations
• existing external tables
• managed storage roots
• path overlap

"Access denied to S3"

Trace:

Unity Catalog privilege
        ↓
External location
        ↓
Storage credential
        ↓
IAM role
        ↓
S3 bucket policy

Do not troubleshoot only from the SQL layer.

"I can query the table but cannot access the S3 path"

This can be expected for managed tables. Managed tables are intended to be accessed through Unity Catalog table identifiers rather than direct cloud paths.

"DROP TABLE deleted my files"

Determine whether the table was managed. Never test DROP behavior on production tables without confirming the table type and recovery requirements.

24. Architect scenario

Scenario: Your company has 400 TB of S3 data created by an existing platform. Databricks must query it, but another platform must continue writing the files.

Recommended starting architecture:

Existing platform
       |
       v
S3
       |
       v
Unity Catalog External Location
       |
       v
External Tables
       |
       v
Databricks

Then identify which datasets should become Databricks-owned products:

External source
      ↓
Bronze managed
      ↓
Silver managed
      ↓
Gold managed

This creates a controlled transition rather than forcing an all-at-once migration.

25. Interview questions

Q: What is the difference between a managed and external table?

A managed table has its storage location and lifecycle controlled by Unity Catalog. An external table points to a storage location you control; Unity Catalog governs its metadata and access.

Q: Does a managed table store data inside Databricks?

No. The data remains in your cloud account.

Q: Why use an external table?

Existing data, external readers/writers, required storage layout, supported non-Delta formats, or specific DR/interoperability requirements.

Q: What happens when an external table is dropped?

Unity Catalog removes the metadata; the underlying files remain.

Q: What happens when a managed table is dropped?

Unity Catalog removes the metadata and follows the managed-table deletion/recovery lifecycle for the underlying data.

Q: Should every S3 dataset be an external table?

No. For new lakehouse workloads, managed tables are generally preferred.

Q: What is the relationship between external location and storage credential?

The storage credential supplies the cloud authentication mechanism; the external location combines that credential with a governed cloud path.

26. Production checklist

• default to managed tables for new workloads unless a requirement says otherwise
• document why every external table is external
• define managed storage at intentional metastore/catalog/schema boundaries
• use dedicated external locations for clear governance boundaries
• prevent path overlap
• restrict storage credentials
• restrict CREATE EXTERNAL LOCATION
• align S3 IAM with Unity Catalog governance
• avoid direct storage access that bypasses UC
• document DROP and retention behavior
• define backup and DR responsibilities
• define external-reader requirements explicitly
• plan migration from legacy external tables where appropriate
• monitor storage cost and file lifecycle
• use table names rather than raw cloud paths for governed table access

27. Final mental model

                    UNITY CATALOG
                         |
          +--------------+--------------+
          |                             |
      MANAGED                        EXTERNAL
          |                             |
   UC chooses path                 You choose path
   UC lifecycle                    You manage lifecycle
          |                             |
          +--------------+--------------+
                         |
                    Cloud Storage
                         |
                        S3

The architectural decision is not:

"Which table type is better?"

It is:

"Who should control the physical storage lifecycle, and what interoperability requirements exist?"

For most new enterprise lakehouse tables, the answer is Unity Catalog managed tables.

For legacy, shared or externally controlled storage, external tables remain an important architecture pattern.

Official references

• Unity Catalog best practices
• Managed storage
• Connect to cloud object storage
• Work with external tables
• Path rules
• Storage credentials
• Unity Catalog architecture