Can parallel pipeline runs upsert into the same Lakehouse table without commit conflicts?

Thanks a lot, this was very helpful. Your prediction was right, and I could reproduce it.

OPTIMIZE without a partition: After I ran OPTIMIZE on the unpartitioned table, it had merged several _db_id values into shared files. Parallel upserts then failed with ConcurrentDeleteDeleteException (not ConcurrentDeleteReadException), and the Copy sink did not retry it on its own.

What I did: The Copy sink refuses a partition column that is also a key column (KeyColumnIntersectsWithPartitionColumn). If I drop _db_id from the key, the sink matches across partitions and silently overwrites other databases' rows (my databases share the same IDs). So I added a second column with the same value (_db_partition, an additional column in the Copy source) and partitioned by that column. The key stays _db_id + primary key.

What I tested:

• 3 databases loaded one after another into the partitioned table: each kept all its rows, one partition and one file per database.
• OPTIMIZE on a partitioned incremental table compacted the files within each partition (34 files down to 5) and mixed no databases.
• 4 databases in parallel re-upserted already loaded windows into those compacted files: no errors, no duplicates, and all row counts matched.

What I'm doing from here:

• OPTIMIZE and VACUUM only as the last step of the orchestrating pipeline, never on a separate schedule. Not tested: OPTIMIZE running at the same time as an ingest of the same database.
• Keeping the Copy retries.
• Your staging pattern stays as the fallback if conflicts show up in production.