ingestion

How it is built

Architecture overview

A data ingestion pipeline copies data from systems you do not control (SaaS APIs, operational databases, files) into a store you query (a warehouse, a lake, a search index), repeatedly and without losing or duplicating rows. It is built from three kinds of process: extractors that talk to a source, a loader that writes to the target, and a state store that remembers how far each stream has been copied. The one idea that makes it work is the checkpoint: the extractor emits records and, now and then, a state token, persisted only after the loader has durably written every record before it. After a crash the extractor restarts from the last token, so records may arrive twice but never go missing (at-least-once delivery), and the loader makes duplicates harmless with keys and merges. Airbyte states this contract for incremental syncs as "at least once" replication (Airbyte docs, sync modes).

Parts:

• Source connector — authenticates, reads pages or a change log, emits records and state — OAuth 2 and OpenID Connect, Secrets management, cursor-pagination (no page yet)
• Request governor — keeps the extractor inside the source's rate limit and retries failures politely — Token bucket, Exponential backoff with jitter
• Change capture — reads the source database's log instead of polling tables — Change data capture
• Backfill — copies history in bounded slices next to live changes — Chunked backfill
• State store — one position per stream or partition — Consumer offsets
• Schema manager — detects added, removed and retyped columns and decides what the target does — schema-evolution (no page yet)
• Staging format — records batched into files before loading — columnar-file-format (no page yet)
• Loader — applies a batch to the target tables exactly once in effect — upsert-merge (no page yet), Idempotency keys
• Error path — rows or batches that cannot be loaded are parked, not dropped — Dead-letter queue
• State durability — keeps the checkpoint and platform metadata alive through failovers and operator mistakes — Primary-backup replication, Point-in-time recovery
• Run supervision and service identity — keeps workers running, stops them without losing a batch, lets services trust each other — Process supervision, Graceful draining, Mutual TLS
• Verification and observability — finds parser and crash-timing bugs early and measures freshness — Coverage-guided fuzzing, Deterministic simulation testing, hdr-histogram (no page yet)

 source system ──► connector ──records + state──► (optional broker) ──► staging files ──► loader ──► target tables
 (API / DB / file)   │  ▲                                              (batches)       (merge)       │
                     │  └── rate limit, retry, auth ──┐                                              │
                     └── change log / cursor / chunk ──┘            state store ◄── "all records before token T are written"
                                                                          ▲
                              scheduler ── start, retry, backfill ────────┘            dead-letter queue ◄── bad rows

How the parts fit together

Source connector: pagination, cursors and credentials

Most sources are HTTP APIs that return pages. Offset pages (?page=7) skip or repeat rows when data changes between requests; a cursor (an opaque token or a sort key such as (updated_at, id)) continues exactly where the previous page ended. That makes cursor-pagination (no page yet) the extraction primitive; the same word names the incremental marker, the stored "highest updated_at seen so far" (Airbyte docs). A timestamp cursor has a trap: rows with the same timestamp straddling a boundary. Use a composite sort key or re-read a small overlap and let the loader deduplicate.

Credentials come from two places. User-authorised SaaS access uses OAuth 2 and OpenID Connect: the connector holds a refresh token and exchanges it for short-lived access tokens (RFC 6749). Databases use passwords or keys. Both go through Secrets management: the connector gets a secret by reference at run time, never in the job definition or logs, and refreshed tokens are written back to it.

Connectors speak a narrow contract to the platform. In Airbyte's protocol a source implements spec, check, discover and read; read takes the catalog and previous state and emits record and state messages as JSON lines on standard output. Singer is similar (SCHEMA, RECORD, STATE).

Request governor: stay under the limit, retry politely

Source APIs rate-limit by key, endpoint or account and answer 429 or 5xx (RFC 6585, RFC 9110). A Token bucket per credential in front of the HTTP client spends the allowed rate smoothly and allows a small burst, which is cheaper than discovering the limit by being rejected. When a request still fails, Exponential backoff with jitter spaces the retries; the AWS Architecture Blog comparison found that jitter cuts client work and server load. Retrying a write needs more than backoff: the retry must carry the same key (see the loader).

Change capture and backfill

Polling a table by updated_at misses hard deletes and rows updated without touching the column, and loads the operational database. Change data capture reads the database's write-ahead log instead: PostgreSQL logical decoding turns the WAL into an ordered stream of row changes that a replication slot holds until the consumer has acknowledged it (PostgreSQL docs, logical decoding). Debezium is the common open-source reader; it uses that stream for PostgreSQL and the binlog for MySQL (Debezium architecture docs).

Two consequences shape the pipeline. First, the slot is the source-side checkpoint and keeps WAL on disk until the consumer confirms, so a stopped consumer fills the source disk; it needs an alert and a size cap (PostgreSQL: max_slot_wal_keep_size, default unlimited, and idle_replication_slot_timeout). Second, the log holds changes, not history, so the first load needs a snapshot, and a new table or a repaired bug needs a backfill. Chunked backfill copies history in bounded key ranges, each its own retryable unit, and runs beside the live change stream; the loader's merge by primary key resolves the overlap, because a row can legitimately arrive from both paths and the version with the higher log position wins.

State store: where the checkpoint lives

The state is a small map from stream (and partition) to position: a cursor value, a log sequence number or the last chunk finished. This is the idea behind Consumer offsets in a message broker, and the same rule applies: commit the position only after the work it covers is durable. Airbyte's destination contract says this directly: a destination that emits a state message is declaring that every record before it was written, so the state it carries is safe to persist. Committing early loses data on a crash; committing late only re-reads. Kafka Connect keeps source connector offsets in a compacted topic (offset.storage.topic) and can provide exactly-once delivery for source connectors that support it (Kafka Connect user guide).

State must be inspectable and resettable per stream: resetting it is how you re-sync after a bug, so the loader must survive a replay. Where the state store lives and how it survives is the next part.

State durability: replication and point-in-time recovery

Losing the checkpoint loses no data (the pipeline re-reads) but costs a re-sync. Primary-backup replication keeps the state store and job metadata on one primary that orders writes and ships them to a standby. Because the merge makes replay harmless, an asynchronous acknowledgement rule is enough: a failover that drops a few checkpoints re-reads a few batches. Only the primary acknowledges state writes; a standby never accepts checkpoints on its own.

Point-in-time recovery covers mistakes that replication copies faithfully, such as a bad deploy that wrote wrong cursors: base backups plus the archived change log rebuild state as of any instant. Contract: restore state to a moment at or before the target's restore point; an older checkpoint only causes a replay, a newer one skips rows.

Schema manager and staging format

Sources change shape: columns are added, renamed, widened or dropped, API fields appear. schema-evolution (no page yet) is the policy for each case. Typical rules: a new column is added automatically; a widened type is applied; a dropped column keeps its history and is filled with nulls; an incompatible change pauses the stream and alerts, because guessing destroys data. For file and message formats the rules come from the format (Avro defines how a reader schema resolves against a writer schema). The catalog from Airbyte discover is the input to the diff.

Between extractor and loader, records are batched into files. A columnar-file-format (no page yet), in practice Parquet, makes the batch compact, typed and cheap to bulk-load. Parquet keeps min/max statistics per column chunk and page, so a reader can skip data (parquet-format README). JSON lines remain fine for small streams.

Loader: idempotent merge into the target

At-least-once delivery means the loader sees duplicates, so every write must be idempotent in effect. Two mechanisms combine:

• Upsert by primary key. upsert-merge (no page yet) loads the batch into a staging table, then runs a single MERGE (or delete-and-insert in one transaction) keyed on the primary key, so replaying a batch rewrites the same rows with the same values. Delta Lake and Iceberg document MERGE INTO for this. Without a primary key the only safe modes are append (keeps duplicates) and full overwrite (dlt: append, replace, merge).
• Idempotency keys for non-idempotent targets. When the target is an API rather than a table, send a key per logical write so a retry returns the stored result; see Idempotency keys. Stripe documents the pattern: the server stores the first result under the key and replays it. Within the pipeline, batch id plus stream position is the natural key.

Deleted rows need a tombstone: CDC emits a delete event, and the merge turns it into a delete or a soft-delete flag. A pure updated_at poll cannot see deletes; reconcile with a periodic key-only comparison.

Error path

Some rows will never load: a type that does not fit, a value over a size limit, a record the schema manager rejects. Failing the stream on one bad row blocks every row behind it; dropping it loses data. A Dead-letter queue takes the bad record with its error and context, lets the stream continue, and gives an operator a place to inspect, fix and replay. Kafka Connect exposes this as configuration (errors.tolerance, errors.deadletterqueue.topic.name; default fail-fast). Put a threshold on it: a high dead-letter rate signals a schema or mapping error and should stop the stream.

Run supervision, draining and service identity

Process supervision starts extractor and loader processes, reaps their exits and restarts them under a delay and burst limit, then stops them with SIGTERM before SIGKILL; without the limit a connector that dies at start-up loops against a rate-limited source. The supervisor restarts processes; the scheduler retries runs.

Graceful draining is what SIGTERM triggers in a deploy: the connector stops reading pages, the loader commits the batch in flight, the state store persists its position, and the process exits before the kill deadline. A late exit is still safe, since the merge turns the replay into a no-op.

Mutual TLS gives each internal hop (connector to broker, loader to state store) a verified, rotatable machine identity instead of a shared password. Secrets management holds the source credentials; mTLS covers the platform's own connections.

Verification and observability

Coverage-guided fuzzing targets code that parses bytes the platform does not control: source payloads, CDC messages, staging files and catalogs. A feedback-driven mutator finds the malformed record that crashes a connector before a source does.

Deterministic simulation testing runs source, connector, loader and state store in one thread under a seeded scheduler with simulated network, disk and clock. It injects a crash between the loader's commit and the state write at every point, checking "no row missing, none duplicated after merge"; a failing seed replays exactly.

hdr-histogram (no page yet) records per-stream latency and freshness (source commit to target commit) with bounded relative error. Workers record locally and the platform adds the counts, so percentiles survive aggregation.

Data and control flow

One incremental sync of a database table (CDC):

1. The scheduler starts the run; the platform loads the stream's saved state (a log position).
2. The connector opens a replication stream from that position and reads change events (insert, update, delete), ordered per table.
3. Events are batched by size or time (order of seconds to minutes) into staging files with the last log position.
4. The loader reads a staging file into a staging table and runs a keyed merge into the target in one transaction; deletes become deletes or flags. Rows that fail go to the dead-letter queue.
5. After the commit, the platform persists the batch's log position as state, and the connector acknowledges it to the source so the slot can release WAL.
6. A crash between steps 4 and 5 replays the batch; the merge makes the replay a no-op.

One first load plus backfill:

1. Record the current log position, then start the change stream so no change is lost.
2. Split the table by primary key range (Chunked backfill); copy chunks in parallel within the source's limits; each finished chunk is checkpointed.
3. Merge chunk rows and change events by primary key; the newer log position or version wins.
4. When all chunks are done and the stream has caught up, mark the table live.

Scaling

Pull the levers in this order:

1. The source's limits are the first ceiling: API rate limits and connection counts. Levers: multiple credentials where the terms allow, only the fields you need, incremental instead of full syncs, log-based CDC.
2. Parallelism across streams and chunks. Both are independent; run them as separate tasks, bounded by the token bucket and source connections.
3. Batch size and file format. Larger batches amortise per-commit cost in the warehouse; columnar staging files cut bytes moved. Airbyte reports 4-10x gains from replacing JSON through the orchestrator with a socket data channel (Airbyte protocol docs): a process in the middle can be the bottleneck, not the network.
4. The loader. Merge cost grows with target size unless it can prune by partition or cluster key; partition on the incoming key or date, or switch hot streams to append plus a deduplicating view.
5. A broker between extractor and loader (Kafka Connect, for example) absorbs bursts and lets several loaders read one stream, at the price of another system to run.

Failure modes

Real implementations

The alternative: query the source in place (federation). It avoids the pipeline but loads the operational database and cannot keep deleted history. Choose a pipeline for history, cross-source joins or isolation.

Sources

• Airbyte documentation: Airbyte protocol (actor interface, state and the destination state-message rule, socket data channel) and Incremental sync - append (cursor definition, at-least-once guarantee).
• Debezium documentation: Architecture.
• PostgreSQL documentation: Logical decoding and Replication settings (max_slot_wal_keep_size, idle_replication_slot_timeout).
• Apache Kafka: Kafka Connect user guide (offset topic, exactly-once source support, error handling and dead-letter queue configuration).
• dlt documentation: Incremental loading (write dispositions).
• Singer: specification.
• Apache Avro: specification (schema resolution).
• Apache Parquet: parquet-format (README: statistics).
• Delta Lake: MERGE (upsert); Apache Iceberg: Spark writes.
• Stripe: Idempotent requests.
• Exponential Backoff And Jitter, AWS Architecture Blog, 2015.
• IETF: RFC 6749 (OAuth 2.0), RFC 6585 (429 Too Many Requests), RFC 9110 (HTTP semantics, Retry-After).