Data Ingestion
Moving data out of systems you do not control and into storage you do — and the difference between what arrived and what happened.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What has to be true for the records that land in my storage to be exactly the records that occurred in the source — no fewer, and no more?
Every transformation, model and dashboard after this point. None of them can see the source; all of them will assume that what landed is what happened. The immediate consumer is the raw layer and the staging models built on it, and what they need is not speed but a stated, checkable answer to "is this period complete".
One extracted record: one source row as of the moment it was read, one change event, one log line, one API item. Note the qualifier — a batch extract's grain is a row *at a point in time*, not a row, and two extracts of the same table produce two records for the same entity. That distinction is the source of most double counting downstream (Grain: What Does One Row Represent?).
A scheduled script: connect to the source, SELECT * FROM orders WHERE created_at >= yesterday, write the result to the warehouse, done. It is fifteen lines, it needs no infrastructure, and for a single table on a single database it works for months. Most good pipelines started exactly here and there is nothing wrong with starting here.
The script runs at 02:00, the source is down for maintenance at 02:00, and the run fails. Tomorrow's run asks for *yesterday* again, so the missed day is never requested by anything, ever. The gap is permanent and nothing in the system is red (Ingestion Failure & Recovery).
- The script runs at 02:00, the source is down for maintenance at 02:00, and the run fails. Tomorrow's run asks for *yesterday* again, so the missed day is never requested by anything, ever. The gap is permanent and nothing in the system is red (Ingestion Failure & Recovery).
- The predicate uses
created_at, so every row updated after creation — a cancelled order, a corrected amount — is extracted once with its original values and never again. The warehouse holds a faithful copy of what the source looked like on day one (Incremental Extraction). - The extract is retried after a network error halfway through the write. The first half is already in the warehouse. Every row in it now exists twice, and
SUM(amount)is quietly too high (Duplicate Rows). - The source adds a column.
SELECT *picks it up, the warehouse table does not have it, and the load fails — or worse, the loader is permissive, silently drops it, and a field somebody needed has been discarded for six months (Schema Evolution). - Volume grows and the extract now holds a long-running read against the production database during the busiest hour, competing with the application for the same buffer pool and locks (Workload Isolation).
- The source is a SaaS API rather than a database. It paginates, it rate-limits, and its notion of "modified since" is documented in one sentence that turns out to mean something different from what you assumed (Ingestion Sources).
What is actually happening
- Ingestion is a pull against a system with its own agenda, or a push from a producer that has already forgotten what it sent. Either way you do not own the retention, the ordering, the clock, or the definition of "changed". Every ingestion design is a negotiation with those four things.
- The core problem is *bounding*. You cannot read everything every time past a certain size, so you must express "everything since last time" as a predicate — and every available predicate is an approximation. A timestamp approximates commit order. A sequence approximates change order. A log position is the only one that is exact, and only some sources have one (CDC vs Polling).
- The second problem is delivery. Anything that can fail mid-write and be retried delivers at least once by default. Getting to effectively-once requires either an idempotent write keyed on something stable, or a transactional sink — and it is a property of the *write*, never of the transport (Idempotent Data Pipelines).
- The third is arrival versus occurrence. Ingestion stamps records with when they arrived. The business cares when they happened. Those differ by a variable amount and the difference is where late data lives (Event Time, Late-Arriving Data).
- What ingestion is *not* is transformation. The moment the extract starts filtering rows, renaming fields or fixing types, it has made a decision that cannot be revisited without going back to a source that has since changed (The Raw Landing Zone).
The three hops, and what each one actually promises
Ingestion is usually drawn as one arrow, which is why its failures surprise people. It is at least three separate things: reading from a source, moving the bytes, and committing both the data and the record of how far you got. Each has its own failure mode, and the interesting bugs live in the seams between them rather than inside any one.
The seam that matters most is the last one. If the bookmark is advanced before the data is durable, a crash between the two turns a transient failure into permanent loss — and nothing observes it, because the next run starts from a bookmark that says the work was done. If the bookmark is advanced after, a crash produces duplicates, which is a bounded and detectable problem. Given the choice, choose duplicates.
Read the guarantee column below as a chain. The end-to-end promise of your ingestion is the weakest link in it, not the strongest, and the weakest link is usually the one nobody wrote down.
- 1Bound the window
Turns "everything since last time" into a predicate: a timestamp range, a sequence range, a log position, a cursor.
guarantees Only that the predicate is what you asked for. Whether it corresponds to "everything that happened" depends entirely on the source's commit and clock semantics.
fails by Excluding rows that committed after their timestamp was assigned, and excluding hard deletes always.
- 2Read from the source
Executes the query, walks the pages of the API, or reads forward from a log offset.
guarantees A consistent view only if the source gives one — a single snapshot-isolated query does, a paginated API walked over ten minutes does not.
fails by Rate limits, timeouts mid-pagination, and a result set that shifts underneath a cursor while the source keeps writing.
- 3Move the bytes
Streams the result to object storage or the warehouse staging area.
guarantees At-least-once. A retried transfer will re-send what it already sent, because it cannot know what landed.
fails by Partial writes that look like complete small files; a retry that appends beside the first attempt rather than replacing it.
- 4Commit the data
Makes the landed files or rows visible to readers.
guarantees Atomic per window only if you built it that way — a directory rename, a manifest write, a single transaction.
fails by Consumers reading a half-published window and seeing a real-looking but incomplete period.
- 5Advance the bookmark
Records how far this run got, so the next one knows where to start.
guarantees Correct restart position — but only if it is written after the data is durable, and only if it is written at all when a run legitimately produced zero rows.
fails by Advancing on an empty or failed run, which is the mechanism behind most permanent gaps.
Order matters: data durable, then bookmark. Reversing those two is a one-line change that converts every crash into silent, unrecoverable loss.
Why ingestion cannot be trusted to notice its own failures
Almost every ingestion failure produces a successful run. That is not a quirk of any particular tool; it follows from the shape of the problem. The pipeline knows what it asked for and what it received. It does not know what it *should* have received, because that information only exists in the source.
A predicate that excludes a slice returns fewer rows, successfully. A source that is down returns zero rows, successfully, if the query is against a replica that is up but empty for that range. A retry that duplicates returns more rows, successfully. In every case the exit code is zero, the table has data, and the only signal available is a comparison against something outside the pipeline.
This is why reconciliation against the source is not an optional maturity item but the first check worth building. It is also why it must run against a closed period: comparing an open window means comparing two moving targets and generating alerts nobody can act on, which trains people to ignore the one that matters.
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Reconcile source and raw row counts for a closed window | Everything that happened in that period arrived. | Missed windows, truncated extracts, dropped pages, predicates that exclude a slice, half-committed loads. | Open periods, so late arrivals never appear; duplicates offsetting losses within the same window; and any error where the source itself is wrong. |
| Uniqueness on the source primary key within raw for a window | Each source record was landed once for this window. | Retried batches, overlapping windows that were not deduplicated, a connector restarting from an old bookmark. | Duplicates across windows — the same order re-extracted tomorrow is unique within each day and doubled across them. |
| Bookmark monotonicity and movement | Ingestion is still making progress against this source. | A stalled connector, a bookmark advanced on an empty run, a bookmark reset by a redeploy. | A bookmark that advances correctly past data it never actually read — movement is not evidence of completeness. |
The first is the only one of the three that looks outside your own system, which is exactly why it is the one most often skipped and the one that catches the failures that matter.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Source unavailable during the scheduled window | One run fails, then everything is green again. Days later a chart has a notch in it. | The schedule is absolute, not catch-up: tomorrow's run asks for tomorrow's window and nothing ever re-requests the missed one. | Make windows explicit parameters and drive them from the bookmark rather than from the clock, so a failed window is retried by definition rather than by somebody remembering. |
| Extract times out after writing half its output | Row count for the window is high after the retry, and revenue for that day is above trend. | The retry re-read the whole range and appended beside the partial first attempt. | Write to a window-scoped path that a re-run replaces wholesale, or merge on the source key rather than appending (Upserts and Merges). |
Row updated in the source after its updated_at was assigned but before commit | A single order is missing from the warehouse and present in the source. Reconciliation catches it; nothing else does. | The predicate reads a timestamp assigned at statement time while visibility happens at commit time — the two are not the same instant (Incremental Extraction). | Overlap the windows, or extract from the change log where the ordering is commit ordering by construction (CDC vs Polling). |
| Row hard-deleted in the source | The warehouse count exceeds the source count and keeps drifting upward, slowly, forever. | No timestamp predicate can observe a row that no longer exists. Incremental extraction is structurally blind to deletes. | Ask the source for soft deletes, take a periodic full key snapshot and diff it, or read the log where the delete is an event (What a CDC Event Contains). |
| API changed its default page size | Volume dropped by a predictable fraction on one day and nobody noticed for a month. | The client assumed a page count instead of following the cursor to exhaustion. | Always paginate to the terminal condition the API defines, and assert that the item count matches any total the API reports (Pagination: Choosing How Lists End). |
Extract, then stop
The strongest single rule in ingestion is that the extract does nothing except move bytes. No filtering, no renaming, no type coercion, no dropping of columns that "we do not use", no dedup, no business logic. It sounds wasteful and it is the reason platforms recover from mistakes.
The argument is asymmetric. If you land everything and later decide you did not need a field, you delete it — a cheap, reversible operation. If you filter at extraction and later discover the filter was wrong, the data you excluded is in a source that has since been updated, aged out, or both. One direction costs storage; the other costs the data.
The counter-argument worth taking seriously is governance: landing everything means landing personal data you have no purpose for, and "we might need it" is not a lawful basis. That tension is real and is resolved by classifying at the boundary and excluding specific known-sensitive fields deliberately — not by letting each connector's author decide what looks useful (PII in Pipelines).
The extract query selects seven of the source's twenty columns, casts `amount` to an integer, filters to `status = 'completed'`, and lowercases the email. The warehouse receives clean, small, ready-to-use rows.
The extract selects everything the source returns and writes it untouched to a path keyed by arrival window. A separate, versioned transformation selects seven columns, casts, filters and lowercases — reading from raw, writing somewhere else.
The two differ in exactly one property: whether the input to the transformation still exists after the transformation is found to be wrong. When it turns out status had a fourth value nobody knew about, the second setup fixes six months of history with a re-run against data you already have; the first has to re-ask a source whose rows have moved on, and for hard-deleted or aged-out records it cannot ask at all (Keeping Raw History: The Recovery Position and the Liability).
How to build it
Most important first.
- Decide the extraction predicate first and write down what it can miss. "We extract on
updated_atwith a five-minute lookback; this misses hard deletes and any row whose commit lagged its timestamp by more than five minutes" is a real design. "We extract incrementally" is not (Incremental Extraction). - Land raw before you touch anything. The extract's only job is to move bytes into storage you control, partitioned by when they arrived (The Raw Landing Zone).
- Make the load idempotent on a key the *source* owns — a primary key plus a version, or a log offset. Idempotency keyed on something you generated during ingestion is not idempotency, it is a new row every run (Deduplication).
- Store the extraction bookmark transactionally with the data, or after it. A bookmark advanced before the write commits converts a crash into permanent data loss (The High-Water Mark).
- Isolate the read. Use a replica, a snapshot, or the change log rather than the primary — analytics competing with checkout is a production incident waiting for a busy day (Replication and Read Scaling).
- Reconcile against the source on a closed period, per source, on a schedule. It is the only check that can detect a systematically missing slice, because everything internal to your pipeline agrees with itself by construction (Reconciliation).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Delivery: at-least-once is the default for anything that retries, which is everything. At-most-once is what you get if the bookmark advances before the write commits, and it is almost never what you wanted. Effectively-once is achievable at the *sink* by keying the write on a stable source identifier — it is a property of the write, not the pipe (At-Least-Once Delivery).
- Ordering: a batch extract gives no ordering at all — the result set is a set. A log-based stream gives order within a partition and none across partitions. If a downstream model computes "latest state", it must define latest by a source-provided value and never by arrival (CDC Ordering and Transaction Boundaries).
- Completeness: nothing in the ingestion path guarantees it. Completeness is measured against the source, after the fact, or it is assumed (Missing Rows).
- Atomicity: a batch of files is not atomic unless publishing makes it so. Partway through a load, consumers can see a genuinely partial period and it looks exactly like a quiet day (Atomic Publish).
- Durability: once the bytes are in object storage they are as durable as that storage. Before that they exist only in the source's retention window, which is somebody else's decision (Retention and Replay).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Row count per extraction window compared with a count run against the source for the same closed window. It catches a truncated extract, a predicate that excluded a slice, a page of an API that was silently dropped, and a load that half-committed.
- It misses anything where the source count is itself the problem, any window that is still open (which is where late arrivals live), and duplicates that happen to offset losses in the same window — an uncomfortably common coincidence when a retry re-reads a range.
- Pair it with a uniqueness assertion on the source primary key in raw. Together they bound the two failure directions; separately, either one can be satisfied while the data is wrong (Data Tests).
- And note what neither can say: that the values are right. An extract that faithfully copies a column the source populated incorrectly passes every ingestion check there is (The Pipeline Succeeded. The Data Is Wrong.).
- Ingestion sets the floor for everything downstream. A consumer can never be fresher than the ingestion interval plus the extraction lag, and every stage after this only adds.
- The shape a batch extract gives is sawtooth: data is at its stalest just before a run and at its freshest just after, so the average and the worst case differ by a full interval. Quoting the average to a consumer misleads them about the moment they will actually be reading.
- A streaming ingest gives a continuous shape, where staleness is consumer lag rather than schedule. That is a different failure surface, not a strictly better one — a lagging consumer degrades smoothly and silently, while a missed batch is discrete and visible (Batch vs Streaming Ingestion).
- The honest question is what decision the data drives. A daily finance close does not benefit from minute-level ingestion, and paying for it buys operational burden with no consumer (Cost vs Freshness).
- Source schemas change without notice, because the source team does not know you exist. The design question is not how to prevent it but where to absorb it: raw should tolerate unknown fields, and the transformation should fail loudly the first time an assumption it makes stops holding (Contract Enforcement).
- A column added upstream is harmless if raw is schema-tolerant and catastrophic if the loader is strict and the pipeline is on the critical path for a morning report. Choose which of those you are running deliberately.
- The dangerous change is the one that types cleanly.
statusgaining a new enum value, oramountswitching from gross to net, breaks every metric and violates no schema (Semantic Changes). - Changing the *extraction method* is itself a schema change for history. Switching from a nightly full snapshot to CDC changes what one row means, and the two periods are not comparable without saying so (Snapshot and Stream: the Bootstrap Problem).
- Re-running an ingestion window must be safe by construction, because you will do it under pressure. That means an explicit range parameter, a write keyed on the source identifier, and a landing path that includes the window so a re-run overwrites its own output rather than appending beside it (Idempotent Data Pipelines).
- Recovery is bounded by whatever the *source* still holds. A database you can re-query for old rows is recoverable; a log with seven days of retention is recoverable for seven days; an API that only exposes "changed in the last 24 hours" is recoverable for one day and after that the data is gone (Retention and Replay).
- Backfilling further than the current window means reasoning about what the source looked like then versus now. A re-extract of last March returns March's rows *as they are today*, which is not what March's extract produced, and publishing it as a fix silently rewrites history (What Backfills Break).
- Keep the bookmark and the landed data in the same recovery story: rolling data back without rolling the bookmark back creates a gap that will never be filled (The High-Water Mark).
What can go wrong
- A run fails, the schedule moves on, and the missed window is never re-requested. The single most common permanent data loss in this domain, and it produces no error after the first one.
- A retry duplicates a partially-written batch. The pipeline is green, the count is high, and the discrepancy is attributed to a good sales day.
- The extraction bookmark advances on a run that wrote nothing, converting a transient source outage into a silent permanent gap.
- The source rate-limits or times out under the extract's own load, so the extract becomes the cause of its own failure and retries make it worse (Rate Limiting, Retry Storms: The Load You Generated Yourself).
- The mitigation fails: a lookback window designed to catch stragglers is shorter than the source's worst-case commit lag, so it catches most of them and hides the rest behind a mechanism everyone believes is working.
- Credentials rotate, the connector fails authentication, and the alert goes to a channel nobody reads because ingestion has been reliable for a year.
- "The connector says it is exactly-once." Ask which of the three it means — input consumption, state update, or output write. It usually means the *consumer offset* and the *write* are committed together for one specific sink; it says nothing about the producer, and nothing at all about a source that generated the same logical event twice (Exactly-Once: Input Consumption, State Update, Output Write).
- "The job succeeded, so the data arrived." The job succeeded means the code exited zero. An extract that returned zero rows because a predicate broke succeeds faster than one that works (The Pipeline Succeeded. The Data Is Wrong.).
- "We can just re-extract if something goes wrong." Only if the source still has it, and only if the source has not changed since. Both assumptions fail routinely and neither fails loudly.
- "Ingestion is the easy part." Ingestion is the only part where the failure is *unrecoverable*, because it is the only part where the original still exists somewhere you do not control.
- "Cleaning during ingestion saves a step." It removes the evidence. The first time a transformation is wrong, the untouched copy is what turns a re-run into a fix rather than a loss (Keeping Raw History: The Recovery Position and the Liability).
- Ingestion is where personal data enters your platform, and landing raw means landing all of it — including fields the source collects and you have no use for. That is a deliberate trade against reprocessability and it should be a decision somebody made, not a side effect (Data Minimization).
- The raw zone inherits every retention and deletion obligation the source had, without any of the mechanisms that enforced them there. A deletion request is now your problem in a place with no primary key index (Deletion Requests).
- Credentials to read a production source are among the most powerful in the platform and are frequently the least rotated, because rotating them breaks ingestion and ingestion is on the critical path for a morning report (Secrets Management).
Operating it
- Rows extracted per window per source, on one chart with its own history. A zero where there should not be one, and a spike where nothing changed, are both visible in the same view (Volume Anomalies).
- Extraction lag: the gap between the newest source record and the newest landed record, per source. This is the number a consumer actually experiences and it is different from "did the job run" (Freshness Monitoring).
- The current bookmark value per source, exposed as a metric, so a bookmark that has stopped advancing is visible before somebody notices the dashboard is flat (Pipeline Metrics).
- Errors and retries against the source, separated by kind — auth, timeout, rate limit, schema — because the response to each is different and an aggregated error count tells you none of them (Retries in Pipelines).
- At 10x volume the full extract stops fitting in its window and incremental becomes mandatory rather than an optimisation — which introduces bookmarks, late data and the whole class of problems that comes with them (Incremental Processing).
- At 100x the load on the source becomes the binding constraint rather than your own compute, and reading the database's change log instead of querying it stops being an architectural preference and becomes the only option (Change Data Capture).
- Source *count* scales worse than source volume. Twenty tables from one database is one connector; twenty SaaS systems is twenty different auth models, pagination schemes, rate limits, deletion semantics and support tickets (Ingestion Sources).
- Frequency scales file count, and file count scales the read cost of everything downstream. Ingesting every minute without compaction moves the cost from ingestion to every query that follows (File Compaction).
- The load on the source is the cost nobody puts on a dashboard, and it is often the largest one: a full-table scan against a production primary competes with the application for exactly the resources it needs most (An Index Scan Is Not Automatically Faster).
- Bytes moved across the network, especially between regions or out of a provider. Extracting a whole table nightly to find the small fraction that changed pays egress on all the rest, every night, forever (Egress: Moving Data Costs Money, Not Just Storing It).
- Bytes retained in raw. Individually cheap, permanently accumulating, and the driver that nobody notices until a retention conversation is forced (Storage Lifecycle).
- Small files. A frequent extract that writes a tiny file per run creates a metadata and listing cost that grows with run count rather than with data, and eventually dominates the read side (File Size and the Small-Files Problem).
- Landing raw untouched buys full reprocessability and costs storage, a governance surface containing fields you do not use, and a layer that consumers must be actively prevented from querying directly.
- Idempotent loads keyed on a source identifier buy safe re-runs and cost a merge instead of an append — more expensive per row, and requiring the source to actually have a stable key, which some do not.
- Isolating the read onto a replica or the change log buys production safety and costs a lag you now have to reason about, plus a second failure mode when replication itself falls behind (Replication Lag: Reads That Are Correct and Stale).
- Every guarantee you add here — reconciliation, lookback windows, deletion detection — costs source load and complexity. Adding all of them to every source is how a two-person team ends up maintaining forty connectors and trusting none of them.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe four negotiations — bounding, delivery, ordering, arrival-versus-occurrence — apply to every source type. What changes is which of them the source helps you with: a database log answers all four, a SaaS API answers roughly one and a half.
- SOURCE-SPECIFICPostgres exposes a logical replication slot that holds position for you and applies backpressure by retaining WAL; MySQL binlog positions are yours to track and the binlog expires on the server's schedule; a typical SaaS API offers only a "modified since" filter with no ordering, no deletes and no way to prove completeness.
- SCALE-SPECIFICBelow a few million rows a nightly full extract is the correct answer and everything in this lesson about bookmarks is premature. The advice inverts when the extract stops fitting its window, or when its load starts being visible in the source's own latency.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns the delivery semantics this lesson depends on — at-least-once, at-most-once, and what makes a write idempotent across a network that can duplicate and reorder. That domain is being built separately.
- — DevOps / Production Engineering owns how a connector is deployed, how its credentials are rotated without an outage, and how a schedule change is rolled back.