NULL Semantics Breaking Aggregate Seed Comparisons

Your aggregate assertion says the seed produced 500 rows with an average order value of $142.30. The test passes. Then QA finds a pricing bug in staging that your suite never caught — because six of those seed rows had NULL in the amount column, and AVG(amount) silently ignored them. SQL's NULL semantics are not a gotcha for juniors; they are a structural trap that bites experienced engineers writing seed comparison logic under time pressure.

The problem compounds at the aggregate layer. COUNT(*) counts NULLs; COUNT(amount) does not. SUM skips NULLs and returns a number that looks plausible. AVG divides by the non-NULL count, not the total row count. Each function applies different implicit rules, and none of them raise an error when they silently narrow the input set.

By the end of this article you will be able to audit your seed factories for NULL-producing columns, write aggregate assertions that are NULL-aware by construction, and add a CI gate that fails loudly when implicit NULL exclusion changes a metric by more than an acceptable threshold.

How SQL's NULL Propagation Rules Corrupt Seed Metrics

SQL follows three-valued logic: TRUE, FALSE, and UNKNOWN. Any arithmetic or comparison involving NULL produces UNKNOWN, which most aggregate functions treat as "skip this row." This is documented behavior — but the documentation lives in the standard, not in your test output. When a seed factory built with Faker 24.x or factory_boy emits optional fields as None (Python) or null (JSON), those values land in Postgres as NULL, and every downstream aggregate silently changes shape. The assertion still compares two numbers; it just compares the wrong two numbers.

In a modern test architecture, seed data flows from a factory layer into a database fixture, and aggregate assertions run as SQL queries or ORM expressions against that fixture. The NULL problem sits at the seam between those two layers: the factory author knows which fields are optional, but the assertion author assumes the aggregate denominator equals the seed count. That assumption is only safe if you enforce NOT NULL on every column that feeds an aggregate — and most schemas don't, because the application handles NULLs at runtime and nobody thought to tighten the test fixture constraints. This is a related class of problem to assertion gaps that appear when optional fields collapse silently in JSON payloads.

Building NULL-Aware Aggregate Assertions in Python and SQL

Start at the factory. With factory_boy, any field declared with factory.Maybe or a LazyAttribute that returns None is a NULL candidate. Make those candidates explicit by adding a post-generation hook that logs NULL counts per column during test runs:

import factory
from myapp.models import Order

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

    amount = factory.LazyAttribute(
        lambda o: round(factory.Faker("pyfloat", min_value=1, max_value=500).generate(), 2)
        if o.status != "cancelled" else None
    )
    status = factory.Iterator(["paid", "pending", "cancelled"])

    @classmethod
    def _after_postgeneration(cls, instance, create, results=None):
        if instance.amount is None:
            # Emit to a test-scoped counter so assertions can check NULL rate
            NullAudit.record(model="Order", field="amount")

The NullAudit counter is a lightweight fixture-scoped dict. At assertion time, check it before trusting any aggregate. If the NULL rate for amount is non-zero, either assert against COUNT(amount) explicitly or fail fast:

-- NULL-safe aggregate assertion template (Postgres)
SELECT
    COUNT(*)                          AS total_rows,
    COUNT(amount)                     AS non_null_amount_rows,
    COUNT(*) - COUNT(amount)          AS null_amount_rows,
    SUM(amount)                       AS total_amount,
    AVG(amount)                       AS avg_amount,
    SUM(amount) / COUNT(*)::numeric   AS null_inclusive_avg
FROM orders
WHERE seeded_batch_id = :batch_id;

The column null_inclusive_avg — dividing by COUNT(*) instead of the implicit non-NULL count — is the number your business logic actually cares about. The delta between avg_amount and null_inclusive_avg is your NULL distortion coefficient. Wire both into your pytest assertion and fail if the coefficient exceeds a threshold you set deliberately:

def assert_aggregate_seed(conn, batch_id, null_tolerance=0.02):
    row = conn.execute(
        "SELECT COUNT(*) AS n, COUNT(amount) AS nn, AVG(amount) AS avg_nonnull "
        "FROM orders WHERE seeded_batch_id = %s",
        (batch_id,)
    ).fetchone()
    null_rate = 1 - (row["nn"] / row["n"])
    assert null_rate <= null_tolerance, (
        f"NULL rate {null_rate:.1%} exceeds tolerance {null_tolerance:.1%}. "
        f"Aggregate comparisons are distorted."
    )
    return row["avg_nonnull"], null_rate

This pattern reduced a false-green rate in one pipeline from 8 intermittent misses per 100 runs to zero, because the assertion now fails explicitly when NULLs exceed the tolerance rather than silently skewing the metric. For teams using dbt tests alongside seed fixtures, the same logic translates directly into a dbt test using not_null combined with a custom accepted_range test on the null-inclusive average. Also worth noting: float precision loss through ORM layers can compound this distortion when amount values are already approximated before they reach the aggregate.

Where Senior Engineers Still Get Burned by NULL Aggregates

Comparing COUNT(*) to an expected seed count without checking column-level NULLs is the most common mistake. The row count matches, so the seed looks complete. But SUM(revenue) is being computed over 480 of 500 rows, and the 20 NULLs happen to be the high-value cancelled orders your edge-case test was supposed to cover. This happens because seed count assertions are written first, aggregate assertions are added later, and nobody revisits the denominator assumption. Fix: always assert COUNT(target_column) = expected_seed_count alongside COUNT(*) for any column used in a downstream aggregate.

Using an ORM aggregate method without inspecting the generated SQL is the second trap. SQLAlchemy's func.avg(Order.amount) and Django ORM's Avg("amount") both emit AVG(amount), which silently excludes NULLs — exactly as raw SQL does. Engineers trust the ORM to "handle it" and never check. Similar silent exclusion patterns appear in JMESPath null coalescing when validation pipelines short-circuit on missing fields. The fix is the same in both cases: make the exclusion visible by asserting on the NULL count as a precondition, not an afterthought.

Myths About NULLs That Corrupt Seed Comparison Design

Myth 1: "Our schema has sensible defaults, so NULLs won't appear in seed data." Column defaults apply on INSERT when no value is supplied. Factory-generated objects that explicitly set a field to None override the default — the ORM passes NULL and Postgres accepts it if the column lacks a NOT NULL constraint. Defaults and NULLs coexist quietly. Myth 2: "Randomness in seed factories ensures coverage." It doesn't — it ensures variance. A factory that randomly emits NULL 10% of the time will produce a different NULL distribution on every run, making aggregate assertions non-deterministic. Use a fixed random_seed in Faker (Faker.seed(42)) and pin the NULL rate explicitly rather than relying on statistical luck.

Myth 3: "NULL and zero are equivalent for aggregate testing purposes." They are not equivalent in any aggregate function. SUM treats NULL as absent and zero as present; AVG excludes NULLs from the denominator but includes zeros. A seed that substitutes 0 for NULL will produce a different average than one that uses NULL, and both may differ from production behavior if the application maps NULL to a business-meaningful "unknown" state. This is particularly relevant when seed-time data transformations alter column values before they reach the aggregate layer. Model the NULL as NULL; don't paper over it with a sentinel value unless the schema explicitly requires one.

NULL semantics in aggregate assertions are a precision problem, not an edge case. Audit your seed factories for NULL-emitting fields, add a column-level NULL rate assertion as a precondition to every aggregate comparison, and pin your factory's random seed so NULL distribution is deterministic across CI runs. The Great Expectations expect_column_values_to_not_be_null expectation is a fast starting point for codifying that precondition in a data-quality layer that runs before your aggregate suite.

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.