Outsourced ProgrammersPowered by StealthAgents

Write a data backfill rehearsal plan for an outsourced release

A rehearsal that freezes the input, runs the backfill on a production-like copy, proves counts and the customer path, and keeps a pause rule for the real run.

Write a data backfill rehearsal plan for an outsourced release
WORK_LANE / FIELD GUIDE / DATAREVIEW QUEUEACCEPTANCEPASSowner review requiredEVIDENCE TRAILTICKET → BRANCH → REVIEW → CLIENT RELEASE
Table of contents
  1. Quick read
  2. Readiness check
  3. Define the rows and the end state
  4. Freeze the input before the rehearsal
  5. Run the backfill against a production-like copy
  6. Compare counts and spot-check the customer path
  7. Set the pause and rollback rule
  8. Schedule the production run with a named owner
  9. Keep the evidence and retire the script
  10. Role brief
  11. Frequently asked questions
  12. Sources

Is the handoff ready?

Use these checks before access expands or the first ticket begins.

Row definition

Ready: Name the keys, tables, end values, and rows that must not change.

Needs work: The lane has no written record for this check.

Frozen input

Ready: Record the snapshot identifier, timestamp, schema, and storage location.

Needs work: The lane has no written record for this check.

Rehearsal evidence

Ready: Measure duration, batch size, locks, customer latency, and query plan.

Needs work: The lane has no written record for this check.

Pause and rollback

Ready: Name the pause owner, thresholds, rollback method, and point of no return.

Needs work: The lane has no written record for this check.

Define the rows and the end state

Write the exact set of rows the backfill must change and the value each row should hold at the end. Include the query or key range, the tables touched, and the rows that must not change. Record the application version that expects the new state. A backfill without a written end state cannot be verified, only hoped over.

Name the source of truth for the new value and the time boundary for inclusion. Decide whether late-arriving rows are handled by the script or by a follow-up job. Record the rows that are intentionally excluded and why. The exclusion list is the part reviewers forget until a customer appears with an old record.

Freeze the input before the rehearsal

Freeze the input by taking a snapshot or by recording the maximum identifier at a stated time. Write the snapshot identifier, the timestamp, and the storage location in the rehearsal note. Use a copy that the team can restore without touching production. A moving input turns every count comparison into an argument.

Record the schema version, the indexes, and the constraints on the target copy. Note the extensions, collation, and time zone that affect comparisons. Confirm that the copy holds enough rows to exercise the largest batch and the slowest path. A small sample can pass while the production size fails on locks or timeouts.

Run the backfill against a production-like copy

Run the real script, not a hand-edited version, against the production-like copy. Record the start time, the batch size, the pause between batches, and the finish time. Capture the query plan and the lock waits for the slowest statement. The rehearsal should measure the pressure the real run will place on the database.

Watch the application while the backfill runs, because a long transaction can block customer writes. Record the read and write latency on the customer path during the run. Note the point where the batch size or pause needed a change. A backfill that only passes on an idle database has not been tested.

Compare counts and spot-check the customer path

Compare the affected row count with the frozen input and with the expected end state. Reconcile the difference row by row, not only as a total, because two errors can cancel out. Record the checksum or a stable hash for the changed values. A count that matches while the values differ is still a failed rehearsal.

Open the customer path on the copy and check the screens that read the new values. Test a search, a report, and one write that depends on the backfilled field. Record the account, the data, and the result of each check. A backfill that satisfies a database query but breaks a screen is not finished.

Set the pause and rollback rule

Write the condition that pauses the real run, such as a lock wait over the agreed threshold or an error rate on the customer path. Name the person who can pause it and the person who can resume. Record the command and the check that confirms the pause worked. A pause rule known only to the script author is not a control.

Decide whether the rollback rewrites the old values or restores the snapshot. Record the time and storage cost of each option and the point of no return. Keep the old values in a side table when an in-place rollback would be slow. The rollback should be chosen before the run, not improvised during it.

Schedule the production run with a named owner

Schedule the run in a window when the customer traffic and the batch jobs leave enough headroom. Record the start time, the expected duration, the on-call engineer, and the client contact. Confirm that the database backup is recent and restorable before the run begins. A production backfill on an unverified backup is an avoidable risk.

Announce the run to support with the customer symptom to watch and the message to use if a screen looks wrong. Give support a reference number and a named escalation contact. Record the confirmation from the client owner. The people who answer customers should hear about the change before the customers do.

Keep the evidence and retire the script

Store the rehearsal record with the query, the timings, the counts, the diffs, and the pause test. Give the client technical owner the record and keep a copy in the delivery workspace. List the manual steps and the person who performed each one. The next engineer should be able to repeat the run without asking the author.

Retire the backfill script when the release no longer needs it, and record the date and the final row count. Keep the script and the record for the retention period in the agreement. Remove any temporary table or elevated permission created for the run. A backfill script left in the repository becomes a tool that someone runs without the plan.

Put the operating agreement in writing.

Scope

List the row keys, tables, end values, and explicit exclusions.

Frozen copy

Record the snapshot, schema version, and the production-like target.

Proof

Reconcile values row by row and open the screens that read the field.

Run control

Name the pause owner, rollback method, and the customer message for support.

Frequently asked questions

Can the team rehearse on a sample of rows?

A sample can catch logic errors, but the final rehearsal must exercise production size, locks, and timing.

What counts as a successful backfill?

Matching values, a working customer path, and recorded timing inside the agreed window.

Who can pause the production run?

A named on-call engineer with a written threshold and a tested pause command.

When should the script be deleted?

After the release no longer needs it and the retention period in the agreement ends.

Sources

  1. PostgreSQL COPY

    Bulk data movement and its transaction behavior.

  2. Flyway database migrations

    Versioned schema change and repeatable migration practice.

  3. Google SRE Workbook

    Change management and canarying production work.

  4. NIST SP 800-34 contingency planning

    Recovery planning and tested restore evidence.

Build a programming lane your technical owner can review.

Bring the stack, backlog, acceptance checks, and release boundaries. We will help scope the role around the work.

Discuss your programming needs