Backfilling three years of data without hitting a rate limit

The daily job pulls yesterday. It makes a handful of calls, finishes in under a minute, and never comes close to a quota. Nobody has ever had to think about its throughput.

A three-year backfill for forty accounts is a different animal. At one call per account per month that is 1,440 calls, and at one call per account per day it is 43,800. Reusing the daily code in a loop is the most common way this goes wrong, because the daily code has no chunking, no checkpoint, and no throttle. It was never asked to have any.

The failure is rarely a hard error. You exhaust a daily operations quota at 60 percent complete, the platform starts returning quota errors, your loop retries them immediately, and now you are generating failed calls that still count against you. The run ends with a partial table and no record of which parts are real.

So treat the backfill as its own program with its own budget. It runs once, or a handful of times, and it is allowed to be slower and more careful than the thing that runs every night.

Every platform publishes what it will tolerate, and the numbers are specific rather than general. The Google Ads API sets out its limits and quotas by access level and operation type, and the tier your developer token sits in changes the ceiling by an order of magnitude.

Analytics is stricter and more subtle, because the cost of a request varies with what you ask for. The GA4 Data API meters tokens per property per hour and per day, with a separate concurrent request limit. A wide report with several dimensions consumes more of the budget than a narrow one, which means your chunk shape and your quota consumption are the same decision.

The practical consequence is that you cannot size a backfill without first knowing three numbers: the per-day cap, the per-minute or per-hour cap, and the concurrency cap. Write them down. If a platform does not publish them, measure them on a small run before you commit to a large one.

Then design to roughly 60 percent of the documented ceiling. The remaining headroom is not waste, it is the budget for your nightly job, your ad hoc queries, and the colleague who runs an export in the middle of your backfill without telling you.

A chunk should be one unit that either fully succeeds or fully fails, and that maps to something the API already treats as a boundary. For ad platform performance data that is usually one account for one month. For event exports it is usually one property for one day.

The temptation is to chunk by row count, so the chunks are even. Resist it. Even chunks make the retry logic harder, because a failed chunk of 50,000 arbitrary rows has no identity, whereas a failed chunk called account 118 for March 2025 does. The identity is what makes checkpointing possible.

Chunk size trades two failures against each other. Chunks that are too large hit response size limits and take too long to retry. Chunks that are too small multiply your call count and burn quota on overhead. One account-month is a good default for performance data because it is almost always one call and almost always under any response cap.

The other virtue of a natural key is that the chunk name is also the partition name in your destination table, which means a rerun of that chunk replaces exactly the rows it wrote last time.

The ordering row is the cheapest decision on the list and the one most often skipped. Loading newest first means that if the backfill dies at 40 percent, you have the most recent fourteen months, which is what the client asked about anyway. Loading oldest first means 40 percent of the way through you have data nobody will look at.

The three retry rows exist because treating all failures identically is what turns a slow backfill into a banned account. A 429 means wait longer. A 500 means try again shortly. A 400 means your request is wrong and will still be wrong in four seconds, so retrying it is pure quota waste.

The last row is a policy, not a technical setting. A backfill that stops itself at 80 percent of the daily quota and resumes the next morning takes four days instead of two and never once puts your nightly reporting at risk. That is a good trade, and it is invisible to everyone except you.

Exponential backoff means waiting longer after each consecutive failure rather than retrying at a fixed interval. Google's own retry guidance describes the pattern as a truncated exponential backoff with added randomness, and the randomness is the part people drop.

Without jitter, every worker that gets throttled at the same moment waits the same interval and retries at the same moment. You have built a synchronised burst that guarantees the next round of throttling, and each round makes it worse. Adding a random fraction of the wait to each worker's delay costs one line and removes the entire failure mode.

There is also a cheaper option than writing any of this. A managed queue with configured dispatch limits does the pacing for you, and Cloud Tasks lets you set the maximum dispatch rate, the concurrency, and the retry policy on the queue itself rather than in your extraction code. Every chunk becomes a task, the queue enforces the rate, and your worker stays simple.

That separation matters more than it sounds. Rate limiting in application code gets tuned, forgotten, and then broken by whoever adds parallelism later. Rate limiting in the queue configuration is a value someone has to deliberately change.

Write one row per chunk to a control table: the chunk key, the status, the row count, the timestamp, and the error text if it failed. Do it after every chunk, not at the end of a batch.

That table is what makes the second attempt cheap. The resume query is simply the list of chunks that are not marked complete, and the run becomes restartable from any point, by anyone, without anyone remembering where it got to.

It is also the completeness report. When someone asks whether the backfill finished, the answer is a count against the expected chunk list, not a person's recollection. When someone asks why February 2024 is missing for one account, the error text is already recorded next to it.

The whole arrangement only works if reloading a chunk produces the same table as loading it once, which is exactly the property described in making a pipeline safe to run twice. Without it, a resumed backfill quietly doubles some rows and nobody finds out until a total looks 12 percent high.

Before building any of the above, check whether the platform will hand you history without a custom extraction. The BigQuery Data Transfer Service supports a backfill for a specified date range on its managed transfers, which means for several major ad platforms the entire problem is a configuration screen and a wait.

The limits are real and worth knowing before you rely on it, which is the point of the Data Transfer Service and what it does not cover. But an afternoon spent checking is cheaper than a fortnight spent building a throttler.

The same test applies to the connector decision generally. A hand-built extraction is a permanent maintenance commitment, and the build against buy arithmetic should happen before the backfill design rather than after it.

Where a backfill is genuinely unavoidable, treat the historical load as a scoped piece of work with its own acceptance test: a row count per month per account, compared against the platform interface, for three sampled accounts. That comparison is what turns a completed script into a trustworthy table.

Getting three years of comparable history into one place is usually the hard half of a marketing data warehouse build, and it was the constraint that shaped a transit authority's move off a legacy BI stack. Once it lands, the recurring work shrinks to the shape described in the monthly client reporting cycle.