Database Error: division by zero in dbt model

TL;DR

Some denominator is zero for at least one row. Wrap it with nullif(denominator, 0) so the division yields null instead of raising, then decide whether null is the right business answer or those rows need filtering. Re-run the model and check the null count.

Error

Database Error: division by zero in dbt model

Steps

1. Find the division in the model SQL: search for / and check each denominator expression. Expected: You locate the division, e.g. revenue / order_count.

2. Query the source to confirm zeros exist: select count(*) where denominator = 0. Also check for nulls, which most warehouses treat as null rather than an error, but confirm. Expected: A nonzero count of zero denominators, proving the data cause.

3. Wrap the denominator: revenue / nullif(order_count, 0). If zero denominators are invalid data, add a test or a where filter instead and fix the source. Expected: The model compiles and runs without the error.

4. Add a dbt test on the metric, e.g. a not_null or a custom test asserting the ratio is sane, so regressions get caught. Expected: dbt test passes; future zero denominators surface as test failures, not crashes.

When to use

• warehouse raises division by zero on a dbt model query
• a metric model computing ratios, rates, or unit economics
• the error appeared after new source data arrived

When not to use

• integer division truncation (that is a type issue, not an error)
• the denominator is null, not zero (nulls propagate as null, no error)
• the error is in a macro, not model SQL

Tool compatibility

• nullif() exists on Postgres, Snowflake, BigQuery, Redshift, DuckDB
• dbt tests catch regressions once the immediate crash is fixed
• works for python models too, guard before dividing

Variant phrasings

division by zero in an incremental model

Same fix, but also check the incremental predicate did not change the denominator distribution; a bad filter can manufacture zeros.

modulo by zero

Same root cause. nullif() on the modulus operand works the same way.

Why it happens

Warehouses raise on division by zero instead of returning null (Postgres, Snowflake, Redshift all raise). The model SQL was written assuming the denominator is always positive, and real data eventually violates that. One zero row kills the whole model build.

Edge cases

• nullif returns null for the row; if downstream sums treat null as zero you may silently undercount, document the choice.
• On BigQuery, IEEE_DIVIDE returns null instead of raising; if you port SQL between warehouses the behavior differs, keep nullif for portability.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstbDutJveWyV-FjfZoV7GVg