How to Design API Extractors for ERP Rate Limits

The extractor that looks fine in test and lies in production#

An ERP API extractor only works if it can prove what it saw, what it missed, and why. If it cannot do that, you do not have record extraction reliability, you have a polite guessing machine.

That matters most during month-end, EOFY, promo spikes, and the kind of busy trading periods Australian wholesalers and distributors know too well. The API still returns HTTP 200. The dashboard still refreshes. And somewhere in the middle of that calm, a page gets truncated, a retry skips forward, or a record appears twice and gets deduped away incorrectly.

That is the failure mode people hate because it is silent. No exception. No pager. Just numbers that do not reconcile later.

Key takeaway: Design for proof, not optimism. If your extractor cannot show page boundaries, watermarks, retries, and record IDs for every sync, it can lose data without ever looking broken.

Start with the thing most teams get wrong#

The first mistake is treating pagination as a transport detail. It is not. In ERP integrations, pagination is part of the data contract, and under load that contract gets fuzzy fast.

I have seen extractors that worked perfectly in a 50-page test and then started dropping rows after a few hundred requests in production because the ERP changed page size, reordered results, or started returning fewer records per page once throttling kicked in. That is exactly when people ask, How do you design API extractors so an ERP rate limit or pagination quirk does not silently drop records during busy trading periods? The answer is not “retry harder”. It is “persist more evidence”.

For an ERP API extractor, your minimum durable state should include:

• request timestamp
• endpoint and filter set
• page number or cursor
• page size requested
• page size returned
• response hash
• first and last record IDs on the page
• watermark used for the run
• retry count and backoff delay
• final run status, with a reason if incomplete

That gives you a chain of custody. If a record is missing later, you can tell whether the ERP never returned it, your code skipped it, or a retry moved the window underneath you.

Real empty page, or pagination bug?#

This is where a lot of teams get fooled. A page of zero records can mean “end of data”, or it can mean “the ERP is having a moment and decided to stop early”.

The safest way to tell the difference is to never trust a single empty page in isolation. Compare it against at least three signals:

1. Expected page count or total count, if the API supplies one.
2. Watermark progression, such as updated_at or modified_since.
3. Boundary continuity, meaning the last record on page N should logically precede the first record on page N+1.

If the API returns page 8 empty but page 7 only had 12 records when you normally see 200, do not assume the dataset is finished. Re-query the same boundary once, ideally with the same filters and a fresh request ID. If the second call returns records, you have a pagination bug or transient truncation, not a true empty page.

This is also where page-number pagination is weaker than cursor-based pagination. Page numbers are easy to reason about, but they are brittle when the source system is under write load. Cursor pagination, or keyset pagination using a stable sort key like updated_at plus ID, is safer because it anchors each request to a record boundary rather than a position that can drift.

Log the proof, not just the outcome#

If you want to prove a record was never returned by the ERP versus your extractor accidentally skipping it during retries, you need logs that are useful after the fact, not just pretty in Grafana.

At minimum, persist:

• the exact request URL or payload
• the auth context used
• the response headers
• the response body checksum
• the record IDs extracted from that page
• the last successful checkpoint before the request
• any retry that happened after a timeout or 429
• the reason a page was accepted, rejected, or re-fetched

A lot of teams log “page 14 failed” and call that observability. It is not enough. If page 14 was fetched twice, once before a timeout and once after a retry, you need both attempts. Otherwise you cannot tell whether the extractor lost records, duplicated them, or simply saw them in a different order.

For lakehouse ingestion, I also like a small immutable audit table alongside the raw landing table. One row per request attempt. That table becomes the forensic trail when finance asks why the Power BI reconciliation report moved by A$18,400 overnight and the ERP still shows the original value. In Australia, that conversation tends to happen right before someone wants answers for GST, stock, and debtor balances at the same time.

Rate limiting is a data integrity problem, not just a speed problem#

When rate limits spike during month-end or sales events, the wrong instinct is to push harder until the sync finishes. That is how you create a gap that looks “caught up” but is actually missing a slice of records.

A safer rate limit handling pattern is:

• exponential backoff with jitter
• a hard cap on retry attempts per page
• checkpoint persistence after every accepted page
• a “resume from last confirmed boundary” rule
• a run-level timeout that fails incomplete, rather than pretending success

The key is to separate retrying a page from advancing the sync window. If a page fails after you have already moved the watermark, you have created a silent gap. Do not do that. Advance the watermark only after the page is durably written and validated.

For busy trading periods, I usually prefer smaller page sizes with more frequent checkpoints over giant pages that maximise throughput. You give up some speed, but you reduce the blast radius of any single bad response. That trade-off is usually worth it when the source ERP starts wobbling under load.

Deduplication has to be deterministic#

When the ERP reorders records across pages and the same record can appear twice in different sync attempts, deduplication is not optional. But the wrong dedupe rule can delete valid updates.

Use a deterministic business key, not just the raw payload hash. For orders, that might be order_id plus line_id. For invoices, invoice_number plus line_number. For inventory movements, movement_id or a composite of source document and timestamp. Then keep the latest version by source_updated_at, with a tie-break on source sequence or record ID if the ERP provides one.

Do not dedupe purely on “same payload”. Two records can look identical except one has a later status or a corrected tax amount. If your extractor collapses them because the JSON body is close enough, you will create reconciliation drift that only shows up days later.

A good pattern in Databricks warehouses is a raw bronze table that stores every accepted response, followed by a curated silver table that applies deterministic merge logic. That way you can reprocess if the ERP changes behaviour, which it often does after a few hundred requests in production even when the test tenant looked clean.

How to know a full extract is incomplete when HTTP 200 keeps coming back#

This is the nastiest version of the problem. The API returns 200 the whole time, but the last pages quietly truncate under pressure. No error. No warning. Just fewer records than expected.

The fix is to build completeness checks that do not depend on HTTP status at all.

Use at least two of these:

• expected count from the source, if available
• page count compared with historical baselines
• continuity checks on sorted keys
• checksum or hash totals over IDs
• reconciliation against a second source, such as the ERP’s own report export or a downstream ledger total

If the API says there are 12,418 modified records since midnight and your extractor only lands 12,031, that run should be marked incomplete, even if every request returned 200. If the API does not supply totals, compare the number of unique IDs landed against the previous few runs for the same trading pattern. A sudden drop in volume during a normal trading day is a signal, not a success.

This is where a lakehouse helps. You can compare ingestion counts, landing counts, and transformation counts separately. If bronze has 12,418 raw records but silver only has 12,031 distinct business keys, you know the problem is either upstream truncation or your merge logic. That is much easier to debug than a single opaque sync status.

What changes after a few hundred requests in production#

The most frustrating bugs are the ones that only appear after the extractor has been running long enough to warm up the ERP’s bad behaviour.

You test against a few pages. It passes. Then production traffic hits, the request count climbs, and after a few hundred requests the ERP starts changing response shape, slowing down, or returning shorter pages. That is not a rare edge case. It is the real case.

The safest response is to build a canary style validation into the extractor itself:

• sample every Nth page for schema and page-size drift
• compare returned page size against requested page size
• detect sudden shifts in average records per page
• alert when the same filter set starts producing inconsistent page boundaries
• fail the run if drift crosses a threshold

If the ERP changes behaviour after 300 requests, you want to know on request 301, not when the finance team notices a mismatch at close of business. For Australia businesses with peak trading in local time zones, that often means the bad behaviour shows up right when the office is still open and the warehouse is already moving orders.

The operating pattern that actually holds up#

How do you design API extractors so an ERP rate limit or pagination quirk does not silently drop records during busy trading periods? You make the extractor boring in the right places and suspicious everywhere else.

That means:

• stable sort keys
• durable checkpoints
• idempotent writes
• page-level audit logs
• deterministic dedupe
• completeness checks independent of status codes
• retry logic that preserves boundaries
• run failures that are loud when the data is incomplete

If you are building this inside a broader analytics stack, this is exactly where a Data Analytics & Lakehouses setup earns its keep. Not because lakehouses are fashionable, but because they let you separate raw ingestion from business-ready reporting and prove where a record went missing. Power BI on top of that only works if the ingestion layer is honest.

The practical test before you trust it#

Before you let an ERP extractor touch production reporting, run this checklist against a staged load with throttling turned on:

1. Force 429s and timeouts mid-page.
2. Reorder records between pages.
3. Return an empty page before the real end.
4. Truncate the last page while still returning 200.
5. Change page size after several hundred requests.
6. Replay the same page twice and confirm dedupe does not drop a changed record.
7. Verify the run fails incomplete when counts do not reconcile.

If it survives those tests, you are close. If it does not, the problem is not “more retries”. It is that the extractor does not yet know how to tell truth from noise.

For teams in Australia managing ERP syncs as part of a bigger operational data platform, this is the kind of work that is worth doing once, properly. It is core infrastructure. It should not be stitched together as a side project between dashboard fixes.

If you want the faster path, our Data Analytics & Lakehouses work is built for exactly this kind of ERP ingestion problem, with extractors designed to survive rate limits, pagination quirks, and production-only behaviour. Book a call with Artigence and we can map the failure points in your current sync before they turn into missing records.