Test Database Backfill Scripts Safely (2026)

TL;DR

To test database backfill scripts safely, isolate a production-shaped dataset, establish a baseline, execute in bounded transactions, and assert row-level transformations plus database-wide invariants. Then rerun, interrupt, resume, and test lock contention before rehearsing the exact production commands.

Key Takeaways

• Test a backfill against a disposable database built from production-shaped, masked fixtures.
• Turn the migration requirement into explicit eligibility, transformation, and invariant queries.
• Use small committed batches so progress survives interruption and rollback scope stays bounded.
• Prove idempotency by running the completed backfill again and asserting zero writes.
• Inject a failure after a committed batch, restart the script, and compare the final state with a clean run.
• Set lock and statement timeouts, inspect the query plan, and rehearse the operational runbook before production.

To test database backfill scripts safely, prove more than a successful exit code. You must show that the script selects exactly the intended rows, writes the correct values, preserves unrelated data, tolerates interruption, and causes acceptable load while normal traffic continues.

This tutorial builds a disposable PostgreSQL lab around a realistic customer-name backfill. You will create adversarial fixtures, run a resumable Python worker, verify business invariants with SQL, automate restart and idempotency cases, and finish with a production rehearsal checklist. The same method applies to one-off repair jobs, schema migrations, denormalization tasks, and historical event reprocessing.

A backfill is application code with unusually broad write access. Treat its selection predicate and transformation rule as a contract, not as an informal query someone reviewed once. If SQL validation is new to you, keep the SQL for QA tutorial nearby while you work through the assertions.

What You Will Build

You will build a small repository named backfill-lab containing:

• PostgreSQL 18.4 in a disposable Docker container.
• A customer_profiles table with eligible, ineligible, already-migrated, whitespace-heavy, and empty-name records.
• A Python backfill that processes three rows per transaction, uses lock timeouts, and can resume after failure.
• SQL oracles for target count, transformation correctness, scope, and source-column preservation.
• Pytest checks for the clean run, second run, and failure-then-resume path.
• A release rehearsal that records counts, duration, query plan, and rollback boundaries.

The example deliberately updates only active customers whose backfill_version is not 1 or whose normalized name is missing. That narrow rule gives you something precise to challenge.

Prerequisites

Use Docker Engine 28.5.2, Docker Compose 2.39.1, PostgreSQL 18.4, Python 3.13.7, psycopg 3.2.10, and pytest 8.4.2. Use those versions to reproduce the output. PostgreSQL 18.4 and 17.10 are supported 2026 releases, but this guide pins 18.4 so planner and collation behavior do not drift between machines.

Create an empty working directory and a virtual environment:

mkdir backfill-lab
cd backfill-lab
python3.13 -m venv .venv
.venv/bin/pip install 'psycopg[binary]==3.2.10' 'pytest==8.4.2'
mkdir -p sql tests

Verify every dependency before creating data:

docker --version
docker compose version
.venv/bin/python --version
.venv/bin/python -c 'import psycopg, pytest; print(psycopg.__version__, pytest.__version__)'

The final command should print 3.2.10 8.4.2. Do not point DATABASE_URL at a shared development, staging, or production database during this tutorial.

Step 1: Create an Isolated Lab to Test Database Backfill Scripts Safely

Create compose.yaml. The named container and dedicated port make the target obvious in process lists and connection strings. The health check prevents a false failure while PostgreSQL is still starting.

services:
  backfill-db:
    image: postgres:18.4
    container_name: backfill-lab-db
    environment:
      POSTGRES_DB: backfill_test
      POSTGRES_USER: backfill_user
      POSTGRES_PASSWORD: backfill_pass
    ports:
      - '55432:5432'
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U backfill_user -d backfill_test']
      interval: 2s
      timeout: 3s
      retries: 15

Start the service and export one explicit connection URL:

docker compose up -d --wait
export DATABASE_URL='postgresql://backfill_user:backfill_pass@localhost:55432/backfill_test'

Never reuse production credentials for a backfill test. Start from a schema-only dump plus masked, production-shaped rows when data distribution matters. Preserve null rates, string lengths, status ratios, and key ranges, but replace names, emails, tokens, and customer content. The API test data management guide explains how to keep fixtures representative without copying sensitive values.

Verify: run docker compose ps. The backfill-lab-db row must show healthy. Then run docker exec backfill-lab-db psql -U backfill_user -d backfill_test -Atc 'select current_database()' and expect backfill_test.

Step 2: Build Production-Shaped Boundary Fixtures

Create sql/001_fixture.sql. The fixed timestamps make before-and-after comparison deterministic. Record 7 is inactive and must never change. Record 8 represents a previous successful run. Record 9 is an active empty string, which the contract intentionally normalizes to an empty string rather than NULL.

DROP TABLE IF EXISTS customer_profiles;

CREATE TABLE customer_profiles (
  id bigint PRIMARY KEY,
  full_name text NOT NULL,
  country_code char(2) NOT NULL,
  status text NOT NULL CHECK (status IN ('active', 'inactive')),
  normalized_name text,
  backfill_version integer,
  updated_at timestamptz NOT NULL
);

INSERT INTO customer_profiles
  (id, full_name, country_code, status, normalized_name, backfill_version, updated_at)
VALUES
  (1, '  Ada   Lovelace  ', 'GB', 'active',   NULL, NULL, '2026-07-01T00:00:00Z'),
  (2, 'GRACE HOPPER',      'US', 'active',   NULL, NULL, '2026-07-01T00:00:00Z'),
  (3, 'Linus Torvalds',    'FI', 'active',   NULL, 0,    '2026-07-01T00:00:00Z'),
  (4, 'Margaret Hamilton', 'US', 'inactive', NULL, NULL, '2026-07-01T00:00:00Z'),
  (5, '  James Gosling',   'CA', 'active',   NULL, NULL, '2026-07-01T00:00:00Z'),
  (6, 'Guido  van Rossum', 'NL', 'active',   NULL, NULL, '2026-07-01T00:00:00Z'),
  (7, 'Inactive Person',   'IN', 'inactive', NULL, 0,    '2026-07-01T00:00:00Z'),
  (8, 'Barbara Liskov',    'US', 'active',   'barbara liskov', 1, '2026-07-02T00:00:00Z'),
  (9, '',                  'IN', 'active',   NULL, NULL, '2026-07-01T00:00:00Z');

Load it with ON_ERROR_STOP so psql returns nonzero on the first SQL error:

docker exec -i backfill-lab-db psql -v ON_ERROR_STOP=1 -U backfill_user -d backfill_test < sql/001_fixture.sql

A tiny happy-path fixture hides risk. Add rows at predicate boundaries, prior-version states, maximum supported lengths, repeated whitespace, empty strings, locale-sensitive text if the requirement covers it, and records another process may modify. Use the SQL test data setup and teardown patterns when the fixture grows beyond one table.

Verify: run docker exec backfill-lab-db psql -U backfill_user -d backfill_test -c 'TABLE customer_profiles'. Expect nine rows, two inactive rows, and only ID 8 at version 1.

Step 3: Translate the Requirement Into Testable Oracles

Write the contract before the worker. An eligible row is active and either lacks normalized_name or is not at version 1. The transformation collapses every whitespace run, trims the edges, and lowercases the result. The script may modify only normalized_name, backfill_version, and updated_at.

Capture the baseline in sql/002_baseline.sql:

DROP TABLE IF EXISTS customer_profiles_before;
CREATE TABLE customer_profiles_before AS TABLE customer_profiles;

SELECT count(*) AS eligible_before
FROM customer_profiles
WHERE status = 'active'
  AND (normalized_name IS NULL OR backfill_version IS DISTINCT FROM 1);

SELECT id, full_name
FROM customer_profiles
WHERE status = 'active'
  AND (normalized_name IS NULL OR backfill_version IS DISTINCT FROM 1)
ORDER BY id;

Run it:

docker exec -i backfill-lab-db psql -v ON_ERROR_STOP=1 -U backfill_user -d backfill_test < sql/002_baseline.sql

The expected eligible count is six: IDs 1, 2, 3, 5, 6, and 9. That count is an oracle, not a production target you should hard-code into the script. In a real rehearsal, have a second person derive the expected population from the ticket or product rule. If the developer and tester copy the same predicate, the same omission can fool both checks.

Also choose invariants that remain true across the whole table: row count is unchanged, primary keys are unchanged, inactive rows are byte-for-byte unchanged, and every version-1 row matches the normalization rule. This is stronger than spot-checking three IDs.

Verify: query select count(*) from customer_profiles_before and expect 9. Query select count(*) from customer_profiles_before where status = 'inactive' and expect 2.

Step 4: Implement a Bounded, Resumable Backfill

Create backfill.py. Each loop locks at most batch_size eligible rows, updates them, and commits. FOR UPDATE SKIP LOCKED permits another worker to skip a batch already held by this one. lock_timeout prevents waiting indefinitely for a busy row, while statement_timeout caps a bad plan or overloaded statement.

import argparse
import os
import sys

import psycopg

VERSION = 1


def normalize_name(value: str) -> str:
    return ' '.join(value.split()).lower()


def run(dsn: str, batch_size: int, fail_after_batches: int | None) -> int:
    batches = 0
    total_updated = 0

    with psycopg.connect(dsn) as conn:
        while True:
            with conn.transaction():
                conn.execute('''SET LOCAL lock_timeout = '2s' ''')
                conn.execute('''SET LOCAL statement_timeout = '15s' ''')
                rows = conn.execute(
                    '''
                    SELECT id, full_name
                    FROM customer_profiles
                    WHERE status = 'active'
                      AND (
                        normalized_name IS NULL
                        OR backfill_version IS DISTINCT FROM %s
                      )
                    ORDER BY id
                    FOR UPDATE SKIP LOCKED
                    LIMIT %s
                    ''',
                    (VERSION, batch_size),
                ).fetchall()

                if not rows:
                    break

                updates = [
                    (normalize_name(full_name), VERSION, row_id)
                    for row_id, full_name in rows
                ]
                with conn.cursor() as cursor:
                    cursor.executemany(
                        '''
                        UPDATE customer_profiles
                        SET normalized_name = %s,
                            backfill_version = %s,
                            updated_at = clock_timestamp()
                        WHERE id = %s
                        ''',
                        updates,
                    )

            batches += 1
            total_updated += len(rows)
            print(f'batch={batches} updated={len(rows)} last_id={rows[-1][0]}')

            if fail_after_batches == batches:
                print('injected failure after committed batch', file=sys.stderr)
                return 75

    print(f'completed batches={batches} total_updated={total_updated}')
    return 0


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument('--batch-size', type=int, default=3)
    parser.add_argument('--fail-after-batches', type=int)
    args = parser.parse_args()
    if args.batch_size < 1:
        parser.error('--batch-size must be positive')

    dsn = os.environ.get('DATABASE_URL')
    if not dsn:
        parser.error('DATABASE_URL is required')
    return run(dsn, args.batch_size, args.fail_after_batches)


if __name__ == '__main__':
    raise SystemExit(main())

The failure switch is test-only. It returns code 75 after a transaction commits, reproducing termination between batches without corrupting the current batch. In production, process termination, a deployment, or a network break creates the equivalent state.

Run the real script:

.venv/bin/python backfill.py --batch-size 3

Verify: expect two lines for three-row batches and completed batches=2 total_updated=6. A stack trace, a silent partial count, or one transaction covering all six rows is a failed verification.

Step 5: Assert Values, Scope, and Preserved Data

Create sql/003_assertions.sql. These queries fail the command when wrapped by the final PL/pgSQL block, so they function as release gates rather than dashboard-only observations.

DO $
DECLARE
  bad_transformations integer;
  changed_protected_rows integer;
  changed_source_columns integer;
  total_rows integer;
BEGIN
  SELECT count(*) INTO bad_transformations
  FROM customer_profiles
  WHERE status = 'active'
    AND (
      backfill_version IS DISTINCT FROM 1
      OR normalized_name IS DISTINCT FROM
         lower(trim(regexp_replace(full_name, '\s+', ' ', 'g')))
    );

  SELECT count(*) INTO changed_protected_rows
  FROM customer_profiles p
  JOIN customer_profiles_before b USING (id)
  WHERE b.status = 'inactive'
    AND p IS DISTINCT FROM b;

  SELECT count(*) INTO changed_source_columns
  FROM customer_profiles p
  JOIN customer_profiles_before b USING (id)
  WHERE (p.id, p.full_name, p.country_code, p.status)
     IS DISTINCT FROM
        (b.id, b.full_name, b.country_code, b.status);

  SELECT count(*) INTO total_rows FROM customer_profiles;

  IF bad_transformations <> 0
     OR changed_protected_rows <> 0
     OR changed_source_columns <> 0
     OR total_rows <> 9 THEN
    RAISE EXCEPTION
      'invariant failure: transform=%, protected=%, source=%, rows=%',
      bad_transformations, changed_protected_rows,
      changed_source_columns, total_rows;
  END IF;
END $;

SELECT id, normalized_name, backfill_version
FROM customer_profiles
ORDER BY id;

Run the invariant suite:

docker exec -i backfill-lab-db psql -v ON_ERROR_STOP=1 -U backfill_user -d backfill_test < sql/003_assertions.sql

Expect DO followed by nine rows. IDs 4 and 7 remain null and unchanged. ID 9 contains an empty normalized name at version 1. PostgreSQL row comparison makes the protected-row check include every column, including timestamp, which catches accidental broad updates.

Checking business invariants complements API-level checks. If a backfill affects responses, add contract assertions using the techniques in API idempotency testing, especially when clients may retry while the backfill runs.

Verify: the command must exit 0. Temporarily change one active normalized_name to an incorrect value and confirm the block exits nonzero, then reload the fixture and repeat Steps 3 through 5. A test you have never seen fail is not yet a trusted guard.

Step 6: Automate How You Test Database Backfill Scripts Safely

Manual SQL is useful during investigation, but restartability and idempotency belong in repeatable tests. Create tests/test_backfill.py. The autouse fixture restores the nine-row dataset before each case and recreates the baseline table.

import os
import subprocess

import psycopg
import pytest

DSN = os.environ['DATABASE_URL']


@pytest.fixture(autouse=True)
def reset_database():
    fixture = open('sql/001_fixture.sql', encoding='utf-8').read()
    baseline = open('sql/002_baseline.sql', encoding='utf-8').read()
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(fixture)
        conn.execute(baseline)


def invoke(*args: str) -> subprocess.CompletedProcess[str]:
    return subprocess.run(
        ['.venv/bin/python', 'backfill.py', *args],
        env={**os.environ, 'DATABASE_URL': DSN},
        text=True,
        capture_output=True,
        check=False,
    )


def scalar(sql: str) -> int:
    with psycopg.connect(DSN) as conn:
        return conn.execute(sql).fetchone()[0]


def test_clean_run_updates_only_six_eligible_rows():
    result = invoke('--batch-size', '3')
    assert result.returncode == 0, result.stderr
    assert 'total_updated=6' in result.stdout
    assert scalar('''
        SELECT count(*) FROM customer_profiles
        WHERE status = 'active' AND backfill_version = 1
    ''') == 7
    assert scalar('''
        SELECT count(*) FROM customer_profiles p
        JOIN customer_profiles_before b USING (id)
        WHERE b.status = 'inactive' AND p IS DISTINCT FROM b
    ''') == 0


def test_second_run_is_a_no_op():
    assert invoke('--batch-size', '2').returncode == 0
    with psycopg.connect(DSN) as conn:
        before = conn.execute(
            'SELECT id, updated_at FROM customer_profiles ORDER BY id'
        ).fetchall()

    second = invoke('--batch-size', '2')
    assert second.returncode == 0, second.stderr
    assert 'total_updated=0' in second.stdout

    with psycopg.connect(DSN) as conn:
        after = conn.execute(
            'SELECT id, updated_at FROM customer_profiles ORDER BY id'
        ).fetchall()
    assert after == before


def test_committed_batch_can_resume_after_failure():
    failed = invoke('--batch-size', '2', '--fail-after-batches', '1')
    assert failed.returncode == 75
    assert scalar('''
        SELECT count(*) FROM customer_profiles
        WHERE status = 'active' AND backfill_version = 1
    ''') == 3

    resumed = invoke('--batch-size', '2')
    assert resumed.returncode == 0, resumed.stderr
    assert 'total_updated=4' in resumed.stdout
    assert scalar('''
        SELECT count(*) FROM customer_profiles
        WHERE status = 'active' AND backfill_version = 1
    ''') == 7

Seven active rows end at version 1 because ID 8 started completed and six rows were eligible. The restart test expects three versioned active rows after the injected failure: precompleted ID 8 plus the first committed two-row batch. It then proves the remaining four are found without a manual checkpoint edit.

Run the suite:

DATABASE_URL="$DATABASE_URL" .venv/bin/pytest -q

Verify: expect 3 passed. Run the suite three times. Each run must begin from the same fixture and produce the same assertions, even though updated_at uses the wall clock.

Step 7: Test Query Cost and Lock Contention

Correct output can still be operationally unsafe. Inspect the candidate-selection plan with production-like volume and statistics. On this nine-row fixture, a sequential scan is normal, so focus on the predicate and sort rather than demanding an index scan.

ANALYZE customer_profiles;
EXPLAIN (ANALYZE, BUFFERS, WAL, FORMAT TEXT)
SELECT id, full_name
FROM customer_profiles
WHERE status = 'active'
  AND (normalized_name IS NULL OR backfill_version IS DISTINCT FROM 1)
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 3;

Save this as sql/004_plan.sql, reload the fixture, and run:

docker exec -i backfill-lab-db psql -v ON_ERROR_STOP=1 -U backfill_user -d backfill_test < sql/001_fixture.sql
docker exec -i backfill-lab-db psql -U backfill_user -d backfill_test < sql/004_plan.sql

For a large table, evaluate actual rows versus estimated rows, buffers read, sort strategy, WAL generated by representative batches, and whether a partial index is justified. Do not add a permanent index solely because the lab has a sequential scan. Building an index can cost more and lock longer than the backfill it supports.

Now test a competing lock. In terminal A, run docker exec -it backfill-lab-db psql -U backfill_user -d backfill_test, then execute BEGIN; SELECT * FROM customer_profiles WHERE id = 1 FOR UPDATE;. Leave that transaction open. In terminal B, run the backfill. Because the worker uses SKIP LOCKED, it should finish other eligible rows instead of waiting on ID 1. Commit terminal A, rerun the worker, and confirm ID 1 completes.

Verify: the first worker run must not hang, and the second must report exactly one update. Finish by running sql/003_assertions.sql successfully.

Step 8: Rehearse the Release and Rollback Decision

Use a staging clone with production-like row count, skew, indexes, constraints, triggers, and concurrent workload. A masked sample alone may miss a rare 500 KB value or a tenant that owns 40 percent of the table. Run the exact artifact and command planned for production, not a copied query in a GUI.

Record this release evidence:

For this example, rollback is not set everything to NULL because ID 8 was valid before the run. A safe inverse operation needs the baseline or an audit table containing only rows changed by this execution. At real scale, decide before release whether recovery means restoring recorded old values, rolling forward with a corrected version, or restoring a database snapshot. The answer depends on data criticality and recovery time, not convenience.

Use a canary: run one small batch, pause, query invariants, inspect application behavior, and then continue. Monitor database CPU, lock waits, dead tuples, WAL rate, replica lag, error rate, and the worker's rows-per-batch. The risk-based testing guide helps set stricter gates for financial, authorization, and customer-visible fields.

Verify: have an operator who did not write the script execute the runbook in staging. They must be able to identify the target database, stop safely, locate progress, run invariants, and state the recovery decision without asking the author for missing commands.

Troubleshooting

Problem: the eligible count is unexpectedly zero -> Confirm the database and schema first with select current_database(), current_schema(). Then inspect the version predicate with IS DISTINCT FROM. SQL <> does not match NULL, so a seemingly equivalent rewrite can omit never-processed rows.

Problem: the second run updates every row again -> Make the completion marker part of the selection predicate and update it in the same transaction as the derived value. Compare values with IS DISTINCT FROM when nullable columns participate. Assert unchanged timestamps on the second run.

Problem: the worker hangs behind application traffic -> Inspect pg_stat_activity and pg_locks, reduce the batch size, retain a short lock_timeout, and use SKIP LOCKED only when skipping and retrying later is valid. Never raise timeouts blindly without identifying the blocker.

Problem: the Python and SQL normalized values disagree -> The two implementations may treat Unicode whitespace, case, or collation differently. Choose one canonical rule, add accented and locale-sensitive fixtures, and implement the oracle independently but equivalently. Do not declare one output correct merely because both paths copied the same helper.

Problem: the failure test rolls back all progress -> Verify the failure occurs after leaving conn.transaction(). If one outer transaction wraps every batch, committed progress does not exist. Observe counts from a second connection so an uncommitted session cannot fool the test.

Problem: a staging run is fast but production replicas lag -> Rehearse with representative volume and watch WAL bytes and replay lag. Lower batch size, add a pause between batches, schedule for lower write traffic, and define a stop threshold before starting.

Interview Questions and Answers

Backfill interviews usually test whether you can connect data correctness with operational safety. Expect questions about independent oracles, null semantics, transaction boundaries, idempotency, concurrency, and observability. The model answers in the interviewQnA section below are concise enough to practice aloud; the senior database testing scenarios provide broader follow-up cases.

Best Practices

• Make the target database visually and technically distinct. Use separate credentials, a nonstandard local port, and a startup assertion for the expected database name.
• Derive eligibility independently from the implementation. Compare counts, boundary IDs, and tenant or status distributions before writes begin.
• Commit bounded batches. Batch size should be based on measured latency, WAL, lock duration, and recovery needs, not a round number chosen by habit.
• Write the completion marker atomically with the transformed fields. This makes resume and idempotency properties testable.
• Preserve before-values for irreversible changes. A database backup is useful only if the restore time and restore procedure satisfy the incident plan.
• Treat observability as part of the feature. Emit batch number, rows changed, duration, last key, and structured failure details without logging private row content.
• Stop on violated assumptions. A backfill that continues after its expected population doubles is not resilient, it is uncontrolled.

Where To Go Next

Adapt the lab to your real schema. Replace the fixture, selection predicate, transformation, and invariants, but keep the lifecycle: baseline, bounded execution, clean-run assertions, no-op rerun, interruption, contention, and staged rehearsal.

Next, strengthen the surrounding test system:

• Use SQL setup and teardown patterns to isolate related-table fixtures.
• Apply API test data management when the backfill changes records exposed through services.
• Review API idempotency testing if clients and workers can repeat writes.
• Practice additional senior database testing scenarios for deadlocks, replicas, and partial failures.
• Prioritize validation using risk-based testing when the dataset contains high-impact fields.

Conclusion

To test database backfill scripts safely, verify the state transition and the operating conditions around it. A clean result is necessary, but restartability, idempotency, protected-row invariants, lock behavior, and measurable stop conditions are what make the change releasable.

Run this lab once unchanged, then substitute one real backfill requirement. If your script cannot pass a second run and a mid-run interruption in a disposable clone, it is not ready for production.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Test Database Migration Rollback Safely (2026)
• Database Rider vs DbUnit Test Data (2026)
• Test scenarios vs test cases (2026)
• A/B test validation: A Complete Guide for QA (2026)
• AI assisted API test generation (2026)
• AI for exploratory test charters (2026)