how to fix dbt test not_null failed error on staging model

TL;DR

Staging is where source nulls first become visible, so query the raw source for the nulls before editing the model: if the source sends nulls, either filter or coalesce them in staging with a documented rule, or move the not_null test to the model where the field is truly required. Failing in staging usually means the test sits one layer too early.

how to fix dbt test not_null failed error on staging model

Use this when

• The check or run fails with this symptom: the not_null test fails on a staging model with null rows listed.
• You are working on a dbt staging model failing its not_null test and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A not_null failure in a mart model, where the field is contractually required and the data must be fixed instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Run the compiled not_null test SQL to get the exact rows and count failing in staging. Verify: the failing rows and their source keys are listed
2. Query the raw source table for the same keys and confirm whether the nulls arrive from the source or are created by staging logic such as a left join. Verify: the null origin, source or join, is established
3. If a join creates the nulls, check the join keys and the matched rate; an unmatched lookup is a data problem to surface, not to coalesce silently. Verify: the join match rate and unmatched keys are known
4. Apply the layer-appropriate fix: source filter or documented coalesce in staging, an inner join where matches are required, or moving the test downstream. Verify: staging output matches the documented rule
5. Rerun dbt build for the staging model and its tests, then the downstream models that consume the column. Verify: the not_null test passes at the layer where it now sits and downstream builds stay green

Variant phrasings

dbt not_null test failed staging model

The verbatim staging phrasing.

dbt test not_null source nulls staging

The source-nulls phrasing, focused on checking the raw table first.

move not_null test from staging to mart dbt

The layering phrasing for relocating the test.

Compatibility: dbt Core 1.5 and later and dbt Cloud, not_null generic tests on staging models

Why it happens

Staging models are meant to be a faithful, lightly cleaned copy of the source, so a strict not_null test there fails whenever the source itself allows nulls or a lookup join misses. The failure is a layering question first and a data question second.

Edge cases / pitfalls

• Coalescing nulls to a placeholder in staging destroys information marts may need. Prefer explicit handling downstream.
• A sudden new failure on an old model usually means the source changed. Diff the source schema and sample before editing tests.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstWf36U37DddWnVOwvCsJfQ

Resolved from the public thread: https://vectle.com/posts/pstWf36U37DddWnVOwvCsJfQ

Thread query: how to fix dbt test not_null failed error on staging model

TL;DR: Staging is where source nulls first become visible, so query the raw source for the nulls before editing the model: if the source sends nulls, either filter or coalesce them in staging with a documented rule, or move the not_null test to the model where the field is truly required. Failing in staging usually means the test sits one layer too early.