Data backfills need replayable evidence

A backfill is not complete because the script reached the end of the table.

Rows can change while a scan advances. One joined source can match twice. A retry can transform an already transformed value. A worker can commit a chunk and lose its checkpoint. A clean row count can hide wrong semantics. The job therefore needs evidence that survives execution and can be replayed against the intended scope.

PostgreSQL's UPDATE documentation makes two useful boundaries concrete: RETURNING can expose values from rows actually updated, while UPDATE with a FROM join should not produce more than one source row for each target because the selected source row is otherwise not readily predictable.

For parallel claims, SELECT with SKIP LOCKED is documented as producing an inconsistent view and being suitable for queue-like access rather than general-purpose reading. That makes it a work-distribution technique, not a substitute for a stable scope definition or final reconciliation.

I would treat each write as one evidenced decision under a versioned transformation, then prove the whole population through independent invariants. Replay means the same evidence can explain skip, change, conflict, retry, and repair without trusting worker memory.

Version the source population

Record selection logic, join assumptions, tenant, effective time, exclusions, revision, and a stable membership strategy.

A scope query is executable product logic. I would store its revision, parameters, effective time, tenant boundary, exclusions, and a plan or snapshot reference with the run. A join also needs a cardinality assertion: one target joined to two candidate owners cannot be resolved by whichever row the database happens to choose. If live membership is intentional, the contract defines a cutoff and late-arrival policy. Otherwise a manifest or snapshot gives every later count a stable denominator.

The population definition should be executable by someone other than the script author. I would store the query revision, parameters, tenant boundary, effective time, exclusions, join-cardinality checks, and either a snapshot or stated live-membership rule. A target joined to two candidate sources must stop before mutation rather than accept whichever row the database selects. Cohort counts should be reproducible from the same definition and explain changes caused by foreground traffic. The scope is ready when an independent reviewer can derive both candidates and exclusions without a ticket narrative. Before approving the run, the team should answer:

• Which rows belong?
• Can membership change?
• Does each join yield one decision?
• Can scope be reconstructed?

Scope proof combines reproducible candidate and exclusion counts, one-to-one join assertions, a recorded cutoff or snapshot, and an independent reconstruction from the retained query revision.

Define transformation and invariants

Define old-state preconditions, deterministic output, normalization, dependencies, and product invariants that explain correctness.

The transformation should behave like a versioned pure function around an explicit old-state precondition, even if applying it requires a transaction. For owner normalization, inputs might include the legacy identifier, tenant mapping revision, deletion state, and legal-hold rule. Examples cover already-normalized values, missing mappings, cross-tenant collisions, and repetition. I would publish both row-level and population invariants so reviewers can tell whether an apparently valid new value preserves the product relationship it represents.

The transformation contract should make business meaning visible around the update statement. I would define the eligible old states, normalized inputs, dependency versions, deterministic output, and row-level and population invariants. Examples should cover ordinary, boundary, invalid, already-correct, and repeated inputs, including any cross-tenant or deleted-source condition. If repetition changes the answer, replay requires an explicit version or guard rather than an idempotent label. Domain owners should review the examples before code freezes. The transform can govern production rows only when reviewers agree on:

• What inputs determine result?
• Is repetition safe?
• Which old states qualify?
• What invariant holds afterward?

The transform is trustworthy when executable examples and repeated application preserve row and population invariants across valid, boundary, already-correct, invalid, and dependency-change cases.

Scope example — one-to-many joins must stop the run. Suppose a legacy case joins two active owner mappings because an earlier import duplicated an external identifier. Allowing UPDATE FROM to choose one row would produce nondeterministic ownership that still looks syntactically valid. The dry run should surface that cohort, the scope contract should require one mapping, and the executor should quarantine violations before mutation. A domain owner can repair or explicitly exclude them. The final denominator then explains those rows instead of letting database join behavior make an unreviewed product decision.

Profile before writing

A dry run should measure candidates, nulls, duplicates, invalid formats, cohorts, expected changes, join fan-out, plans, runtime, and load.

A dry run should stratify evidence rather than print one impressive total. I want candidate and exclusion counts by tenant size, data age, source format, and any cohort likely to fail differently. It should expose nulls, duplicate join keys, proposed no-ops, invalid values, and redacted old-new samples reviewed by a domain owner. Query plans, buffer use, replication impact, and expected write amplification turn that profile into a capacity decision instead of a semantic-only rehearsal.

A dry run needs more than a candidate total. I would profile nulls, duplicate source keys, invalid formats, proposed no-ops, cohort skew, join fan-out, query plans, buffer use, estimated WAL, and redacted old-new samples. Counts should be grouped by the characteristics most likely to change failure or load, such as tenant size, data age, and source format. A domain reviewer validates semantic samples while an operator validates capacity assumptions. The profile should predict enough of the real run to set budgets and quarantine expectations. Its approval depends on answers to:

• How many rows change?
• Which cohort has exceptions?
• What load does selection create?
• Do samples match domain meaning?

Dry-run approval requires cohort forecasts that explain exceptions, samples accepted by a domain owner, and measured selection and write costs that fit the initial operating budget.

Chunk by a stable order

Use a unique indexed key or durable manifest so checkpoints represent membership rather than shifting OFFSET positions.

OFFSET is not a durable cursor over a population that can change while rows are updated or deleted. I prefer a unique indexed key with a fixed upper bound, or a manifest when eligibility is expensive or mutable. A checkpoint records the last committed key and the scope revision, not the last row a worker remembers reading. Parallel workers may claim nonoverlapping ranges or queue records, but final reconciliation remains independent because work distribution can be intentionally inconsistent.

A cursor should represent committed progress through stable membership. I would use a unique indexed key with a fixed bound or a durable manifest, then record the scope revision and last committed key in each checkpoint. Parallel workers can claim disjoint ranges or queue rows, but they must not use shifting OFFSET pages as evidence that nothing was skipped. Inserts after the cutoff follow the declared late-arrival rule. Restart tests should kill workers after selection and after commit, then prove every candidate is visited without duplicate irreversible work. The chunking review should establish:

• Which key orders candidates?
• Can workers overlap?
• What does checkpoint mean?
• How are inserted rows handled?

Chunking passes when killed and parallel workers resume from committed cursors without gaps, shifting membership, duplicate irreversible work, or ambiguity about late-arriving rows.

Cursor example — a committed chunk loses its checkpoint response. If row decisions and the durable checkpoint share a transaction, restart continues after the committed boundary. If they cannot, replay must safely encounter already-satisfied rows and classify them unchanged under the same transform. The worker never infers commit from a log message or an acknowledgement it may not receive. This scenario belongs in fault injection because it separates idempotent transformation from exactly-once execution and proves that resuming cannot skip work or apply the same irreversible mutation twice.

Guard every write

Each mutation should assert the intended old state, remain idempotent, and classify failed preconditions as concurrent decisions.

The update should encode its observation: update the row only when the current version or relevant old fields still match what the transformation evaluated. Zero affected rows then means reclassify, not silently count success. A foreground edit may have already established a newer correct owner, made the row ineligible, or introduced a fresh candidate state. I would reread and apply the published conflict policy. That protects live user intent without making the backfill stall on every benign concurrent change.

Every write should assert the state from which its decision was made. I would include a row version or relevant old values in the update predicate and interpret zero affected rows as a conflict requiring reread, not as success. A foreground correction may already satisfy the invariant, remove eligibility, or require the current transform against new facts. That path deserves its own outcome class. Repeating the guarded write should not damage a row that already converged. Concurrency fixtures should race real product edits against each chunk, and the write-policy review needs to decide:

• What protects live edits?
• Can the write repeat?
• Does zero mean success or conflict?
• How is the row re-evaluated?

Guarded-write proof is a race suite where fresh product edits survive, zero-row outcomes are reclassified correctly, and eligible stale states converge under safe repetition.

Capture committed old-new evidence

Record row, transform version, attempt, bounded old-new references, commit outcome, and reason from the changing transaction.

Intent logs cannot prove a mutation committed. Where possible, the guarded transaction should use RETURNING to capture the row identity, prior-version reference, resulting value hash, transformation revision, attempt, and outcome while committing the checkpoint or ledger entry. Sensitive values can be encrypted briefly or represented by keyed hashes and redacted samples. Unchanged decisions also deserve evidence because a repair run must distinguish rows already satisfying the invariant from rows skipped without evaluation.

Evidence of intent is not evidence of commit. Where possible, the changing transaction should return the row identity, precondition reference, transform version, result hash, attempt, and decision class while it records the ledger or checkpoint. Sensitive values can use short-lived encryption, keyed hashes, and redacted samples instead of permanent copies. Unchanged rows also need a class so repair does not confuse evaluated success with omission. After simulated response loss, the ledger and database should still reconcile without trusting worker logs. Reviewers should verify the evidence boundary by answering:

• Can evidence race mutation?
• Which values need redaction?
• How are unchanged rows represented?
• What identifies commit?

The row ledger is credible when transaction-loss tests reconcile committed values with attempts and checkpoints, while redaction and retention controls prevent it from becoming a shadow database.

Concurrency example — foreground correction beats historical inference. An operator fixes an account owner after the backfill reads the old row but before it writes. A guarded update detects the changed version and refuses to replace the correction with a value derived from stale sources. Reevaluation may classify the row already valid, no longer eligible, or needing a new transform. The ledger preserves that path. Without the precondition, the script can achieve perfect completion counts while silently undoing the very product state customers and operators most recently established.

Coexist with foreground traffic

Set query, lock, CPU, I/O, replication, connection, and error budgets with throttle, pause, and abort controls.

Backfill throughput is subordinate to foreground health. I would set budgets for statement time, lock wait, CPU, read and write I/O, WAL volume, replication lag, connection use, and customer latency, then let the controller reduce chunk size or pause. A failover, replica recovery, or incident should stop new claims quickly without invalidating committed checkpoints. Capacity evidence is gathered in production cohorts because staging rarely reproduces data skew, hot keys, autovacuum pressure, or actual competing queries.

The pace controller should protect foreground latency, errors, locks, CPU, I/O, connections, WAL, and replica health rather than maximize rows per second. I would canary a representative cohort, raise concurrency gradually, and require pause to stop new claims within a measured bound. Failover, incident response, or replica recovery should take priority without invalidating committed checkpoints. Production skew may make staging estimates wrong, so budgets need live signals and automatic reduction. The operating plan should name who can resume and what evidence they inspect. Capacity review should resolve:

• Which signals control pace?
• Can chunks hold locks too long?
• How fast can work pause?
• What happens during failover?

Capacity evidence should show representative production cohorts staying inside latency, error, lock, resource, WAL, and replica budgets while pause and incident handoff meet their stated bounds.

Quarantine ambiguity deliberately

Invalid data, missing dependencies, conflicts, timeouts, unknown commits, and permanent failures need distinct reasons, retries, evidence, and owners.

Quarantine reasons should suggest the next action. Invalid source format may require product-owned cleanup; a missing mapping may wait for another import; a serialization failure can retry; an unknown commit must be reconciled before mutation repeats; a legal hold may become an explicit exclusion. Each record carries scope and transform versions, bounded evidence, attempts, next eligibility time, and owner. One retry counter obscures these differences and turns deterministic bad data into noisy infrastructure work.

Quarantine classes should lead to different actions. Invalid source data may need product cleanup; a missing dependency can wait for another import; serialization can retry; an unknown commit must reconcile before repetition; a legal hold may become an approved exclusion. Each record should carry scope and transform revisions, bounded evidence, attempts, next eligibility, and an owner. A single error counter creates noise and can retry deterministic failures forever. I would rehearse one row through correction and repair to prove the selector works. The exception taxonomy should answer:

• Is failure deterministic?
• Could the write have committed?
• Can correction restore eligibility?
• Who owns unresolved work?

Quarantine closes operationally when each reason selects the intended retry, correction, reconciliation, exclusion, or escalation path and every unresolved row has a named owner.

Capacity example — replication lag changes the plan. A chunk size that looks safe for the primary may generate enough WAL to delay replicas used by reads, failover, or downstream processing. The controller should observe replica replay delay and recovery state, reduce concurrency, and pause before the lag consumes the operating budget. Checkpoints make that pause boring. The next run resumes from committed keys rather than rescanning by OFFSET. This is why execution speed is never an isolated metric: the backfill shares a live system whose recovery options must remain healthy.

Reconcile outside the executor

Closure should independently rerun population and invariants, balance decision classes, compare cohorts, sample, and account for live change.

The validator should not import the worker's success list and call it truth. It reruns the versioned eligibility query, checks product invariants directly, balances changed, unchanged, conflicted, excluded, quarantined, and remaining classes, and compares meaningful cohorts with the dry run. A separate implementation or simpler query reduces shared-bug risk. Because foreground writes continue, the closure protocol states its observation window and explains any delta instead of demanding an impossible frozen database.

Closure must come from a validator that does not merely consume worker success totals. I would rerun the versioned population, test domain invariants directly, balance every decision class, compare cohorts with the dry run, and sample results through a simpler or independently implemented query. Foreground traffic means the observation window and expected delta must be explicit. Zero remaining candidates is insufficient if rows were transformed incorrectly or quietly excluded. The run closes only when no unexplained remainder exists and every quarantine has disposition. Reconciliation reviewers should examine:

• Do totals equal scope?
• Which candidates remain?
• Do invariants hold by cohort?
• Could worker and validator share a bug?

Reconciliation proof is a balanced denominator and clean independent invariants by cohort, with all foreground-change deltas explained and no reliance on executor success counters.

Design replay, repair, and retention

Later runs should resume the definition, target unresolved rows, compare transform versions, and retain bounded support evidence.

A repair selects a named unresolved class from the original receipt and either reuses the transformation revision or declares a new one. It does not restart a broad scan with edited code and overwrite lineage. Checkpoints, row decisions, reconciliation summaries, and approval link the runs so repeated success is avoided. Detailed old-new evidence can expire sooner than aggregate closure proof; retention should support the expected support and audit window without preserving personal values indefinitely.

A repair run should select a named unresolved class from the original receipt, preserve its scope lineage, and declare whether the transform or dependency revision changed. Successful rows remain outside the selector. The child receipt records its own checkpoints and decisions while linking resolutions back to the parent's balanced totals; it does not rewrite history to make the first run appear perfect. Detailed old-new evidence may expire before aggregate closure proof, so retention should follow support and audit purpose. Before operationalizing replay, I would ask reviewers to settle:

• Can repair select unresolved work?
• How is a new transform marked?
• What evidence supports repair?
• When does row detail expire?

Replay is safe when a targeted repair resolves its selected remainder, leaves prior successes untouched, preserves transform lineage, and links new evidence to the original closed receipt. A second independent reconciliation should balance the combined parent and child outcomes, verify that corrected rows now satisfy the invariant, and leave any still-unresolved class visible with ownership. The closure remains independently reviewable.

Repair example — quarantine deserves a selector. After a missing mapping is corrected, the repair run should select only rows with that reason under the original scope, record the new dependency revision, and reuse the transformation if its semantics did not change. Rows already changed remain untouched. Reconciliation links the repaired decisions back to the first receipt and reduces the owned remainder. The parent receipt stays closed with its original totals while the linked repair records the later resolution. That workflow turns exception handling into a bounded product operation; exporting IDs to a spreadsheet and launching an edited script would discard the lineage needed to explain both attempts.

The evidence pack I would approve

The release packet begins with purpose, product invariant, scope revision, transformation version, dry-run profile, and capacity budget. It includes the chunking and guarded-write protocol, pause and abort controls, quarantine taxonomy, reconciliation queries, and named owners. During execution it accumulates checkpoint freshness, decision totals, cohort deltas, and incident notes without rewriting the approved definition. Closure adds independent invariant results, a balanced denominator, owned exceptions, and the follow-up retention date. That pack allows another engineer to pause, resume, validate, or repair the change without learning undocumented intent from the original script author.

# definition
scope q7 / transform normalize-owner v4 / snapshot s88
Invariant: every active case has one canonical owner; exclusions include deleted tenants and open legal hold; source join uniqueness prechecked.
# execution
run bf_2026_07 / cursor account_id:981… / attempts 3
4.8M candidates; 4.76M changed, 31K unchanged, 7K conflicted, 2K quarantined; foreground latency budget held.
# closure
remaining eligible 0 / violations 0 / quarantine owned
Independent reconciliation hash and cohort counts retained; sampled old-new evidence redacted; repair run references original row decisions.

A row-decision ledger is not a data dump

The illustrative run IDs, dates, cursors, row counts, rates, percentages, and hashes in Figure 4 are invented examples of evidence shape, not production measurements. A useful ledger records stable row reference, scope and transform revisions, old-state precondition, result class, bounded before-after evidence, attempt, and commit reference. It does not retain entire customer records or raw payloads by default. Aggregates and keyed hashes can preserve reconciliation value after detailed samples expire. I would test access control and deletion behavior alongside replay so an operational safeguard does not quietly become a second ungoverned copy of sensitive data.

Throttle from product pain, not worker ambition

The controller should react to the signals customers feel: foreground latency, errors, lock waits, replication health, and resource saturation. A target rows-per-second rate is only a starting guess. I would canary one representative cohort, raise concurrency gradually, and verify that pause takes effect within the stated bound. Scheduled quiet periods can help, but the job must remain safe when traffic surprises the forecast. If an incident begins, relinquishing capacity is the correct outcome even when it extends completion; a backfill that meets its deadline by destabilizing the product has violated its operating contract.

Reconciliation closes the semantic change

Closure requires more than a zero remaining query. The new values must satisfy the product invariant by cohort, decision totals must balance against the approved scope, quarantine must have explicit owners or exclusions, and sampled results must make sense to a domain reviewer. I would observe the remainder for a defined horizon when live writers can recreate legacy states, then either fix those writers or make the invariant enforceable. A worker completion event starts this review. The independent reconciliation receipt and owner approval end it, leaving a queryable explanation for what changed and what intentionally did not.

How I would present this backfill

The case study would center on one concurrent-edit failure that the original approach would overwrite. I would show the scope and transformation contract, a dry-run surprise such as duplicate join cardinality, the conditional write that protects fresh product state, and a reconciliation result after an injected crash. The downloadable evidence pack would contain executable queries with synthetic fixtures and redacted receipt examples. I would avoid treating millions of updated rows as the achievement. The stronger outcome is that every candidate acquired an explainable class, foreground health stayed inside budget, and a targeted repair could run without repeating successful work.

The population boundary I would defend

I would ask whether to snapshot the population or let membership remain live. A snapshot gives a stable denominator and reproducible evidence but costs storage and can miss newly eligible rows. A live query adapts to current truth but complicates checkpoints, totals, and closure. The right choice depends on product semantics, data volume, available database features, and whether a later incremental mechanism exists. I would also discuss when old-new evidence is worth its write amplification and privacy cost. The answer should tie each expense to a specific recovery or proof requirement, not collect telemetry by habit.

What replayable evidence demonstrates

A strong backfill demonstrates more than SQL fluency. It connects domain meaning, population definition, deterministic transformation, concurrency control, transactional proof, capacity management, privacy, and independent validation. Reviewers can see why each row changed, why another was protected, how a crash resumes, and which query proves closure. Operators receive bounded pause and repair controls instead of a one-shot script. That is the hiring signal: I can make a large irreversible-looking data operation incremental, explainable, and governable while the product continues to serve real traffic.