How to resolve Location Overlap

ep208
New Contributor

‎05-14-2025 11:43 PM - edited ‎05-14-2025 11:46 PM

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?

0 Kudos

Isi
Honored Contributor III

‎05-17-2025 12:36 PM

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

0 Kudos