How do you verify a one-off data backfill will finish inside its window before running it in production?

## Why the naive projection is wrong The common approach is: process a sample, divide, multiply. Take a parcel-tracking gateway that must populate a derived field across 12.4 million rows inside a 3-hour-40-minute window. The rehearsal did 1,600,000 rows in 22 minutes, which extrapolates to roughly 2 hours 50 minutes and looks comfortable with 50 minutes to spare. It finished in 3 hours 55 minutes and blew the window. The reason is that the rate is not constant. Per-chunk instrumentation showed it starting at about 1,210 rows per second and drifting to about 740 by the final chunks. Extrapolating from the average of the first few minutes is extrapolating from the fastest part of the run. ## Where the non-linearity comes from Name these in an interview; they are the substance of the answer. - **The target grows as you write into it.** Every maintained secondary structure gets larger and more expensive to update as the run proceeds, so late chunks cost more than early ones. - **Cache falloff.** The first chunks often touch recently written, still-warm data. As the run sweeps into colder regions, more of every chunk comes from storage. - **Uneven regions.** If chunks are cut by an identifier range, the rows are not evenly distributed across ranges. A skewed region processes far more rows per chunk than an average one, and if it sits late in the ordering it lands exactly where you have least slack. - **Growing companion structures.** Journals, change logs and replication streams accumulate during the run and their own maintenance competes with it. - **Live traffic the rehearsal did not have.** A quiet copy has no concurrent readers or writers. Production does, and contention is the effect a rehearsal reproduces worst. - **Feedback limits.** Many runs are deliberately throttled by an observed signal such as replication lag; when that signal degrades, the run slows itself down, which is correct behaviour and ruins a linear projection. ## Instrument the rehearsal properly Record per chunk, not per run: chunk identifier, rows processed, elapsed, rows per second, and any lag or queue signal you throttle on. Then read it as a series: 1. **Plot rate against progress.** A flat line means projection is safe. A downward trend means project from the tail, not the head. 2. **Take the slowest decile of chunks and assume the remainder runs at that rate.** This is a deliberately pessimistic projection and it is the number to plan against. 3. **Check the region distribution.** Confirm the rehearsal actually covered the dense, skewed part of the key space. A rehearsal that only processed the first tenth of the range may have processed the easy tenth. 4. **State the contention allowance explicitly** as a percentage haircut, and say it is an estimate rather than a measurement, because it is. A reasonable planning rule is to require the pessimistic projection to finish inside about 60 to 70 percent of the window. If it does not, the answer is not to hope; it is to change something — narrow the scope of what gets backfilled, split it across several windows, or increase the resources available to the run. ## Turn the window into a runtime assertion The rehearsal gives a prediction; the real run needs a decision rule, and this is what separates a senior answer from a plausible one. Before starting, write down: **at 25 percent of the window, at least 30 percent of rows must be complete; at 50 percent of the window, at least 60 percent.** Those thresholds come from the pessimistic projection with its margin. Then define the branch: if a checkpoint is missed, do you continue and accept overrunning, pause and resume in the next window, or stop and revert? Whichever you choose, **rehearse it**. Practise stopping the run mid-flight on the copy and confirm three things: that progress is durably recorded so a resume does not redo completed work or skip anything, that stopping leaves the data in a state the application tolerates, and that someone other than the author can execute the stop from the written procedure. On an 11-person team the person on call during the maintenance window is frequently not the person who wrote the migration, which is precisely why the abort has to be a documented, practised action rather than institutional knowledge. ## Report it as a decision, not a duration The output of this exercise is not "the backfill takes about three hours". It is: measured slowest sustained rate; pessimistic projection with margin; the assumptions the rehearsal could not test; the checkpoint thresholds; the abort procedure and who runs it. That is a package a reviewer can challenge on specifics, and challenging it is far cheaper than discovering the shortfall halfway through the window.