Offset pagination is unstable when new records are inserted ahead of the current page. Page boundaries move between requests, so the client can receive an ID twice and skip another ID entirely.
Last updated: November 8, 2026.
seen = set()
offset = 0
while True:
page = fetch_page(limit=100, offset=offset, before=started_at)
if not page:
break
for row in page:
if row["id"] not in seen:
upsert(row)
seen.add(row["id"])
offset += len(page)The before filter represents a run-level high-water mark and works only if the API supports an immutable timestamp or equivalent filter. Deduplication makes retries safe, but it cannot recover a row that moved behind the offset and was never returned.
Prefer cursor or keyset pagination
Microsoft’s keyset pagination guide explains that a continuation token resumes after the last ordered key and avoids shifting offset boundaries. If the API returns nextLink or a cursor, follow it exactly and preserve the original filters and API version.
If you control the API, order by a unique immutable key, such as created_at, id, and return an opaque cursor. A nonunique sort alone remains unstable because tied rows can change order.
Contain risk when offset is the only option
Capture the import start time and request only records at or before it when the API supports such a filter. Upsert by immutable ID, persist page checkpoints, and record the IDs or a hash for every completed page. Run a second overlap window after the main import to catch late-arriving records.
Microsoft’s REST API implementation guidance recommends deterministic ordering and evaluating cursor pagination for large or frequently changing collections. Even with unique ordering, concurrent inserts can shift offsets unless the provider offers a snapshot or fixed filter. If neither exists, document that completeness cannot be guaranteed and schedule reconciliation by ID.
Continue with idempotent API operations, stable composite keys, and retaining imported history.
