What causes CONVERSION_FAILURE?

CAST(string_col AS INT) where the string contains non-numeric characters or is empty. A date string in an unexpected format being cast to DATE or TIMESTAMP. An implicit type coercion in a JOIN or WHERE clause where types are incompatible. A DECIMAL value with too many digits being cast to a DECIMAL with lower precision. A boolean string 'yes'/'no' being cast to BOOLEAN, which only accepts 'true'/'false'

How do I fix CONVERSION_FAILURE?

Replace CAST with TRY_CAST to return NULL on failure instead of raising an error: TRY_CAST(col AS INT).. Pre-clean the column: filter out non-numeric strings before casting, or use regexp_replace.. For dates, use to_date(col, 'yyyy-MM-dd') with an explicit format string rather than implicit CAST.. Increase DECIMAL precision to accommodate the actual value range: CAST(col AS DECIMAL(18,4)).. Normalize boolean strings before casting: CASE WHEN col IN ('yes','true','1') THEN true ELSE false END.

Medium severitydata quality

Power BI Refresh Error:
CONVERSION_FAILURE

What does this error mean?

A value could not be converted from its source type to the target type during a CAST, type coercion, or data loading operation. Unlike TRY_CAST which returns NULL on failure, a hard CAST raises CONVERSION_FAILURE when the value is incompatible.

Common causes

1CAST(string_col AS INT) where the string contains non-numeric characters or is empty
2A date string in an unexpected format being cast to DATE or TIMESTAMP
3An implicit type coercion in a JOIN or WHERE clause where types are incompatible
4A DECIMAL value with too many digits being cast to a DECIMAL with lower precision
5A boolean string 'yes'/'no' being cast to BOOLEAN, which only accepts 'true'/'false'

How to fix it

1Replace CAST with TRY_CAST to return NULL on failure instead of raising an error: TRY_CAST(col AS INT).
2Pre-clean the column: filter out non-numeric strings before casting, or use regexp_replace.
3For dates, use to_date(col, 'yyyy-MM-dd') with an explicit format string rather than implicit CAST.
4Increase DECIMAL precision to accommodate the actual value range: CAST(col AS DECIMAL(18,4)).
5Normalize boolean strings before casting: CASE WHEN col IN ('yes','true','1') THEN true ELSE false END.

Frequently asked questions

What is the difference between CAST and TRY_CAST in Databricks?

CAST raises CONVERSION_FAILURE if the value cannot be converted. TRY_CAST returns NULL instead. For production pipelines processing user-generated or external data, prefer TRY_CAST and validate nulls downstream.

Does ansi_mode=true affect conversion behavior?

Yes. With spark.sql.ansi.enabled=true, implicit type coercions that would otherwise succeed may raise CONVERSION_FAILURE. Databricks SQL warehouses have ANSI mode enabled by default.