How to resolve Location Overlap

[ep208]

Hi,

I am trying to ingest abfss://datalake@datalakename.dfs.core.windows.net/Delta/Project1/sales_table but when writting the table schema on the yamls, I uncorrectly wrote this table in other unit catalog table:

---

kind: SinkDeltaTable

metadata:

  name: delta-Project1-sales_table

spec:

  dataLakeConnection: dlc-datalake

  database: dbrdelta

  table: post_sales

  tablePath: Delta/Project1/sales_table

Once I realized of my mistake, i corrected it: 

---

kind: SinkDeltaTable

metadata:

  name: delta-Project1-sales_table

spec:

  dataLakeConnection: dlc-datalake

  database: dbrdelta

  table: sales_table

  tablePath: Delta/Project1/sales_table

As I have run the code with the typo, after I have refined the yaml, I tried again to create the sales_table but I got this message:

AnalysisException: [RequestId=79ba396e-1572-4ae7-9ae5-004467cb5b10 ErrorClass=INVALID_PARAMETER_VALUE.LOCATION_OVERLAP] Input path url 'abfss://datalake@datalakename.dfs.core.windows.net/Delta/Project1/sales_table' overlaps with other external tables or volumes within 'CreateTable' call. Conflicting tables/volumes: datalakename.dbrdelta.post_sales.

I have tried to remove the table in DL and in Unit Catalog, but still the Databricks job failed. Furthermore, I have checked out the schema table and it seems correct.

Do you have any idea how to amend this error?

[Isi]

Hey @ep208 ,

From the error message you’re seeing (LOCATION_OVERLAP), it seems that Unity Catalog is still tracking a table or volume that points to the same path you’re now trying to reuse:

abfss://datalake@datalakename.dfs.core.windows.net/Delta/Project1/sales_table

Even if you’ve deleted the table using DROP TABLE, Unity Catalog may still retain metadata references, or there may be a volume or undropped table linked to that location.

You can run this SQL to get a list of tables and volumes in the schema along with their physical paths:

SELECT 
  table_catalog,
  table_schema,
  table_name,
  storage_path
FROM system.information_schema.tables
WHERE table_schema = 'dbrdelta'
  AND storage_path LIKE '%Project1/sales_table%';

This will help confirm if any other object is still pointing to that location.

If you find a table like post_sales or a volume using that path, try:

DROP TABLE IF EXISTS dbrdelta.post_sales;
DROP VOLUME IF EXISTS dbrdelta.post_sales;

If that doesn't work

Sometimes Unity Catalog retains dropped tables for 7 days for safety (this is the “time travel” retention window). If that’s the case, you can recover the dropped table with:

UNDROP TABLE dbrdelta.post_sales;

Then simply rename it to the correct name:

ALTER TABLE dbrdelta.post_sales RENAME TO dbrdelta.sales_table;

This is often the cleanest approach since:

• The table already owns the correct path.

• The data and metadata are valid.

• You avoid LOCATION_OVERLAP errors.

Hope this helps, 🙂


Isi