{"course":{"slug":"agents-and-automation","title":"Agents and Automation","url":"https://addaly.com/learn/agents-and-automation","level":"intermediate","track":"build"},"lesson":{"slug":"the-batch-backfill","title":"Pattern: forty thousand rows, once","url":"https://addaly.com/learn/agents-and-automation/the-batch-backfill","position":52,"of":72,"minutes":9,"answer":"Sample randomly and stratify before a backfill, because the first two hundred rows are almost always the oldest or the tidiest, and they will tell you the job is safer than it is.","keyIdea":"Sample randomly and stratify before a backfill, because the first two hundred rows are almost always the oldest or the tidiest, and they will tell you the job is safer than it is."},"body":{"format":"markdown","text":"## Different economics entirely\n\nA 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.\n\nTwo 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.\n\n## Three runs, in this order\n\n**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.\n\n**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.\n\n**The full run**, with checkpointing.\n\n## Sample randomly, and stratify\n\nThe 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.\n\nTake 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.\n\n## Checkpoint, always\n\nWrite each result as it completes, keyed by input id, and skip ids already present on restart:\n\n```python\ndone = load_done_ids(\"out.jsonl\")\nfor row in rows:\n    if row.id in done: continue\n    result = process(row)\n    append_jsonl(\"out.jsonl\", {\"id\": row.id, **result})\n```\n\nNever 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.\n\nThe same structure gives you resumability, partial results you can inspect while it runs, and safe reruns.\n\n## Caps and limits\n\nEstimate 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.\n\nBound 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.\n\n## Deduplicate the work before you pay for it\n\nBackfills 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.\n\nOn 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.\n\n## Write somewhere reversible\n\nA backfill is close to irreversible by volume. Fixing the prompt afterwards does not fix forty thousand rows.\n\nSo 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.\n\nAnd 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."},"check":{"question":"Before backfilling 40,000 stored invoices, a team validates the extractor on the first 200 rows returned by their query and measures 96 per cent accuracy. The full run is far worse. What went wrong?","options":[{"text":"The sample was too small; 200 rows cannot support a claim about 40,000.","correct":false,"tempting":"Sample size does set an error bar of a few points, but it cannot explain a large gap: the bias, not the width, is the issue here."},{"text":"Unsorted queries return rows in insertion order, so the first 200 share a period and a supplier mix and are systematically tidier than the whole set.","correct":true},{"text":"The model’s accuracy degrades over a long run as the context accumulates across items.","correct":false,"tempting":"Each item is an independent call with its own context, so there is no accumulation between them to degrade anything."},{"text":"Rate limiting during the full run caused retries that produced lower-quality outputs.","correct":false,"tempting":"Retries affect throughput and cost; a retried request is not answered less carefully than a first attempt."}]},"prev":{"slug":"the-monitor","title":"Pattern: watch something, say when it changes","url":"https://addaly.com/learn/agents-and-automation/the-monitor"},"next":{"slug":"the-coding-agent","title":"Pattern: the agent that writes and runs code","url":"https://addaly.com/learn/agents-and-automation/the-coding-agent"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Pattern: forty thousand rows, once', in Agents and Automation. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/agents-and-automation/the-batch-backfill (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Pattern: forty thousand rows, once","courseTitle":"Agents and Automation","module":{"index":6,"slug":"automation-patterns","title":"Eight patterns that keep working"},"answer":null,"keyIdea":"Sample randomly and stratify before a backfill, because the first two hundred rows are almost always the oldest or the tidiest, and they will tell you the job is safer than it is.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"different-economics-entirely","runs":[{"text":"Different economics entirely"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"three-runs-in-this-order","runs":[{"text":"Three runs, in this order"}]},{"t":"p","runs":[{"text":"Twenty, read every one.","b":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"Two hundred, measured.","b":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"The full run","b":true},{"text":", with checkpointing."}]},{"t":"h2","id":"sample-randomly-and-stratify","runs":[{"text":"Sample randomly, and stratify"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"Take a random sample ("},{"text":"ORDER BY random() LIMIT 200","code":true},{"text":"), 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."}]},{"t":"h2","id":"checkpoint-always","runs":[{"text":"Checkpoint, always"}]},{"t":"p","runs":[{"text":"Write each result as it completes, keyed by input id, and skip ids already present on restart:"}]},{"t":"code","lang":"python","label":"Python","text":"done = load_done_ids(\"out.jsonl\")\nfor row in rows:\n    if row.id in done: continue\n    result = process(row)\n    append_jsonl(\"out.jsonl\", {\"id\": row.id, **result})"},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"The same structure gives you resumability, partial results you can inspect while it runs, and safe reruns."}]},{"t":"h2","id":"caps-and-limits","runs":[{"text":"Caps and limits"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"Bound the parallelism too. Ten concurrent requests with exponential backoff and respect for "},{"text":"Retry-After","code":true},{"text":" will finish sooner than fifty that trigger rate limiting and retries."}]},{"t":"h2","id":"deduplicate-the-work-before-you-pay-for-it","runs":[{"text":"Deduplicate the work before you pay for it"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"write-somewhere-reversible","runs":[{"text":"Write somewhere reversible"}]},{"t":"p","runs":[{"text":"A backfill is close to irreversible by volume. Fixing the prompt afterwards does not fix forty thousand rows."}]},{"t":"p","runs":[{"text":"So write into a "},{"text":"new column or a new table","b":true},{"text":", 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."}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"Before backfilling 40,000 stored invoices, a team validates the extractor on the first 200 rows returned by their query and measures 96 per cent accuracy. The full run is far worse. What went wrong?"}],"options":[{"text":[{"text":"The sample was too small; 200 rows cannot support a claim about 40,000."}],"correct":false,"tempting":[{"text":"Sample size does set an error bar of a few points, but it cannot explain a large gap: the bias, not the width, is the issue here."}]},{"text":[{"text":"Unsorted queries return rows in insertion order, so the first 200 share a period and a supplier mix and are systematically tidier than the whole set."}],"correct":true,"tempting":null},{"text":[{"text":"The model’s accuracy degrades over a long run as the context accumulates across items."}],"correct":false,"tempting":[{"text":"Each item is an independent call with its own context, so there is no accumulation between them to degrade anything."}]},{"text":[{"text":"Rate limiting during the full run caused retries that produced lower-quality outputs."}],"correct":false,"tempting":[{"text":"Retries affect throughput and cost; a retried request is not answered less carefully than a first attempt."}]}],"order":[0,1,3,2],"why":null},"prev":{"slug":"the-monitor","title":"Pattern: watch something, say when it changes","minutes":9},"next":{"slug":"the-coding-agent","title":"Pattern: the agent that writes and runs code","minutes":10}}}