The “why” worth knowing

Oleksandra

The “why” worth knowing

Choosing external tables is usually about flexibility: you keep dedicated storage, maintain raw files that multiple engines can query directly, and control your own partitioning.

But control is also a constraint. To guarantee query performance, you spend time understanding query patterns and surgically crafting your data layout; when performance degrades, you run emergency maintenance operations to improve it. Any engine can only guarantee how fast a query runs if it can decide how the data is laid out on disk, and with external tables, that decision is on you. Open storage and adaptive performance work against each other, and most of us have quietly accepted that as the cost of keeping our data open.

Unity Catalog Managed tables resolve that tension: your files stay in open formats (Delta Lake or Apache Iceberg) in your own cloud (AWS, Azure, GCP), but the platform controls how they're laid out on disk. This post is about what you give up in that trade, and what you get back.

Why are performance and control linked?

Performance isn't a one-time setup. The right file sizes, clustering keys, and partitioning all depend on how the table is queried, and that drifts: new dashboards, new join patterns, a column that suddenly gets filtered on. Last quarter's optimal layout quietly stops being optimal, and you're back to re-clustering by hand.

That's the real cost of owning the layout: it's never done. You act on a schedule, after a query gets slow enough to notice. An automated process acts on the workload's current shape, before you'd have gotten to it. It's not that it optimizes better than you could; it's that chasing a moving target by hand leaves you a step behind the queries.

What do you trade with Unity Catalog Managed Tables?

The trade is narrow: you give up control of the file layout and the code that depended on knowing it, in exchange for a storage layer that the platform can keep fast and enforce access on. What you don't give up is custody. The bytes still sit in your own cloud storage in open Delta Lake and Apache Iceberg formats.

What you hand over is the layout: the per-table directories, the file names, and the file sizes. You still choose the root storage location at the catalog or schema level, but everything below it is Unity Catalog's to organize. It means that the code that reaches the table by its path has to change. Path-based reads like SELECT * FROM delta.s3://...``, streaming jobs pointed at the storage location, and outside tools that read the files directly all have to switch to the name-based catalog.schema.table form. Unity Catalog serves managed tables through the catalog because that's where its permissions apply, so a direct path read isn't available.

You can point other engines to it using Unity Catalog’s Open APIs (such as the Iceberg REST Catalog endpoint or OpenSharing), stay in control of your own region and security perimeter (such as your encryption keys, network rules, and bucket policies), which is what your compliance team cares about, and there is only one copy of the data.

What do you gain by letting the engine optimize layout automatically?

This is the other half of the trade. Once Unity Catalog controls how the files are laid out, it can do the work you used to do by hand, and some work you couldn't.

The clearest example is maintenance. On external tables, keeping queries fast is your job: you schedule OPTIMIZE to compact small files, ANALYZE to refresh statistics, VACUUM to clear tombstoned files. Predictive Optimization runs those for you on Unity Catalog managed tables and decides when each is worth the compute, so the maintenance that used to sit in your job scheduler stops being something you own.

The same control changes how table features get turned on. Enabling a new Delta Lake feature on an external table means running ALTER TABLE and then checking that every engine reading the table can still understand it. On Unity Catalog managed tables, Auto Upgrades works by observing how your existing tables are accessed, verifying that every workload is ready, and then applying features on your behalf so you're not the one testing downstream readers by hand.

And because Unity Catalog coordinates writes centrally instead of every engine committing to object storage on its own, it can offer things external tables can't: atomic transactions across multiple statements and tables, and concurrent writes from different engines without corrupting the log.

Dropping a table also stops being irreversible. On an external table, DROP TABLE leaves the files sitting in object storage, so recovering from a mistaken drop means digging them back out or restoring a storage snapshot. On a managed table, it's a soft delete: UNDROP TABLE catalog.schema.table brings back the files, history, and permissions within the retention window.

How do you actually convert an external table to a Unity Catalog Managed Table?

One command does it: ALTER TABLE catalog.schema.table SET MANAGED. Foreign tables use SET MANAGED MOVE or SET MANAGED COPY instead.

You can run it on a live table, because it converts in two phases. In the first phase, Unity Catalog copies the data files and the Delta log to the managed location while reads and writes keep running against the table. In the second phase, it replays whatever was written during that copy, repoints the table's metadata to the new location, and briefly blocks writes for the switchover. The copy takes as long as the table is large; the write outage in the second phase stays short regardless of size because it only has to catch up on commits from the copy window, rather than move the whole table again.

The table retains its name, permissions, and history throughout this. Queries and jobs that already reference it by catalog.schema.table don't change; they point at the same table, now managed. The only thing that changes is the physical location of the files, which is exactly what you handed over.

And it's reversible. ALTER TABLE ... UNSET MANAGED converts back within 14 days, so trying it on one table isn't a commitment you're stuck with.

# Scaling Up with Bulk Conversion
# This script executes the conversion command for every external table within the 'your_catalog.your_table' schema using PySpark.

# Define the target catalog and schema
catalog = "your_catalog"
schema = "your_table"

# 1. Collect a list of all external tables in the target schema.
tables_df = spark.sql(f"""
   SELECT table_name
   FROM system.information_schema.tables
   WHERE table_schema = '{schema}'
     AND table_catalog = '{catalog}'
     AND table_type = 'EXTERNAL'
""")

# Extract table names into a Python list
external_tables = [row.table_name for row in tables_df.collect()]

# 2. Loop through the list and execute the SET MANAGED command for each table.
for table_name in external_tables:
   full_table_name = f"{catalog}.{schema}.{table_name}"
   try:
       spark.sql(f"ALTER TABLE {full_table_name} SET MANAGED;")
       print(f"Successfully converted {full_table_name} to managed.")
   except Exception as e:
       print(f"Standard conversion failed for {full_table_name}: {e}")

What should you check before converting a table?

The command is simple, but three things are worth checking first, especially on large or heavily used tables.

Where your managed location sits. Converting copies the whole table to the managed location. If it's in the same region as your data, there's no cross-region transfer. If it's in another region, your cloud provider charges egress out of the source region and ingress into the new one.
What Delta features the table is on. Most tables convert without any of this mattering. But it's worth running DESCRIBE DETAIL first to see the table's Delta features and protocol version, because a few older combinations need a second look, a table with column mapping on reader minReaderVersion: 2, minWriterVersion: 7, for instance, won't convert directly (DELTA_TRUNCATED_TRANSACTION_LOG). One habit that saves trouble: pick the runtime you'll convert on and stick with it. Retrying a failed conversion on a different Databricks Runtime can fail again (VERSIONED_CLONE_INTERNAL_ERROR), because runtimes serialize table metadata differently.
What reaches the table by path. Path-based reads and streaming jobs stop working after conversion. There's a redirect layer that catches path-based access and routes it to the managed table, but it adds a few hundred milliseconds for each path-based read or write, doesn’t support streaming writes, and requires a recent runtime. It's a bridge, not a destination. The actual fix is repointing that code from delta./path`` to catalog.schema.table before you convert.

What if you're starting from scratch?

Then most of this post doesn't apply to you. There's no external table to convert, no path-based code to repoint, no region copy to pay for. New tables in Unity Catalog are managed by default, so you get the layout that the platform can keep fast and govern from the first write, and skip the migration entirely.

The one thing worth deciding up front is where the data lives: set the managed storage location at the catalog or schema level so your tables land in the cloud account and region you want. After that, you just create tables and let Unity Catalog own the layout.

So why does this matter?

For most tables, converting is worth it, and the reason isn't the automation itself; it's what the automation makes possible. When the engine controls how data is laid out on disk, it can keep queries fast as access patterns shift by compacting files, refreshing statistics, and re-clustering, without you having to schedule any of it. And it's the same control that lets the platform put faster query paths on top of your tables over time: performance work you get by staying on managed tables, not by building anything yourself. Your files never leave your own cloud storage while any of this happens.

That's the trade worth taking on most tables. A few are worth pausing on first: tables whose storage is in a different region from your managed location, tables written to heavily by external or streaming clients, and tables on a Delta protocol that won't convert cleanly. None of those are reasons not to migrate; they're reasons to do those tables deliberately rather than in a batch.