Unity Catalog LOCATION_OVERLAP errors: external tables, external locations and managed storage paths that collide

#Symptoms

Creating a table, volume or external location fails with errors such as:

• [LOCATION_OVERLAP] Input path url 'abfss://data@stlake.dfs.core.windows.net/sales/orders' overlaps with managed storage within 'CreateTable' call.
• INVALID_PARAMETER_VALUE: ... overlaps with other external tables or volumes
• External location ... overlaps with an existing external location

Or a migration from Hive metastore to Unity Catalog stops on dozens of tables with overlap errors.

#The rules Unity Catalog enforces

Each storage path belongs to at most one securable, so permissions can't be bypassed through a different object:

Tables and volumes inside an external location are fine and expected: the external location grants access; the table claims its own sub-path.

#Step 1: find who owns the path

SHOW EXTERNAL LOCATIONS;
DESCRIBE EXTERNAL LOCATION raw_data;

DESCRIBE CATALOG EXTENDED sales;        -- Storage Root / managed location
DESCRIBE SCHEMA EXTENDED sales.core;    -- Location (managed storage for the schema)

Search tables and volumes by path in the information schema:

SELECT table_catalog, table_schema, table_name, table_type, storage_path
FROM   system.information_schema.tables
WHERE  storage_path LIKE 'abfss://data@stlake.dfs.core.windows.net/sales/%';

SELECT volume_catalog, volume_schema, volume_name, volume_type, storage_location
FROM   system.information_schema.volumes
WHERE  storage_location LIKE 'abfss://data@stlake.dfs.core.windows.net/sales/%';

Also check the metastore-level storage root (account console, or DESCRIBE METASTORE / databricks metastores summary).

#Step 2: classify the conflict

#Step 3: fix common layouts

#Managed storage and external data in the same container

A frequent setup: catalog sales has managed location abfss://data@stlake.../ and raw files also live in abfss://data@stlake.../landing/. Every external table under landing/ overlaps managed storage.

Better layout:

abfss://uc-managed@stlake.dfs.core.windows.net/      <- managed storage only (catalog/schema roots)
abfss://landing@stlake.dfs.core.windows.net/         <- external location: raw files, volumes
abfss://curated@stlake.dfs.core.windows.net/         <- external location: external tables if you need them

Changing a catalog's managed location affects where new managed tables go; existing managed tables stay where they are. Plan moves with DEEP CLONE or CREATE TABLE ... AS SELECT into the new layout.

#Nested external tables from Hive metastore

Hive allowed /sales/ (a partitioned table) and /sales/archive/ (another table). In UC, give each its own sibling folder before upgrading:

/sales/orders/            -> external table sales.core.orders
/sales/orders_archive/    -> external table sales.core.orders_archive

Move files with azcopy or dbutils.fs.mv (for small data), then create the tables at the new paths.

#Nested external locations

-- Keep the parent, drop the child, grant on the parent
DROP EXTERNAL LOCATION landing_sales;
GRANT READ FILES ON EXTERNAL LOCATION landing TO `sales-engineers`;

Dropping an external location doesn't delete files, but tables/volumes that depended on it must still be covered by another location.

Caution

Never "fix" overlap by dropping a managed table or volume to free the path: dropping managed objects deletes their data (after the retention period). Check table_type first.

#Step 4: managed instead of external

Many teams create external tables out of habit. Managed tables in Unity Catalog get predictive optimization, liquid clustering defaults and simpler lifecycle, and never overlap because UC chooses the path. Keep external tables for data that other systems must read or write directly by path.

#Prevent it next time

• One container (or top-level prefix) for UC managed storage, separate containers for external data.
• Never create external tables at nested paths; one folder per table.
• Define external locations at container or high-level prefix; don't nest them.
• Document the storage layout and check new requests against it in review.