Finance reported that a reconciliation report was short by a handful of transactions. Not many, maybe twenty out of ninety thousand, and not the same twenty each night. Our nightly export ran green every time, logged the row count it had fetched, and the count looked plausible. It took me two days to work out that the job was correct at every step and still wrong overall.
The export pulled from a partner API using offset pagination: ?limit=500&offset=0, then 500, then 1000, ordered by created_at. The partner's dataset is live. While we walked the pages, new records were being inserted, and because the sort was descending on creation time, each insert pushed everything down by one position. A record that had been the last row of page three moved to the first row of page four just after we had read page three and just before we read page four. We never saw it. The row we did read at that position was one we had already taken.
Offset pagination assumes a stable ordering over a snapshot. Over a mutating table it guarantees neither completeness nor uniqueness, and the failure is silent by construction, because every individual request is valid and every response is well formed.
We moved to keyset pagination, which the partner supported and nobody had used: pass the last seen (created_at, id) and ask for records strictly after it, with id breaking ties on identical timestamps. Position is now derived from the data rather than from a count, so inserts elsewhere cannot shift it. Where a partner offers only offsets, we pin the window instead, requesting an explicit closed range on created_at that ends before the job started, and accept the last few minutes on the next run.
The change that caught it, though, was a check rather than a fix. The export now records the min and max keys and the count per page, and a reconciliation step compares our total against a count endpoint for the same time window. It runs in eight seconds and it would have flagged this on night one.
An integration that cannot detect missing records will not tell you it is missing them.
– Sergey Shinder
Top comments (0)