An integration can run every night, return HTTP 200 on every request and still omit customer records, transactions or updates. That is what makes pagination defects dangerous. The system looks healthy, the dashboard refreshes, and nobody knows the source was only partially copied until an operational exception or reconciliation reveals the gap.
Pagination is the mechanism by which an API splits a large result set into smaller responses. Implementing it correctly is not simply a loop that keeps requesting pages. It requires an understanding of ordering, changes occurring during extraction, continuation tokens, failure recovery and what the provider means by the end of a result set. The business requirement is not “fetch pages.” It is “prove that the destination contains the records it should contain.”

Executive summary: five controls that matter
First, use the provider’s documented continuation mechanism rather than guessing the next offset or parsing a token. Second, define a stable extraction boundary or a documented consistency strategy for data that changes while pages are being read. Third, persist progress only after the destination has durably accepted the page. Fourth, make replay safe with record-level idempotency and an explicit strategy for updates and deletions. Fifth, reconcile source and destination by meaningful business partitions, not merely by whether the job finished.
These controls belong in the design and acceptance criteria of every business-critical API integration. Actiknow’s custom integration and automation solutions explicitly cover connecting systems through APIs and managing operational data flows; the same discipline applies whether the destination is a warehouse, CRM, finance platform or application.

Why pagination defects are hard to spot
Consider a hypothetical order API containing 12,400 records. A connector requests 500 records per page. It successfully reads 24 pages, but a transient error occurs before the final page. The job catches the exception, logs a warning and reports success because it received some data. The destination now has 12,000 records. A dashboard showing revenue by region may look entirely plausible, especially if the omitted orders are concentrated in one segment.
The opposite failure is equally serious: a retry repeats the last page and appends duplicate orders. Both outcomes can pass superficial monitoring. A successful HTTP response confirms that the server answered a request; it does not confirm that the entire business dataset was transferred.
A reliable design distinguishes request success, page traversal success, durable loading success and dataset completeness. These are separate states with separate evidence.
Understand the four pagination patterns
1. Page-number and offset pagination
A common API accepts page=3 and limit=100, or offset=200 and limit=100. This is straightforward when the dataset is static and ordering is deterministic. It becomes risky when records are inserted or deleted during traversal. If a new record appears at the beginning after page one is fetched, items can shift to later offsets. One item may be duplicated and another skipped. An API that does not guarantee a stable sort makes the problem worse.
Offset pagination can be appropriate for immutable snapshots, historical exports or APIs that provide a snapshot identifier. If the provider does not offer a stable view, consider an explicit bounded time window, a keyset strategy if supported, or a second reconciliation pass. Increasing the page size does not solve a consistency defect.
2. Cursor or keyset pagination
Cursor pagination returns a continuation token or an object key that tells the client where to resume. It often avoids offset shifting, but it is not a universal guarantee of snapshot consistency. Cursor lifetime, ordering, token reuse, filtering and mutation semantics are provider-specific. Some cursors expire; others embed a server-side snapshot. Never assume a token can be reconstructed or reused indefinitely.
For example, Stripe documents list endpoints that accept starting_after or ending_before object IDs and return a has_more flag. The correct next request follows the documented direction and last returned object, not an invented numeric offset. A different vendor may use a completely opaque token. The common principle is to honor the contract of that endpoint.
3. Next-link pagination
Some APIs return a complete URL for the next page. Microsoft Graph uses @odata.nextLink on paginated responses and instructs clients to request the entire returned URL until no next link remains. Its documentation also notes that paging support and maximum sizes differ across endpoints. Treating a next link as a string from which to extract only one parameter can lose filters or service-specific state.
GitHub’s REST API uses a Link response header on many paginated endpoints. A client should follow the rel=”next” link when present rather than assuming every endpoint exposes the same page parameter or a calculable last page.
4. Time-window and incremental pagination
An incremental API may accept updated_since or a timestamp range and then paginate the matching records. This combines two independent concerns: which records qualify for the run and how to traverse all qualifying records. If the time boundary moves while pages are fetched, or timestamps have insufficient precision, records can fall between runs. If two records share the same timestamp, a simple greater-than watermark can skip one of them.
Use a documented inclusive or exclusive boundary, preserve the exact comparison semantics and include a deterministic tie-breaker such as (updated_at, id) where the API supports it. If not, overlap extraction windows and deduplicate by source identity and version. The overlap must reflect observed source delay and correction behavior, not an arbitrary guess.
Seven failure modes that silently lose or duplicate data
Stopping because a page is shorter than expected
A connector may stop when it receives fewer than the requested 100 records. That is safe only if the provider explicitly defines a short page as the end. Some APIs can return sparse or filtered pages while still providing a next token. Terminate using the documented completion signal, not page length. Conversely, a full page does not prove another page exists.
Discarding a next token on retry
A timeout can happen after the server processed a request but before the client received the response. If the connector increments a page counter prematurely, it may skip the failed page. If it retries by appending without deduplication, it may double-count records. The safe unit of recovery is the last durably committed page or record boundary.
Treating empty pages as completion
An empty page can mean no results, but some paginated interfaces allow an empty page with a valid continuation. A robust loop checks the documented next-page signal. It should also detect repeated tokens or cycles and fail explicitly rather than spin forever.
Changing filters between requests
An initial request might filter records to one tenant and a date range, but subsequent requests accidentally omit those parameters. When the API provides a full next URL, follow it exactly. Where the client must reconstruct requests, preserve immutable query parameters, authorization scope, tenant identity, sort order and page size for the entire run.
Assuming the source dataset is frozen
A CRM contact created during extraction can change page boundaries. A finance transaction can be corrected while the connector is reading the next page. The integration needs a consistency policy: snapshot if available, bounded extraction followed by incremental catch-up, or a repeatable overlap and reconciliation strategy. Cursor pagination alone does not establish that policy.
Losing deletions and status transitions
An incremental feed of changed records may not return hard-deleted objects. Even flawless pagination will then produce a stale destination. Ask whether the source exposes tombstones, deletion endpoints, audit logs or full-snapshot comparisons. Also decide whether a record moving out of a filter, such as an order changing status, should be removed from a filtered destination or retained with its updated state.
Mistaking rate-limit handling for data completeness
A connector may receive HTTP 429, sleep and continue, but accidentally drop the current page or overwrite its checkpoint. Respect Retry-After where supplied, use bounded exponential backoff for transient failures, and do not mark a batch complete until all required pages and loads succeed. Persistent authorization failures, malformed tokens and contract changes should produce actionable failures, not silent partial success.
A production-ready extraction design
Step 1: Write the source contract
For each endpoint, document the resource, tenant scope, unique record key, sort order, pagination mechanism, page-size limits, token expiration, update and deletion behavior, rate limits, timestamp precision, and whether the API promises a consistent snapshot. Capture a few real responses, including an empty result, final page and error. This contract is the foundation for tests and future vendor-version changes.
Step 2: Define a bounded run
Record the run ID, extraction start time, source scope and high-watermark boundary before fetching. If the API supports a consistent snapshot token, use it. Otherwise, use a fixed upper timestamp boundary when possible and plan a subsequent catch-up pass. A run should have an explicit state such as running, incomplete, failed, reconciled or complete.
Step 3: Fetch, validate and stage each page
Validate response shape, required fields, page token and tenant identity. Store raw payloads or sufficiently detailed audit metadata when retention and security requirements permit. Stage records with their source ID, source update timestamp, ingestion time and run ID. Avoid overwriting the only copy of a partially processed page.
Step 4: Commit before advancing the checkpoint
Only after the page is durably written should the connector persist its next-page token or position. Ideally, the page load and checkpoint update share a transaction; where that is impossible, prefer at-least-once delivery with idempotent upserts. A replayed page must not inflate counts or undo a newer record version.
Step 5: Finish traversal, then reconcile
A job can be marked traversal-complete only when the API’s end-of-data condition has been reached. Business completeness requires additional evidence: record counts, distinct IDs, monetary control totals, status counts or comparisons against an authoritative source export where available. Reconciliation may be exact for closed periods and provisional for live periods.
Step 6: Publish only verified data
Separate raw extraction completion from the availability of trusted reporting data. For critical dashboards, expose the latest reconciled partition and its freshness timestamp. If reconciliation fails, retain the previous certified result or mark the new result provisional rather than quietly publishing a partial batch. This is especially important when API feeds power executive BI and financial operations.
Illustrative pseudocode: checkpoint after durable writes
run = open_run(scope, fixed_upper_bound)
next_ref = load_committed_checkpoint(run) or first_request(scope, run.upper_bound)
seen_refs = set()
while next_ref is not None:
if next_ref in seen_refs:
raise PaginationCycleError(next_ref)
seen_refs.add(next_ref)
response = fetch_with_retry(next_ref) # honor 429 and Retry-After
validate_response(response, scope)
records = extract_records(response)
following_ref = documented_next_reference(response)
durable_upsert(records, source_key=”id”, version_key=”updated_at”)
commit_checkpoint(run, following_ref) # only after durable upsert
next_ref = following_ref
mark_traversal_complete(run)
reconcile_against_source_controls(run)
mark_publishable_only_if_checks_pass(run)
This is a design sketch, not drop-in code. In a real implementation, the checkpoint must be transactionally coordinated with the load or deliberately replay-safe. A source version may require more than updated_at to order conflicting changes. The seen_refs set is a useful in-run cycle guard but should not be the only protection against repeated pages across restarts.

How to prove completeness without a perfect source count
A source-provided total count is helpful, but not always authoritative. It may represent the count at query time, include records excluded by permissions or change while extraction is running. Use it as one control, not a universal guarantee. Stronger evidence comes from multiple independent checks chosen for the business object.

For orders, reconcile count and amount by order date, currency, business unit and status. For customers, compare distinct IDs and changes by day. For inventory, compare location-level quantities and a source snapshot where available. For events, check time-window coverage, source sequence numbers and late-arrival distributions. Compare closed periods separately from active periods so normal business updates are not misclassified as defects.
Build a reconciliation ledger with source system, entity, partition, expected count, loaded count, distinct ID count, duplicate count, control totals, run ID, extraction watermark, reconciliation time and result. Where the provider offers no independent control, be explicit: you can validate internal consistency and detect anomalies, but cannot honestly certify absolute source completeness.
The monitoring metrics leaders should actually see
Request success rate is not enough. Monitor pages fetched versus expected where known, distinct records received, records inserted and updated, repeated page references, retry counts, rate-limit delays, age of the last successful watermark, oldest unreconciled partition and the number of unresolved count or control-total mismatches. Alert on unexplained changes in record volume relative to comparable periods, while allowing for seasonality and genuine business variation.
A useful operational status has three separate indicators: connectivity (can the API be reached?), processing (were all pages traversed and committed?) and completeness (did reconciliation pass?). Displaying only a green “last sync succeeded” badge conceals the distinction.
Choosing between API polling, webhooks and bulk files
Pagination reliability is sometimes an architectural problem rather than a coding problem. A provider’s API may be suitable for small incremental updates but unsuitable for a multi-year historical backfill under strict rate limits. A bulk export may provide a more stable snapshot and control totals. Webhooks can reduce polling, but delivery can be duplicated or missed and still requires periodic reconciliation. Actiknow’s discussion of API-versus-file integration trade-offs explains why business-critical flows should be chosen on freshness, recovery and auditability rather than on whether an API sounds more modern.
As the number of connected systems grows, shared checkpointing, authentication, alerting and replay capabilities may justify an integration platform or reusable internal layer. The choice between point-to-point connections, iPaaS and a custom integration layer should reflect the business process and operational ownership, not just the number of connectors. See the related guide to SaaS integration architecture for that broader decision.

A practical testing matrix before go-live
Test an empty dataset, one page, an exact multiple of page size, a short intermediate page with a continuation, and a large multi-page dataset. Inject an HTTP 429 and a timeout before and after a page write. Restart the process after a successful load but before checkpoint persistence. Confirm that replay does not create duplicates.
Then test data mutation during traversal: an inserted record at the start, a deleted record, a status change and multiple records with the same update timestamp. Test token expiration, invalid or repeated next tokens, cross-tenant access rejection and changed API response fields. Finally, reconcile a known closed period against an independently generated export. Each test should specify the expected record set, not merely assert that the job exits without an exception.
A 30-day remediation plan
Week 1: Inventory and risk-rank
List the integrations feeding decisions, money movement or customer operations. Identify endpoint contracts, current pagination code, checkpoint storage, update/delete handling and existing reconciliations. Prioritize feeds where silent omission has high business impact and where there is no independent source control.
Week 2: Add evidence and reproducible tests
Instrument page counts, continuation references, retries, unique record counts and partition totals. Build test fixtures for page shifting, token expiry and partial failures. Run the current connector against known source snapshots to establish a baseline of discrepancies before changing the extraction logic.
Week 3: Harden traversal and replay
Implement provider-correct pagination, stable run boundaries where possible, durable checkpointing and idempotent writes. Add bounded retries and explicit incomplete-run states. Separate raw ingestion from publishable reporting tables.
Week 4: Reconcile, backfill and operationalize
Reprocess affected historical windows under change control, compare independent totals, and document any restatements to business users. Assign an owner for alerts and reconciliation exceptions. Publish a runbook explaining how to resume, replay, reauthorize and escalate a failed integration.
An executive acceptance checklist
Before approving a production API integration, ask:
- Can the team demonstrate a multi-page run from first request to documented end?
- Can it restart after any page without loss or duplication?
- Does it handle changes occurring during extraction?
- Are updates and deletions accounted for?
- Can the owner explain what the watermark means?
- Is there a documented reconciliation method and a defined response to mismatches?
- Will the dashboard show stale or provisional data when a batch is incomplete?
If any answer is unknown, the integration is not yet fully governed.
Frequently asked questions
What is an API pagination bug?
A defect in traversing an API’s multi-page response that causes missing, repeated or incorrectly scoped records. It can occur even when every successful request returns HTTP 200.
Is cursor pagination always safer than offset pagination?
Often for changing datasets, but not automatically. The provider’s cursor consistency, ordering and expiration rules still matter. Offset pagination can work well for immutable snapshots.
Can I stop when a page has fewer rows than the limit?
Only when the API explicitly documents that behavior. Otherwise use its has_more flag, next token, next URL or other defined termination signal.
Why do records go missing when new data arrives?
With offset-based paging, inserts and deletes can shift positions between page requests. Time-window boundaries can also miss records if timestamps or comparison rules are mishandled.
How should an integration resume after a timeout?
Resume from the last durably committed checkpoint, retry the uncertain page safely and use idempotent writes. Never advance the checkpoint before the page is stored.
Do webhooks eliminate pagination problems?
No. Webhooks may reduce polling, but missed events, duplicates and backfills still require reconciliation and often a paginated recovery API.
How do we handle records deleted in the source?
Use documented tombstones, deletion feeds or periodic snapshot comparisons. A changed-since endpoint that omits hard deletes cannot independently keep the destination accurate.
Should we always compare API totals with warehouse totals?
Yes when the totals are comparable, but understand source count semantics and timing. Add distinct-key checks and business control totals for stronger assurance.
What if the source provides no total count?
Use source exports or partition-level business controls if available, plus anomaly checks and replayable audit logs. State the remaining limitation rather than claiming absolute completeness.
How often should reconciliation run?
Match it to business impact and source behavior. High-value operational feeds may need checks every run; closed-period financial data may require formal daily or period-end sign-off.
Is increasing page size enough to prevent missed records?
No. It reduces request count but does not fix unstable ordering, incorrect continuation handling, checkpoint timing or deletion logic.
Who should own integration completeness?
Engineering owns reliable extraction and recovery; the business data owner defines acceptable completeness, reconciliation thresholds and the response to material discrepancies.
Conclusion: treat completeness as a product requirement
A dependable integration does more than move data quickly. It preserves the meaning and completeness of the source as far as the provider’s contract permits, exposes uncertainty when it cannot, and gives teams a controlled way to replay and reconcile. Pagination is a small piece of code with an outsized effect on business trust. Make it observable, restartable and testable before the numbers reach decision-makers.
If you are reviewing an integration that feeds a dashboard, finance workflow or operational application, Actiknow can help assess the extraction contract, failure recovery and reconciliation design. You can discuss a reliability review with Actiknow to scope the right next steps.
Technical references
For vendor-specific behavior, consult Microsoft Graph paging guidance, GitHub REST API pagination documentation and Stripe pagination documentation. These examples illustrate different provider contracts; always verify the documentation for the exact endpoint and API version you use.

