A Backfill Is Not Finished When the Script Stops
Author: OmniStack
Published at: 11/19/2026

On this page
- Why exit code 0 is not an acceptance event
- Place the backfill inside parallel change
- Define the population before checking completeness
- Build the proposed completeness and invariants acceptance template
- Use PostgreSQL mechanisms without overstating them
- Handle concurrent writes and retries as acceptance concerns
- Make the contract gate hard to misread
- FAQ
- What are backfill acceptance criteria?
- Why is exit code 0 insufficient for a database backfill?
- What does NOT VALID mean in PostgreSQL?
- Who should accept unresolved backfill exceptions?
- Sources and limits
Why exit code 0 is not an acceptance event
A backfill should be planned as a data change, not as a long-running script with a happy-path finish line. It is accepted only when the defined population satisfies the required business invariant and the evidence has been approved by the owner of that invariant. A clean process exit proves that the job reached its own stopping condition. It does not prove that every eligible row was selected, that concurrent writes were covered, or that the new representation is safe to make mandatory.
Take a hypothetical commerce platform operating warehouses in Singapore and Australia. The team adds a nullable fulfilment_region column to an orders table. New application code can read either the old address-derived value or the new column. A batch job processes eligible orders and stops with exit code 0. The log reports that every selected row was updated.
That result answers one operational question: did the batch complete according to its implementation? It does not answer the questions that matter to operations:
- Were cancelled orders, archived customers and migrated stores intentionally included or excluded?
- Did an order change after the batch selected it?
- Did an offline warehouse workflow write an old representation after the new application was deployed?
- Does the new region value agree with the source of truth used by finance and fulfilment?
- Can the old column be removed without breaking a delayed export or an admin screen?
Row count equality is weak evidence when the population query is wrong. A script can update every row it selected and still miss rows created during the run, omit a site because of a tenant filter, or write a derived value from stale source data. The database may be internally consistent while the business rule remains false.
This is why data governance and management practices matter during a schema migration. The question is not merely where the data sits. The question is who defines its meaning, which source wins when records disagree, and who can approve an exception.
For a multi-site operator, the acceptance record should separate four events:
- Process completion: the job reached its configured end condition.
- Population completeness: every row defined as eligible was examined or covered by an explicit follow-up pass.
- Invariant validity: the new business rule holds for the accepted population.
- Contract approval: the owner accepts the evidence and authorises removal of the old path.
Only the fourth event permits the destructive step. Until that decision is recorded, a completed script is a migration input, not a migration outcome.
Place the backfill inside parallel change
Martin Fowler's Parallel Change pattern gives the useful operating model: expand, migrate, contract. The backfill belongs in migrate, and acceptance is the gate between migrate and contract. Contracting before that gate turns an uncertain data change into an outage or a silent reporting defect.
The pattern matters because deployment states overlap in APAC operations. In a hypothetical order platform, a Singapore warehouse screen may receive the new release before an Australian finance export. A mobile device may remain offline while an administrator edits an order. An integration partner may continue sending the old payload after the primary application has switched to the new field.
- Expand: add the new column, table, event or representation without breaking existing readers and writers.
- Migrate: populate historical data, reconcile live changes and establish evidence for the new invariant.
- Contract: remove the old field, disable compatibility logic or enforce the new constraint only after acceptance.
The common failure is to treat the migration script as the centre of the work. The acceptance gate is the centre. The script is one mechanism inside it. A dedicated engineering team can provide continuity across application, QA, data and infrastructure work, but ownership still has to sit with the business owner of the invariant. No delivery team can turn an undefined population into an accepted one by producing a more polished log.
Expand should make coexistence explicit. Identify old readers, new readers, old writers and new writers. Record which of them can operate simultaneously and which one is authoritative when values conflict. If both old and new paths write, define the reconciliation rule before the backfill begins. If only one path should write after a release, monitor for evidence that an old client, offline workflow or integration has violated that assumption.
A useful migration plan names the contract trigger in advance. It might be a validated database constraint, a reconciliation query with zero unresolved exceptions, or a business sign-off tied to a defined exception register. "The job has finished" is not a contract trigger because it describes execution rather than state.
For systems undergoing broader renewal, legacy modernisation and migration planning should preserve this separation. Replacing an old representation while the business continues operating requires a coexistence plan, not a single cutover timestamp.

Define the population before checking completeness
The first backfill acceptance criterion is a written population definition. It must state which rows are eligible, which are excluded, the time boundary, the tenant or site boundary, and the source of truth. Without that definition, "complete" means only "the query returned no more rows".
A hypothetical order platform might define its population as orders created for active Singapore and Australian sites before a specified boundary timestamp, excluding test tenants and permanently cancelled orders. That sounds precise until the team asks what to do with an order that was active at selection time and cancelled during the run. The answer belongs in the business rule, not in an engineer's private assumption.
The approach fails when the population is inherently unstable and nobody can establish a boundary. A high-volume system with no reliable change marker, no audit record and no authoritative source may not be ready for a destructive backfill. The right decision can be to postpone the contract, add change capture, or keep the old and new representations in parallel. A short script is not a substitute for missing history.
Write the scope in terms that an operations leader can challenge:
- Included rows: the tables, status values, sites, tenants and creation or update window that must satisfy the rule.
- Excluded rows: test data, archived records, soft-deleted entities, cancelled orders or migrated stores, with a reason for each exclusion.
- Boundary: the timestamp, sequence, version or change marker that makes the population reproducible.
- Source of truth: the field, event, ledger or external record used to derive the new value.
- Late arrivals: records inserted after the initial selection, including offline uploads and delayed integration messages.
- Corrections: manual edits or upstream changes that can invalidate a value after it has been populated.
Historical cancelled orders are a common trap. They may be excluded from current fulfilment logic but still appear in reporting, customer service searches or data exports. Archived customers may be outside the live application yet remain inside a reconciliation requirement. A migrated store may have old records whose source system uses a different code set. None of these cases is automatically an exception. Each one needs a named rule.
The population query should be versioned with the migration. Capture its SQL, parameters, boundary timestamp and result count. A later reviewer must be able to tell whether a changed count reflects new data, a changed query or a changed business definition. If the query cannot be reproduced, the acceptance evidence is weaker than the log suggests.
Completeness also needs a late-write policy. A backfill may use a snapshot at the start of a batch while live transactions continue. The team can use a delta pass, a change log, a dual-write period or a recomputation after live writes have exercised the new path. The choice depends on workload and database behaviour. The acceptance record must state which mechanism was used and what evidence shows that it covered the gap.
Build the proposed completeness and invariants acceptance template
The following is a proposed template, not an OmniStack deployment. It is designed to force a decision about evidence, failure modes and ownership. A completed row should contain links to logs, queries, constraint status or review records rather than a statement that the job ran successfully.
Acceptance question | Evidence to capture | Failure or edge case | Exit decision |
|---|---|---|---|
Which rows are in scope? | Population query, boundary timestamp, exclusions | Late insert or archived row omitted | Accept, rerun slice or revise scope |
Does each invariant hold? | Validation query or database constraint status | Null, orphan, duplicate or inconsistent derived value | Fix data, record exception or block contract |
What happened during concurrent writes? | Dual-write or recompute evidence, replay window | Row changed after selection but before update | Run delta pass or hold contract |
Can the job resume safely? | Checkpoint, batch log, deterministic update rule | Partial batch committed before crash | Resume, repair batch or use an approved forward repair path |
Who accepts exceptions? | Named owner, reason, expiry or follow-up | Business exception hidden as technical success | Conditional accept or reject |
The invariant row should describe the business rule, not merely the storage shape. "Column is non-null" may be a database condition, but the actual rule could be "every active order is assigned to the operational region that owns its fulfilment workflow". Those are different checks. A non-null value can still be wrong, stale or assigned to the wrong site.
Useful database backfill invariants often include referential integrity, uniqueness, permitted values, temporal consistency and agreement with a source record. In the hypothetical fulfilment-region change, the validation may compare the new value with the region resolved from the order's active warehouse mapping. In a hypothetical customer migration, it may check that every active account has one canonical identifier and that downstream references point to it.
Do not hide exceptions inside a successful count. An exception register should identify the record or slice, the reason it differs, the operational impact, the owner and the expiry or follow-up action. A time-limited exception can be accepted when the business owner understands it. An unowned exception is a contract blocker.
Acceptance evidence should also capture the query version and the database version. A validation query that depends on collation, time zone or a particular PostgreSQL feature should be reproducible in the environment where the decision was made. This is especially important when Singapore and Australian sites share a platform but run different operational schedules or data conventions.
Related:data engineering and analytics services, useful context for building repeatable reconciliation and data quality checks around migration work.
Use PostgreSQL mechanisms without overstating them
PostgreSQL features can support a safer rollout, but none of them turns an incomplete backfill into accepted data. The PostgreSQL ALTER TABLE documentation describes constraints that may be added as NOT VALID. That can be useful during rollout because existing rows are not immediately scanned for compliance. It is not evidence that old rows satisfy the rule. A later VALIDATE CONSTRAINT step is a different event and should be part of acceptance if the constraint expresses the invariant.
For example, a foreign key or check constraint added as NOT VALID can be useful while legacy rows remain unexamined. That creates a boundary between protecting future changes and proving historical data. The contract gate should require the relevant VALIDATE CONSTRAINT event, or an equivalent validation query, when the constraint expresses the invariant being accepted.
Locks need the same precision. The PostgreSQL explicit locking documentation explains that commands acquire locks with defined conflict behaviour and that locks are held for the relevant transaction scope. The practical impact depends on the exact command, pinned PostgreSQL version, transaction scope and workload. An ALTER TABLE, validation query and repair update can affect order entry, picking, invoicing or customer service in different ways.
Do not prescribe one universal lock strategy. Ask these questions against the actual workload:
- What lock mode does the chosen DDL or validation operation request on the pinned PostgreSQL version?
- Which production statements conflict with that lock?
- How long can the operation hold the lock under the expected table size and concurrent traffic?
- What monitoring signal tells the team that customer-facing work is waiting?
- What is the abort procedure if the maintenance window is consumed by lock contention?
A constraint that protects new writes may be valuable during expand, while full validation belongs in migrate. A validation query may be safer than a blocking DDL operation for an initial assessment, but it does not replace the constraint if the invariant must be enforced continuously. The acceptance record should state whether evidence is observational, enforced by the database, or both.
Isolation also limits what a batch can claim. The PostgreSQL transaction isolation documentation explains that a statement observes a database state according to the selected isolation level. A batch that commits each slice is not examining one timeless version of the business. Rows can be inserted, updated or corrected after earlier slices have committed.
Teams working on data-heavy modernisation should connect the migration plan to their DevOps and CI/CD operating model so that schema changes, validation queries, alerts and rollback procedures are reviewed as one delivery path. The link between deployment and data acceptance should be explicit, not left to a release checklist.
Handle concurrent writes and retries as acceptance concerns
A backfill running against live data must show what happened after each batch selected its rows. One defensible acceptance pattern is a post-write invariant check after the live-write pattern has been exercised, supported by a delta pass, recomputation rule or change replay that covers the interval between selection and validation.
Imagine a hypothetical batch selecting orders where fulfilment_region is null. It derives the value from the warehouse mapping and updates the rows. While the batch is working, an operations user changes the warehouse assignment, or an offline device uploads a correction. The update may commit successfully with a value that was correct at selection time and wrong at acceptance time.
Several controls can address that race, but they are not interchangeable:
- Dual write: new and old representations are updated together for a defined coexistence period.
- Delta pass: rows changed after the initial selection are identified and processed again.
- Recomputation: the derived value is recalculated after the live-write window and compared with the stored value.
- Change capture: an audit stream or change table records mutations for replay or reconciliation.
- Optimistic predicate: the update applies only if the source version or timestamp remains unchanged.
The right control depends on the system's write patterns and the consequence of a stale value. A warehouse allocation field may need a stronger rule than a non-critical display label. A financial export field may require an authoritative source and an explicit exception owner. The migration plan should not promise a single perfect delivery semantic. It should describe the observable behaviour and the repair path.
Retries are part of correctness. PostgreSQL guidance on serialization failure handling points to retrying the complete transaction, including the decision logic that selected and transformed the rows. Retrying only the final SQL statement can apply a stale decision to a new database state. Deadlock handling has the same shape: the unit of work must be safe to repeat, and its effects must be idempotent or reconciled.
A resumable job needs more than a last processed identifier. The checkpoint must identify the population slice, the ordering rule, the source version or boundary used, the transaction outcome and any exceptions emitted. If a batch partially commits before a process failure, rerunning it should either produce the same accepted value or route the row to a repair path. A job that cannot distinguish "updated", "skipped by predicate", "failed and retried" and "excluded by policy" cannot produce strong acceptance evidence.
Test the live-write pattern before the contract decision. For a hypothetical multi-site platform, exercise admin edits, delayed integration messages, offline uploads and reporting reads while the migration runs. The purpose is not to prove that no race exists. The purpose is to show how a race is detected, corrected and included in the final invariant check.

Make the contract gate hard to misread
Before removing compatibility code, dropping the old field or making a new constraint mandatory, record a contract decision that names the accepted population, unresolved exceptions, validation evidence, live-write coverage, retry behaviour and decision owner. The contract gate should be a business decision supported by engineering evidence, not an automatic follow-on from a green pipeline.
The decision record should answer five questions in plain language:
- What population was accepted? Include sites, tenants, statuses, time boundaries and exclusions.
- Which database backfill invariants were proven? Link the validation queries, constraint validation results and reconciliation outputs.
- What changed during the backfill? State the delta method, replay window, source version checks and unresolved late writes.
- What exceptions remain? Give each one an owner, business reason, expiry or follow-up action.
- Who authorises contract? Name the product, operations, data or technology owner with authority to accept the business risk.
The contract step should also have a bounded rollback or repair plan. Dropping a column may be irreversible once dependent data or code is removed. If rollback is not practical, the plan needs a forward repair path, preserved evidence and a clear incident route. Restoring a backup may restore storage without restoring the business state created after the backup.
Acceptance should be conditional when the evidence is good but the business has approved known exceptions. Conditional acceptance is not a weaker version of full acceptance if it states the limits. It becomes dangerous when teams use the label to avoid naming an owner or expiry.
The approach is the wrong choice when the data set is small, static, isolated from live writes and covered by a direct review that the business owner can perform in one transaction or controlled maintenance window. In that case, a heavyweight coexistence plan can add more operational machinery than the change warrants. The condition is not convenience. The condition is that the population is stable, the invariant is observable, the blast radius is bounded and the contract decision remains explicit.
It is also the wrong choice to outsource acceptance to a rotating group that has no continuity with the roadmap. A staff augmentation arrangement can provide people to run queries, but it does not inherently provide ownership of the outcome. OmniStack's model is a dedicated engineering team on its payroll and on the client's roadmap, which can suit a company that needs continuous engineering, QA and infrastructure ownership across the migration. In-house is the right answer when the organisation has the capacity and domain authority to own the invariant, operate the migration and maintain the system after contract.
For a CTO, CIO or operations leader, the next decision is concrete: approve the population definition and name the invariant owner before the first production batch runs. If those two fields are blank, the backfill is not ready to start, regardless of how reliable the script appears.
FAQ
What are backfill acceptance criteria?
Backfill acceptance criteria define the eligible population, required business invariants, treatment of concurrent writes, retry and resume behaviour, reconciliation evidence, exception ownership and the decision required before the old representation is removed.
Why is exit code 0 insufficient for a database backfill?
Exit code 0 shows that the process reached its configured completion condition. It does not prove that the population definition was complete, that live writes were covered, or that every new value satisfies the business invariant.
What does NOT VALID mean in PostgreSQL?
A NOT VALID constraint can be added without immediately scanning existing rows for compliance. It is a rollout mechanism, not proof that historical rows comply. Historical data needs a later validation event or equivalent evidence.
Who should accept unresolved backfill exceptions?
The owner of the business invariant should accept or reject exceptions, with engineering evidence attached. Engineering can provide queries, logs, constraint state, retry behaviour, the lock plan and the batch log. Operations or product leadership must decide whether the remaining exceptions are acceptable before the old path is removed.
Sources and limits
This article uses the following sources for mechanisms and framing only. It does not rely on client cases, performance figures or certification claims.
- Martin Fowler, Parallel Change, for the expand, migrate and contract frame.
- PostgreSQL documentation, ALTER TABLE, for
NOT VALIDandVALIDATE CONSTRAINTbehaviour. - PostgreSQL documentation, transaction isolation, for statement visibility under isolation levels.
- PostgreSQL documentation, explicit locking, for lock modes, conflicts and transaction scope.
- PostgreSQL documentation, serialization failure handling, for retrying the complete transaction decision.

