What is API Data Ingestion?

What is API data ingestion?

Most production data does not live in databases you can directly query — it lives behind APIs. Salesforce CRM records, Stripe payment events, GitHub repository data, Shopify orders, weather feeds, financial market data — all delivered via REST or GraphQL APIs with authentication, pagination, and rate limits.

API data ingestion is the engineering discipline of extracting this data systematically: fetching pages, handling errors, refreshing credentials, tracking what you have already ingested, and writing results idempotently to your warehouse or lake. Done well, it runs unattended for months. Done poorly, it fills your warehouse with duplicates or silently stops ingesting when a token expires.

The core loop is the same for every API: authenticate, fetch a page, validate the response, write to storage, advance the cursor, repeat. The complexity hides in the failure modes — 429s, schema drift, OAuth expiry, partial pages, network blips — and the production patterns that absorb them.

Why does API ingestion matter?

• 80% of business data lives behind APIs, not in databases you can query directly
• The hard part is not the HTTP call — it is reliability at scale (rate limits, token refresh, schema drift)
• Bad ingestion silently corrupts downstream marts; good ingestion is invisible
• Incremental extraction with watermarks turns a 6-hour full refresh into a 3-minute delta
• Idempotent writes survive network failures, retries, and replay without creating duplicates
• The patterns are reusable — once you have a Stripe connector pattern, you have a Shopify, Salesforce, and HubSpot pattern

How does API ingestion work?

A production ingestion job runs the same loop for every connector:

1. Authenticate — load credentials (API key or refreshed OAuth token), inject into headers
2. Fetch page — call the API with the current cursor + watermark parameters
3. Validate schema — parse the response into a typed model (Pydantic); route unexpected shapes to a DLQ
4. Upsert to lake — write records with a deterministic key so replays do not duplicate
5. Advance cursor — persist the new cursor + max-watermark for the next run
6. Loop or exit — repeat until the API signals no more pages

Here is the rate-limit-aware fetch with exponential backoff:

import httpx, time, random
from pydantic import BaseModel

class OrderRecord(BaseModel):
    order_id: str
    amount_usd: float
    created_at: str
    status: str

def fetch_all_orders(since: str) -> list[OrderRecord]:
    records, cursor = [], None
    while True:
        resp = fetch_page_with_backoff(since=since, cursor=cursor)
        records += [OrderRecord(**r) for r in resp["data"]]
        cursor = resp.get("next_cursor")
        if not cursor:
            break
    return records

def fetch_page_with_backoff(**params) -> dict:
    for attempt in range(5):
        r = httpx.get(API_URL, params=params, headers=auth_headers())
        if r.status_code == 429:
            wait = (2 ** attempt) + random.random()
            time.sleep(wait)  # exponential backoff + jitter
            continue
        r.raise_for_status()
        return r.json()

For long-running jobs against OAuth 2.0 APIs, you also need automatic token refresh — a 401 mid-run should trigger a re-auth, not a pipeline failure.

Polling vs webhook vs CDC

The right answer is usually both. Use webhooks for low-latency change capture and a nightly polling job for backfill + reconciliation. Stripe's events API is the canonical example — push for immediate handling, pull for completeness.

The 6 core challenges of production API ingestion

Every production connector eventually hits the same six problems. The patterns that solve them are well-known:

• Pagination — APIs return data in pages. Cursor-based is most reliable; offset breaks under concurrent inserts. Always prefer cursor or keyset for production.
• Rate limiting — every API has a quota. Handle 429s with exponential backoff + jitter. Proactively track the request budget with a token bucket.
• Authentication refresh — OAuth 2.0 access tokens expire. Build automatic refresh into your HTTP client so long-running jobs do not die on a mid-run 401.
• Schema changes — external APIs change response shapes without notice. Validate against a Pydantic schema and route unexpected fields to a staging area instead of failing hard.
• Incremental extraction — full refresh is expensive and wasteful. Use watermark columns (updated_at) and the API's since/after parameters to fetch only new or changed records.
• Idempotent writes — network failures mean your pipeline may replay the same response. Write with UPSERT or deduplication keys so replays do not create duplicate rows.

Common mistakes (and what to do instead)

• Full refresh on every run — re-fetching the entire dataset wastes quota, increases run time, and can exhaust rate limits. Use watermark-based incremental from day one.
• No backoff on 429 responses — retrying immediately burns through your remaining quota and makes the throttle worse. Always implement exponential backoff with jitter.
• Ignoring schema validation — external APIs are uncontrolled surfaces. A silent field rename can corrupt downstream tables for weeks. Validate every response on ingestion.
• Storing raw JSON blobs without normalization — JSON in a warehouse makes downstream queries painful and expensive. Flatten to typed columns at ingestion time.
• Hardcoded credentials — API keys in code leak through git history. Always pull credentials from a secrets manager (AWS Secrets Manager, Vault, 1Password).
• No dead-letter queue — when a record fails validation, dropping it loses data; failing the run loses everything. A DLQ + alert lets the pipeline keep flowing while you triage.

Who is API ingestion for?

API ingestion is a foundational data engineering skill. Every modern data team owns at least a handful of API connectors, and the level of mastery scales with seniority:

• Junior data engineer — makes authenticated HTTP requests, handles basic pagination, uses Airbyte or Singer connectors, understands REST vs GraphQL
• Senior data engineer — builds rate-limit-aware ingestion with backoff, implements watermark-based incremental sync, validates schemas with Pydantic, designs idempotent write strategies
• Staff / platform engineer — designs multi-source ingestion platforms, defines connector standards across teams, architects webhook fan-out at scale, manages API credential rotation and security

Teams that benefit most: SaaS-heavy data stacks (Stripe + Salesforce + HubSpot + Shopify), marketing analytics (every ad platform is an API), platform teams building internal ingestion frameworks, and any team where Airbyte/Fivetran is too expensive at volume.