A backfill that resumes from the rows it already wrote
Shipped
v0.3.0 added a multi-year history backfill to a local-first budgeting tool, plus a purchases report built on what it collects. A rolling sync explains the last few weeks; this walks the whole range the data covers, which is a long job against a source that will throttle you.
Long jobs get interrupted. The interesting part of that release is not the fetching, it is that the backfill has no checkpoint file, no cursor, and no resume flag to get wrong. It works out where to start by querying rows it already wrote.
The two tables this needs
The whole mechanism rests on one table you probably already have and one you may not. charges is the data already in your database, whatever the import is meant to enrich. runs is the ledger of completed work, and it is the piece doing the resuming.
# backfill.py
"""A backfill whose resume state is the rows it already wrote."""
from __future__ import annotations
import sqlite3
import time
SCHEMA = """
CREATE TABLE IF NOT EXISTS runs (
run_id INTEGER PRIMARY KEY,
scope TEXT NOT NULL,
status TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS orders (
order_id TEXT PRIMARY KEY,
year INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS charges (
charge_id INTEGER PRIMARY KEY,
posted_date TEXT NOT NULL
);
"""
def connect(path: str = ":memory:") -> sqlite3.Connection:
conn = sqlite3.connect(path)
conn.row_factory = sqlite3.Row
conn.executescript(SCHEMA)
return conn
class AuthError(RuntimeError):
"""The credential is bad or the source is challenging us."""
runs.scope is a free-text label for one unit of work. Using a string rather than an integer column is what lets the same table describe several kinds of run later without a migration.
Derive the scope from data you already have
Before fetching anything, decide how far back to go. The tempting version is a constant, or a --from-year the user has to know. Both are guesses, and both fail in the same direction: too far back wastes requests on empty history, too recent silently truncates it.
If you already hold data that implies the range, read it:
def year_range(conn: sqlite3.Connection) -> tuple[int, int] | None:
"""First and last year worth fetching, derived from data we already have.
The scope is the period there is something to reconcile. Guessing a start
year either wastes requests on empty history or silently truncates it.
"""
row = conn.execute(
"SELECT MIN(substr(posted_date,1,4)) AS lo, "
" MAX(substr(posted_date,1,4)) AS hi FROM charges").fetchone()
if not row or not row["lo"]:
return None
return int(row["lo"]), int(row["hi"])
Here the bank charges are already imported, and they are what the fetched orders will eventually be matched against. Fetching an order from a year with no charges produces something nothing can reconcile, so the charges define the useful window exactly.
Make resume a query, not a checkpoint file
The usual approach to a resumable job is a cursor: write last_completed=2023 somewhere, read it on startup. It works until the file and the database disagree, which happens the first time a run dies between writing rows and updating the cursor. Now you have two sources of truth and no way to tell which one is lying.
Skip the second source of truth. If you record each unit of work as a row when it finishes, “what have I already done” is a query:
def completed_years(conn: sqlite3.Connection) -> set[int]:
"""Years already fetched, read back out of the run ledger.
This is the whole resume mechanism. A finished year is a row that exists,
so there is no checkpoint file to write, corrupt, or get out of sync with
the data it claims to describe.
"""
done: set[int] = set()
for r in conn.execute(
"SELECT scope FROM runs WHERE status='success' AND scope LIKE 'year=%'"):
try:
done.add(int(str(r["scope"]).split("=", 1)[1]))
except (ValueError, IndexError):
continue
return done
def plan(conn: sqlite3.Connection, resume: bool = True) -> dict:
"""What a backfill would do, without doing it."""
rng = year_range(conn)
if rng is None:
return {"years": [], "skipped": [], "reason": "no charges in the ledger"}
lo, hi = rng
done = completed_years(conn) if resume else set()
return {
"years": [y for y in range(lo, hi + 1) if y not in done],
"skipped": sorted(y for y in range(lo, hi + 1) if y in done),
"reason": None,
}
Two properties fall out of this that are worth naming.
The run row is written in the same transaction as the data it describes, so a crash cannot leave “done” recorded for work that did not land. This is the idempotent consumer pattern applied to a batch job: record what you processed, then use that record to discard the duplicate on the next pass.
And plan() is separately callable, which gives you a dry run for free. A long job that can tell you what it intends to do before it starts is much easier to trust.
Retry the transient, stop on the terminal
Not every failure deserves a retry, and treating them alike is how a soft rate-limit becomes a hard block.
The Amazon Builders’ Library puts it directly: retries amplify load on a dependency, and if that dependency is already overloaded, retrying makes it worse rather than better. Their rule is to retry only when the dependency looks healthy. The Google SRE book reaches the same place from the other side, recommending a per-request retry budget of about three attempts before letting the failure bubble up.
An expired credential is not a transient failure. Neither is a bot challenge. Both mean the next attempt fails the same way, only now you have also told the server you are a script:
#: Short and few on purpose. A long scrape that keeps hammering a throttling
#: server gets blocked harder, and a human can always resume.
BACKOFF_SECONDS = (0.1, 0.2, 0.4)
def with_retry(fn, *, what: str, on_progress=None):
"""Retry transient failures only.
An auth error or a bot challenge is re-raised immediately and never
retried: the credential is not going to become valid on the third attempt,
and re-hitting a challenge is how a soft block becomes a hard one.
"""
last: Exception | None = None
for i, wait in enumerate((0, *BACKOFF_SECONDS)):
if wait:
if on_progress:
on_progress(f" retrying {what} in {wait}s "
f"({i}/{len(BACKOFF_SECONDS)}), last error: {last}")
time.sleep(wait)
try:
return fn()
except AuthError:
raise
except Exception as e: # noqa: BLE001
if "challenge" in str(e).lower():
raise
last = e
raise RuntimeError(f"{what} failed after {len(BACKOFF_SECONDS)} retries: {last}")
Those delays are deliberately tiny so the example runs fast; use seconds in a real job. Note also what this schedule does not do: it has no randomness. For a single-client backfill that is fine, because there is no herd to synchronize. The moment many clients retry against one service, add jitter, or they will all come back at the same instant and rebuild the spike you were backing off from.
The payoff for classifying failures shows up in the driver: a terminal error breaks the loop so the run stops cleanly and resumes later, while a unit that merely looks wrong is skipped without being marked done.
def run_backfill(conn, fetch_year, *, resume=True, on_progress=print) -> dict:
"""Fetch year by year; each year is its own run row and its own gate."""
p = plan(conn, resume)
if p["reason"]:
return {"years": [], "skipped": [], "orders": 0, "reason": p["reason"]}
if p["skipped"]:
on_progress(f" skipping {len(p['skipped'])} completed year(s): "
f"{', '.join(map(str, p['skipped']))}")
done, total = [], 0
for year in p["years"]:
on_progress(f" {year} fetching")
try:
orders = with_retry(lambda y=year: fetch_year(y),
what=f"orders {year}", on_progress=on_progress)
except AuthError as e:
on_progress(f" ! {e} on {year}, stopping. Re-run to resume here.")
break
# A year that returns nothing while the ledger shows charges IN THAT
# YEAR is a broken parse, not a quiet year. Skip it without marking it
# done, so a later run tries again.
expect = conn.execute(
"SELECT COUNT(*) n FROM charges WHERE posted_date LIKE ?",
(f"{year}-%",)).fetchone()["n"]
if expect and not orders:
on_progress(f" ! {year} returned nothing but the ledger has "
f"{expect} charge(s); not marking it done")
continue
with conn:
conn.executemany(
"INSERT OR IGNORE INTO orders (order_id, year) VALUES (?,?)",
[(o, year) for o in orders])
conn.execute("INSERT INTO runs (scope, status) VALUES (?, 'success')",
(f"year={year}",))
total += len(orders)
done.append(year)
on_progress(f" stored {len(orders)} orders")
return {"years": done, "skipped": p["skipped"], "orders": total, "reason": None}
That emptiness check is worth keeping. A scraped source that quietly returns zero results is indistinguishable from a genuinely empty period unless you compare against something you already trust. Here the charges are that something, scoped to the same year, so a parse break surfaces as a loud skip instead of a year that looks finished forever.
Run it, break it, run it again
The test that matters is not that it completes. It is that killing it costs you nothing.
# demo.py
from backfill import AuthError, connect, run_backfill
conn = connect("demo.db")
with conn:
conn.executemany("INSERT OR IGNORE INTO charges (charge_id, posted_date) VALUES (?,?)",
[(i, f"{y}-06-0{i%9+1}") for i, y in
enumerate([2022, 2022, 2023, 2023, 2024, 2024, 2025], start=1)])
def fetch_year(year: int) -> list[str]:
if year == 2024:
raise AuthError("session expired")
return [f"{year}-order-{n}" for n in range(1, 4)]
print("=== first run ===")
print(run_backfill(conn, fetch_year), "\n")
def fetch_year_ok(year: int) -> list[str]:
return [f"{year}-order-{n}" for n in range(1, 4)]
print("=== second run, session restored ===")
print(run_backfill(conn, fetch_year_ok))
=== first run ===
2022 fetching
stored 3 orders
2023 fetching
stored 3 orders
2024 fetching
! session expired on 2024, stopping. Re-run to resume here.
{'years': [2022, 2023], 'skipped': [], 'orders': 6, 'reason': None}
=== second run, session restored ===
skipping 2 completed year(s): 2022, 2023
2024 fetching
stored 3 orders
2025 fetching
stored 3 orders
{'years': [2024, 2025], 'skipped': [2022, 2023], 'orders': 6, 'reason': None}
The second run reads skipped: [2022, 2023] out of the same database that holds the orders, and starts at the year that failed. Nothing was refetched and no state file was involved.
The other two behaviors are worth seeing on their own. Give the fetcher a transient error for one year and an empty result for another:
2023 fetching
retrying orders 2023 in 0.1s (1/3), last error: connection reset
retrying orders 2023 in 0.2s (2/3), last error: connection reset
stored 1 orders
2024 fetching
! 2024 returned nothing but the ledger has 1 charge(s); not marking it done
2023 recovered on the third attempt and was marked done. 2024 came back empty against a year the ledger says had activity, so it was skipped and left for the next run to retry.
Gotchas
A checkpoint file will disagree with your data eventually. If the cursor is written outside the transaction that stores the rows, a crash in between leaves one of them wrong, and you cannot tell which. Symptom: a resumed run that skips a unit it never actually finished, leaving a permanent hole nobody notices. Escape: write the completion row in the same transaction as the data, and treat the row’s existence as the only resume signal.
Retrying an auth failure turns a soft block into a hard one. A bad session and a bot challenge both fail identically on attempt two and three, and each attempt is another data point that you are automated. Symptom: a job that worked yesterday now fails at the first request, headless, on a source that was merely rate-limiting you before. Escape: classify errors before retrying, re-raise terminal ones immediately, and stop the run rather than continuing to the next unit.
An empty result is not evidence of an empty period. A scraper whose selector broke returns zero rows and looks exactly like a quiet year, so a naive run marks it complete and never comes back. Symptom: a backfill that reports success while a chunk of history is permanently missing. Escape: compare each unit against data you already trust, scoped to that same unit, and refuse to mark it done when the two disagree.
Fixed backoff is fine alone and wrong in a crowd. A constant schedule is easy to reason about for a single-client job, but if the same code ever runs concurrently, every client retries at the same moment. Symptom: a throttled service that recovers, then immediately gets hit by a synchronized wave of retries. Escape: add randomized jitter to the delay before the job ever runs in more than one place.
Sources
- Timeouts, retries, and backoff with jitter — Amazon Builders’ Library on retry amplification and retrying only a healthy dependency.
- Addressing cascading failures — the Google SRE book on retry budgets and synchronized retry storms.
- Exponential backoff and jitter — why randomness is what actually spreads retries out in time.
- Idempotent consumer — recording processed work so a repeated pass discards duplicates.
Changelog
- Amazon history backfill + purchases report (#5) (25c530d)