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 severitysqlDatabricks →

Databricks Error:
DIVIDE_BY_ZERO

What does this error mean?

Databricks raises DIVIDE_BY_ZERO (SQLSTATE 22012) when a SQL expression divides by zero and ANSI mode is active — the default on DBR 12+. The query aborts immediately without partial results. In a scheduled pipeline this means the entire Databricks job fails, downstream Delta tables are not updated, and any Power BI dataset or dbt model that depends on that table stays stale until the next successful run. The error typically surfaces during aggregations, window functions, or ratio calculations where a denominator column contains zero for a subset of rows. Engineers usually first see it as a red FAILED state in the Databricks job run UI, with the Spark driver log showing the DIVIDE_BY_ZERO class and the exact line of SQL that triggered it.

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).

Example log output

org.apache.spark.SparkException: [DIVIDE_BY_ZERO] Division by zero. SQLSTATE: 22012. To tolerate division by zero, use TRY_DIVIDE or set spark.sql.ansi.enabled to false.
  at Job_20260511_143201, Stage 3, Task 47 (attempt 0) FAILED
  Caused by: net.snowflake.client.jdbc.SnowflakeSQLException at transform_revenue_kpis.sql, line 38: revenue_per_order = total_revenue / order_count

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.

Source · docs.databricks.com/aws/en/error-messages/error-classes.html