Referential Nullability Gaps in Aggregate Seeds

Aggregate queries lie when the seed data has the wrong nullability distribution. A SUM(amount) grouped by region_id returns plausible numbers in CI even though 30% of rows have a NULL region_id — because the seed factory never produced a NULL there, so the implicit filter has always been hiding a whole slice of the domain. The test passes. The production report is wrong. Nobody connects the two.

The gap isn't in the query logic or the schema definition. It's in the test data's referential nullability profile: the ratio of NULL to non-NULL on foreign key and dimension columns doesn't match production, so any aggregate that depends on those columns exercises only a subset of the real code paths. COUNT(*) vs COUNT(col), LEFT JOIN vs INNER JOIN, COALESCE fallback branches — all of these behave differently when NULLs are present.

By the end of this article you'll know how to audit your seed factories for nullability gaps, instrument aggregate assertions to catch them, and generate seeds whose NULL distribution is intentional rather than accidental.

Build an API Automation Framework With Node.js

Learn Node.js, Cucumber, GitHub Copilot, APIs, CI/CD, and modern automation by building a complete framework.

Learn more

Why Nullable FKs Corrupt Aggregate Seeds at the Root

A referential nullability gap exists when a column that is nullable in production is always populated in test seeds — or vice versa. For aggregate seeds specifically, the damage is asymmetric: a single nullable dimension column (sales_rep_id, cost_center_id, channel_id) can cause a GROUP BY to silently drop rows, a FILTER clause to short-circuit, or a window function's partition to collapse to a single bucket. The schema allows NULLs; the seed factory just never emits them, so the test never exercises that branch.

This sits at the intersection of two test data concerns: referential scope collapse (underpopulated join paths) and nullability distribution. In a modern test architecture, seed generation is a first-class concern — not an afterthought bolted onto migration fixtures. Aggregate seeds in particular need a declared nullability contract, not just a valid-row contract, because SQL aggregation semantics treat NULL as a separate partition that most test assertions never inspect.

Auditing and Fixing Nullability Profiles in Seed Factories

Start by profiling the nullability ratio on every FK and dimension column in production (or a sanitized clone). A quick Postgres query gives you the ground truth:

SELECT
    column_name,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE column_value IS NULL) AS nulls,
    ROUND(
        COUNT(*) FILTER (WHERE column_value IS NULL)::numeric / COUNT(*), 4
    ) AS null_ratio
FROM (
    SELECT 'sales_rep_id' AS column_name, sales_rep_id::text AS column_value FROM orders
    UNION ALL
    SELECT 'channel_id', channel_id::text FROM orders
) profiled
GROUP BY column_name;

That ratio becomes a target. If sales_rep_id is NULL 18% of the time in production, your aggregate seed factory should emit approximately that distribution — not 0%. Encode it explicitly in a Pydantic model or a factory_boy declaration:

import factory
from factory import fuzzy
import random

class OrderFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = Order

    amount = fuzzy.FuzzyDecimal(10.0, 5000.0)
    region_id = factory.LazyFunction(
        lambda: random.choice([1, 2, 3, None, None])  # ~40% NULL
    )
    sales_rep_id = factory.LazyFunction(
        lambda: None if random.random() < 0.18 else random.randint(1, 50)
    )

The random.random() < 0.18 guard is deliberate and documented — it's not sloppiness, it's a specification. Commit the ratio alongside the factory so the next engineer knows why it's there. For dbt seed files, encode nullability in a YAML schema test so the seed itself is validated on load:

# models/seeds/schema.yml
seeds:
  - name: orders_seed
    columns:
      - name: sales_rep_id
        tests:
          - not_null:
              config:
                severity: warn   # warn, not error — NULLs are valid here
          - accepted_values:
              values: [null]     # documents intent explicitly

Now instrument your aggregate assertions to verify both the NULL and non-NULL partitions. A pytest fixture that checks both paths closes the gap in under 9 seconds on a 50k-row seed (vs. the 12-minute full-suite run before the seed was scoped):

def test_revenue_by_region_excludes_null_partition(db_session):
    rows = db_session.execute(
        "SELECT region_id, SUM(amount) FROM orders GROUP BY region_id"
    ).fetchall()

    null_row = next((r for r in rows if r.region_id is None), None)
    assert null_row is not None, "NULL partition missing — seed nullability gap"
    assert null_row.sum > 0, "NULL partition has no revenue — check seed factory"

The assertion on the NULL partition's existence is the key addition most teams skip. Without it, a seed that accidentally emits zero NULLs will pass every aggregate test silently.

Pitfalls Senior Engineers Hit When Seeding Nullable Dimensions

The first mistake is treating nullable FK columns as optional at the schema level but mandatory at the factory level. This happens because factory authors think in terms of "valid rows," and a valid row often means a populated row. The mental model is wrong for test data: a row with a NULL cost_center_id is just as valid as one with a value — and it exercises a completely different code path in any aggregate that groups or filters on that column. Fix it by adding a nullability checklist to your factory review process, the same way you'd review index coverage.

The second mistake is relying on database-level NOT NULL constraints to catch gaps. If the column is nullable, the constraint won't fire, and no CI step will warn you. This is compounded when seeds are generated from JSON Schema definitions that mark nullable columns as "type": ["integer", "null"] but the generator (Faker, Mimesis, a custom script) never emits the null branch because the code path was never wired. Similar silent failures occur with orphaned seeds from partial rollbacks — the data looks structurally intact but is semantically broken in ways no constraint catches.

Myths About NULL Coverage That Persist in Mature Codebases

Myth 1: "We test NULLs — we have a negative test that inserts a NULL and checks the error." That tests constraint enforcement, not aggregate behavior. A test that inserts one NULL row and checks a 400 response tells you nothing about how SUM(amount) FILTER (WHERE region_id IS NOT NULL) behaves when 18% of your production rows are excluded. Aggregate NULL coverage requires seeding at distribution scale, not at single-row scale. Myth 2: "Our seed data comes from a prod snapshot, so the nullability profile is correct." Prod snapshots are sanitized, subset, and often backfilled before export — the NULL ratio in a 10k-row snapshot is rarely representative of the full table. Snapshot-as-seed is a known fragility for FK integrity and it's equally fragile for nullability profiles.

Myth 3: "Randomness in factories gives us NULL coverage eventually." Not without an explicit NULL probability. Faker and Mimesis don't emit NULLs for FK columns by default. factory_boy's SubFactory always creates or resolves a related object — it never returns None unless you override it. Hypothesis can generate NULLs via st.none() | st.integers(), but only if you wire it that way. Randomness without a declared distribution is not coverage; it's noise with a passing grade.

Referential nullability gaps are a seed design bug, not a schema bug — and they're fixable with one afternoon of profiling and factory hardening. Profile your nullable FK columns against production ratios, encode those ratios explicitly in your factories, and add partition-existence assertions to your aggregate tests. For deeper reading, the referential scope collapse pattern covers the broader class of join-path gaps that this problem belongs to.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.

Understanding how systems actually work is the first step toward navigating them effectively.

Browse all articles