Pulling data from a SaaS tool’s API looks easy. You write a loop, call an endpoint, dump the JSON. Then it runs in production, and you meet the real problems: the API returns only a hundred records at a time, throttles you after a burst, times out in the middle of page forty, and returns the same record twice because someone edited it while you were paging.

Managed connectors cover many popular APIs, and they are worth using where they fit. But sooner or later you will write one yourself, for an internal service or a tool without a connector. These are the pieces to get right.

Read the API’s Contract First

Before writing code, read the vendor’s current documentation and answer five questions:

• How is pagination done (page numbers, offsets, cursors)?
• What are the rate limits, and how are they communicated (headers, error codes)?
• Can you filter by a modified-since timestamp?
• How are deleted records represented, if at all?
• What authentication is used, and do tokens expire?

The answers decide your design. An API with no modified-since filter cannot support efficient incremental loads, and an API that never reports deletions means you need a periodic full comparison.

Pagination Patterns

Offset or page-number pagination is simple but fragile. If records are inserted or deleted while you page, later pages shift and you can skip or repeat rows. For large or fast-changing datasets, results can be inconsistent.

Cursor pagination returns an opaque token for the next page. It is much more stable under change and usually what you want when offered. Save the cursor after each successfully processed page so a failed run can resume rather than restart.

Whichever you use, never assume the last page is the one with fewer rows than the page size. Follow the API’s explicit signal, whether that is a missing next token or an empty response.

Respect Rate Limits Politely

Rate limits are a negotiated fact of life. Handle them in layers:

1. Throttle your own requests to stay below the documented limit instead of hitting it deliberately.
2. When you do receive a “too many requests” response, honor any wait time the API specifies.
3. Without a specified wait, use exponential backoff with some random jitter, so parallel workers do not all retry at the same instant.
4. Cap the total retries and fail loudly afterward.

Be careful with parallelism. Running ten workers against one API key shares one limit, and parallelism can make you slower if every worker keeps tripping the throttle.

Retries Need Idempotency

A retry is safe only if repeating the operation does not cause harm. Reading data with GET is generally safe to retry. Your load into the warehouse is where duplicates sneak in: if a page was written successfully but the acknowledgment was lost, a retry writes it again.

The fix is to make loads idempotent. Write each batch to a staging location keyed by run ID and page, then merge into the target on the record’s primary key. Rerunning the same page overwrites rather than duplicates.

Incremental Syncs

Pulling the full dataset every night is easy and wasteful, and many APIs would throttle you for it anyway. If the API supports a modified-since filter, store a high-water mark: the latest modified timestamp from the last successful run. Next time, request records changed after that mark, minus a small overlap window to catch records committed late. The overlap produces duplicates by design, which the merge-on-primary-key step resolves.

Update the high-water mark only after the whole run succeeds. If you update it as you go and the run fails halfway, you will skip data.

Also schedule an occasional full refresh or reconciliation count, since incremental syncs miss deletes and silent backdated edits.

Keep the Raw Response

Store the raw JSON exactly as received in a landing area, with the request time and parameters, before you flatten or transform it. When the vendor changes a field or you find a parsing bug, you can reprocess from storage instead of re-calling an API with limits. Treat any personal data in those payloads according to your organization’s governance rules.

Common Failure Modes

Symptom	Likely cause	Fix
Duplicate rows after a failed run	Non-idempotent load	Stage, then merge on primary key
Missing recent records	High-water mark moved too early or no overlap	Advance only on success, add overlap window
Run stuck repeating the same page	Offset pagination under inserts, or ignored cursor	Use cursors; validate next token advances
Frequent throttling errors	Parallel workers sharing one limit	Central throttle, backoff with jitter
Counts drift from the source over time	Deletes never captured	Periodic full reconciliation
Job fails after weeks of success	Expired credentials	Monitor token expiry, automate refresh

A Realistic Example

Imagine a team syncing support tickets from a helpdesk tool. The first version pages by number and does a full pull each night. Runs grow slow, and one night the job dies at page ninety. They redesign: use cursor pagination, store the cursor in a state table, request only tickets modified since the high-water mark minus an hour, land raw JSON, then merge into a tickets table on ticket ID. A weekly job compares total counts with the API to catch deletions. The nightly run shrinks from a long full pull to a short incremental one, and a mid-run failure now resumes from the saved cursor.

Log Enough to Debug at Night

Log the request parameters, page or cursor, response status, record count per page and elapsed time. When something fails you want to answer “which page, which call, what did the API say” in minutes. Never log secrets such as tokens.

Frequently Asked Questions

Should I use a managed connector instead? Where a maintained connector exists for your source and meets your needs, it usually saves effort. Write your own when none exists or when you need control the connector lacks.

What if the API has no way to filter by modified date? Then incremental loading is hard. Options include a full pull on a less frequent schedule, using webhooks if offered, or asking the vendor about export features.

How big should my retry limit be? Small enough to fail visibly within a reasonable time. A handful of attempts with growing delays is typical, then alert a human.

Can I paginate in parallel? Only if the API allows it and supports independent page requests, such as known offsets. Cursor-based pagination is inherently sequential.

How do I handle API versions changing? Pin the version if the API supports it, watch vendor deprecation notices, and keep raw responses so you can reprocess when you update parsing.

Next Step

Pick one API integration you run now and check three things: does a failed run resume or restart, does the load merge on a primary key, and when does the high-water mark advance? Fixing whichever answer is wrong prevents your next duplicate-data incident.