ConcurrentAppendException on parallel MERGE into the same Delta table, despite disjoint keys

[Islam_hoti]

Hi everyone, Hoping someone has been through this, because I am running out of ideas. Setup. DBR 15.4 LTS, Unity Catalog managed Delta table, roughly 900 million rows. Six upstream sources each write into the same target table through their own job, running in parallel on a schedule. Each job does a MERGE keyed on entity_id, and the sources are disjoint, meaning no two jobs ever touch the same entity_id. The table is partitioned by source_system, and each job only ever writes its own partition value. Problem. Two or three of the six jobs fail on most runs with ConcurrentAppendException, saying files were added by a concurrent update. Retries usually succeed, but the retry storm pushes the whole window well past its SLA, and it is getting worse as the table grows. What I have tried. Adding source_system to the merge condition explicitly, so the predicate names the partition, not just the join key. This helped a little but did not eliminate it. Enabling deletion vectors, which I understood should allow row level concurrency. The exceptions still occur, though possibly less often. I am not confident this actually took effect, since I am not sure how to verify that row level concurrency is being used for a given commit. Staggering the job start times, which reduced collisions but obviously does not scale and is not a real fix. Questions. Does row level concurrency actually apply to MERGE, or only to DELETE and UPDATE? I have read conflicting things and cannot find a clear statement. Is partitioning by source_system helping or hurting here? I chose it specifically to isolate writers, but I am now wondering whether liquid clustering would give better isolation, or whether that is unrelated to the conflict detection. Is there a way to inspect a commit after the fact and see why the conflict was detected, rather than guessing at the predicate? Something in the transaction log or the history that shows which files the conflict was attributed to. And the architectural question: is a single table with six concurrent writers simply the wrong design? The alternative I am considering is six separate tables with a union view on top, but that pushes complexity onto every downstream consumer and I would rather not if the concurrency issue is solvable. Happy to share the exact merge condition and a history output if that helps narrow it down. Thanks.

[data_pulse]

The key point you mentioned is that table is partitioned by source_system. Row-level concurrency does apply to MERGE, but Databricks documents that partitioned tables do not support row level concurrency, even with deletion vectors enabled. So these MERGEs can still conflict at the file/partition level.

If each job owns a single source_system, make that partition restriction explicit in the predicate.

Eg: AND t.source_system = 'SOURCE_A'

rather than relying only on t.source_system = s.source_system

Liquid clustering is relevant mainly because it lets you keep the table unpartitioned, which is compatible with row level concurrency but it does not itself guarantee conflict free writes.

There is also a similar community discussion around concurrent MERGE operations hitting same exception here

Before splitting into six tables, try validating the exact MERGE predicate and consider whether an unpartitioned table with deletion vectors / row-level concurrency is a better fit.

[Khasim_1 · Accepted solution]

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.