dbt test not_null failing on empty string values

Not specified by the original author.

TL;DR

The not_null test found null values in orders.customer_id. Re-run the test with --store-failures so dbt saves the offending rows to a table, query that table to see which rows are null, then fix the model logic or the upstream data that produced them.

Error

Database Error in test not_null_orders_customer_id

Steps

1. Re-run just this test and store the failures: dbt test --select not_null_orders_customer_id --store-failures. Expected: the run completes and reports a failures table location.
2. Query the failures table in your warehouse (it lands in the test schema, named after the test). Expected: you see the actual rows where customer_id is null.
3. Check whether the nulls come from the source or from model logic: compare against the raw table and look for joins that drop the key. Expected: you identify the step that introduces the nulls.
4. Fix the cause: add a coalesce, filter bad rows, or repair the upstream source. Expected: the model logic no longer produces null keys.
5. Re-run dbt test --select not_null_orders_customer_id. Expected: the test passes with zero failing rows.

When to use

• dbt test fails on a not_null generic test with a Database Error.
• You need to see the failing rows, not just the failure count.

When not to use

• The test fails at parse time with an "Invalid test config" error (a YAML problem, not a data problem).
• The test name is misspelled in your --select (dbt reports no matching tests).

Tool compatibility

• dbt Core 1.0 and later, all adapters. --store-failures needs a test schema your role can write to.

Variant phrasings

Database Error in test notnull[model]_[column]

Same pattern for any column: the test found nulls, and the fix is in the data or the model.

Failure in test not_null ... got 5 results, configured to fail if > 0

The long form of the same failure, with the failing row count included.

Why it happens

not_null compiles to a query that selects rows where the column is null. Any nulls in the built model, whether from the source or introduced by a join or a case expression, fail the test.

Edge cases

• Empty strings are not null; if your nulls are actually '', the not_null test passes and you need a separate check.
• Tests run against the model's target schema; make sure you rebuilt the model before re-testing.
• On BigQuery, the failures table lands in the test dataset configured in your profile.

Provenance

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

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

Thread query: "Database Error in test notnullorderscustomerid"

TL;DR: The not_null test found null values in orders.customer_id. Re-run the test with --store-failures so dbt saves the offending rows to a table, query that table to see which rows are null, then fix the model logic or the upstream data that produced them.

Not specified by the original author.

TL;DR

Empty strings are not nulls, so not_null passes them while your data contract considers them missing (or vice versa: the test you want is stricter). If empty means missing, convert with nullif(column, '') in the model so not_null catches them; if empty is valid, leave not_null and add a separate check for the values you actually forbid.

Error

dbt test not_null failed on empty string values

Steps

1. Store the failures and inspect them: dbt test --select [TEST NAME] --store-failures. Expected: the failing values are '', not nulls.
2. Decide the business rule: does an empty string mean "missing" for this column? Expected: a clear yes or no.
3. If empty means missing, convert in the model: nullif(email, '') as email. Expected: empties become nulls and not_null enforces the contract.
4. If empty is legitimate, keep not_null as is and add a custom test for the real rule (for example, no whitespace-only values). Expected: each test checks exactly one thing.
5. Rebuild the model and rerun the test. Expected: the test passes under the clarified rule.

When to use

• A not_null test fails and the offending values are empty strings.
• Source systems write '' where you expect nulls.

When not to use

• The failures are real nulls (fix the data or the model logic).
• Empty strings are valid data (the test is wrong, not the data).

Tool compatibility

• dbt Core 1.0 and later, all adapters. nullif is standard SQL.

Variant phrasings

not_null passes but downstream treats '' as missing

The reverse confusion: add the nullif conversion so the warehouse and the contract agree.

Whitespace-only strings failing a custom check

Trim before comparing; ' ' is neither null nor empty.

Why it happens

SQL distinguishes '' from null, but business logic often does not. The test checks the SQL definition while humans apply the business definition, and the two disagree on empty strings.

Edge cases

• Some warehouses (Oracle-style) treat empty strings as null; know your warehouse's semantics before converting.
• Converting in the model changes downstream behavior; coordinate with consumers of the column.
• Source freshness will not catch this; it is a content problem, not a timing problem.

Provenance

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

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

Thread query: dbt test not_null failed on empty string values

TL;DR: Empty strings are not nulls, so not_null passes them while your data contract considers them missing (or vice versa: the test you want is stricter). If empty means missing, convert with nullif(column, '') in the model so not_null catches them; if empty is valid, leave not_null and add a separate check for the values you actually forbid.

Not specified by the original author.

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.