How can I avoid conflicts between UPDATE and DELETE operations on a Delta table in Databricks when the table is not partitioned by the same columns used in the conditions of these operations?
To avoid conflicts between UPDATE and DELETE operations on a Delta table in Databricks when the table is not partitioned by the same columns used in the conditions of these operations, you can partition the table by the same columns as those used in the conditions of the operations. This will make the two sets of files disjoint and reduce conflicts significantly. However, partitioning a table by a column that has high cardinality can lead to other performance issues due to the large number of subdirectories.

For example, if you have the following two commands:

UPDATE table WHERE date > '2010-01-01'...
DELETE table WHERE date < '2010-01-01'...

You can avoid conflicts by partitioning the table by date. This will ensure that the two operations do not modify the same set of files.

Additionally, you can make the separation explicit in the operation condition by adding specific date and country to the merge condition, as shown in the following 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.

It's important to note that, with the default isolation level of WriteSerializable, files added by blind INSERT operations (that is, operations that blindly append data without reading any data) do not conflict with any operation, even if they touch the same partition (or anywhere in an unpartitioned table). If the isolation level is set to Serializable, then blind appends may conflict.