Keeping historical backfill cost under control when you reload everything twice

The invoice arrives in the month after the backfill and it is four times what anyone quoted. Nobody did anything reckless. The team loaded five years of history, found that the 2022 registration codes had been mapped wrongly, reloaded, found duplicate scans in three editions, reloaded again, and then reloaded a fourth time when the finance lead pointed out that the exhibitor counts moved.

Historical backfill cost control is mostly about accepting that sequence in advance. One clean pass over five years is affordable in almost any warehouse. Six passes over five years, each one reading everything, is a different number, and the difference is entirely avoidable.

Why a backfill gets run more than once

A backfill is a discovery exercise that produces a loaded table as a side effect. The discoveries only happen once real data goes through real transformations, so the first pass is where the defect list gets written.

Tobiko Data's SQLMesh documentation, current in 2026, is direct about the economics, noting that when backfilling incremental models across large intervals "there is an extra cost associated with this operation due to additional runtime involved". The same documentation frames plans around reviewing changes before they touch the warehouse, and previewing in a development environment first, which is the cheapest form of cost control there is: finding out that a transformation is wrong before it has read 136 gigabytes.

Budget three to six passes. A team that plans one pass does not run one pass. It runs the others without having planned them, which is how the passes end up unbounded.

The defects that trigger a corrective pass are predictable enough to plan against. A code mapped to the wrong conformed value, which surfaces as a segment that is empty for three years. A column silently dropped because the old export named it differently. Duplicate rows that only appear once two overlapping vendor files are loaded together. A timezone conversion applied during load instead of at the source. Each is cheap to fix and expensive to fix badly, because a partial reload that misses two editions leaves the portfolio inconsistent in a way nobody notices until a roll-up disagrees with the sum of its parts.

What does a dry run actually tell you?

Google Cloud's BigQuery documentation on estimating and controlling costs, current in 2026, describes the mechanism plainly: submit the job as a dry run and the service returns the bytes it would process, without executing the query. The command line tool takes a flag and the API takes a dryRun property set to true.

bq query --dry_run --use_legacy_sql=false '
SELECT COUNT(*) FROM warehouse.fact_badge_scan
WHERE edition_key BETWEEN 33 AND 40'

The estimate is the useful part, and the comparison is what makes it a control. Before every pass, write down the bytes you expect to scan, then run the dry run and compare. If you intended to reload eight editions and the estimate comes back at the size of the whole table, the filter is not doing what you think, and you have learned that for nothing.

That check catches the single most expensive class of backfill mistake, which is a predicate that looks like it restricts the scan and does not. Wrapping the partition column in a function, comparing it to a subquery, or filtering on a derived date column will each read the whole table while looking perfectly reasonable in the SQL. Writing filters that genuinely prune is a subject in itself, handled in partition pruning to cut scan cost.

The 40 partition budget, worked through

Put numbers on the portfolio. Eight shows, five years, 40 editions. Say the raw exports for one edition come to 3.4 gigabytes, which makes the full raw layer 136 gigabytes.

A complete backfill pass reads all of it: 136 gigabytes. That is the unavoidable one.

Now the corrective passes. The 2022 code mapping defect affected the editions from one platform, which is eight of the 40. Reloading only those eight reads 27.2 gigabytes, which is 20 per cent of the full pass and an 80 per cent cut against reloading everything. Run six corrective passes at that average size and you read 163.2 gigabytes in total. Run the same six passes as full reloads and you read 816 gigabytes.

Add the first pass to each and the project reads 299.2 gigabytes against 952 gigabytes. Five times the volume, for exactly the same loaded table, with the difference sitting entirely in whether each corrective pass named the editions it was fixing.

The mechanism is documented and unglamorous. Google Cloud's 2026 documentation on querying partitioned tables defines it: "If a query uses a qualifying filter on the value of the partitioning column, BigQuery can scan the partitions that match the filter and skip the remaining partitions. This process is called partition pruning." And on the effect: "The pruned partitions are not included when calculating the bytes scanned by the query. In general, partition pruning helps reduce query cost."

This is also the argument for partitioning the raw layer by edition rather than by load date. An edition is the unit you correct, so it should be the unit you can address. Partition by the date the file happened to land and a corrective pass has no way to say the thing it means.

Guardrails that fail the query instead of the bill

Estimation catches mistakes you look at. A ceiling catches the ones that run at 3am.

The BigQuery documentation describes limiting the bytes billed for a query, with the behaviour that when the estimate exceeds the limit the query fails without incurring a charge. Set that on every backfill job at something a little above what you expect. A corrective pass over eight editions that expects 27.2 gigabytes can carry a 40 gigabyte ceiling, and the pass that quietly stopped pruning then fails with an error instead of reading 136.

Three more guardrails are worth the hour they take to set up.

• A row count assertion before the write. If the pass is meant to touch eight editions, count the distinct editions in the staged output and fail if the answer is not eight.
• A hard cap on concurrent backfill jobs. Parallelism is how a controlled pass becomes an uncontrolled one, because eight jobs each scanning what they were told to scan is still eight times the bytes at once.
• A dated log of every pass. Date, editions touched, bytes estimated, bytes actually processed. Four columns, and after three passes it is the only honest record of what the project is costing.

The costs that are not bytes

Bytes scanned is the number on the invoice, which is why it gets all the attention, and it is rarely the largest cost of a backfill.

The slot time or cluster time spent on transformation usually exceeds the scan cost on a job that does real work per row. Deduplication with window functions over 1.5 million scan rows, string normalisation on company names, and fuzzy matching against a dimension are all compute rather than input, and a dry run byte estimate says nothing about any of them.

Then there is storage of the intermediate results, which is small, and the cost of holding the raw layer, which is smaller than anyone expects and worth paying anyway.

The largest cost is a person. A five year backfill with a discovery pass, three corrective passes and a reconciliation against published figures is weeks of somebody's attention, and that somebody is normally the one person who understands the registration exports. Comparing byte cost against staff cost is the comparison that usually justifies buying more compute to shorten the calendar, and how you attribute any of it back to an individual show is a separate exercise in warehouse cost per edition.

How do you know the estimate was right?

Record both numbers, every time. Estimated bytes from the dry run, actual bytes processed from the job statistics.

They diverge for reasons worth understanding. Clustering can reduce the actual below the estimate, because the estimate is computed before the engine knows which blocks it can skip. Cached results can drop it to zero, which flatters a rerun and misleads anyone reading the log. A change in the underlying table between the dry run and the execution moves it in either direction.

After five or six passes you have a small table of estimate against actual, and the ratio between them is the thing you use to plan the next backfill. That table is worth more than any published benchmark, because it was measured on your data with your transformations, and it took no extra work to collect beyond writing two numbers down.

Where this stops

Cost control on a backfill is bounded by a decision you make earlier, which is how much history to load at all. Loading five years efficiently still costs more than loading three years efficiently, and the order and scope of the backfill is where most of the money is really decided.

The other limit is that pruning only helps a pass that genuinely needs a subset. A defect in a shared transformation, a change to the company dimension, a correction to the date logic: any of those affects all 40 editions and there is no clever predicate that avoids reading them. Those passes are full passes, and the honest response is to schedule them deliberately, run them once, and verify the output thoroughly enough that they do not need repeating.

Start by adding the dry run to your loader as a step rather than a habit. Before any pass that touches history, have the job print the estimated bytes and the editions in scope, and refuse to continue if the operator has not seen both. It costs nothing to run, it fails before it spends, and it will pay for itself the first time somebody in your data platform team reloads five years by accident.