Spatial Epi Public Health GIS

Backfilling Historical Case Geographies Safely

This guide, part of Orchestrating Spatial Surveillance Pipelines, covers the operation every surveillance system eventually needs and frequently botches: reprocessing years of case geography after a method improves.

Problem Context & Constraints

A backfill is triggered by something good — a better geocoder, a corrected boundary crosswalk, a bug fix in the areal interpolation. The work itself is mechanical. The risks are organisational and statistical.

A published figure changes. A partner agency cited the 2021 rate for a county in a report. Re-running 2021 with better geocoding moves it. That may be correct and it must not happen silently, because the partner’s report now disagrees with the source and nobody can explain why.

A trend becomes an artefact. If the backfill covers 2018–2023 and the nightly pipeline continues with the old method, the series contains a method change at an arbitrary point. Any trend analysis across it is measuring the method. The backfill must either cover the whole series or not be applied to a series at all.

A rate-limited service is overwhelmed. Five years of addresses against a geocoder with a daily quota is a multi-week job. Running it as one batch either exhausts the quota and fails, or starves the nightly run of capacity.

Concurrency with the nightly run. A backfill executing while the nightly job runs must not contend for the same partitions or the same service credentials.

A Partial Backfill Creates a Trend That Is Not There Two versions of the same case rate series across six years. In the first, a backfill improved geocoding for the earlier years only, so the match rate and therefore the mapped case count step upward at the boundary between backfilled and non-backfilled years, producing an apparent increase that is entirely a method change. In the second, the whole series including the current period is processed by the same method, so the series is comparable end to end and any change in it reflects the data rather than the pipeline. Half a series on a new method is worse than none of it Backfill stopped at 2021 method changes here a step that looks like an increase Whole series reprocessed comparable end to end Rule: a backfill either reaches the present, or the series is not used for trend and the boundary date is recorded in the metadata of every product built on it The step is largest where the method improved most, which is usually the rural end

Prerequisites

  • python 3.11
  • pandas 2.2.2, numpy 1.26.4
  • The run context, partition layout and idempotency helpers from the sibling guides
  • A published-artifact registry recording what was released, when and to whom
  • A geocode ledger so already-matched addresses are not re-sent
  • A rate budget for any external service, expressed per day

Step-by-Step Solution

Plan the backfill as a sequence of date partitions with a rate budget, run it into its own partitions, compare against what was published, and cut over only when the series is complete.

# Planning and executing a bounded, comparable backfill.
# Pinned: pandas==2.2.2, numpy==1.26.4
import logging

import numpy as np
import pandas as pd

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("pipeline.backfill")


def plan_backfill(dates: pd.DatetimeIndex, new_addresses_per_date: pd.Series,
                  daily_budget: int, reserve_for_nightly: int = 0) -> pd.DataFrame:
    """Group dates into daily batches that fit inside the external-service budget."""
    usable = max(daily_budget - reserve_for_nightly, 1)
    plan, batch, used, day = [], [], 0, 0
    for d in sorted(dates):
        need = int(new_addresses_per_date.get(d, 0))
        if batch and used + need > usable:
            plan.append({"batch_day": day, "dates": list(batch), "calls": used})
            batch, used, day = [], 0, day + 1
        batch.append(d)
        used += need
    if batch:
        plan.append({"batch_day": day, "dates": list(batch), "calls": used})
    df = pd.DataFrame(plan)
    log.info("backfill plan: %d dates over %d days, %d calls total, budget %d/day "
             "(%d reserved for nightly)",
             len(dates), len(df), int(df["calls"].sum()), daily_budget, reserve_for_nightly)
    return df


def compare_against_published(new: pd.DataFrame, published: pd.DataFrame,
                              key: list[str], value: str,
                              material_pct: float = 5.0) -> pd.DataFrame:
    """Which published figures would this backfill change, and by how much?"""
    m = published.merge(new, on=key, how="outer", suffixes=("_published", "_new"))
    m["abs_change"] = m[f"{value}_new"].fillna(0) - m[f"{value}_published"].fillna(0)
    denom = m[f"{value}_published"].replace(0, np.nan)
    m["pct_change"] = 100.0 * m["abs_change"] / denom
    material = m[m["pct_change"].abs() >= material_pct]
    log.info("backfill would change %d of %d published figures; %d by %.0f%% or more",
             int((m["abs_change"] != 0).sum()), len(m), len(material), material_pct)
    if len(material):
        log.warning("material changes to already-published figures — these require "
                    "a correction notice, not a silent overwrite")
    return m.sort_values("pct_change", key=abs, ascending=False)


def cutover_ready(backfilled_dates: set, series_start: pd.Timestamp,
                  series_end: pd.Timestamp) -> bool:
    """Refuse to publish a series that mixes methods."""
    needed = set(pd.date_range(series_start, series_end, freq="D").date)
    have = {d.date() if hasattr(d, "date") else d for d in backfilled_dates}
    missing = sorted(needed - have)
    if missing:
        log.error("series not cutover-ready: %d dates still on the old method "
                  "(earliest %s, latest %s)", len(missing), missing[0], missing[-1])
        return False
    log.info("series cutover-ready: %d dates all processed by the new method", len(needed))
    return True

The comparison against published figures is the step that turns a technical operation into a governed one. It produces a list of exactly which released numbers would move and by how much, before anything is republished, which is what allows the agency to decide between issuing a correction, annotating the change, or declining to apply the backfill to already-published products.

The cutover check enforces the series rule mechanically. A backfill that has reached 2023 but not the current month cannot be published as a series, and having the pipeline refuse rather than relying on someone to remember is the difference between a policy and an intention.

Backfill and Nightly Run Side by Side Two parallel tracks over time. The nightly run continues writing current dates into its partitions using the existing method, undisturbed. The backfill works backwards through historical dates, writing into the same date partitions but under its own run identifiers, consuming a reserved share of the external service budget. Neither touches the other's partitions. When the backfill reaches the present, a cutover step switches the published series to the new method for every date at once, and the old outputs are retained rather than deleted. The two never contend, because each owns its own run partitions Nightly run continues on the current method, one new date each night Backfill done in progress still on the old method 2018 2022 today Cutover only when the dashed region is empty

Validation & Edge Cases

Re-geocoding addresses already in the ledger. Most historical addresses are already matched. Filtering to genuinely new work usually reduces a multi-week job to a few days, and the call plan should be built from the cache-miss count rather than the record count.

Boundary vintage for historical dates. Reprocessing 2019 with 2024 tract boundaries produces counts in areas that did not exist then. Use the boundary vintage contemporaneous with the case date, or state clearly that the whole series has been restated on current geography — both are defensible, silently mixing them is not.

Addresses that no longer exist. A 2014 address may have been demolished, renumbered or absorbed into a redevelopment. A modern geocoder will either fail it or match it to something plausible and wrong, and the second outcome is worse because it looks like an improvement. Compare the backfilled match type against the original per record, and treat a record that moved from a street-level match to a rooftop match more than a few hundred metres away as a candidate error rather than a success.

A backfill that reveals a historical bug. Reprocessing frequently surfaces that the original run was wrong in some period — a mis-set parameter, a bad boundary file used for two months. That is a finding in its own right and it belongs in the record even though it is uncomfortable, because the affected products may need correcting independently of whether the backfill itself is adopted.

Partial adoption across products. A restated series may be adopted for internal analysis and declined for a public dashboard whose figures are cited externally. That is a legitimate decision and it creates two versions of the truth, so each product must state which method it uses and the registry must record the mapping, or the discrepancy will be discovered by a user rather than explained by the agency.

Denominators that also change. A backfill that improves case geography without restating denominators produces rates whose numerator and denominator come from different methods. Restate both or neither.

Backfilled data entering a live model. A prospective scan’s baseline period may lie inside the backfill range, so the baseline shifts mid-backfill and the scan’s sensitivity moves with it. Freeze the scan’s baseline during a backfill and recompute once at cutover.

Storage growth. Retaining old outputs alongside new ones is correct and doubles the archive for the backfilled range. Budget for it, and apply the retention policy to the superseded outputs after a stated period rather than immediately.

INFO pipeline.backfill backfill plan: 2192 dates over 11 days, 214880 calls total, budget 25000/day (5000 reserved for nightly)
WARNING pipeline.backfill material changes to already-published figures — these require a correction notice, not a silent overwrite
ERROR pipeline.backfill series not cutover-ready: 214 dates still on the old method (earliest 2026-02-16, latest 2026-09-17)

The published-figure comparison produces a list, and the useful way to read it is by the size of each change:

Which Published Figures Would Move, and by How Much A distribution of the percentage change a proposed backfill would make to each already-published county figure. Most changes are within a per cent or two and fall below the materiality threshold, so they can be annotated rather than formally corrected. A small tail of figures changes by more than five per cent, concentrated in rural counties where the improved geocoder recovered previously unassignable addresses. Those figures require a correction notice before the restated series is published. Most figures barely move; the tail needs a correction notice 5% materiality threshold annotate correction notice required mostly rural counties <1% 5–10% >20% absolute change to already-published county figures under the backfill

Compliance Notes

Record the reason for the backfill, the method change it applies, the date range, the boundary and denominator vintages used, the rate budget and reserve, the comparison against published figures with the material-change list, and the cutover decision with its date and approver.

The comparison report is the artifact that matters for governance. An agency that republishes a restated series without it cannot answer a partner asking why a cited figure moved, and the answer “we improved the geocoder” is only credible when accompanied by the list of what changed and by how much. Where a material change affects a figure used in a funding formula or a legal filing, the correction process for that product governs, and the backfill waits for it rather than the other way around.