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.

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.