A connector extracts a SaaS object by paging with offset and a limit of 500 under a modified-at filter. Every nightly run loads exactly 10000 rows and finishes green. Reconciliation shows 61000 matching records at source. The API returns HTTP 200 with an empty page at offset 10000 and support confirms an undocumented deep-paging cap. Which change actually closes the gap?

The first three things I would look at

1. The run's row count across history. Exactly 10,000, every night, is not a data pattern - it is a limit. Round numbers in a volume chart are the signature of a cap somewhere in the path.
2. The last request of the run, in full. The paging loop stops on an empty page, and an empty page returned with a 200 is indistinguishable from "no more data" unless you look at the response.
3. The watermark the run stored. If it was advanced from the last row loaded rather than the last row confirmed complete, the next run starts after the truncation point and the missing 51,000 rows are now unreachable without a backfill.

The diagnosis

Offset paging has two independent defects and this stem contains both. The cap means offsets beyond 10,000 return nothing. And because the filter and sort are on modified_at, rows updated during the walk move to the end of the result set, so a window that shifts under you re-reads some rows and skips others even below the cap.

Keyset paging removes both: order by an immutable, unique composite - modified_at plus the primary key - and ask for rows strictly greater than the last key seen. There are no offsets to cap, and a row that changes mid-walk reappears later rather than displacing another.

The misleading signal and why it misleads

"The pipeline is green." The connector did exactly what it was told: it paged until a page was empty. Green means "no exception was raised", and a silent truncation raises none. The second misleading clue is the reconciliation gap of 51,000, which invites a hunt for filter logic - the filter is correct; the transport is not.

Why the other options fail

• Raise the page limit. The instinct is reasonable because larger pages do reduce request count, and it fails here because the cap is on offset depth, not on requests. Ten pages of 1,000 die at the same place as twenty of 500.
• Backoff and retries. Right for 429s and 5xxs, which this is not. Nothing failed, so there is nothing to retry, and adding retries makes the run slower and no more complete.
• Nightly full reload. It appears to sidestep incremental complexity, and it still walks offsets into the same cap - so it loads the same 10,000 rows and then overwrites good history with a truncated snapshot, which converts a gap into data loss. It is also unaffordable the moment the object has tens of millions of rows.

The alert that would have caught it earlier

Assert completeness per run, and alarm on round numbers. Where a count endpoint exists, compare loaded rows to the source count for the same filter and fail the load on mismatch. Where it does not, alarm when a run's row count is exactly a power-of-ten-shaped value, when it equals the previous run's exactly, or when it deviates from the trailing 14-day median by more than a set share. A load that cannot fail cannot be trusted, and the cheapest way to give it the ability to fail is a count assertion.

When this is the wrong fix

When the source offers a bulk export. At 61,000 rows keyset paging is right. Above roughly 10 million, the answer is not better paging but a different transport - a snapshot drop into object storage or a log-based feed - because a paged API at a few hundred rows a second cannot finish a backfill in a working week.