Data Ingestion Strategies: APIs, Databases, and Event Streams Compared

Every data pipeline starts with ingestion — getting data out of source systems and into a format your pipeline can process. The technical approach varies significantly depending on whether the source is a REST API, a relational database, a message queue, or a SaaS product. Using the wrong ingestion pattern for a given source leads to reliability problems, excessive load on source systems, and pipelines that cannot scale.

This guide covers the main source types, the ingestion patterns that fit each, and when it makes sense to build custom ingestion versus using a managed connector.

REST APIs

REST APIs are the most common source for SaaS product data — CRMs, marketing platforms, payment processors, analytics tools, communication tools. They are also the most varied in terms of how they handle pagination, rate limiting, and incremental access.

Pagination patterns

Most APIs paginate results. Common approaches:

• Offset/limit pagination — GET /orders?limit=100&offset=0, then &offset=100, etc. Simple to implement but becomes inconsistent if records are inserted or deleted during pagination.
• Cursor-based pagination — the API returns a cursor token pointing to the next page. More reliable for large, frequently updated datasets.
• Link-based pagination — the API response includes a next URL in the response body or headers. Follow the link until no next URL is returned.

Always implement pagination handling for any API with more than a few hundred records. Missing it silently truncates the data.

Rate limiting

API rate limits are enforced per token, per endpoint, or per time window. Hitting a rate limit typically returns a 429 response with a Retry-After header indicating when to retry.

Implement exponential backoff with jitter for all API requests. Never retry immediately on a 429 — you will burn through quota faster and get a longer backoff. Honor the Retry-After header when provided.

Store a request counter per API and time window and proactively slow requests before hitting the limit rather than reacting to 429 errors.

Incremental access

Many APIs support filtering by a last-modified or created timestamp parameter. Use this to implement incremental ingestion — fetch only records newer than the last successful run's high-water mark. Not all APIs support this. When they do not, full pulls are unavoidable, and cost and duration scale with the total record count.

Relational Databases (Postgres, MySQL, SQL Server)

Databases offer more ingestion flexibility than APIs, with direct access to the storage engine.

Direct query ingestion

The simplest approach: connect to the database and run a SELECT query. For incremental ingestion, filter on a timestamp or ID column. For full loads on small tables, select everything.

Considerations:

• Run queries against a read replica, not the primary, to avoid impacting application performance.
• Add indexes on incremental key columns if they do not already exist. A full table scan on a large table for every pipeline run can be significantly slow and consume database resources.
• Use SELECT column lists rather than SELECT * to reduce data transfer and avoid surprises when source schemas change.

Change Data Capture (CDC)

CDC reads the database's replication log (binary log in MySQL, write-ahead log in Postgres) and captures every committed change — inserts, updates, and deletes — as a stream of events. This is the most complete ingestion strategy for databases.

CDC is appropriate when:

• You need near-real-time data (sub-minute latency)
• You must capture deletes, not just inserts and updates
• Table volume is too large for periodic queries to be practical

Debezium is the most widely used open-source CDC tool. Fivetran and Airbyte both offer managed database CDC connectors that handle log configuration, offset tracking, and schema change detection.

CDC requires database configuration: enabling binary logging in MySQL or WAL in Postgres, and granting the replication user the appropriate permissions. This is a one-time setup but requires coordination with the team managing the source database.

Event Streams (Kafka, Kinesis, Pub/Sub)

Event streams deliver data as it is produced rather than in batches. If your application emits events to Kafka or a similar system, ingestion from the stream is often the lowest-latency path to your data warehouse.

Stream-to-warehouse patterns

Micro-batch: A consumer reads from the stream in short intervals (every 30 seconds, every minute) and writes batches to the warehouse. This provides near-real-time freshness with the simplicity of batch loading. Tools like Spark Structured Streaming, Flink, and Kafka Connect JDBC sink connectors support this pattern.

Streaming insert: Some warehouses support streaming row-by-row inserts (BigQuery Streaming API, Snowflake Streaming Ingest). This achieves the lowest latency but is more expensive per row than batch loading and typically requires deduplication logic at query time.

Landing in object storage: The stream is consumed and events are written to S3 or GCS as Parquet files in time-partitioned directories. A separate pipeline then loads from object storage to the warehouse on a schedule. This is the most operationally simple pattern and works well when latency of minutes rather than seconds is acceptable.

Consumer offset management

Kafka consumers track progress via offsets. If a consumer crashes mid-batch, it must be able to resume from the correct offset without reprocessing events it already wrote (or handle duplicates via upsert if it does reprocess). The choice of auto.offset.reset behavior (earliest vs latest) affects what happens when a new consumer group starts — understand the tradeoffs before deploying.

Managed Connectors vs. Custom Ingestion

Managed connectors (Fivetran, Airbyte, Stitch) handle authentication, pagination, rate limiting, schema detection, incremental sync, and error handling for a library of common sources. For sources they support, they eliminate most ingestion engineering work.

Use a managed connector when:

• The source is a common SaaS product in the connector's catalog (Salesforce, HubSpot, Stripe, Shopify, Google Analytics, Postgres, MySQL, etc.)
• The connector's schema handling is compatible with your destination
• The cost of the managed service is less than the engineering time to build and maintain equivalent custom code

Build custom ingestion when:

• The source is a proprietary internal system or a source not in any connector catalog
• The managed connector does not support incremental sync for your specific source
• You need transformation or filtering logic during ingestion that the connector does not support
• Compliance requirements prevent data from passing through a third-party service

For most product teams, managed connectors cover a large portion of their sources. Custom ingestion is reserved for internal databases, proprietary data sources, and high-volume event streams where the managed connector's cost or control limitations are a constraint.

Designing an ingestion layer that balances reliability, cost, and coverage across diverse sources is one of the more nuanced parts of data platform architecture. If your team is building out ingestion for a growing data stack, Clixo can help you design the right approach for your sources and requirements.