Backfills and building your own connector
Time a backfill under a rate limit, make it resumable with a stored cursor, survive schema drift, and price building a connector against buying one.
On this page
The first sync is the largest job a connector ever runs, and the one most often run once, by hand, at the wrong time. Before the agency's overdue-reminder automation can say which clients pay late, it needs two years of the accounting system's history; after that it needs only the changes. The backfill is where the rate limit, the cursor and the data's oddities all arrive on the same night.
The backfill, timed
The accounting system holds 12,000 historical records, returns 50 to a page, and allows 30 requests a minute.
list pages 12,000 ÷ 50 = 240 requests
at 30 a minute 240 ÷ 30 = 8 minutes
Eight minutes, if the list endpoint returns everything you need. It rarely does. Lists return summaries, and the line items and payments need one detail call per record:
detail calls 12,000 × 1 = 12,000 requests
total 240 + 12,000 = 12,240 requests
at 30 a minute 12,240 ÷ 30 = 408 minutes ≈ 6.8 hours
The limit is counted per account, so the backfill shares it with the live invoice run and the overdue reminders. Leave them 10 a minute:
at 20 a minute 12,240 ÷ 20 = 612 minutes ≈ 10.2 hours
Two decisions follow. Run it overnight: started at 20:00, it ends about 06:15, before anyone needs the API. And first check whether the list endpoint can embed the detail; many APIs have an expand or include parameter. If it can, 10 hours become 12 minutes, the largest saving in this lesson for ten minutes of reading the docs. Run at full speed in working hours instead, the backfill holds the whole limit for 6.8 hours and every live call in that window is refused, as rate limits, retries and backoff describes.
Cursors and resuming
A 10-hour job will be interrupted: a deploy, a closed laptop, a run of 503s at 03:00. Without a cursor it restarts at page 1 and spends again the hours already done. With one, it resumes where it stopped.
The cursor is the position of the last fully committed page: the API's own next token, or the last (created_at, id) pair if you page by sort order. Save it in the same transaction as that page's records. Save it before the records commit, and a crash skips a page. Save it after, in a separate write, and a crash re-reads one page, which an upsert by source id makes harmless. That is the right way to be wrong.
Page by something that does not move. Offset paging (page=171) over records that change during a 10-hour run shifts under you: a record deleted ahead of your position pulls every later page back by one, so one record falls through each page boundary, and an insertion pushes them on, so one is read twice. Keyset paging on (created_at, id), or the API's own cursor token, does not shift.
Record a high-water mark too. When the backfill starts at 20:00, note the time. When it finishes, the incremental sync from webhooks versus polling starts from 20:00 minus an overlap, so the changes made during those 10 hours are picked up, and the overlap's duplicates are upserts.
Schema drift
Two years of records were not all written by one version of the accounting system. Expect an amount that is a number in 2024 and a string with a comma in 2026, a status the docs never listed (partially_refunded), a field renamed in last year's API version, and nulls where the docs promise a value. A mapper that throws on the first surprise stops the backfill at 03:00 on record 7,412.
Three defences. Store the raw payload beside the mapped columns, so a mapping bug can be fixed and replayed without fetching again. Send a record that will not map to a dead-letter queue with the reason, and keep going. Alarm when the dead-letter rate crosses a threshold, say 1% of records, which tells "a few odd records" apart from "the API changed under us". The live sync needs the same defences, because the provider will change fields after you ship.
The five things every connector needs
- A credential that refreshes, and an alarm when it cannot, from how systems talk.
- A limiter and a retry policy: pacing under the provider's limit, 429 honoured, a 400 never retried.
- A cursor that survives a crash, for the backfill and for the incremental sync.
- An idempotent write: upsert by source id, event ids deduplicated, a key on every create, from idempotency and deduplication.
- A dead-letter queue someone reads, for records that will not map and calls that will not succeed.
Each part is small, and together they separate a script that worked once from a connector. Built as separate parts behind one interface, so the fetcher does not know whether the limiter is a token bucket or a fixed delay, each can be tested and replaced on its own: the composition argument, applied to plumbing.
Build versus buy, priced
Assume the founder builds the five parts for the accounting system in 30 hours: 30 × $100 = $3,000, once. Running it takes about an hour a month of the founder's time on drift and alarms and an hour of the operations manager's on the dead-letter queue: $130 a month.
Buying means an integration platform or a hosted connector. Assume a quote of $150 a month, 4 founder hours to set it up, $400, and the same hour a month on its failures, $30, because a bought connector still has records that will not map. Over the 12-month life that module 9 gives every automation:
build $3,000 + 12 × $130 = $4,560
buy $400 + 12 × ($150 + $30) = $2,560
Buy, for a common system with standard objects at this volume. The answer flips on pricing per record moved. Assume $0.005 a record: 300 changes a day is 9,000 a month, $45, still a buy. Add a nightly reconciliation that reads all 12,000 records, and the month is 369,000 records, $1,845, so building pays back in under two months. Build also wins when the system has no supported connector, or when the data may not pass through a third party's servers.
What you buy is the five things, or some of them. Ask which. Does it refresh tokens and tell you when it cannot? Does it pace under the limit and honour Retry-After? Does it resume a backfill, and how far back can it go? Does it deduplicate, on which id, for how long? Where do failed records go, and who sees them? A connector that answers three of the five has sold you the other two as your problem.
In practice
To a vendor or a contractor: the five questions above, and the backfill question. "How long for 12,000 records under our 30-a-minute limit, and what happens to our live traffic while it runs?" A good answer has the 10-hour figure in it, or asks whether the list endpoint can expand.
The mistake that costs money is running the backfill at full speed in working hours. It holds the whole limit for nearly 7 hours, the live invoice run fails with 429s, and if the connector is priced per record, the first day's bill carries all 12,000 of them.
Check yourself
1. A CRM migration: 30,000 records, 100 a page, one detail call each, a limit of 60 a minute, of which live traffic needs 15. How long, and when do you start?
300 + 30,000 = 30,300 requests at 45 a minute ≈ 673 minutes, 11.2 hours. Start at 19:00 to finish by about 06:15. Check for an expand parameter first: list pages alone would be 300 requests, under 7 minutes.
2. The backfill crashes after committing page 171 of 240. The cursor is saved in the same transaction as each page. Where does it resume, and what happens to records that changed during the crash?
At page 172, with nothing skipped and nothing repeated. Changes made to earlier records while it was down are not the backfill's job: the incremental sync starts from the high-water mark minus its overlap, and its upserts catch them.
3. A platform charges $0.005 a record moved. Your sync moves 300 changes a day, and the vendor's connector also re-reads all 12,000 records every night. Build or buy?
Ask first whether the nightly re-read counts as records moved. If it does, the month is 9,000 + 360,000 = 369,000 records, $1,845, and the $3,000 build pays back in under two months: build. If it does not, the month is $45 and buying wins by a wide margin.