MERGE - Notebook cell giving error while doing data merge in table using Optimize and Shuffle.

Hi akakkar ,

Thanks for reaching out to the Microsoft fabric community forum.

Thanks for sharing the details and error trace. Based on your description and the stack trace (Py4JJavaError during .merge().execute()), the issue seems to be caused by one or more of the following factors, especially in combination with the Delta Lake optimization settings you're using.
Data Skew or Partitioning Issues
Using spark.microsoft.delta.merge.lowShuffle.enabled=true can lead to shuffle or skew-related failures if the data is not evenly distributed (e.g., if AccountId has many repeated values).
Incompatible Schema or Column Mapping
The set_dict used for updating may include columns with mismatched data types or missing in either the source or target table, which can cause merge execution to fail.
Delta Table State or Concurrency Conflicts
If another process is writing to the Delta table or if there's a stale transaction log, the merge might fail intermittently.
Resource Constraints or Executor Failures
Optimizations like optimizeWrite and lowShuffle can increase memory or shuffle pressure, causing sporadic failures under certain cluster loads.
Disable Optimizations for Isolation Testing
Try disabling the optimizations to confirm stability:
spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "false")
spark.conf.set("spark.microsoft.delta.merge.lowShuffle.enabled", "false")
Validate Column Mappings
Ensure all columns in set_dict exist in both DataFrames and their data types match.
print(df_fullaccount.dtypes)
print(target_table.toDF().dtypes)
Repartition Input Data
Improve distribution to prevent skew:
df_fullaccount = df_fullaccount.repartition("AccountId")
Add Full Error Logging
To see the actual Java exception behind Py4JJavaError, use:
import traceback
traceback.print_exc()
Check Spark UI Logs
If the above doesn’t isolate the issue, please review the executor logs in the Spark UI for detailed exception traces.

Low Shuffle Merge optimization on Delta tables - Azure Synapse Analytics | Microsoft Learn

Delta Lake table optimization and V-Order - Microsoft Fabric | Microsoft Learn

Tutorial: Delta Lake - Azure Databricks | Microsoft Learn

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana