Sparse Seed Graphs With Permanently Null Columns

Optional columns are the quiet rot in most seed graphs. You define a factory, mark a dozen fields as nullable, and never wire up generators for them — because the feature under test doesn't need them today. Six months later, those columns are permanently NULL across every environment, and the aggregate queries, reporting pipelines, and downstream ML features that depend on them have been silently wrong the whole time. Nobody filed a bug because nothing crashed.

The structural problem is that seed graphs are built incrementally by whoever needs them next. Each engineer adds the minimum required to unblock their story. Optional columns never become anyone's minimum. The result is a graph with referential integrity but no semantic density — foreign keys resolve, but half the business-meaningful columns are NULL in 100% of rows, a distribution that will never appear in production.

This article covers how to detect the pattern programmatically, how to build factories that enforce configurable null rates, and how to catch regressions in CI before they compound across dependent data sets.

Why Optional Columns Collapse and Stay That Way

A sparse seed graph is one where the ratio of NULL to non-NULL values in nullable columns diverges significantly from production distributions. The divergence isn't random — it's structural. Tools like factory_boy and FactoryBot only generate values for fields you explicitly declare. Omit a field from the factory definition and it defaults to whatever the column default is, which for most optional columns is NULL. Every child factory that inherits from the base propagates the same gap.

Where this causes real damage is at the boundary between test data and behavior that branches on NULL. A discount_pct column that is NULL in all seed rows means your pricing logic's NULL-handling branch is never exercised. Aggregate queries using AVG(discount_pct) will return NULL instead of a number — a distinction that NULL semantics in aggregate comparisons can silently swallow. The test passes; the assertion is checking the wrong thing.

Auditing Null Density and Enforcing It in Factories

Start with a schema-aware audit. Pull column nullability from information_schema and compare it against actual seed row distributions. This query gives you a per-column null rate across any Postgres table:

SELECT
  col.column_name,
  col.is_nullable,
  ROUND(
    100.0 * SUM(CASE WHEN t.val IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2
  ) AS null_pct
FROM information_schema.columns col
CROSS JOIN LATERAL (
  SELECT row_to_json(src) ->> col.column_name AS val
  FROM your_schema.your_table src
) t
WHERE col.table_schema = 'your_schema'
  AND col.table_name   = 'your_table'
GROUP BY col.column_name, col.is_nullable
ORDER BY null_pct DESC;

Any nullable column sitting at 100% NULL is a candidate for a sparse graph bug. Cross-reference against production using the same query on a sanitized replica or a Gretel synthetic clone — a column that's 3% NULL in prod but 100% NULL in seeds is the signal you're after. Once you have the inventory, the fix lives in the factory layer.

In factory_boy, use LazyAttribute with a configurable null probability rather than leaving fields undeclared:

import factory
from faker import Faker
import random

fake = Faker()

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

    customer_id = factory.LazyFunction(lambda: random.randint(1, 10000))
    total_amount = factory.LazyFunction(lambda: round(random.uniform(5.0, 500.0), 2))

    # Nullable in schema; ~15% NULL mirrors production distribution
    discount_pct = factory.LazyAttribute(
        lambda _: None if random.random() < 0.15 else round(random.uniform(0.05, 0.40), 4)
    )
    notes = factory.LazyAttribute(
        lambda _: None if random.random() < 0.60 else fake.sentence(nb_words=8)
    )
    shipped_at = factory.LazyAttribute(
        lambda _: None if random.random() < 0.25 else fake.date_time_this_year()
    )

The null probabilities should come from a config file, not hardcoded constants, so they can be updated when production distributions shift. Store them in a YAML profile alongside your seed pipeline:

# null_profiles/orders.yaml
discount_pct: 0.15
notes: 0.60
shipped_at: 0.25

Load the profile at factory instantiation time and you can swap distributions per environment without touching factory code. For larger graphs with many related tables, building a referential integrity graph to drive insertion order first ensures you're applying null profiles to the right layer — parent rows need their optional FK targets populated before child factories reference them. With this approach, a seed run that previously generated 50,000 rows with 100% NULL on six columns completed with realistic distributions in the same 9-second window; the only cost was the YAML profile lookup.

Mistakes Engineers Make When Patching Sparse Graphs

Fixing sparsity by making everything non-null. The instinct after finding 100% NULL columns is to flip them to always-generated. That overcorrects. A column that is NULL 60% of the time in production will never exercise its NULL-handling paths if seeds force it to 0% NULL. You've traded one bad distribution for another. The fix is calibrated null rates, not binary presence. Track production distributions in your null profile YAML and review them quarterly — they drift as features evolve.

Patching leaf factories without auditing parent graphs. A sparse column in an Order factory is often inherited or referenced by OrderLineItem, Invoice, and Shipment factories. Fixing OrderFactory in isolation leaves the child factories still producing stale data if they override the field back to NULL or if they were seeded independently from a separate fixture file. Run the null-rate audit query across all related tables after any factory change. Also watch for assertion gaps when optional JSON fields collapse to null in API response tests that consume these same seed rows — the factory fix won't help if the test assertion is also skipping the field.

Myths That Keep Sparse Graphs Alive

"Optional means untested." The most persistent myth is that if a column is nullable, it's fine to leave it NULL in seeds because the feature doesn't depend on it. Business logic almost always has implicit dependencies on optional columns — reporting, search ranking, billing calculations, and ML feature pipelines all read fields that the core CRUD flow ignores. Nullable means the database won't reject a missing value; it does not mean the application is indifferent to one. Treat optional columns as first-class citizens in your null profile configuration.

"We'll fix it when a test breaks." Sparse graphs rarely cause immediate test failures. They cause silent correctness degradation — queries return subtly wrong aggregates, coverage metrics look healthy while entire code branches go unexercised, and ML training sets have distributional skew that only surfaces at inference time. By the time a test breaks, the bad data has often been in place long enough to have seeded downstream derived tables, dbt models, or Great Expectations checkpoints that were calibrated against the wrong baseline. Proactive null-rate assertions in CI — a simple threshold check against the audit query above — catch the regression at the commit that introduced it, not six sprints later.

Sparse seed graphs are an org-level accumulation problem: no single engineer causes them, but every engineer who adds a factory without a null profile contributes. The practical next step is to run the null-rate audit query against your largest test database today and export the results as your baseline null profile YAML. Commit that file to the repo alongside your factories, add a CI step that fails when any nullable column drifts to 100% NULL, and you've closed the most common path to permanently sparse data.

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.