# Restart a backfill without losing the skipped work Original LockedIn Labs instruction · 50-minute migration and receiving lab · reviewed 27 October 2026. Your migration worker stopped. Its cursor says it scanned two rows. Which writes survived? After restart, the cursor reaches four, but one owner is still missing. You will compare three checkpoint placements against an actual child-process exit, then make a receiving decision from committed rows rather than a progress label. A **backfill** populates an existing field or representation for stored records. A **batch** bounds one piece of that work. A **checkpoint** records where scanning may resume. A **reconciliation queue** retains specific records needing another look. None is a synonym for a migration-complete decision. You need Python 3.11+ with its standard library. All four records are fictional. The runner creates and removes temporary SQLite files, accepts no database path or external host, and performs no production migration. ## 1. Identify the data and progress contracts Four synthetic records start with `owner = Queue A`, revision 1, and an empty `new_owner`. The source `owner` and its revision are authoritative for this exercise. The target stores both the copied owner and the revision it represents. The scan uses increasing primary keys, two rows per batch, with an explicit upper ID of 4. The checkpoint records the highest ID examined, not the number of rows successfully changed. Every row in a batch must either be current already, be updated under its captured revision, or retain a pending reconciliation ID. The fixture does not include deletes, new IDs, parallel migration workers or replicas; a production plan must define those coverage boundaries separately. Before the first batch writes, an actual separate parent connection changes row 2 to Queue B at revision 2. The child captured Queue A at revision 1. A guarded UPDATE checks the captured revision and empty target; it refuses that stale copy. A zero-row update is a branch to investigate, not a success counter. ## 2. Draw the commit boundary around progress The child sends its captured rows over a local process pipe. The parent commits the source change and releases the child. That handshake fixes the interleaving without timing sleeps. The worker then exits abruptly with code 23 at the chosen boundary. The parent joins it before reopening the file and resuming. In `cursor-first`, checkpoint 2 commits separately before the data transaction. The child writes inside that later transaction and exits without committing it. Reopening SQLite rolls those data and pending writes back. The checkpoint is now ahead of the work it describes. In `data-first`, data and pending row 2 commit, but checkpoint 0 remains. Restart reexamines rows 1 and 2. It recognizes row 1 as already current and rereads row 2 at revision 2. There is additional scanning, but no second target write for row 1. In `atomic`, data, pending row 2 and checkpoint 2 commit together. Restart scans rows 3 and 4. It has no reason to revisit row 2 through the cursor, so its durable pending ID matters. Finishing that scan is not finishing reconciliation. The six visual frames are authored explanations of the runner's observed states. They do not launch a worker in the browser. The written ledger below retains the same evidence for reading, printing and receiving review. ## 3. Interrupt and resume three actual workers Download [the resumable backfill runner](/templates/fde-resumable-backfill.py). Keep the original and changed reports in separate local files: ```sh python3 --version python3 fde-resumable-backfill.py --all > backfill-original.json python3 fde-resumable-backfill.py --case atomic --late-change > backfill-late-change.json ``` `worker` reports the owned PID, actual exit code and captured batch. Each later checkpoint is a fresh connection's committed read. `rows` includes source and target revisions; `unmatched_ids` compares both value and revision with the current source. `attempts` contains committed per-row results. Work that rolled back does not invent an applied audit entry. ### The written ledger | Experiment | After abrupt worker exit | After scan resumes to ID 4 | After one bounded reconciliation pass | | --- | --- | --- | --- | | `cursor-first` | Cursor 2; no target rows; pending empty | Rows 3 and 4 copied; rows 1 and 2 still missing | Still missing 1 and 2: the pending queue never retained them | | `data-first` | Cursor 0; row 1 copied; row 2 pending | Revisit 1 without rewriting; fresh row 2 becomes Queue B/revision 2; all four match | No pending work to process | | `atomic` | Cursor 2; row 1 copied; row 2 pending | All scanned; row 2 still absent and pending | Reread row 2 and copy Queue B/revision 2; all four match | Inspect the `data-first` attempts for row 1: one `applied`, then one `already_current`. That second observation does not overwrite the target or create another business operation. Inspect `atomic.after_scan_resume`: cursor 4 coexists with `unmatched_ids = [2]` and pending ID 2. The program preserves that intermediate state before performing reconciliation. Now compare the changed report. `--late-change` is available only with `--case atomic`. After the scan completes but before reconciliation, a real source write changes pending row 2 again, to Queue C/revision 3. The bounded pass rereads its current source and copies revision 3, rather than using the earlier Queue B or the original captured Queue A. Its final result still concerns only these four rows at that observation point. The process interruption is real; power-loss durability, disk corruption, network partitions, row-lock performance and replication lag are not exercised. The local pipe coordinates the demonstration. It is not a production scheduling or distributed-worker design. ## 4. Do not confuse safe skipping with continuous synchronization A conditional write protects this attempt from overwriting a newer source. It does not make every later source change update the new field. Old writers still need a compatibility plan before new readers rely on the target. An empty pending queue also cannot prove coverage when a faulty checkpoint advanced without recording the skipped IDs. For a receiving review, inspect three things independently: 1. Scan position and coverage: which stable key range and source population were covered, and which records remain outside it? 2. Reconciliation: which IDs were skipped, why, what current read was used, and what remains unresolved after a bounded pass? 3. Target meaning: do value and represented revision match the authoritative record, and can supported old/new writers and readers maintain that meaning? The runner refuses to overwrite a nonempty target that differs from its current source. It keeps that record pending for an explicit repair decision. Blindly replacing any nonempty target could erase reviewed work or obscure a broken writer contract. The same caution applies to a row that disappears: define a deletion/tombstone policy rather than counting it as migrated automatically. ## 5. Receive the migration as an enterprise pod Use application owner, migration operator and receiving engineer roles. Combine roles explicitly for smaller groups; label solo work self-review. Each person keeps their own prediction, evidence and assistance. The receiver chooses one checkpoint and asks the operator to locate the exact persisted rows without author takeover. | Role | Receiving responsibility | | --- | --- | | Application owner | Explain the authoritative owner, revision, mixed writer/reader contract and criteria for retiring the old field | | Migration operator | Identify checkpoint/data commits, pending IDs, batch bounds, stop route and the next bounded reconciliation action | | Receiving engineer | Independently reproduce a selected condition, compare value and revision, and return a decision tied to observed gaps | | Platform/database owner | Supply actual lock, lag, load, backup/recovery and runtime permission evidence before an environment change | ```text My role / exact runner and command: Prediction before the worker exits: Observed PID / exit code / checkpoint: Source and target rows personally inspected: Pending IDs / reason / committed attempt references: One changed condition / prediction before that run: Changed observation kept separately: Receiving action actually attempted / assistance: Unresolved coverage or compatibility / next owner and bounded action: ``` A local run does not authorize a production migration or establish independent review, qualification or client placement. Keep the receiving decision scoped to what was actually observed. An operator can say “scan reached ID 4; row 2 awaits reconciliation” without inventing completion. ## 6. Defend the design in an engineering interview Why is checkpoint-first unsafe here? When is rereading a batch acceptable? Why does an atomic checkpoint still need pending-row reconciliation? Why must a represented revision travel with a copied value? Which evidence would permit old consumers to retire? Answer with particular commits and rows. [SQLite's transaction documentation](https://www.sqlite.org/lang_transaction.html) explains the local commit/rollback boundary. [GitLab's batched migration guidance](https://docs.gitlab.com/development/database/batched_background_migrations/) requires small, idempotent jobs and warns against depending on data before the migration is actually finished. Those sources were checked 27 October 2026. This runner is original LockedIn Labs instruction, not an implementation of GitLab's framework or a production migration tool. Continue with [migration compatibility](/training/guides/integration-engineering#5-preserve-old-consumers-during-migration) and the [PostgreSQL lock-queue workshop](/training/guides/postgres-lock-waits). The protected [migration lesson](/app/academy?course=enterprise-integration&lesson=migration-compatibility&view=focus) adds the receiving mechanism within existing training access.