Pattern: forty thousand rows, once

Different economics entirely

A backfill has no latency requirement and enormous volume: categorise every support ticket from the last three years, extract fields from eighty thousand stored PDFs, translate a product catalogue.

Two consequences. Batch endpoints become worth using: several providers offer roughly half price for asynchronous batches returned within a day, though the discount and the turnaround vary and change, so check current terms. And because the job runs once, an error is baked into forty thousand rows rather than showing up tomorrow and being fixed.

Three runs, in this order

Twenty, read every one. By hand, all twenty outputs, looking at the input beside each. This takes half an hour and it is where you find that the prompt misreads a date format used by one supplier.

Two hundred, measured. Label them and compute accuracy per field. Then respect the error bar: at n = 200 and around 90 per cent accuracy, the standard error is √(0.9 × 0.1 / 200) ≈ 0.021, so a two-standard-error interval is roughly ±4 points. Your “90 per cent” is somewhere between 86 and 94. If your decision needs finer resolution than that, you need a bigger sample, not a firmer opinion.

The full run, with checkpointing.

Sample randomly, and stratify

The rows you look at first are almost never representative. Tables come back ordered by insertion, so the first two hundred are the oldest: a different supplier mix, a different form layout, sometimes a different currency. Directories list alphabetically, which groups by client. Either way, the head of the list is a biased sample and it is biased in the flattering direction, because early data is usually tidier.

Take a random sample (ORDER BY random() LIMIT 200), and stratify it: some from each source, each year, each document type, each customer size. Then check the rare-but-important stratum separately, because it will be swamped in an overall average. A backfill that is 94 per cent accurate overall and 41 per cent accurate on the one supplier who sends handwritten invoices has a problem that no aggregate will show you.

Checkpoint, always

Write each result as it completes, keyed by input id, and skip ids already present on restart:

done = load_done_ids("out.jsonl")
for row in rows:
    if row.id in done: continue
    result = process(row)
    append_jsonl("out.jsonl", {"id": row.id, **result})

Never accumulate results in memory to write at the end. A four-hour job that dies at hour three with nothing on disk is four hours and the entire spend, gone, and it will die: a rate limit, a network blip, a laptop lid closing.

The same structure gives you resumability, partial results you can inspect while it runs, and safe reruns.

Caps and limits

Estimate the cost before starting: items × tokens per item × price, written down. Then put a hard spend limit inside the loop that aborts when exceeded, because the estimate will be wrong in a direction you did not predict: usually because a subset of documents is ten times longer than the median.

Bound the parallelism too. Ten concurrent requests with exponential backoff and respect for Retry-After will finish sooner than fifty that trigger rate limiting and retries.

Deduplicate the work before you pay for it

Backfills are full of repeats. The same supplier’s template appears four hundred times; the same product description is attached to nine variants; a third of the tickets are the automated notifications from one system. Hash the normalised input and process each distinct value once, then fan the answer out to every row that shares the hash.

On real corpora this routinely removes a fifth to a half of the work, and it costs about six lines. Check the duplicate distribution first (group by the hash and look at the top twenty counts) because that query also tells you whether your corpus is what you think it is. Finding that 12 per cent of your “customer emails” are one system’s delivery receipts changes the project before it starts.

Write somewhere reversible

A backfill is close to irreversible by volume. Fixing the prompt afterwards does not fix forty thousand rows.

So write into a new column or a new table, never over the existing values. Then compare, sample the comparison, and switch over as a separate deliberate act. If you must write in place, keep the undo log (id, field, before, after) so reversal is a script rather than a restore.

And run the whole thing in a dry-run mode first that produces the outputs and writes nothing. The first real backfill anybody runs teaches this lesson; doing it in the right order means learning it from a diff instead of from an apology.