Hi @Islam_hoti 

This is a classic "Optimistic Concurrency Control" bottleneck in Delta Lake. Even with partitions, the MERGE operation requires a metadata-level lock on the table's transaction log, which is where your contention is happening.

Here is a breakdown of the answers to your questions, framed for your Architecture Review with Ilir:

1. Does Row-Level Concurrency (Deletion Vectors) apply to MERGE?

Yes, but with caveats. Deletion Vectors (DVs) improve concurrency by avoiding the need to rewrite data files during DELETE or UPDATE operations. However, when you run a MERGE, Delta still performs a "read-modify-write" operation. If two jobs are attempting to commit at the same time, the Delta protocol detects that the table version has advanced since the MERGE started, triggering the ConcurrentAppendException. DVs help with data file contention, but they do not eliminate transaction log contention.

1. Is Partitioning by source_system helping or hurting?

It is currently helping by pruning data, but it is not preventing the metadata lock. Partitioning is necessary for performance, but Delta's concurrency model still checks the entire table's transaction log for serializability. Because you are using MERGE (which is a heavy operation compared to APPEND), Delta is strictly enforcing transaction isolation. Liquid Clustering is likely a better long-term strategy for data layout, but it will not solve the concurrency conflict—in fact, it might make it worse because Liquid Clustering does not use static partitions that Delta can lock as easily.

1. How to inspect the commit conflict?

You can inspect the DESCRIBE HISTORY output to see the operation metrics. More importantly, use the delta.log files (via DESCRIBE DETAIL table_name to get the path) to see if there are frequent small commits. If you want to see exactly what happened, you can use:

SELECT * FROM table_changes('your_table_name', start_version, end_version)

This shows the actual data changes, but to see why the conflict occurred, you need to look at the Delta Log JSON files in the _delta_log folder. If you see many small files being created, your write amplification is causing long transaction times, increasing the window for collisions.

1. Is one table with six concurrent writers the wrong design?

Yes, for your current scale and frequency. The "Retry Storm" you described is a clear indicator that the architecture is hitting a physical limit of the Delta Log.

• The Recommendation: Stop trying to force concurrent MERGE into one table.
• The "Gold Standard" Alternative: Instead of a UNION view, move to a Medallion Architecture. Have each job write to its own staging table, and then have a single, separate, scheduled job that performs a MERGE from those staging tables into the final table.
• Why? This decouples the "Writer" (which is now just a fast APPEND) from the "Merger" (which handles the expensive logic). This moves the complexity into a single, predictable window, eliminating the ConcurrentAppendException entirely.