topic Re: Create external tables with properties set in delta log and no collation in Data Engineering

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

santosh-santosh — Wed, 10 Sep 2025 18:01:22 GMT

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?

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

ManojkMohan — Wed, 10 Sep 2025 18:09:03 GMT

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.

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

santosh-santosh — Wed, 10 Sep 2025 18:12:16 GMT

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

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

ManojkMohan — Wed, 10 Sep 2025 18:17:51 GMT

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.

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

santosh-santosh — Thu, 11 Sep 2025 08:02:43 GMT

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

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

ManojkMohan — Thu, 11 Sep 2025 08:32:32 GMT

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

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

ManojkMohan — Fri, 12 Sep 2025 09:09:30 GMT

@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

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

santosh-santosh — Mon, 22 Sep 2025 12:29:17 GMT

@ManojkMohan
In the steps that you mentioned, I'm still getting the same issue

CREATE TABLE catalog.schema.table1 (
id BIGINT,
name STRING
)
USING DELTA
LOCATION 's3://bucket/path/table1'
TBLPROPERTIES (
... all my properties in the _delta_log .....
'spark.sql.collation' = 'UTF8_BINARY'
);

It says the spark.sql.collation is not matching with the propeties in the _delta_log if that particular table

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

ManojkMohan — Mon, 22 Sep 2025 12:43:21 GMT

can you share the below logs here if possible

Log Source	How to Enable/Use
Unity Catalog audit logs	Enable in workspace settings, configure external log destination
Spark/Delta engine logs	Set log level DEBUG, configure cluster log collection
Query logs as Delta tables	If audit/logs ingested as tables
Delta transaction logs	Inspect raw _delta_log JSON files & DESCRIBE DETAIL

These logs combined will pinpoint if spark.sql.collation mismatch is due to UC defaults, inconsistency in CLI properties payload, or a Delta transaction metadata drift