Why do records go missing when we pull large result sets from an API?
Almost always pagination. Offset pagination asks for page three of a result set. If records are added or changed while you are paging, rows shift across page boundaries, so you skip some and repeat others. Cursor pagination asks for what comes after a specific record and is stable under change. When only offset is available, sort by an immutable field and freeze the date range.
Page three is not a fixed thing
Offset pagination is a position in a list that the server re-computes for every request. Between your request for rows 200 to 299 and your request for rows 300 to 399, someone books a job. The list shifts by one. The record that was at position 300 is now at 301, and you never see it.
The reverse happens with deletions and re-sorting: a record slides backward and you fetch it twice. If your sort key is something that changes, like last-modified descending, the shifting is continuous rather than occasional.
Symptoms worth recognizing
- Totals that are slightly off, and off by a different amount each run. A consistent gap suggests a filter problem; a wandering gap suggests pagination.
- Records that exist in the source and never appear downstream, with no error anywhere in the logs.
- Duplicates clustered near round numbers such as multiples of your page size.
- Bigger gaps during busy hours, because more changes happen while the pull is running.
Making offset pagination survivable
If the vendor only offers offset, three adjustments make it far safer. Sort by an immutable ascending key such as record id or created date, so new activity lands at the end rather than in the middle. Constrain each pull to a closed date window that is no longer changing. And keep page sizes modest so each request completes before much can shift.
Cursor pagination, where the API hands back a token meaning "resume here," avoids the class of problem entirely. When we evaluate a platform for an integration project, cursor support is one of the first things checked.
Verify with counts, not confidence
The only real proof that a pull was complete is a comparison. Pick a closed period, ask the source for its own count of matching records, and compare it against what you stored. Do it automatically after each sync, not once during launch week.
This check catches pagination bugs, filter mistakes, permission gaps and silent throttling with one number, which is why it belongs in every reporting build before anyone looks at a chart.
Use a closed window for the comparison, such as the month before last. Comparing a period that is still receiving edits produces small differences that are real rather than defects, and chasing those will teach the team to ignore the alert entirely.
Topics: pagination · cursors · data completeness · API design
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.