What you will be able to do
- Convert a Unity Catalog external Delta table to a managed table with ALTER TABLE ... SET MANAGED or Catalog Explorer
- Check the conversion prerequisites and avoid the MOVE/COPY syntax error
- Roll a converted table back to external with UNSET MANAGED, and know when this is mandatory
- Replace table contents safely and resync external table metadata
1.Converting external to managed with SET MANAGED
Databricks recommends managed tables, so the usual conversion goes from external to managed. The tool for this is ALTER TABLE ... SET MANAGED. For external tables you can also use Catalog Explorer. The table keeps its name, settings, permissions, dependent views and history, so downstream consumers see no change. You *could* copy the data into a new managed table with CTAS, but SET MANAGED is recommended. It keeps reader and writer downtime short, handles concurrent writes during the conversion, can be rolled back, and redirects old path-based reads and writes.
Before you convert, confirm the table type in Catalog Explorer or with DESCRIBE EXTENDED, because the syntax depends on it. External tables take plain SET MANAGED. Foreign tables require MOVE or COPY, and leaving both out fails with DELTA_ALTER_TABLE_SET_MANAGED_COPY_OR_MOVE_REQUIRED. If the external table already has Iceberg reads enabled, add TRUNCATE UNIFORM HISTORY. That clause truncates only the UniForm Iceberg history and leaves the Delta history alone.
Checkpoint 1 of 5· Fill the gap
Complete the command that converts a Unity Catalog external table (no Iceberg reads) to managed.
ALTER TABLE catalog.schema.my_external_table SET ? ;External tables use plain SET MANAGED. MOVE and COPY are only for foreign tables.
Source: docs.databricks.comALTER TABLE catalog.schema.my_external_table SET MANAGED TRUNCATE UNIFORM HISTORY;| Requirement | What it means |
|---|---|
| Format | The table must use the Delta Lake format |
| Runtime | Databricks Runtime 17.3 LTS or above, or Serverless compute |
| Permissions | Ownership of the table |
| Readers and writers | Databricks readers and writers must use Databricks Runtime 15.4 LTS or above |
| Feature compatibility | Tables with minReaderVersion=2, minWriterVersion=7 and columnMapping fail with DELTA_TRUNCATED_TRANSACTION_LOG; check with DESCRIBE DETAIL |
Checkpoint 2 of 5· Check yourself
An external table stored as Parquet (not Delta) needs to become a managed table. You own the table and are on Serverless compute. Will ALTER TABLE ... SET MANAGED work?
Format is a prerequisite: SET MANAGED converts external Delta Lake tables only.
“Format: The table must use the Delta Lake format.”Source: docs.databricks.com
Sources1
2.What happens during conversion, and the Catalog Explorer route
Conversion with SET MANAGED runs in two steps. First, the command copies the table data and the Delta transaction log from the external location into managed storage. Readers and writers keep running during this step. Second, it moves over any commits made during the copy and repoints the table metadata at the managed location. Writes are blocked for a short time during this switch. Readers on Databricks Runtime 16.4 LTS or above see no downtime. For a table of 100 GB or less, the docs estimate one to two minutes of downtime at most.
Before converting, cancel any OPTIMIZE jobs running on the table (liquid clustering, compaction, ZORDER), and schedule no new ones until the conversion finishes. Afterwards, three things change. Existing read and write streams fail and must be restarted. Path-based reads and writes are redirected to the new location, with a small performance cost. Predictive optimization switches on unless you had turned it off yourself.
No. Path-based reads and writes are automatically redirected to the managed location. The redirect adds a small performance overhead, so Databricks recommends moving that job to name-based access.
For external tables, Catalog Explorer can do the same conversion for one or more tables in a schema at once. This route is in Beta. It doesn't convert the tables directly. It generates a notebook that runs SET MANAGED. When that finishes, the table type shows as MANAGED instead of EXTERNAL.
Checkpoint 3 of 5· Put it in order
Put the Catalog Explorer conversion steps in order
- 1.Click Continue in the Why migrate to Unity Catalog managed tables? dialog
- 2.Under About this table (or About this schema), click Explore optimizations
- 3.Open the table or schema in Catalog Explorer
- 4.Run the SET MANAGED Queries cell in the notebook
- 5.Select the external tables to convert
- 6.Click Create conversion notebook
Explore optimizations opens the migration dialog. You pick tables, generate a notebook, and the notebook's SET MANAGED cell does the conversion.
“Under About this table (table detail page) or About this schema (schema detail page), click Explore optimizations.”Source: docs.databricks.com
Sources1
3.Going back: UNSET MANAGED
Conversion can be undone. Within 14 days of converting, UNSET MANAGED points the table metadata back at the original external location. Writes made to the managed location since the conversion are kept. Like the forward conversion, it needs Serverless compute or Databricks Runtime 17.3 LTS and above, and you must restart streaming jobs afterwards. Seven days after a rollback, Databricks deletes the data in the managed location.
ALTER TABLE catalog.schema.my_managed_table UNSET MANAGED;UNSET MANAGED is the rollback for a table that was converted with SET MANAGED. The ALTER TABLE reference describes it only in that role, and the sources document no other way to use it. If the table has Iceberg reads enabled and you leave out TRUNCATE UNIFORM HISTORY, the command fails with DELTA_ALTER_TABLE_UNSET_MANAGED_DOES_NOT_SUPPORT_UNIFORM. Creating an external table from scratch is a different operation. You must specify a storage location with LOCATION, either in a CREATE TABLE statement or in a CREATE TABLE ... LOCATION ... AS SELECT statement.
One ordering rule matters a lot here. If you plan to drop a converted table and keep the original external files, run UNSET MANAGED *first*. A converted table is now managed, so dropping it straight away follows managed-table drop behavior.
Checkpoint 4 of 5· Check yourself
Last week a team converted an external table to managed. Now they want to drop it from Unity Catalog and keep the original external files. What must they do first?
The docs require UNSET MANAGED before dropping. Dropping without it might cause data loss or inconsistencies.
“You MUST run UNSET MANAGED before dropping the managed table.”Source: docs.databricks.com
4.Modifying tables: replace contents, resync metadata, change defaults
To overwrite a table's contents, use CREATE OR REPLACE TABLE. It behaves the same for managed and external tables. It runs as a single transaction, so the table never stops existing. It keeps the table's identity and history (so RESTORE still works), and it keeps column masks on any columns that remain. The pattern to avoid is DROP TABLE followed by CREATE TABLE. On any table type, that can produce errors, lose records or corrupt results for concurrent readers.
CREATE OR REPLACE TABLE table_name
AS SELECT * FROM parquet.`/path/to/files`Checkpoint 5 of 5· Check yourself
A production table must have all its data replaced while dashboards keep querying it. Which approach does Databricks recommend?
CREATE OR REPLACE is atomic and keeps the table's history, so concurrent queries carry on. DROP then CREATE is the anti-pattern the docs warn about.
“Instead, Databricks recommends always using CREATE OR REPLACE TABLE, as in the following example:”Source: docs.databricks.com
Beyond replacing contents, ALTER TABLE changes a table's metadata and settings in place. OWNER TO principal transfers ownership. SET TAGS and UNSET TAGS apply and remove tags, and you need the APPLY TAG permission for that. ENABLE, DISABLE or INHERIT PREDICTIVE OPTIMIZATION controls automatic OPTIMIZE and VACUUM on a managed Delta table. By default a table inherits the setting from its schema. CLUSTER BY adds, changes or drops the clustering strategy of a Delta table. ALTER TABLE ... RENAME TO renames a table.
ALTER TABLE test SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');ALTER TABLE my_table ENABLE PREDICTIVE OPTIMIZATION;External tables have one extra maintenance task. If a non-Databricks client, or path-based access from inside Databricks, changes the table's metadata, Unity Catalog does not pick up the change on its own. Databricks recommends against making changes that way. If one happens anyway, run MSCK REPAIR TABLE <table-name> SYNC METADATA to bring the Unity Catalog schema up to date.
For managed tables, two catalog-level settings can be changed. ALTER CATALOG ... SET MANAGED LOCATION changes where *new* managed tables are stored. RETAIN DROPPED TO sets how long dropped managed tables can be recovered: 0 to turn recovery off, or between 7 and 30 days.
ALTER CATALOG my_catalog RETAIN DROPPED TO 30 DAYS;Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Converting an external table works like converting a foreign table, so you add MOVE or COPY to SET MANAGED.Why is that wrong?
MOVE and COPY are for foreign tables only. On an external table they cause the command to fail. Use plain SET MANAGED.
2.Any external table can be converted with SET MANAGED, whatever its file format.Why is that wrong?
SET MANAGED works only on external tables in Delta Lake format. External tables in CSV, JSON, Parquet and similar formats don't meet the prerequisite.
3.A table converted from external still drops like an external table, so you can drop it and the original files are safe.Why is that wrong?
After conversion the table is managed. Run UNSET MANAGED before dropping it, or you risk data loss or inconsistencies.
Covered in Going back: UNSET MANAGED
4.UNSET MANAGED works the same on every table, with no extra clause, including tables that have Iceberg reads enabled.Why is that wrong?
If the table has Iceberg reads enabled and you omit TRUNCATE UNIFORM HISTORY, Databricks raises DELTA_ALTER_TABLE_UNSET_MANAGED_DOES_NOT_SUPPORT_UNIFORM.
Covered in Going back: UNSET MANAGED
Practise it for real
Convert a Unity Catalog external Delta table to managed, confirm the change, then roll it back.
1.Run DESCRIBE EXTENDED on your external Delta table and check its type. Run DESCRIBE DETAIL to check its protocol versions and table features.
Why: The SET MANAGED syntax depends on the source type, and tables with columnMapping at minReaderVersion=2/minWriterVersion=7 fail to convert.
You should see: The table is reported as EXTERNAL, with no blocking feature combination.
2.On Serverless compute or Databricks Runtime 17.3 LTS+, run ALTER TABLE catalog.schema.my_external_table SET MANAGED; (no MOVE or COPY).
Why: This is the documented command for converting external tables.
You should see: The command finishes, and Catalog Explorer shows the table type as MANAGED (refresh if needed).
3.Query the table by name, and restart any streams that were reading it.
Why: The name, permissions and history are kept, but existing streams fail after conversion.
You should see: Name-based queries return the same data, and restarted streams run.
4.Run ALTER TABLE catalog.schema.my_external_table UNSET MANAGED;
Why: Rollback is supported within 14 days and repoints the metadata at the original external location.
You should see: The table shows as EXTERNAL again, and writes made while it was managed are kept.
Stuck? Get a nudge
If SET MANAGED fails with DELTA_ALTER_TABLE_SET_MANAGED_UNSUPPORTED_COPY_MOVE_SYNTAX, remove the MOVE or COPY keyword.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The conversion retains table configurations, including name, settings, permissions, and views, and retains table history.”
↩︎ Converting external to managed with SET MANAGED“Although you can also use CREATE TABLE AS SELECT (CTAS) to convert an external table to a managed table, Databricks recommends SET MANAGED”
↩︎ Converting external to managed with SET MANAGED“Initial data copy (no downtime): The command copies table data and Delta transaction log from the external location to the managed location.”
↩︎ What happens during conversion, and the Catalog Explorer route“During this step, all writes to the external location are temporarily blocked, resulting in writer downtime.”
↩︎ What happens during conversion, and the Catalog Explorer route“After table conversion, existing read and write streams fail.”
↩︎ What happens during conversion, and the Catalog Explorer route“Converting external to managed tables using Catalog Explorer is in Beta.”
↩︎ What happens during conversion, and the Catalog Explorer route“After converting an external table to a managed table, you can roll back within 14 days by using the UNSET MANAGED command.”
↩︎ Going back: UNSET MANAGED“Seven days after rollback, Databricks automatically deletes data in the managed location.”
↩︎ Going back: UNSET MANAGED“To convert an external table, use SET MANAGED without a MOVE or COPY option. Those options apply only to foreign tables.”
↩︎ Exam trap 1“Format: The table must use the Delta Lake format.”
↩︎ Exam trap 2“Dropping the table without running UNSET MANAGED first might result in data loss or inconsistencies.”
↩︎ Exam trap 3“If you add MOVE or COPY options when converting an external table, the command fails with DELTA_ALTER_TABLE_SET_MANAGED_UNSUPPORTED_COPY_MOVE_SYNTAX.”
↩︎ Prediction“Format: The table must use the Delta Lake format.”
↩︎ Checkpoint“Under About this table (table detail page) or About this schema (schema detail page), click Explore optimizations.”
↩︎ Checkpoint“Databricks preserves all writes made to the managed location after conversion.”
↩︎ Prediction“You MUST run UNSET MANAGED before dropping the managed table.”
↩︎ Checkpoint - 2.
“Rolls a table converted with SET MANAGED back to an external table by updating the table metadata to point to the original external location.”
↩︎ Going back: UNSET MANAGED“Transfers ownership of the table to principal.”
↩︎ Modifying tables: replace contents, resync metadata, change defaults“If the table has Iceberg reads enabled and you omit this option, Databricks raises DELTA_ALTER_TABLE_UNSET_MANAGED_DOES_NOT_SUPPORT_UNIFORM.”
↩︎ Exam trap 4 - 3.https://docs.databricks.com/aws/en/tables/externalOfficial docs
“When you define a Unity Catalog external table, you must specify a storage location.”
↩︎ Going back: UNSET MANAGED“you must run MSCK REPAIR TABLE <table-name> SYNC METADATA to bring the schema in Unity Catalog up to date.”
↩︎ Modifying tables: replace contents, resync metadata, change defaults - 4.
“CREATE OR REPLACE TABLE has the same semantics regardless of the table type or metastore in use.”
↩︎ Modifying tables: replace contents, resync metadata, change defaults“Instead, Databricks recommends always using CREATE OR REPLACE TABLE, as in the following example:”
↩︎ Checkpoint - 5.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-alter-catalogOfficial docs
“New managed tables and managed volumes created in the catalog use the updated location. Databricks does not move existing objects.”
↩︎ Modifying tables: replace contents, resync metadata, change defaults“Databricks does not move existing objects.”
↩︎ Prediction