Katkat
Back

Design a Reliable Third-Party Data Ingestion Service

AttentiveSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteSystem Design

Last updated

Design an extensible, resilient data ingestion service capable of extracting data from heterogeneous third-party APIs while managing rate limits, pagination state, schema drift, and fault recovery.

Scenario

Many modern marketing and messaging platforms rely on integrating with external merchant systems (such as e-commerce platforms, CRM tools, and payment gateways) to sync customer profiles, product catalogs, and transaction events. External APIs vary widely in pagination styles, strict rate limits, network stability, and schema consistency.

Design a reliable, scalable third-party data ingestion service that pulls data from external partner APIs, buffers the raw data, and transforms it into a unified internal canonical format.

Requirements

Functional Requirements

  • Scheduled & On-Demand Syncs: Support scheduled incremental syncs as well as ad-hoc historical backfills.
  • Pagination & Checkpointing: Persist progress cursors/checkpoints so sync jobs can pause, resume, or recover from worker failures without starting over.
  • Raw vs. Normalized Decoupling: Ingest raw data first (ELT style) before transforming into standard canonical entities.
  • Schema Drift Handling: Handle unexpected, missing, or newly added fields without dropping payloads or halting ingestion.
  • Replayability: Support reprocessing previously ingested data if normalization logic changes or downstream systems suffer data loss.

Non-Functional Requirements

  • Reliability & Idempotency: Provide at-least-once ingestion guarantees paired with idempotent downstream writes to eliminate duplicate processing.
  • Tenant Isolation & Quota Management: Respect third-party per-tenant API rate limits. Ensure slow or failing merchant connections do not degrade processing for other tenants.
  • Observability: Expose clear metrics and alerts on sync lag, API quota exhaustion, failure rates, and dead-letter queue (DLQ) volume.

Scale Assumptions

  • Integrations: 25,000 active external connections across multiple platforms (e.g., Shopify, Salesforce, custom webhooks).
  • Volume: 300 million events/records ingested per day (~3,500 records/sec average, 15,000 peak).
  • External Limits: Partner APIs enforce varying quotas (e.g., 2 to 40 requests per second per tenant API key).

Key Areas to Cover

  1. High-Level Architecture: Decoupling the fetch/ingest layer, staging/raw storage, normalization pipeline, and delivery to downstream stores.
  2. State & Checkpoint Management: How to track incremental timestamps, cursor tokens, and job execution states reliably.
  3. Rate Limiting & Fair Scheduling: Mechanism for scheduling tasks while honoring external per-tenant quotas.
  4. Failure Modes & Error Handling: Retries with backoff, handling permanent vs. transient errors, and DLQ routing.
  5. Data Consistency & Schema Evolution: Strategy for raw payload preservation, partial failures within a page, and deduplication.

Hints

Hint 1

Decouple network-bound data fetching from compute-heavy normalization using a durable buffer (such as Kafka or an S3-backed event queue) to make retries and backfills cheaper.

Hint 2

Persist pagination cursors transactionally with the write of the corresponding page to durable storage to avoid skip-or-duplicate bugs upon worker restarts.

Hint 3

Use per-tenant queues or a distributed token bucket to throttle outbound requests before they hit external rate limits, avoiding cascading 429 penalties.

Follow-up questions

  • How does your architecture handle an ad-hoc 1-year historical backfill for an enterprise customer without starving incremental real-time syncs for other tenants?
  • If an external vendor abruptly changes a field type (e.g., from an integer to an array of strings), how do you detect and handle this drift without dropping data?
  • How would you handle third parties that enforce concurrency-based limits (e.g., max 5 parallel requests) instead of rate-based limits (requests per minute)?