What causes DIVIDE_BY_ZERO?

A ratio or rate calculation (e.g., revenue / orders) where the denominator can be zero. A window function or aggregation producing a zero denominator in some partitions. Upstream data change introduced zero values in a column used as a divisor. A normalization step dividing by a count that is zero for certain groups. A NULL-to-zero coalesce in the denominator without guarding the division

How do I fix DIVIDE_BY_ZERO?

Wrap the division in a NULLIF to avoid division by zero: col / NULLIF(denominator, 0). Use a CASE expression: CASE WHEN denominator = 0 THEN NULL ELSE numerator / denominator END. Use TRY(numerator / denominator) to return NULL instead of raising an error. Investigate why zero values appear in the denominator — they may indicate an upstream data quality issue. Set spark.sql.ansi.enabled = false to return NULL on division by zero instead of raising an error (not recommended for production — masks real data issues)

Medium severitysql

Power BI Refresh Error:
DIVIDE_BY_ZERO

What does this error mean?

A SQL expression attempted to divide a value by zero. By default Databricks raises this as a runtime error and aborts the query.

Common causes

1A ratio or rate calculation (e.g., revenue / orders) where the denominator can be zero
2A window function or aggregation producing a zero denominator in some partitions
3Upstream data change introduced zero values in a column used as a divisor
4A normalization step dividing by a count that is zero for certain groups
5A NULL-to-zero coalesce in the denominator without guarding the division

How to fix it

1Wrap the division in a NULLIF to avoid division by zero: col / NULLIF(denominator, 0)
2Use a CASE expression: CASE WHEN denominator = 0 THEN NULL ELSE numerator / denominator END
3Use TRY(numerator / denominator) to return NULL instead of raising an error
4Investigate why zero values appear in the denominator — they may indicate an upstream data quality issue
5Set spark.sql.ansi.enabled = false to return NULL on division by zero instead of raising an error (not recommended for production — masks real data issues)

Frequently asked questions

Does Databricks always raise an error on division by zero?

Only when ANSI mode is enabled (spark.sql.ansi.enabled = true). In non-ANSI mode, Databricks returns NULL. Most Databricks runtimes enable ANSI mode by default.

Is NULLIF the best way to handle this?

NULLIF(denominator, 0) is the most concise approach. For complex expressions, TRY(expression) is cleaner. Avoid disabling ANSI mode globally as it hides other data errors.