Convert external Delta tables to Unity Catalog managed tables with SET MANAGED: prechecks, cutover and rollback

#Goal

You have Unity Catalog external tables on ADLS. You want them as managed tables so you get predictive optimization, automatic liquid clustering and managed-table features. You also want to stop handing out storage paths. You don't want to rebuild them with CREATE TABLE AS SELECT and break every job that writes to them.

ALTER TABLE ... SET MANAGED does this in place. It copies the data and Delta log to the managed location, then switches the table metadata. The table name, grants and history stay the same. Writers pause for a few minutes, and you have 14 days to roll back.

Note

Runtime requirements have changed during the preview and GA of this feature. The current Azure Databricks docs say Databricks Runtime 17.3 LTS or above, or serverless, for SET MANAGED and UNSET MANAGED. Check the current page before you plan.

#Prechecks

#1. Confirm the table qualifies

You need OWNER or MANAGE on the table. The table must be a Unity Catalog external Delta table. Hive metastore and Glue federated (foreign) tables use a different syntax: SET MANAGED MOVE | COPY.

-- Type must be EXTERNAL and provider delta
DESCRIBE EXTENDED prod.sales.orders;

SELECT table_catalog, table_schema, table_name, table_type, data_source_format, table_owner
FROM system.information_schema.tables
WHERE table_catalog = 'prod'
  AND table_type = 'EXTERNAL'
  AND data_source_format = 'DELTA'
ORDER BY table_schema, table_name;

#2. Check the protocol and health of the table

DESCRIBE DETAIL prod.sales.orders;
-- Look at: minReaderVersion, minWriterVersion, tableFeatures, sizeInBytes, numFiles

Some tables fail with DELTA_TRUNCATED_TRANSACTION_LOG: those with minReaderVersion = 2, minWriterVersion = 7 and columnMapping in tableFeatures. If DESCRIBE DETAIL or a simple SELECT COUNT(*) already errors with DELTA_TXN_LOG_FAILED_INTEGRITY or DELTA_STATE_RECOVER_ERRORS, the source table is damaged. Fix that first. Don't convert it.

#3. Find where the data will land

The data moves to the managed location of the schema, or of the catalog if the schema has none. If that location is in another region, you pay cross-region transfer costs and get slower reads.

DESCRIBE SCHEMA EXTENDED prod.sales;    -- Location / managed location
DESCRIBE CATALOG EXTENDED prod;

#4. List everyone who reads or writes the table

Path-based access is what breaks. Find jobs and notebooks that use the abfss:// path instead of the three-part name:

SELECT entity_type, entity_id, source_table_full_name, source_path, target_table_full_name, target_path,
       max(event_time) AS last_seen
FROM system.access.table_lineage
WHERE event_date >= current_date() - INTERVAL 30 DAYS
  AND (source_table_full_name = 'prod.sales.orders'
       OR target_table_full_name = 'prod.sales.orders'
       OR source_path LIKE 'abfss://lake@stprodlake.dfs.core.windows.net/sales/orders%'
       OR target_path LIKE 'abfss://lake@stprodlake.dfs.core.windows.net/sales/orders%')
GROUP BY ALL
ORDER BY last_seen DESC;

Also check for these:

• Readers and writers on Databricks Runtime 15.3 or below. After conversion they can time travel by version only, and they can't write until you drop the inCommitTimestamp feature.
• Non-Databricks clients, such as Synapse, Fabric shortcuts or an older Spark. They must be able to read Unity Catalog managed tables.
• Databricks-to-Databricks Delta Sharing. Recipients don't pick up the new location by themselves, so you reshare the table after conversion.

#Symptom, cause and fix during conversion

#Run the conversion

#1. Freeze maintenance

Cancel any running OPTIMIZE, ZORDER or liquid clustering jobs on the table. Pause their schedules until you finish. Only one session should run SET MANAGED on a table at a time.

# Pause the maintenance job (Databricks CLI, new CLI syntax)
databricks jobs get 123456 > job-123456.json
databricks jobs update --json '{"job_id": 123456, "new_settings": {"schedule": {"quartz_cron_expression": "0 0 3 * * ?", "timezone_id": "UTC", "pause_status": "PAUSED"}}}'

#2. Convert

The copy runs while readers and writers keep going. A short switch-over at the end blocks writes. Microsoft's published estimates are copy times from minutes to about 1.5 hours, with 1 to 5 minutes of write downtime up to about 10 TB. Readers on Runtime 16.4 LTS or above don't see the downtime. Run the conversion from a job or serverless notebook that won't idle-terminate partway through.

ALTER TABLE prod.sales.orders SET MANAGED;

-- Tables with UniForm (Iceberg reads) enabled:
ALTER TABLE prod.sales.orders SET MANAGED TRUNCATE UNIFORM HISTORY;

TRUNCATE UNIFORM HISTORY only truncates the Iceberg metadata history. Delta history stays.

#3. Verify

DESCRIBE EXTENDED prod.sales.orders;        -- Type: MANAGED, Location now under the managed root
DESCRIBE HISTORY prod.sales.orders LIMIT 5;  -- history is intact
SELECT COUNT(*) FROM prod.sales.orders;      -- compare with the pre-change count
SHOW GRANTS ON TABLE prod.sales.orders;      -- grants unchanged

#4. Restart streams and fix path users

Restart every read and write stream on the table with the same checkpoint. They resume from the last committed offset.

On Runtime 18.1 and above, path-based reads and writes to the old location are redirected to the managed location. Each operation costs a few hundred milliseconds more. Path-based streaming writes need 18.2. The redirect needs the old Delta log to stay in the external location. Treat it as a bridge for the migration and change code to use the table name:

# Before
df = spark.read.format("delta").load("abfss://lake@stprodlake.dfs.core.windows.net/sales/orders")
# After
df = spark.read.table("prod.sales.orders")

#5. Reshare if the table is in a Databricks-to-Databricks share

ALTER SHARE partner_share REMOVE TABLE prod.sales.orders;
ALTER SHARE partner_share ADD TABLE prod.sales.orders AS sales.orders WITH HISTORY;

Warning

Removing the table from the share cuts off recipient access until you add it back. Run both statements together, in a window you have agreed with the recipient.

#Roll back

If something important breaks, run this within 14 days:

ALTER TABLE prod.sales.orders UNSET MANAGED;
DESCRIBE EXTENDED prod.sales.orders;   -- Type: EXTERNAL

Writes made after the conversion are kept. Restart the streams again. Time travel by version still works, but time travel by timestamp doesn't work for commits made between conversion and rollback. If the rollback is interrupted, rerun it. Databricks deletes the managed copy seven days after a rollback.

#Clean up the old files

The original data files stay in the external location for 14 days so that rollback is possible. After that:

• Predictive optimization enabled: Databricks deletes them, but it doesn't guarantee when. Small or rarely used tables can keep old files for longer.
• Predictive optimization off: run VACUUM yourself after day 14, on Runtime 17.3 LTS+ or serverless.

VACUUM prod.sales.orders;

Only data files are deleted. The old Delta log stays, and the path redirect depends on it.

Caution

Don't delete the old ADLS folder with az storage fs directory delete or a lifecycle rule. That removes the Delta log the redirect uses and ends your chance to roll back. Remove a storage lifecycle policy on that path only once all path-based users have moved off it.

#Prevent it next time

• Create new tables as managed by default. Grant CREATE EXTERNAL TABLE only where an outside system has to own the files.
• Give every catalog or schema a managed location in the same region as the workspace.
• Make code reviews reject abfss:// paths for tables. Use three-part names.
• Convert in batches by schema. Keep a log of table name, size, conversion time and rollback deadline.
• Run maintenance jobs on Runtime 17.3 LTS+ or serverless so that they can VACUUM converted tables.