What causes Test Failure: not_null?

A required field in the source system was not populated for some records (e.g., a new optional field with no backfill). A LEFT JOIN in the model produced NULLs for the right-side columns when there was no match. A COALESCE or CASE expression did not cover all possible input combinations, leaving some rows with NULL output. An incremental model insert path skips a transformation that the full-refresh path applies, leaving NULL in new rows. A source table column was renamed and the model now reads the old column name, which returns NULL for all rows

How do I fix Test Failure: not_null?

Run this query to understand the scope of null values: `SELECT COUNT(*) FROM WHERE IS NULL`.. Trace the column back through the model SQL to find where NULLs are introduced.. If the source is the origin, add a filter or default value at the staging layer using COALESCE.. Review any LEFT JOINs — if NULLs from unmatched rows are expected, reconsider whether the column should have the not_null test.. If a column was renamed in the source, update the model SQL to use the new column name.

Medium severitydbtdbt Cloud →

dbt Error:
Test Failure: not_null

What does this error mean?

dbt's not_null test found NULL values in a column expected to always have a value — missing source data, an unmatched JOIN, or a transformation that lost values.

Common causes

1A required field in the source system was not populated for some records (e.g., a new optional field with no backfill)
2A LEFT JOIN in the model produced NULLs for the right-side columns when there was no match
3A COALESCE or CASE expression did not cover all possible input combinations, leaving some rows with NULL output
4An incremental model insert path skips a transformation that the full-refresh path applies, leaving NULL in new rows
5A source table column was renamed and the model now reads the old column name, which returns NULL for all rows

How to fix it

1Run this query to understand the scope of null values: `SELECT COUNT(*) FROM <model> WHERE <column> IS NULL`.
2Trace the column back through the model SQL to find where NULLs are introduced.
3If the source is the origin, add a filter or default value at the staging layer using COALESCE.
4Review any LEFT JOINs — if NULLs from unmatched rows are expected, reconsider whether the column should have the not_null test.
5If a column was renamed in the source, update the model SQL to use the new column name.

Frequently asked questions

Can I configure the test to only warn instead of failing the job?

Yes. In your schema.yml or dbt_project.yml, set `severity: warn` for the test. This allows the dbt run to complete while still flagging the data quality issue in the test results.

Source · dbt-docs