Create external tables with properties set in delta log and no collation

[santosh-santosh]

• There is an external delta lake table that need to be mounted on to the unity catalog
• It has some properties configured in the _delta_log folder already
• When try to create table using CREATE TABLE catalog_name.schema_name.table_name USING DELTA LOCATION 's3://table_path' it throws, [DELTA_CREATE_TABLE_WITH_DIFFERENT_PROPERTY] The specified properties do not match the existing properties at 's3://table_path' due to the collation property getting added by default to the create table query
• How to mount such external table to the unity catalog?

[ManojkMohan]

Root Cause:

Unity Catalog’s CREATE TABLE command automatically adds default Delta table properties (like collation) which do not match the existing properties stored inside the table’s _delta_log folder on disk.

Solution thinking:

Use Unity Catalog CLI (uc table create) with parameters that exactly match the existing table metadata and properties. 

The CLI command is used to create an external table in Unity Catalog that points to existing data stored in an external location (e.g., an S3 path containing a Delta table).

Key Required Parameters
--full_name: The full name of the table including catalog, schema, and table name.
Example: my_catalog.my_schema.my_table

--columns: The schema of the table expressed as SQL column definitions with names and data types.
Example: "id INT, name STRING, ts TIMESTAMP"

--storage_location: The external storage path where the Delta table files reside.
Example: s3://my-bucket/path/to/delta_table

Optional Parameters
--format: Data format (default is DELTA), explicitly specify if needed.

--properties: Table properties JSON string that must exactly match the existing Delta table properties on disk to avoid mismatch errors.

Example CLI command to register existing Delta table:
bash
bin/uc table create \
--full_name my_catalog.my_schema.my_table \
--columns "id INT, name STRING, ts TIMESTAMP" \
--storage_location "s3://my-bucket/path/to/delta_table" \
--format DELTA \
--properties '{"delta.appendOnly": "false", "delta.autoOptimize.optimizeWrite.enabled": "true", "collation": "..."}'
Here, --properties must reflect the actual properties from the _delta_log folder of the existing Delta table (including the collation property if present).

This avoids errors such as [DELTA_CREATE_TABLE_WITH_DIFFERENT_PROPERTY] caused due to property mismatches.

[santosh-santosh]

But, we would need to achieve it via SQL interface as there are multiple of such tables that needs on-boarding

[ManojkMohan]

Use standard CREATE TABLE SQL DDL commands specifying:

The full Unity Catalog table name: catalog.schema.table

The exact column schema to match the external Delta table schema

The external LOCATION of the existing Delta table

The USING DELTA clause to specify data format

This will register an external table in Unity Catalog that points to the existing Delta files without moving or modifying data.

The table properties must match the existing Delta table properties exactly.
If Unity Catalog/Databricks adds extra default properties such as collation, and they differ from the Delta table's _delta_log properties, you may face property mismatch errors

To avoid conflicts with automatic property additions (like collation), explicitly specify all existing properties in TBLPROPERTIES

to onboard multiple tables efficiently:
Create a script or notebook to:

Read the schemas and properties of all existing external Delta tables.

Generate the SQL CREATE TABLE statements with schema, location, and exact properties.

Execute those SQL statements in batch to register all external tables in Unity Catalog.

[santosh-santosh]

Thanks for the reply Manoj

To avoid conflicts with automatic property additions (like collation), explicitly specify all existing properties in TBLPROPERTIES

Even when we try adding the TBLPROPERTIES param, its still failing with the mismatch of properties as the collation is added by Databricks to all CREATE TABLE queries by default

[ManojkMohan]

I will try reproducing this, i have sent you a DM can you check that 

[ManojkMohan]

@santosh-santosh Did you execute the steps part by part i shared in the DM ? Step 0: Define your external tables, Step 1: Inspect external Delta table schema & properties 

Success Check:

Ensure all expected properties  are captured  Step 2: Inspect Unity Catalog defaults  

Success Check:

Check the spark.sql.collation property in the DESCRIBE EXTENDED output.

Note all UC default properties Step 3: Generate CREATE TABLE statements  

Success Check:

Query the table after creation 

Check that the location points to the correct S3 path.

Check TBLPROPERTIES matches expected.   can you let me know if these steps are working as expected