Home › Learn › Data systems › Upstreams › APIs

Data systems · upstreams, in depth · for data leaders, then builders

API sources: paging, limits, cost and completeness

Every record the API holds arrives once, within the source's limits, and we can prove the load is complete.

What goes wrong when data comes through APIs, and the questions to ask first: bulk versus record APIs, paging, changed-since filters, rate limits and quotas, cost per call, deletes, versions and completeness.

For leaders

In plain words

Copying a library by asking at the desk

An API is a librarian's desk. It is built for people asking for a few books at a time. Copying the whole library through it means asking for one shelf at a time, all day, while others queue behind you, the librarian limits how often you may ask, and books are being returned and moved while you work. It can be done, but only with a plan: use the bulk delivery service if there is one, keep your place, and count what you got.

Kinds of APIs

Seven kinds of APIs

KindHow it worksGood forWatch for
Paged REST APIGET a page of records, then the nextMost SaaS apps and partner APIsPages shifting while you read; rate limits
Bulk or export APIAsk for a job; download one or more files when it finishesBackfills and big daily loads (Salesforce Bulk API, many ERPs)Job queues and size limits; still the best way to move volume
Change feed or events APIRead what changed since a cursor, including deletesIncremental loads that see deletesCursor retention: if you fall behind too far, you must reload
WebhooksThe source calls us when something changesLow-latency updatesCalls missed while we are down; need a catch-up pull
OData and SAP-style servicesQuery entities with filters and paging, often with delta tokensSAP and Microsoft business appsDelta tokens expire; server-side page limits
GraphQLAsk for exactly the fields and nested objects you needModern product APIsQuery cost limits; nested paging inside one query
SOAP and legacy servicesXML requests and responsesOlder enterprise and government systemsSlow, verbose, strict; few changed-since filters

Pagination

How paging decides whether a read is complete

PagingLooks likeWhat goes wrongUse it
Offset or page numberpage=1, 2, 3 or offset=0, 100, 200Rows inserted or deleted while you read shift the pages: some rows are skipped, others read twice; deep pages get slowOnly for small, static lists
Cursor or page tokenEach response gives a token for the next pageStable while reading; tokens can expire mid-runThe best general choice when offered
KeysetNext page = records with id (or updated time, id) greater than the last one seenStable and fast if the sort is unique; ties on time need a second sort columnBest when you can choose the filter
Time windowRecords changed between two times, one window at a timeDepends on the API's clock and precision; records changed during the read can fall between windowsOverlap each window a little and remove duplicates

In production

Common API problems, and the fix

ProblemWhat you seeFix
Rows shift between pagesA few records missing or doubled every run, never the same onesCursor or keyset paging; remove duplicates on the record id; compare counts
Throttled (HTTP 429) half wayA partial load that looks completeBack off with jitter, respect Retry-After, save a checkpoint after each page, check completeness before publishing
Quota shared with other integrationsOur backfill stops the sales team's other toolsOur own share of the quota; backfills out of hours; bulk API for volume
Changed-since filter uses another clockChanges near the window edges are missedOverlap each window, use the API's own time, remove duplicates
API reads from a search index, not the databaseRecent changes appear minutes laterRe-read a recent window on the next run
Deletes are invisibleDeleted records live on in the platformA deleted-records endpoint, a change feed, or a regular full list of ids
Nested JSON keeps changingNew fields lost; type changes break parsingLand the raw JSON in bronze; parse in silver with explicit schema-change rules
Token expires mid-runLoad stops after an hour, every nightRefresh tokens automatically; keep secrets in a vault
Version retired by the vendorA working integration stops on a date announced months agoTrack the vendor's changelog and retirement dates in the source contract
Each call costs moneyThe data bill arrives from the vendor, not the cloudIncremental loads, larger pages, a cost budget per source

Load and cost

Load on the source, and cost

Every API call is work for the source system, and some APIs charge per call or per record. Before the first load, work out the backfill: records to read, divided by records per page, times the time per call, against the rate limit. If it comes to days, ask for the bulk API or a file export. Run backfills out of hours, stay inside your share of the quota, and keep a pause switch the source team can use. The protecting the source rules apply to APIs too.

The question list

Every question, why we ask, and what breaks if nobody does

#Question to chaseWhy we askWhat breaks in production if nobody asksDo this (not this)
API · APIsfor sources reached through an API
API1Besides the normal API, is there a bulk export, a change feed or a webhook?Normal APIs are built for apps reading a few records, not for copying everything.A backfill through the normal API takes nine days and burns the whole quota.Bulk export for backfills and big loads, a change feed for increments, webhooks only with a catch-up pull. Not: the record-by-record API for everything.
API2How does it page: offset, cursor, keyset or time window, and is the sort order stable and unique?Paging decides whether a read is complete.Offset paging over changing data misses a few records on every run.Cursor or keyset paging on a unique sort; remove duplicates on the id. Not: page numbers over live data.
API3Can we filter by 'changed since', on which clock, to what precision, and is the boundary inclusive?Incremental API loads depend on this filter being exact.Changes made in the same second as the last watermark are skipped.An overlap window and duplicate removal on every run. Not: trusting the boundary.
API4What are the limits: requests per second, daily quota, parallel connections, page size, and is the quota shared?Limits decide backfill time and how fast we can catch up after an outage.Throttled midway; the load stops and nobody notices it was partial.Plan calls against limits; back off and checkpoint; keep our share of the quota. Not: retrying as fast as possible.
API5Does the API charge per call or per record, and who pays?Some APIs bill by usage, outside the cloud bill.A full refresh every hour triples the vendor's invoice.Incremental loads, large pages, a monthly budget per source. Not: full pulls for convenience.
API6How do we learn about deleted records?Most APIs return only records that still exist.Deleted customers stay active in the platform.A deleted-records endpoint or change feed; otherwise a regular full id list compared with ours. Not: ignoring deletes.
API7Does the API give a total count, or anything else we can check completeness against?Without a total, a partial load looks complete.A load that stopped at page 40 of 60 is published as complete.Compare our count with the API's total, or with a separate count call. Not: assuming the last page was reached.
API8Does the API read the live database or a search index or replica that lags?Lag means recent changes appear late.Records changed in the last minutes before a run are missed until someone notices.Re-read a recent window each run. Not: a window that ends 'now'.
API9How are new fields and versions announced, and when do old versions retire?APIs change on the vendor's schedule, not ours.The integration stops on the day v1 is switched off.Pin the version, watch the changelog, record retirement dates in the source contract. Not: 'latest' with no watcher.
API10How does authentication work, how long do tokens last, and who rotates the secret?Expired credentials are the most common cause of silent stops.The load fails every night after the token's one-hour life.Automatic token refresh and secrets in a vault with expiry alerts. Not: a token pasted into a config file.

Design decisions

The decisions to take

How do we pull large volumes?

OptionVerdict
The bulk or export APIPursue · Pursue for backfills and big daily loads.
The paged record API, in parallel within the limitsPursue when · Pursue only when no bulk API exists.
The paged API, one thread, all historyDrop · Drop. Days of calls and a burnt quota.

How do we page?

OptionVerdict
Cursor or keyset on a unique sortPursue · Pursue. Complete and repeatable.
Offset or page numberDrop · Drop for data that changes while you read.

How do we stay current?

OptionVerdict
Change feed, or webhooks plus a scheduled catch-up pullPursue · Pursue. Fast, and nothing is lost when we're down.
Webhooks onlyDrop · Drop. Every outage on our side loses changes.

FAQ

Questions people ask

Why do API data loads miss records?

Usually because offset paging over changing data skips rows, a changed-since filter misses records at the window edges, throttling stops the load part way, or the API reads from an index that lags. Cursor or keyset paging, overlapping windows, checkpoints and a count comparison fix most of it.

How do you load a large dataset from a SaaS API?

Use the vendor's bulk or export API for history and big daily loads, and a change feed or a changed-since filter for increments, within your share of the rate limits.

Next

Where to go next

Other source types: files; databases are covered on the main Upstreams page (change data capture, replicas, protecting the source).

Back up: Upstreams: know your sources, and the printable questionnaire.