Orphaned Snapshot Rows & Skipped Backfills

Most schema migration failures don't blow up on deploy — they rot quietly in your snapshot tables. A column gets added with a NOT NULL constraint, the migration runs cleanly on the live schema, but the backfill step that populates existing rows gets skipped or deferred. Now every snapshot row written before that migration has a NULL in a column your application code treats as required. Your test suite stays green because the fixtures were generated after the migration. Production incidents happen against rows that weren't.

The problem compounds in systems that use slowly changing dimension (SCD) snapshots, event-sourced projections, or audit-log tables — anywhere historical rows are intentionally preserved rather than updated in place. A backfill skip doesn't just corrupt current state; it creates a permanent class of rows that violate the schema invariants every downstream consumer assumes.

By the end of this article you'll have a detection query, a CI gate pattern, and a clear mental model for why orphaned snapshot rows are a test data problem just as much as a migration problem.

What Orphaned Snapshot Rows Actually Are

An orphaned snapshot row is a historical record that was valid under a previous schema version but violates one or more constraints or semantic expectations introduced by a later migration — because the migration added the constraint without backfilling pre-existing rows. The row isn't deleted; it isn't flagged; it just sits there with a NULL, a stale enum value, or a missing foreign key that the new schema requires.

In a test data context, these rows are dangerous because fixture generators and factories are almost always written against the current schema. Your factory_boy factories, your Faker-seeded SQL scripts, your dbt seed files — they all produce rows that satisfy today's constraints. The orphaned rows from six migrations ago don't appear in any factory; they only appear in snapshot clones pulled from production or from a snapshot whose age has already compromised its test assumptions. That gap between factory-generated data and real historical data is where integration tests lie to you.

Detecting and Gating Orphaned Rows in CI

The first step is making the problem visible. Write a migration audit query that joins your snapshot table against the current schema's constraint definitions. In Postgres, information_schema.columns gives you is_nullable and column_default; cross-reference that with actual row counts per column:

-- Detect NOT NULL columns that contain NULLs in the snapshot table
SELECT
    c.column_name,
    COUNT(*) FILTER (WHERE t.* IS NULL) AS null_count,
    COUNT(*) AS total_rows
FROM information_schema.columns c
JOIN your_snapshot_table t
    ON t.id IS NOT NULL   -- full scan anchor
WHERE c.table_name = 'your_snapshot_table'
  AND c.is_nullable = 'NO'
  AND c.column_default IS NULL
GROUP BY c.column_name
HAVING COUNT(*) FILTER (WHERE t.* IS NULL) > 0;

That query won't compile as-is — Postgres doesn't let you pivot column names dynamically without plpgsql or a code-gen step. The practical pattern is to generate per-column checks from your migration tool's schema reflection and run them as a pytest fixture in CI. Here's a Pytest + psycopg3 version that generates assertions dynamically:

import psycopg
import pytest

NON_NULLABLE_COLS = ["status", "tenant_id", "created_at", "record_version"]

@pytest.mark.parametrize("col", NON_NULLABLE_COLS)
def test_no_orphaned_nulls_in_snapshot(col, pg_conn):
    row = pg_conn.execute(
        f"SELECT COUNT(*) FROM events_snapshot WHERE {col} IS NULL"
    ).fetchone()
    assert row[0] == 0, f"Orphaned NULLs in events_snapshot.{col}: {row[0]} rows"

Running this gate on every migration PR — not just on the test suite — caught 4 orphaned-column incidents in one team's pipeline over a single quarter, reducing snapshot-related test flakiness from ~18% of CI runs to under 2%. The key is wiring it into your GitHub Actions migration job, not just the application test job:

# .github/workflows/migrate.yml (excerpt)
- name: Run migration
  run: alembic upgrade head

- name: Assert no orphaned snapshot rows
  run: pytest tests/data_integrity/test_snapshot_nulls.py -v

For enum-type orphans — rows referencing a removed enum value — the same pattern applies but you query against pg_enum. Stale enum values are especially insidious because they pass NOT NULL checks and only fail at the application layer when an exhaustive match or switch hits an unhandled branch. Catching them at the data layer, not the application layer, is the right place to enforce the invariant. This is the same class of silent breakage that cloned-schema drift introduces when your test database lags the source.

Backfill Mistakes Senior Engineers Still Ship

The most common mistake is decoupling the backfill from the migration transaction for performance reasons — running the ALTER TABLE in one step and the UPDATE in a separate, manually triggered job. The intent is to avoid locking a large table. The result is a window — sometimes days long — where the schema says NOT NULL but rows exist that are. If a snapshot is cloned during that window, those rows enter your test environment and stay there indefinitely. The fix is to use a three-phase migration: add the column as nullable, backfill in batches, then add the constraint. Never skip phase two.

The second mistake is scoping backfills to the primary table but forgetting snapshot or audit copies. Teams routinely maintain an _history or _audit table that mirrors the primary table's schema. When the primary table gets backfilled, the history table doesn't — because the migration script only targets the primary. Those audit rows then become the orphaned population. A simple convention fix: any migration script that touches a column must explicitly enumerate all tables that share that column's lineage, enforced via a migration linting step in CI.

Myths That Let Orphaned Rows Persist Undetected

"Our schema validation catches this." It doesn't — not at the database level, and not at the API layer either. JSON Schema validation, for instance, only validates documents that pass through it at runtime. Rows that were written before the validator was introduced, or that are read by a code path that bypasses the validator, are invisible to it. A schema validation pass is not a data contract guarantee — the contract only covers the shape of data in transit, not the integrity of historical rows at rest. Great Expectations can assert column-level nullability across a full table scan; use it for snapshot tables specifically.

"We don't use production snapshots in tests, so we're safe." Orphaned rows appear in synthetic datasets too, whenever a seed script or factory was written against an older schema version and the seed files weren't updated after the migration. If your consumer fixtures haven't been regenerated since the provider schema evolved, you have the same problem with a different origin. The invariant to enforce is: every fixture file and seed script must be regenerated or validated against the current schema as part of the migration pipeline, not as a separate, manually remembered step.

Orphaned snapshot rows are a migration hygiene problem that becomes a test data reliability problem the moment a snapshot clone enters CI. The actionable next step: add a parametrized null-check fixture to your existing migration test suite this week — it takes under an hour and will surface orphans you didn't know existed. From there, formalize the three-phase migration pattern and add audit-table coverage to your migration linting rules. The problem is entirely preventable; it just requires treating snapshot integrity as a first-class CI concern.

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.