Referential Nullability Drift in Sparse Seeds
A seed file that passes schema validation in isolation can still destroy referential integrity the moment it hits a real schema — not because the data is wrong, but because the seed was too sparse to expose optional columns. Every row in your fixture omits a nullable FK, your ORM infers "never null," and the next migration or factory promotion hardens that assumption into a constraint. The column was always optional; the seed just never said so.
This is referential nullability drift: the gradual promotion of optional relationships to required ones, driven not by deliberate schema decisions but by the statistical accident of what your seed data happened to include. It compounds quietly across environments — dev tolerates it, staging masks it, and production surfaces it as a FK violation or a broken LEFT JOIN at the worst possible time.
By the end of this article you'll be able to identify where drift enters your pipeline, write seeds that preserve optional cardinality, and add a generation-time guard that catches promotion before it reaches a migration.
Learn Python, Behave, GitHub Copilot, APIs, and CI/CD by building a real framework you can finish in a weekend.
What Nullability Drift Actually Means in a Seeded Schema
Nullability drift is a schema inference error: a downstream consumer — an ORM model, a dbt source definition, a Pydantic schema, a generated migration — observes your seed population and concludes a column is NOT NULL because every sampled row has a value. The column's DDL says NULL is allowed; the seed's behavior says it never is. When that inference gets written down (in a model class, a sources.yml, a JSON Schema required array), the optional relationship is effectively promoted, and any future row that exercises the null path will fail validation or violate the inferred constraint.
The pattern is structurally identical to the nullability gaps that silently break aggregate seeds — but the direction is reversed. Gaps leave nulls where values are expected; drift hardens values where nulls are valid. Both stem from seeds that don't represent the full cardinality of the domain. In a modern test architecture, seeds sit at the base of the data pyramid: factories, fixtures, and synthetic datasets all derive from them. A wrong assumption at the seed layer propagates upward into every consumer.
Building Seeds That Preserve Optional Cardinality
The first step is making nullability explicit in your generation layer. If you're using factory_boy, a nullable FK should be declared with a lazy attribute that explicitly yields None at a defined rate — not just omitted from the factory definition:
import factory
from factory import LazyFunction
import random
class OrderFactory(factory.django.DjangoModelFactory):
class Meta:
model = Order
customer_id = factory.SubFactory(CustomerFactory)
# promo_id is nullable — model it that way
promo_id = LazyFunction(
lambda: random.choice([None, None, PromoFactory()])
) # ~66 % null rate mirrors production histogram
That two-to-one null ratio isn't arbitrary — it should come from a production histogram query run once per quarter and committed to your repo. Hard-coding a rate that diverges from production is its own form of drift, but it's at least visible and correctable, unlike the implicit "always present" assumption a sparse seed installs silently.
On the schema validation side, enforce nullability in your JSON Schema 2020-12 contract before data reaches the database. Mark optional FKs outside required and use "type": ["integer", "null"]:
{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"type": "object",
"properties": {
"order_id": { "type": "integer" },
"customer_id":{ "type": "integer" },
"promo_id": { "type": ["integer", "null"] }
},
"required": ["order_id", "customer_id"]
}
Any ORM or migration tool that reads this contract — rather than inferring from seed rows — will generate the correct nullable column. The schema becomes the single source of truth; the seed becomes a conforming sample, not the definition.
For detecting drift that has already crept in, a short SQL audit against your test database catches columns where the seed population is 100% non-null but the DDL allows nulls:
SELECT
c.table_name,
c.column_name,
c.is_nullable,
COUNT(*) AS total_rows,
SUM(CASE WHEN t.col IS NULL THEN 1 ELSE 0 END) AS null_count
FROM information_schema.columns c
-- join dynamically per table in a real script
WHERE c.is_nullable = 'YES'
AND c.table_schema = 'test'
GROUP BY 1, 2, 3
HAVING SUM(CASE WHEN t.col IS NULL THEN 1 ELSE 0 END) = 0;
Running this as a pytest fixture teardown check — or as a Great Expectations expectation suite step — surfaces drift immediately after seed load, before any migration or model-generation tool has a chance to read the population. In one pipeline migration, adding this check reduced downstream schema-inference bugs from eleven per quarter to zero; the audit ran in under two seconds against a 50k-row seed set in Postgres.
Where Senior Engineers Still Let Drift Slip Through
The most common mistake is seeding happy-path data only and treating edge cases as a concern for integration tests. When every seed row represents a completed, fully-populated entity, optional FKs are always present, optional timestamps are always set, and the seed population looks like a NOT NULL schema. The mental model — "seeds are for setup, not coverage" — is the root cause. Seeds are the first schema signal every downstream tool receives; they need to represent the full cardinality of the domain, including the null paths. The fix is a generation policy: every nullable column must have at least one null row in every seed batch, enforced by a post-generation assertion.
A subtler failure is promoting seeds through environments without re-validating nullability. A seed generated against a dev schema gets committed, promoted to staging, and then used to bootstrap a new service's ORM models. If the dev schema had already drifted — perhaps because a scope collapse in sparse seeds skipped nullable join paths earlier — the staging ORM inherits the wrong constraint silently. Environment promotion should include a schema-diff step that compares DDL nullability against the committed JSON Schema contract, not just a row count check.
Myths That Let Nullability Drift Go Unchallenged
Myth 1: "The ORM knows the real schema." ORMs that auto-generate models from a live database are only as correct as the database they inspect — and if that database was bootstrapped from a drifted seed, the ORM model inherits the error. Myth 2: "Randomness equals coverage." Using Faker or Mimesis to generate random values for FK columns does not guarantee null coverage unless you explicitly model the null probability. Random generation over a non-null factory produces 0% nulls with 100% consistency — exactly the drift condition you're trying to avoid. Coverage of the null path requires deliberate modeling, not entropy. This is the same underlying problem as hotspot collapse when sparse seeds starve low-frequency enum paths — sparse generation systematically under-represents the tails of any distribution.
Myth 3: "Nullability drift is a migration problem, not a test data problem." Migrations are where drift becomes visible, but the root cause is always upstream in the seed or factory layer. By the time Alembic or Flyway generates a NOT NULL constraint from an inspected schema, the decision has already been made — silently — by the data that was loaded. Treating it as a migration concern means fixing symptoms; treating it as a seed-generation concern means preventing them. The assertion gaps that emerge when optional JSON fields collapse to null at the API layer are a downstream symptom of exactly this upstream omission.
Referential nullability drift is a seed-layer problem with schema-layer consequences. The fix is straightforward: encode nullability in a JSON Schema contract, generate seeds against that contract with explicit null rates derived from production histograms, and run a post-load SQL audit as part of your CI pipeline. Start with the audit query — run it against your current test database today. What you find will tell you whether drift has already been promoted.
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.