delta merge - multiple conditions

Question

import delta
Factevents = delta.DeltaTable.forPath(spark, 'abfss://*******************.dfs.core.windows.net/silver/C365/events/')

# Merge new data
(
    Factevents.alias('events')
    .merge(
        dfNewData.alias('New'),
        "events.uprn = New.uprn AND events.id = New.id"
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Notebook extraction. As you can see there is multiple join conditions for the merge. Just using id column results in duplicates in target hence the inclusion of uprn to make composite / unique key. Doesn't work. Anyone able to tell me correct syntax please. Spent far too much time on this already. Thanks.

Answer 1

@Anthony Carroll

Thanks for reaching out to Microsoft Q&A.

Your syntax seems mostly correct, but there might be a few adjustments needed. Here's a refined version of your code:

import delta
from delta.tables import *
# Load the Delta table
Factevents = DeltaTable.forPath(spark, 'abfss://*******************.dfs.core.windows.net/silver/C365/events/')
# Merge new data
(
    Factevents.alias('events')
    .merge(
        dfNewData.alias('New'),
        "events.uprn = New.uprn AND events.id = New.id"
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Here are a few steps to check:

1. Data Types: Ensure that the data types of uprn and id columns in both events and New are the same.
2. Null Values: Check for any null values in the uprn or id columns that might be causing issues.
3. Primary Key: Make sure that the combination of uprn and id is unique in both datasets.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you