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
- APIs are built for apps reading a few records, not for copying whole datasets. Bulk extraction through them fights their limits.
- The main risks are missed or doubled records from paging, partial loads after throttling, invisible deletes, changing JSON and cost per call.
- Prefer, in order: the vendor's bulk or export API or change feed, a managed connector, then plain paged polling.
- Every API load ends with a completeness check: our count against the API's total.
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
| Kind | How it works | Good for | Watch for |
|---|---|---|---|
| Paged REST API | GET a page of records, then the next | Most SaaS apps and partner APIs | Pages shifting while you read; rate limits |
| Bulk or export API | Ask for a job; download one or more files when it finishes | Backfills 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 API | Read what changed since a cursor, including deletes | Incremental loads that see deletes | Cursor retention: if you fall behind too far, you must reload |
| Webhooks | The source calls us when something changes | Low-latency updates | Calls missed while we are down; need a catch-up pull |
| OData and SAP-style services | Query entities with filters and paging, often with delta tokens | SAP and Microsoft business apps | Delta tokens expire; server-side page limits |
| GraphQL | Ask for exactly the fields and nested objects you need | Modern product APIs | Query cost limits; nested paging inside one query |
| SOAP and legacy services | XML requests and responses | Older enterprise and government systems | Slow, verbose, strict; few changed-since filters |
Pagination
How paging decides whether a read is complete
| Paging | Looks like | What goes wrong | Use it |
|---|---|---|---|
| Offset or page number | page=1, 2, 3 or offset=0, 100, 200 | Rows inserted or deleted while you read shift the pages: some rows are skipped, others read twice; deep pages get slow | Only for small, static lists |
| Cursor or page token | Each response gives a token for the next page | Stable while reading; tokens can expire mid-run | The best general choice when offered |
| Keyset | Next page = records with id (or updated time, id) greater than the last one seen | Stable and fast if the sort is unique; ties on time need a second sort column | Best when you can choose the filter |
| Time window | Records changed between two times, one window at a time | Depends on the API's clock and precision; records changed during the read can fall between windows | Overlap each window a little and remove duplicates |
In production
Common API problems, and the fix
| Problem | What you see | Fix |
|---|---|---|
| Rows shift between pages | A few records missing or doubled every run, never the same ones | Cursor or keyset paging; remove duplicates on the record id; compare counts |
| Throttled (HTTP 429) half way | A partial load that looks complete | Back off with jitter, respect Retry-After, save a checkpoint after each page, check completeness before publishing |
| Quota shared with other integrations | Our backfill stops the sales team's other tools | Our own share of the quota; backfills out of hours; bulk API for volume |
| Changed-since filter uses another clock | Changes near the window edges are missed | Overlap each window, use the API's own time, remove duplicates |
| API reads from a search index, not the database | Recent changes appear minutes later | Re-read a recent window on the next run |
| Deletes are invisible | Deleted records live on in the platform | A deleted-records endpoint, a change feed, or a regular full list of ids |
| Nested JSON keeps changing | New fields lost; type changes break parsing | Land the raw JSON in bronze; parse in silver with explicit schema-change rules |
| Token expires mid-run | Load stops after an hour, every night | Refresh tokens automatically; keep secrets in a vault |
| Version retired by the vendor | A working integration stops on a date announced months ago | Track the vendor's changelog and retirement dates in the source contract |
| Each call costs money | The data bill arrives from the vendor, not the cloud | Incremental 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 chase | Why we ask | What breaks in production if nobody asks | Do this (not this) |
|---|---|---|---|---|
| API · APIsfor sources reached through an API | ||||
| API1 | Besides 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. |
| API2 | How 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. |
| API3 | Can 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. |
| API4 | What 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. |
| API5 | Does 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. |
| API6 | How 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. |
| API7 | Does 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. |
| API8 | Does 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'. |
| API9 | How 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. |
| API10 | How 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?
| Option | Verdict |
|---|---|
| The bulk or export API | Pursue · Pursue for backfills and big daily loads. |
| The paged record API, in parallel within the limits | Pursue when · Pursue only when no bulk API exists. |
| The paged API, one thread, all history | Drop · Drop. Days of calls and a burnt quota. |
How do we page?
| Option | Verdict |
|---|---|
| Cursor or keyset on a unique sort | Pursue · Pursue. Complete and repeatable. |
| Offset or page number | Drop · Drop for data that changes while you read. |
How do we stay current?
| Option | Verdict |
|---|---|
| Change feed, or webhooks plus a scheduled catch-up pull | Pursue · Pursue. Fast, and nothing is lost when we're down. |
| Webhooks only | Drop · 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.