nulls appeared in a non-null column root cause

Not specified by the original author.

TL;DR

Check the real DDL first, because the constraint often was never there or was added without validating old rows. If the constraint exists, the load path bypassed it: LEFT JOINs manufacturing nulls on non-matches, UNION branches with misaligned columns, or a bulk load that skipped validation. It happens because constraints only guard the paths that check them, and several common write paths do not.

nulls appeared in a non-null column

Use this when

• A column your team treats as non-null suddenly has nulls
• An insert succeeded that should have violated a not-null constraint
• Nulls appeared right after a pipeline or migration change
• A dbt test on not_null started failing on a previously clean model

Not for

• Columns that are nullable by design
• Nulls you expect from outer joins in analytics queries
• Null handling bugs in application code rather than the data layer

Steps

1. Look at the actual DDL, not what you remember it being:

SELECT column_name, is_nullable, data_type
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'customer_id';

Expected output: the true nullability flag. If it says YES, there was never a constraint and the mystery is solved in one query.

2. Check whether the constraint exists but was never validated:

-- Postgres: is the constraint valid?
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'n';

Expected output: a row with convalidated = false means the constraint was added with NOT VALID, so pre-existing nulls were grandfathered in and only new writes are checked.

3. Find when the nulls arrived and how many there are:

SELECT DATE(created_at) AS d, COUNT(*) AS null_rows
FROM orders WHERE customer_id IS NULL
GROUP BY d ORDER BY d;

Expected output: nulls clustered on specific dates point at a deploy or a pipeline change on that day; spread-out nulls point at a chronic load-path issue.

4. Inspect the load query for LEFT JOINs that manufacture nulls:

-- the classic factory: unmatched left join rows produce null keys
SELECT COUNT(*) FROM staging_orders s
LEFT JOIN customers c ON s.customer_id = c.id
WHERE c.id IS NULL;

Expected output: a nonzero count confirms the join is the source. The fix is an inner join, a pre-filter, or a rejected-rows quarantine, not a prayer.

5. Check UNION branches for column misalignment:

-- compare column order and count across branches
SELECT 'branch_a' AS src, COUNT(*) FROM staging_a
UNION ALL
SELECT 'branch_b', COUNT(*) FROM staging_b;

Expected output: if one branch selects columns in a different order, values land in the wrong columns and nulls appear where data should be. Align the column lists explicitly instead of using SELECT *.

6. Quarantine and backfill once the source is fixed:

UPDATE orders SET customer_id = s.customer_id
FROM staging_orders s
WHERE orders.id = s.id AND orders.customer_id IS NULL;

Expected output: the null rows get their real values back from the source, and the fixed load path keeps new nulls out going forward.

Variant phrasings

column has nulls but defined not null

Start at step 1 every time. Human memory of DDL is unreliable; the catalog is not.

not null constraint violated but insert succeeded

Either the constraint is NOT VALID, it was dropped and re-added, or the write went through a path like a direct bulk load with validation skipped. Step 2 distinguishes these.

nulls after left join

The most common factory. A left join preserves all left rows by design, and every non-matching row gets nulls in the right-side columns. If the downstream column must be non-null, the join was the wrong join.

Why it happens

A not-null constraint is a gate, and gates only work on the paths that pass through them. Bulk loads, constraint changes with NOT VALID, and ETL tools that disable constraints during migration all walk around the gate. Meanwhile LEFT JOINs and misaligned UNIONs do not violate anything: they produce nulls legitimately, and the nulls land in a column everyone assumed was protected. The root cause is almost always either a missing gate or a legitimate null factory upstream, never database misbehavior.

Edge cases

• Views: the nulls may live in the base table while the view just surfaces them; check the base table first.
• Spark and dataframe writes: writers that use append mode against a table with constraints can behave differently per connector; verify at the warehouse, not in the job log.
• Soft deletes: a null customer_id might be a deleted customer reference rather than a load bug; check whether the nulls correlate with deleted dimension rows.
• The constraint might be on a different column than the one you are looking at after a rename; match by position in the DDL, not by memory.

Provenance

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

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

Thread query: "nulls appeared in a non-null column" root cause

TL;DR: Check the real DDL first, because the constraint often was never there or was added without validating old rows. If the constraint exists, the load path bypassed it: LEFT JOINs manufacturing nulls on non-matches, UNION branches with misaligned columns, or a bulk load that skipped validation. It happens because constraints only guard the paths that check them, and several common write paths do not.

Not specified by the original author.

TL;DR

Do not just rerun the suite: query the table for the null rows the expectation flagged, decide whether the nulls are a source bug or a rule that is too strict for a legitimately optional column, fix at that layer, and rerun only the failed expectation. Most expectcolumnvaluestonotbenull failures are real nulls arriving from an upstream change, not a Great Expectations bug.

great expectations error: expect_column_values_to_not_be_null validation failed

Use this when

• The check or run fails with this symptom: expectcolumnvaluestonotbenull returns success false with unexpected null rows.
• You are working on a Great Expectations suite guarding a production table 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 column that is documented as nullable by design, where the rule itself should be removed rather than the data fixed.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Run the single expectation against the latest batch and open the result detail to get the unexpected count and a sample of row keys, instead of reading only the suite summary. Verify: the output names the column, the null count, and sample keys
2. Query the source table directly for nulls in that column, grouped by load date or source file, to find when the nulls started. Verify: the nulls cluster on a date, file, or source system rather than spreading evenly
3. Trace one null row end to end through the upstream job and check for a renamed field, a failed join, or a new record type that never populates the column. Verify: one concrete upstream cause is identified for the sample row
4. Fix at the correct layer: repair the upstream mapping or join, backfill the affected rows, or, only if the column is genuinely optional, change the expectation to mostly with a documented threshold. Verify: the chosen fix is applied and the reasoning is recorded on the suite
5. Rerun the checkpoint on the repaired batch, then on the next scheduled batch. Verify: both runs return success true for this expectation with zero unexpected rows

Variant phrasings

great expectations expectcolumnvaluestonotbenull unexpected_count

The result-detail phrasing, focused on reading unexpected_count and the partial unexpected list.

expectcolumnvaluestonotbenull success false

The validation-result phrasing users paste from the JSON result.

great expectations not null expectation failing on production data

The production-data phrasing, where the fix is upstream rather than in the suite.

Compatibility: Great Expectations 0.18 and 1.x with Pandas, Spark, or SQL datasources

Why it happens

The expectation counts any null in the column as a failure, including nulls produced by outer joins, schema changes that add records without the field, and source systems that start sending empty values. Because the check runs at the end of the pipeline, the error surfaces in Great Expectations even though the defect was introduced upstream.

Edge cases / pitfalls

• A mostly threshold hides a growing null rate. Pair it with an alert on the null percentage itself.
• Sampling can miss nulls concentrated in one partition. Validate the failing partition in full before closing.

Provenance

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

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

Thread query: great expectations error: expectcolumnvaluestonotbenull validation failed

TL;DR: Do not just rerun the suite: query the table for the null rows the expectation flagged, decide whether the nulls are a source bug or a rule that is too strict for a legitimately optional column, fix at that layer, and rerun only the failed expectation. Most expectcolumnvaluestonotbenull failures are real nulls arriving from an upstream change, not a Great Expectations bug.

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.