Your Warehouse Is the Index the API Won’t Give You

A pull I had run cleanly for months started dying. Same endpoint, same code, same credentials. It would page through tens of thousands of AP registered invoices and then, somewhere north of 19,000 records, fall over. The frustrating part was the consistency. It did not fail randomly. It failed at the same place every time.

That consistency turned out to be the whole story. By the time I was done, the ceiling had moved on me twice, and the most dangerous version of the failure was the one that threw no error at all. It took two wrong answers to get there.

Dead end one: it looks transient, so add retries

My first instinct was the lazy one. A long-running extract making thousands of sequential requests will eventually catch a bad moment — a dropped connection, a gateway hiccup, a rate-limit nudge. The original loop had no timeout and no retry. One bad response and the entire run aborted. So the fix seemed obvious: wrap each page in a retry with backoff, add a timeout, reuse the session, and let a single blip stop being fatal.

Then I looked at the actual error instead of the category of error. It was not a timeout or a 429. It was a flat HTTP 400 Bad Request, and it landed on the request for offset=19000. The request for offset=18500 had succeeded and returned a full page. The next one did not.

That changes everything, because a 400 at a fixed offset is not bad luck. It is a rule. Retrying a deterministic 400 just fails slower. The server was enforcing a maximum pagination window: past a certain depth, the door was closed. And since the dataset was larger than that window, no amount of retry tuning would ever reach the rest of it. Deep offset pagination simply could not return the whole table.

The lesson I should have started with: classify the failure before you fix it. Transient and deterministic failures look identical in a stack trace and demand opposite responses. Retry is for the dice. A 400 at a repeatable boundary is not the dice.

Dead end two: just slice it by date

If I cannot page deep, I reasoned, I will page shallow inside windows. Walk the history in date ranges, fetch each window from offset zero, and never approach the ceiling. The endpoint exposed a date finder, so this felt clean.

It was not. Two problems surfaced, and the second is the more instructive.

First, the date finder took a single “since” value with no upper bound. You can ask for everything on or after a date, but you cannot box a window between two dates. So “slice into bounded windows” was not actually available; the only honest version was a forward-advancing cursor.

Second, and this is the trap worth remembering: to advance a date cursor safely you must order the results by that date, ascending. Otherwise the maximum date you have seen is not the true frontier, and advancing to it skips rows. So I added an orderBy on the create-date column. And the request hung until it timed out.

The reason is quiet but obvious in hindsight. The cheap order the API had been using rode the indexed key. Asking it to sort the entire matching set by an unindexed date column, with the cursor still down at the beginning of history so the matching set was everything, forced a full-table sort before it could return page one. The first cursor position is the most expensive one there is. I had traded a hard wall for a slow one.

Date-based extraction is a fine pattern when the API gives you a bounded range and an indexed sort. This one gave me neither.

The reframe: stop paginating, start partitioning

The mistake underneath both dead ends was the same. I kept trying to make pagination reach further. The answer was to need it less.

When I read the endpoint’s finder documentation properly, instead of skimming for the one parameter I wanted, the shape of the solution was sitting right there. The API offered first-class finders to filter by company code, and to filter by company and vendor together. Each of those filters shrinks the result set. And paging a shrunken set from offset zero with the cheap indexed order is exactly the operation that had always worked.

So the design flips. Do not pull the fact table as one flat scan. Pull it one company at a time. Offset resets to zero for each company and never climbs toward the ceiling — as long as no single company holds more rows than the window allows.

Which raised the obvious question: where do the company codes come from?

The insight: the warehouse already holds the keys

I was about to hardcode a list of company codes when I realized I was being slow again. I already ingest a companies extract. It is a table in the warehouse, refreshed on its own schedule, with one clean row per legal entity — fourteen of them. That extract is not just a dimension to join against at the gold layer. It is a list of partition keys for extracting the fact table that references it.

So the script reads the companies extract at runtime and loops the codes it finds. No hardcoding, no separate config to drift out of sync. When a new entity is onboarded in the ERP and shows up in that extract, the invoice pull picks it up on the next run for free. The dimension drives the fact extract.

This is the part I think is genuinely reusable. We tend to treat ingestion as one-directional: the API is the source, the warehouse is the sink. But the reference data you have already landed is itself an input to the extraction layer. Your conformed dimensions are the index the API declined to give you. Load them first, because they are the keys that make your facts retrievable at all.

The wall moves: when your measured ceiling lies

Here is the humbling part I did not see coming. I had measured the ceiling at around 18,500 on the unfiltered scan, so I set my overflow guard there. The first partitioned run died anyway — this time at offset 9,000.

Applying a finder lowers the ceiling. The pagination window you get when you filter is smaller than the window you get when you do not. My guard, calibrated on the wrong query, never tripped, and the deterministic 400 I thought I had designed around came back to kill the run a second time, at a depth I had not budgeted for.

The fix was to stop trusting any number I had measured. Rather than ask “is my offset past the boundary I recorded,” the code now treats the 400 itself as the signal: a Bad Request mid-pagination means this partition is too big, so escalate. I keep a conservative offset guard so I usually avoid making the doomed request, but the guard is a courtesy, not the contract. The contract is the error. A boundary you hardcode is a boundary that will move on you. A boundary you detect adapts on its own.

Adaptive escalation: when one key is not enough

One loose end remained. What if a single company holds more invoices than the window allows? With a skewed portfolio — one dominant operating entity and a long tail of small ones — that is not hypothetical. The big company hits the wall inside its own partition.

So the partitioning escalates. Page a company normally. If it overflows — whether the offset guard trips or a 400 comes back — discard the partial pull and re-fetch that company as a company-by-vendor grid: filter by company and vendor together, iterating the vendor codes that belong to that company. Each cell is tiny. Offset never goes deep.

And the vendor codes come from exactly where you would now expect: the vendors extract I already ingest, which carries a company code on every row, so I can build the per-company vendor list directly. Roughly two thousand vendors for the dominant entity, each one a cheap, bounded call. The same dimension-as-key move, one level finer.

The result is a fan-out that tunes itself. Small companies finish in a handful of pages. The one big company drops into vendor-grid mode without my intervention. And the whole thing shouts if it ever hits a cell it still cannot split — a single vendor inside a single company with more rows than the window — so a silent gap can never masquerade as a complete load.

What the completed run actually showed

The run finished with the full history in hand. It also returned several thousand more invoices than the version that had “worked” for months.

That gap is the uncomfortable part, and it is the real point of the whole exercise. The old runs did not error. They just quietly returned less than the truth — dying or truncating somewhere past the wall while the log said nothing was wrong. A loud 400 is annoying, but it is honest; it tells you it failed. The silent short count had been lying to every report downstream of it, and would have kept lying indefinitely.

One more thing surfaced in the merge. I run the extract three times and union the results, because the API’s default sort is not stable enough to trust across deep pulls. On this run, a small handful of invoices appeared in only one of the three passes. A single-pass run would have dropped those — silently, of course, just like the wall did. The three-pass merge is insurance against a failure mode you cannot see while it is happening.

The pattern, stated plainly

Strip out the specifics and the recipe is portable to any API with the same shape — an offset cap, exact-match finders, and no real cursor, which describes a lot of Oracle-backed and legacy enterprise REST endpoints:

1. Classify the failure before you fix it. A deterministic boundary error is a rule to design around, not a blip to retry through.
2. When pagination hits a wall, stop extending it. Partition the pull on a key that shrinks the result set.
3. Source those partition keys from data you already ingest. Your dimensions are extraction inputs, not just join targets.
4. Do not hardcode the boundary. The ceiling can move — a filter can lower it — so treat the boundary error itself as the signal to escalate, and let the data decide the depth.
5. Instrument for the gap you cannot see. The dangerous failure is not the loud one. It is the partition that silently returns less than it should, and the report that trusts it.

The deeper point is about ordering. In a medallion architecture we obsess over the flow from bronze to silver to gold. This was a reminder that there is an ordering inside bronze too: land your reference data first, because tomorrow it stops being just a thing you loaded and becomes the thing that lets you load everything else. And the failure worth fearing is rarely the one that fills your log with red. It is the one that fills your warehouse with a confident, plausible, incomplete number.