Problem

While performing a merge operation in Databricks Runtime 14.3 LTS or above, you encounter an error related to incompatible decimal scales. 

Caused by: com.databricks.sql.transaction.tahoe.DeltaAnalysisException: [DELTA_MERGE_INCOMPATIBLE_DECIMAL_TYPE] Failed to merge decimal types with incompatible scale 6 and 5 (or any other scale) when attempting to merge fields with decimal types in Databricks.

Additionally, you may see the following error message.

[DELTA_FAILED_TO_MERGE_FIELDS] Failed to merge fields '<your-column-name>' and '<your-column-name>' SQLSTATE: 22005
Caused by: com.databricks.sql.transaction.tahoe.DeltaAnalysisException: [DELTA_MERGE_INCOMPATIBLE_DECIMAL_TYPE] Failed to merge decimal types with incompatible scale 6 and 5.

Cause

You have mismatched decimal scales between the merging fields.

Context

A change in the behavior of decimal type casting has been introduced as of Databricks Runtime 14.3 LTS. 

In earlier Databricks Runtime versions, a bug in Apache Spark caused automatic casting of decimal values during operations, which could result in unintentional loss of precision. Databricks Runtime 14.3 LTS includes a fix which enforces stricter type compatibility for decimal scales. 

Solution

If you require compatibility with earlier Databricks Runtime behavior, set the following configuration property using a notebook. This configuration reverts the runtime to the legacy behavior, ensuring that decimal values are cast in a manner compatible with earlier versions.

spark.conf.set("spark.sql.legacy.decimal.retainFractionDigitsOnTruncate", True)

For greater control and accuracy, explicitly cast the columns to a specific decimal type using the cast function. This ensures that the column is explicitly defined with the correct precision and scale, avoiding compatibility issues during operations.

Example

from pyspark.sql.functions import col
df = df.withColumn("<your-column-name>", col("<your-column-name>").cast("decimal(<cast-precision>)"))