Offset pagination breaks when the data changes while you read it. If a record is inserted on page 1 after you fetched it, everything shifts by one and the last item of page 1 appears again on page 2 (a duplicate). If a record is deleted, one record is skipped. So the fix is to use a more stable way to walk through the data, and then to design the ingestion so duplicates and gaps cannot hurt.
Use cursor-based pagination
Where the API offers it, use a cursor or a "next page token". The cursor marks a position in a stable ordering (for example by id or updated time), and does not move when data changes. Prefer this over page=3&limit=100.
Incremental with an overlap, and dedupe
Pull records with updated_at >= last_watermark - overlap (say 10 minutes back), sorted by a stable key. The overlap catches records that were committed late or missed in the last run. Then deduplicate by the record id when loading, keeping the latest version:
MERGE INTO raw.tickets t
USING (SELECT * FROM batch QUALIFY ROW_NUMBER() OVER
(PARTITION BY id ORDER BY updated_at DESC) = 1) s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;Because the load is idempotent, extra overlap only costs a little extra reading.
Reconcile
If the API returns a total count, compare it with what you loaded. If it does not, run a periodic check against a separate source of truth, or do a slower full sync of ids once a week to detect missing records. A mismatch should raise an alert.
Handle the network politely
- Retry on timeouts and 5xx responses with exponential backoff and jitter. Respect 429 and
Retry-Afterheaders. - Make each page fetch safe to repeat (it is a read, so it is).
- Set sensible timeouts, and a maximum number of pages per run so a looping cursor does not run forever.
Keep the raw responses
Store each raw response (page body, request parameters, fetch time) in object storage. If the transformation has a bug, or the API changes its format, you can reprocess without calling the API again, which also saves quota.
Be ready for API-specific traps
Some APIs return updated_at that is not updated for every kind of change, or limit history. Read the documentation closely, and test your logic against a sandbox with changing data.