ConcurrentAppendException Liquid Clustered Table Different Row Concurrent Writes

[georgecalvert]

I have multiple databricks jobs performing a MERGE command simultaneously into the same liquid clustered table but for different rows of data and I am receiving the following error message:

 [DELTA_CONCURRENT_APPEND] ConcurrentAppendException: Files were added to the root of the table by a concurrent update.

I am using databricks runtime 15.3. My understanding is with liquid clustered tables and Databricks runtime > 13.3 concurrent row-level MERGE / UPDATE operations should be possible. DeletionVectors and RowTracking are enabled in my config. 

[Walter_C]

This exception often occurs when concurrent operations may be physically updating different partition directories, but one of them may read the same partition that the other one concurrently updates, thus causing a conflict.

Even though you're working with different rows of data, the condition in your MERGE command might not be explicit enough and can scan the entire table, which can conflict with concurrent operations updating any other partitions.

To avoid this, you can make the separation explicit in the operation condition. For example, if your 'deltaTable' is partitioned by date and country, you can add specific date and country to the merge condition. Here's an example:

deltaTable.as("t").merge(  
  source.as("s"),  
  "s.user_id = t.user_id AND s.date = t.date AND s.country = t.country AND t.date = '" + <date> + "' AND t.country = '" + <country> + "'")  
  .whenMatched().updateAll()  
  .whenNotMatched().insertAll()  
  .execute()

This operation is now safe to run concurrently on different dates and countries. Please replace <date> and <country> with your specific values.