Why does a paged API export sometimes skip or duplicate records?
Offset paging asks for rows 500 through 599 of a result set that is still changing underneath you. When records are inserted or reordered while you page, rows shift: some slide past your window unseen and others repeat. Cursor paging avoids this by remembering a position in a stable sort order instead of a row number. If you are stuck with offsets, freeze the window with a fixed date range and a deterministic sort.
The result set is moving while you read it
An offset is a position in an ordered list computed fresh on every request. Pull page one, and while you are processing it three new jobs are created. On page two the server recomputes the list, everything shifts by three, and three records that were at the top of page two are now at the bottom of page one — which you already read past. They are simply never returned.
The mirror image happens when records are deleted or when the sort key changes: rows shift the other way and you see the same record on two pages. Both failures are silent. The API returns HTTP 200 the entire time.
How to tell this is what is happening
- Compare total rows to distinct ids. If distinct is lower than total, you have duplicates, and offset drift is the first suspect.
- Look at where duplicates cluster. Drift duplicates concentrate at page boundaries — the last few rows of one page repeating at the start of the next.
- Watch the reported total. If the API returns a total count and that number changes between page one and page twenty, the set is live and offsets are unreliable by definition.
- Check whether misses correlate with volume. Exports that are clean at night and lossy at ten in the morning are almost always this.
The fixes, in order of preference
Use cursor or keyset paging when the vendor offers it — the cursor encodes the last record you saw, so inserts elsewhere do not move your position. When only offsets exist, constrain the query so the underlying set cannot change: filter to a closed date range that is already in the past, and sort by an immutable column such as the record id rather than a modified timestamp.
Sorting by modified descending is the worst possible choice with offsets, because every update reshuffles the list you are walking. Sorting by id ascending is the safest. This is standard practice in the data integration work behind any reliable reporting dashboard.
Do not paper over it with a second pass
The tempting fix is running the export twice and merging. That reduces the miss rate without eliminating it, and it doubles your rate limit consumption. Fix the paging strategy instead, then use reconciliation counts to confirm rather than to compensate.
Topics: pagination · API design · data quality · ETL
Have a version of this question about your own business?
The useful answer usually depends on which systems you run and how they're connected. That's a conversation, not a blog post.