Merge operation to delta table with new column starting with upper case seems to be not working

[alpar]

Hello,

I have a simple spark dataframe saved to a delta table:

data = [
    (1, "John", "Doe"),
    (2, "Jane", "Smith"),
    (3, "Mike", "Johnson"),
    (4, "Emily", "Davis")
]
columns = ["Id", "First_name", "Last_name"]
df = spark.createDataFrame(data, schema=columns)

df.write.format('delta').mode('overwrite') \
    .option('delta.columnMapping.mode', 'name') \
    .save(delta_path)

I want to merge another dataframe to the delta table, containing a new column 'Age'. I have schema evolution enabled, so I would expect the new column to appear in the delta table, but it doesn't.

data = [
    (1, "John2", "Doe2", 25),
    (2, "Jane2", "Smith2", 30),
    (30, "Mike2", "Johnson2", 35),
    (4, "Emily2", "Davis2", 40)
]
columns = ["Id", "First_name", "Last_name", "Age"]
df = spark.createDataFrame(data, schema=columns)

spark.conf.set('spark.databricks.delta.schema.autoMerge.enabled', 'true')

dt = DeltaTable.forPath(spark, delta_path)

dt.alias('existing') \
    .merge(df.alias('updates'), f"existing.Id = updates.Id") \
    .whenMatchedUpdate(
    set = {
        'Last_name': 'updates.Last_name'
    }) \
    .whenNotMatchedInsert(values = {c: f"updates.{c}" for c in columns}) \
    .execute()

If I change the new columns casing to 'age' then it is added to the delta table.

Am I doing something wrong? Does a column name starting with uppercase has any special meaning?

[Alberto_Umana]

Hi @alpar,

The issue you’re experiencing is due to the way Delta Lake handles column names during schema evolution, especially when column mapping is enabled.

Here are the key points to understand:

Delta Lake schema evolution is case-insensitive by default.

When you enable column mapping with `delta.columnMapping.mode = 'name'`, it allows for more flexible column naming, including the use of special characters and spaces.

However, when schema evolution is performed, Delta Lake internally stores column names in lowercase.

In your case, the new column ‘Age’ is not being added because Delta Lake is treating it as equivalent to ‘age’ (which doesn’t exist in the original schema). When you change it to lowercase ‘age’, it’s recognized as a new column and added successfully.

[hari-prasad]

@alpar, the code below is working perfectly for me. You might want to check the DBR version you’re using. I’m running it on version 14.3 LTS without any errors.

# Create New Delta table with columnMapping.mode='name'
data = [
    (1, "John", "Doe"),
    (2, "Jane", "Smith"),
    (3, "Mike", "Johnson"),
    (4, "Emily", "Davis")
]
columns = ["Id", "First_name", "Last_name"]
df = spark.createDataFrame(data, schema=columns)

df.write.format('delta').mode('overwrite') \
    .option('delta.columnMapping.mode', 'name') \
    .save(delta_path)

# Update table with Age column
data = [
    (1, "John2", "Doe2", 25),
    (2, "Jane2", "Smith2", 30),
    (30, "Mike2", "Johnson2", 35),
    (4, "Emily2", "Davis2", 40)
]
columns = ["Id", "First_name", "Last_name", "Age"]
df = spark.createDataFrame(data, schema=columns)

spark.conf.set('spark.databricks.delta.schema.autoMerge.enabled', 'true')

dt = DeltaTable.forPath(spark, delta_path)

dt.alias('existing') \
    .merge(df.alias('updates'), f"existing.Id = updates.Id") \
    .whenMatchedUpdate(
    set = {
        'Last_name': 'updates.Last_name'
    }) \
    .whenNotMatchedInsert(values = {c: f"updates.{c}" for c in columns}) \
    .execute()

[alpar]

@Alberto_Umana Columnmapping doesn’t seem to affect this behaviour, it’s not working even if columnMapping is not set.
If it’s really the case that you cannot add a new column containing any uppercase letters, I think it would be worth at least mentioning in the docs. Otherwise it can really cause confusion, as it did for me, since both spark dataframe and delta table column names are case-sensitive, so I expected that I can merge a new column with uppercase letters.

@hari-prasad Actually, I’m using Spark 3.4 with Delta Lake 2.4. It is not throwing an error (I would prefer if it did), it’s just the new ‘Age’ column is not added to the delta table. Is this something that’s fixed in newer versions of Delta Lake?

[hari-prasad]

I assume you must be facing an error referred here on GitHub issues page. you can follow it, they make release fix for same.

[BUG][Spark] issue when merge using autoMerge property · Issue #3336 · delta-io/delta · GitHub