Forum Discussion
Can parallel pipeline runs upsert into the same Lakehouse table without commit conflicts?
I want to ingest from 10 DB2 databases into a Lakehouse using one generic pipeline, invoked once per DB. They all write into the same Bronze tables (e.g. .raw_<table>). Every row carries a _db_id, so no two runs ever touch the same record.
The Copy activity uses Table action = Upsert with key columns _db_id + the source primary key. The tables are not partitioned or clustered. In DESCRIBE HISTORY the commits show operation = MERGE, engineInfo = datafactorycopy, and operationParameters, operationMetrics, readVersion and isBlindAppend are all NULL.
I would like to run the DBs in parallel, but we are unsure about commit conflicts. The concurrency control article says conflicts depend on the files a MERGE reads, and that the partition column must be a literal in the merge condition. It describes Spark, though, and I could not find anything about the Copy sink.
Questions:
- Does the Copy activity upsert read the whole target table during the MERGE, or can it prune files, for example by _db_id?
- Can two parallel Copy upserts (different DBs, same table) fail with something like ConcurrentAppendException? Does the sink retry internally?
- Would partitioning the table by _db_id help with the Copy sink, even though we cannot set a literal predicate?
- Is there a recommended pattern for this, for example append into per-DB staging tables and a single MERGE afterwards?
For now we run the DBs sequentially. Any experience or documentation pointers would be appreciated.
3 Replies
Thanks, I found the same note in the Lakehouse connector docs ("Partition columns cannot overlap with key columns"), so partitioning by _db_id is not an option for us.
As suggested, I tested parallel upserts in a non-production setup.
Setup
- One generic ingest pipeline loops over the tables (sequential ForEach). Each iteration runs a Copy activity from DB2 into a Lakehouse table: Upsert with keys _db_id + primary key, unpartitioned tables, 2 retries.
- To simulate 10 databases, a separate test pipeline calls the ingest pipeline through 10 Invoke pipeline activities without dependencies. Each uses a different _db_id and the same DB2 connection.
Test 1: two parallel runs, small tables. The Copy activities overlapped on the same table, and the MERGE commits landed 7 to 21 seconds apart. Both runs succeeded.
Test 2: ten parallel runs, small table (about 35 rows per run). My first attempt was misleading: the notebook activities queued for Spark sessions, so the Copy activities barely overlapped. With a shared session tag, all 10 Copy activities started within about 2 seconds. All runs succeeded, with no errors and no Copy retries. Sink write time was 11 to 18 seconds, compared to about 9 seconds for a single run, with no staircase of growing durations.
Test 3: ten parallel runs, larger full-load table (57,512 rows per run). A high concurrency session holds at most 5 notebooks, so the runs started in two groups of 5. Most databases already had rows in the table, so this was mostly updates.
- All 10 runs succeeded, with no errors and no Copy retries. Each database ended up with exactly 57,512 rows.
- DESCRIBE HISTORY shows 10 consecutive MERGE versions. The 5 commits of one group landed within 1.5 seconds, roughly 0.35 seconds apart, as if committed one after another. Sink write time stayed at 11 to 14 seconds.
- The table had one file per database afterwards, so the databases did not rewrite each other's files. I suspect that is part of why there were no conflicts, but I have not confirmed it.
What this does not show: how the Copy sink handles conflicts internally (operationParameters, readVersion and isBlindAppend are still NULL), or what happens after OPTIMIZE, which could merge several databases into one file. As you said, a passing test is no guarantee under higher load.
- rajendraongole1
Super User
Hi Microfyx -Adding to the previous response, one important consideration is that Fabric Lakehouse Copy activity does not support using the same column as both a partition column and an Upsert key column.
Since _db_id is part of your composite Upsert key, partitioning by _db_id may not be supported through the Copy activity configuration.
Also, the NULL values in DESCRIBE HISTORY do not confirm whether the Copy sink performs partition pruning or which files were accessed during the MERGE.
I would suggest testing parallel Upserts in a non-production environment with two different _db_id values and checking for concurrency exceptions. However, a successful test would not guarantee conflict-free execution under higher concurrency.
For production, the parallel Append to staging → controlled MERGE into Bronze approach would provide better control over concurrency, retries, and error handling.
You can also make the MERGE idempotent using _db_id and the source primary key to support safe retries.
Reference: Lakehouse Copy activity configuration
Hope this helps!
v-aatheequeCommunity Support
Hi Microfyx
Thank you for sharing the details of your scenario.
Based on the information available, I would recommend avoiding parallel Upsert operations against the same Lakehouse Delta table until the Copy sink's concurrency behavior has been verified. Although each pipeline run uses a different _db_id, this alone does not guarantee that concurrent transactions will be conflict-free.
Here are the recommended considerations:
Avoid parallel upserts into the same Lakehouse table until you verify the Copy sink's concurrency behavior. Different _db_id values do not guarantee conflict-free writes.
Best practice: Ingest all 10 DB2 sources in parallel into staging tables using Append, then run a single sequential MERGE into the Bronze tables using _db_id and the source primary key.
Partitioning by _db_id may help reduce conflicts, but only if the Copy sink's merge predicate enables effective partition pruning.Retries: Microsoft's documentation does not confirm the Copy sink's automatic retry behavior for Delta commit conflicts.
Concurrency control for Delta tables - Microsoft Fabric | Microsoft Learn
