Ingestion Sources
Databases, APIs, logs, files, event streams, SaaS systems and object storage — seven extraction models, seven failure behaviours, and seven different meanings of "everything since last time".
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.
For this particular source, what can I actually ask it, what will it refuse to tell me, and how would I know if a record never arrived?
The person who has to answer "is this dataset complete?" during an incident. They need to know, per source, which failure modes are possible and which are structurally impossible to detect from inside the pipeline — because that determines whether the answer is a query or a shrug.
The grain is set by the source and it is rarely a business entity. A database extract gives one row-as-of-read. A change log gives one change. An API gives one item as the API models it, which may be a nested document containing what your warehouse will treat as three tables. A log file gives one line, which may be one third of one event (Grain: What Does One Row Represent?).
Treat every source as a table. Write a connector interface with read(since) and implement it seven times. The abstraction is clean, the code is uniform, and everything downstream can pretend all sources are alike.
The interface has a since parameter, but "since" means commit position in the log source, wall-clock updated_at in the database source, an opaque cursor in the API source, and a filename prefix in the file source. Only one of those is exact, and the interface has erased the difference (Incremental Extraction).
- The interface has a
sinceparameter, but "since" means commit position in the log source, wall-clockupdated_atin the database source, an opaque cursor in the API source, and a filename prefix in the file source. Only one of those is exact, and the interface has erased the difference (Incremental Extraction). - Deletes exist in three of the seven and are invisible in the other four. Code written against the uniform interface has no place to represent one, so nothing downstream ever learns that a row went away.
- The SaaS source rate-limits. The uniform retry policy — retry three times, back off — turns a soft limit into a hard ban for the whole account, including the application that actually depends on it (Rate Limiting).
- The event-stream source is push, not pull. Wrapping it in a pull interface means buffering somewhere, and the buffer is where records are silently dropped under load (Backpressure).
- The file source drops a file into a bucket, and the connector reads it while it is still being written. Half a CSV parses cleanly, because CSV always does (CSV, JSON and Their Limits).
- The object-storage source is re-uploaded by its producer with a corrected version under the same key. The connector, which tracks filenames, never re-reads it and holds the wrong data forever.
What is actually happening
- Every source answers three questions differently, and those three answers are the whole taxonomy: how do I bound a read (predicate, cursor, offset, listing), how do I learn about a delete (log event, tombstone, snapshot diff, or never), and what happens when I read too hard (queue, throttle, fail, degrade the source).
- Databases can be read two ways and the choice is architectural. Querying them asks the current state and gets an approximation of change; reading their replication log gets the changes themselves, in commit order, without competing with the query workload (Change Data Capture, Write-Ahead Logging).
- APIs give you the vendor's model of change, which is usually a
modified_sincefilter with unstated semantics — is it inclusive, is it the server's clock, does it cover related sub-objects, does it include deletes? Pagination over a shifting result set can skip and repeat items, and both are silent (Cursor Pagination: An Opaque Bookmark, Not a Position). - Logs and files are append-oriented and their unit of completeness is a file, not a record. The critical question is how you know a file is *finished*: a rename from a temporary name, a marker object, or a notification. Reading by "it exists" reads partial writes.
- Event streams invert control: the producer decides when data arrives, and your only lever is how fast you consume. Everything you know about the past is bounded by the broker's retention rather than by your storage (Retention and Replay).
- SaaS systems are APIs plus organisational reality. The schema changes when the vendor ships, the rate limits are shared with whatever else in your company uses that API, and the "export" endpoint that gives you everything is often the only complete answer available.
Seven sources, three questions
The useful way to categorise a source is not by technology but by what it will tell you. Three questions separate them completely, and answering them for a new source takes an hour and saves a quarter.
How is change bounded? A log gives an exact position. A database query gives an approximation based on a column. An API gives whatever the vendor implemented. A file listing gives names and modification times, which are the producer's claims rather than facts.
How do deletes appear, and what happens when you read too hard? These two decide, respectively, whether your row count can drift upward forever, and whether your ingestion job can become the cause of an outage in a system you do not own.
| Source | How change is bounded | Deletes | Under load | Completeness check |
|---|---|---|---|---|
| Operational database (query) | A predicate on a timestamp or sequence column, evaluated at read time. | Invisible unless the source soft-deletes. | Competes with the application for buffer pool, locks and CPU. | Count and sum reconciliation against the source for a closed window. |
| Operational database (log) | A position in the replication log — exact, and in commit order. | Appear as delete events, with the key and sometimes the old row. | Almost no query load; the cost is log retention on the server. | Log position continuity plus a periodic snapshot diff. |
| HTTP / REST API | A vendor-defined filter or cursor, with vendor-defined semantics. | Usually invisible; sometimes a status field; rarely an event. | Rate limits, then throttling, then bans — often shared with other consumers. | Assert item count against any total the API reports; periodic full listing of keys. |
| Application logs | File rotation plus byte offset within a file. | Not a concept — logs are append-only by nature. | Disk and shipping bandwidth on the machine producing them. | Sequence-number gaps if the producer emits them; otherwise volume trend only. |
| File drop / batch export | File name, modification time, or an explicit completion marker. | Not a concept unless the export is a full snapshot each time. | Nothing — the producer already paid the cost. | Expected-file arrival by schedule, plus a row count in a manifest. |
| Event stream / broker | A consumer offset per partition. | Only if the producer models a delete as an event. | Consumer lag grows; the broker is largely indifferent. | Lag per partition, plus coverage of every partition, plus offset continuity. |
| Object storage | Listing plus object version id or ETag. | A delete marker, if versioning is on; otherwise the object is simply gone. | Listing large prefixes is itself the expensive operation. | Object count and total size per prefix against the producer's manifest. |
What "everything since last time" means, source by source
The phrase is the same in every design document and means something different in every implementation. This is the single largest cause of silently incomplete ingestion, because the code reads identically regardless of which meaning applies.
Consider the strongest and weakest ends. A log position is a statement about the source's own commit sequence: everything after position N is, by construction, everything that committed after that point, in order, including deletes. There is no approximation anywhere in that sentence.
An API filter on modified_since is a statement about a column, maintained by application code, on a clock you do not control, covering whatever objects the vendor decided it covers. Every one of those clauses is a place a record can hide, and none of them raises an error.
Between those extremes sit the interesting cases, and the point of the pipeline below is that the guarantee degrades in a specific, nameable way at each step — not that some sources are simply worse.
- 1Log position
Reads forward from an offset in the source's own commit log.
guarantees Every committed change, once, in commit order, including deletes. The only exact answer available.
fails by Retention: fall further behind than the server keeps, and the changes are gone rather than late (CDC Failure Modes and the Retention Deadline).
- 2Broker offset
Reads forward from a consumer offset in a partition.
guarantees Everything the producer published to that partition, in order, within retention.
fails by Saying nothing about what the producer failed to publish, and giving no order across partitions (CDC Ordering and Transaction Boundaries).
- 3Monotonic sequence column
Selects rows with a source-assigned id or version greater than the bookmark.
guarantees Every row whose sequence was assigned after the bookmark — provided the sequence is assigned in commit order, which for gap-free sequences it is not.
fails by Rows with a lower sequence committing after a higher one, so the window closes over a gap that still had a writer in it.
- 4Timestamp column with lookback
Selects rows whose
updated_atfalls in an overlapping window.guarantees Every row whose commit lag was shorter than the lookback. Nothing about longer ones, and nothing about deletes.
fails by Clock skew between source and extractor, and any commit that lagged its timestamp by more than the overlap (Incremental Extraction).
- 5Vendor cursor
Follows an opaque cursor the API returns.
guarantees Whatever the vendor implemented, which is usually good for stable collections and unstable for live ones.
fails by Items shifting between pages under concurrent writes; a cursor expiring mid-walk and restarting from the beginning.
- 6File listing
Lists a prefix and compares against files already processed.
guarantees That you processed every object that both existed and was complete at listing time.
fails by Partial uploads that parse; corrected re-uploads under the same key; objects that were never produced at all.
Only the top two are statements about the source's own ordering. Everything below approximates change with a column or a name, and every approximation has a specific set of records it cannot see.
Choosing how to read a database you also own
The most common real decision is not between exotic sources but between two ways of reading the same Postgres or MySQL instance. Both are legitimate; the criteria are load on the source, whether you need deletes, whether you need ordering, and how much operational surface the team can carry.
The instinct to reach for change data capture immediately is worth resisting for a while. It is a genuinely better read — commit ordering, deletes, no query load — and it is also a stateful distributed component whose failure mode is falling silently behind a retention window. A team that cannot yet monitor connector lag will lose more data to CDC than to a nightly extract.
The instinct to stay with a nightly full extract forever is worth resisting too, and the trigger is specific: when the extract stops fitting its window, when its load is visible in the source's own latency percentiles, or when someone asks a question that requires knowing a row was deleted.
Scales with table size and runs regardless of how little changed. The cost paid by a team that is not yours.
Scales with changed rows, provided the predicate column is indexed. Without an index it is a full scan wearing a WHERE clause.
Scales with write volume and with how far behind you are allowed to fall — the retention window is a recovery budget bought with disk on the source.
Flat rather than volume-driven; paid while idle, which matters most for sources that change rarely.
Dominated by whether you move all rows or only changed ones, and multiplied where the move crosses a region or provider boundary.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a single mid-sized transactional table, shown to establish ordering rather than magnitude. The teaching is where the cost *lands*: full snapshots put it on the source owner, CDC puts it on the source's disk, and the connector puts it on you.
What does the consumer need, and what can the source absorb?
when The table is small enough to copy whole within the window, deletes matter, and nobody needs sub-daily freshness.
cost A heavy periodic read on the source and no intra-window history. Buys the simplest possible correctness story: what you have is what the source had, entirely, at a known instant (Full Refresh vs Incremental).
when The table is too large to copy whole, changes are a small fraction of it, and hard deletes are rare or absent.
cost A permanent blind spot for deletes and for commits that lag their timestamp. Needs an overlapping window and a periodic key snapshot to bound the drift (Incremental Extraction).
when Query load on the source is a real constraint, deletes matter, or downstream needs commit ordering to reconstruct state correctly.
cost A stateful connector, replication configuration on the source, and a failure mode — falling behind log retention — that is silent and unrecoverable by catching up (Change Data Capture).
when The business meaning matters more than the row change: "order cancelled" rather than "status column went from 2 to 5".
cost The application team now owns a contract, and any write that bypasses the emitting code path is invisible. Buys semantics that a row diff can never recover (Event vs Snapshot Modeling).
when Volume is small, the analytical questions are few, and a pipeline is not yet justified.
cost Replication lag becomes your freshness, and a heavy scan can make the replica lag for whoever else reads it. Buys no pipeline at all, which is frequently the right answer (Read Replicas From the Application).
Logical replication slots, binlog formats and change-feed features differ between database products and change between major versions — including whether a slot blocks WAL cleanup while a consumer is down, which is the difference between a stalled connector and a full disk on the primary. Verify current documentation for the specific engine and version before designing around any of it.
How to build it
Most important first.
- Write a one-page fact sheet per source before writing the connector: how change is expressed, whether deletes are visible, what the completeness check is, what the retention is, and what the failure behaviour under load is. Most connector bugs are this document not existing (Dataset Documentation).
- Prefer the log over the query wherever a log exists. It gives commit ordering, deletes, and zero query load, and it is the difference between approximating change and observing it (CDC vs Polling).
- For anything without a log, pair an incremental extract with a periodic full key snapshot whose only job is to detect deletes and drift by diffing the key set (Full Refresh vs Incremental).
- Treat rate limits as a design input rather than an error condition. Respect the source's advertised limit, back off with jitter, and cap total concurrency per source rather than per job (Without Jitter, Every Client That Failed Together Retries Together).
- For file and object sources, key the bookmark on something that changes when content changes — a version id or an ETag — not on the name alone, so a corrected re-upload is not invisible.
- Normalise late, not early. Land each source in its own shape and let a staging model per source do the reconciliation to a common one; a uniform ingestion interface pushes the differences somewhere they cannot be seen (Raw, Staging, Curated: Layers by Purpose).
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.
- A database query guarantees a consistent snapshot for the duration of that statement, and nothing about changes that happen between statements. A database log guarantees every committed change in commit order, and is bounded by the server's retention of that log (MVCC: Multi-Version Concurrency Control).
- An API guarantees the semantics its documentation states and, in practice, slightly less. Pagination across a live collection guarantees neither completeness nor absence of duplicates unless it is cursor-based over a stable ordering (Unbounded Collections: The Anti-Pattern With a Fuse).
- An event stream guarantees durability and order within a partition, replayability within retention, and nothing about cross-partition order or about producers that failed before publishing (Topics and Partitions).
- A file drop guarantees nothing at all until the producer says the file is complete, and "the object exists" is not that statement.
- No source guarantees that it will tell you about a schema change before making one. Every one of them is entitled to break you at any time, and most will (CDC and Schema Drift).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Per source, one completeness check chosen to match how that source expresses change: count reconciliation for a queryable database, log-position continuity for a log, page-total assertion for an API, expected-file-arrival for a file drop, and offset-lag plus partition coverage for a stream.
- A single generic check across sources is worse than none, because it will pass on the sources it cannot see into and create the impression that all of them are covered.
- Each of these misses something specific and it is worth naming: count reconciliation cannot see value errors, log continuity cannot see a producer that never wrote, and file-arrival checks cannot see a file that arrived complete and wrong.
- Add a cross-source consistency check where two sources describe the same entity — a customer that exists in the CRM and not in the product database is a real finding, and it is only visible from above both (Reconciliation).
- A log-based source is bounded by the connector's lag, which is continuous and degrades smoothly. A query-based source is bounded by its polling interval, which is discrete and sawtoothed. The same database can give either shape depending on which one you read.
- API sources are bounded by the rate limit before they are bounded by anything else. Wanting fresher data from a rate-limited API means fewer objects per poll, not more polls, and past a point the freshness is simply not purchasable.
- File-drop sources are as fresh as the producer's schedule, which is outside your control and frequently outside your knowledge. Freshness monitoring on such a source is really monitoring somebody else's cron job (Freshness Checks).
- The freshness a platform can promise is the worst of its sources, not the best. A dashboard joining a streaming source to a daily SaaS export is a daily dashboard, however the streaming half is described.
- Source-owned schemas change on the source owner's schedule. Databases you share a company with can at least be given a contract; SaaS APIs change when the vendor ships and the release note is optional (Data Contracts).
- Nested API payloads evolve in a way flat tables do not: a field can move down a level, or a single object can become an array, without any field being added or removed. Landing raw as the source's own document preserves the evidence of what happened (Avro).
- A source can change its *extraction* surface too — deprecating an endpoint, changing default page sizes, altering what
modified_sincecovers. This is invisible to schema checks and shows up as a volume anomaly (Volume Anomalies). - When a source is replaced entirely — a CRM migration, a new payments provider — the historical data is not comparable across the boundary and the model must record which source a row came from. Discovering this after the fact means every trend chart spanning the cutover is wrong (Source of Truth).
- Recovery depth is a property of the source, not of your pipeline, and it should be written down per source: a queryable database is deep but returns current state rather than historical state, a log is exact but only within retention, an API is usually shallow, and a file drop is as deep as the producer's archive.
- For push sources, the only recovery is replay from the broker. That makes retention a recovery-window decision that should be argued in terms of "how long can we be broken before data is lost", not in terms of storage (Replay from the Log).
- For SaaS sources, the recovery mechanism is often a full export rather than the incremental endpoint. Knowing that the export exists — and that it is rate-limited differently — before the incident is the difference between a bad afternoon and a permanent gap.
- Re-extracting from a queryable source rewrites history with current values. That is sometimes the right fix and it is never a neutral one; it must be a decision with a note attached, not a default (What Backfills Break).
What can go wrong
- A source whose "modified since" filter does not cover changes to nested or related objects, so a parent looks unchanged while its children moved.
- Pagination over a live collection: rows shift between pages as the source writes, and items are skipped or repeated with no error.
- A file read while still uploading, producing a valid parse of an incomplete file — the characteristic failure of text formats, which have no footer to be missing (Parquet).
- Shared rate limits: your extract exhausts a quota that the customer-facing integration also depends on, turning a data job into a customer incident (Quotas vs Rate Limits).
- A log-based connector falling behind its source's log retention. Unlike lag, this is not recoverable by catching up — the changes are gone, and the only path forward is a fresh snapshot with a gap in the middle (CDC Failure Modes and the Retention Deadline).
- The mitigation fails: a periodic full snapshot intended to catch deletes runs monthly, so a delete is detectable but only after up to a month of a wrong number, and the check's existence is used as evidence that deletes are handled.
- "All sources are basically the same." They differ on the only three questions that matter — bounding, deletes, and behaviour under load — and a shared abstraction that hides those differences hides them from the incident too.
- "The API says it supports incremental sync." Ask what it does with deletes, whether the filter is inclusive of the boundary, whose clock it uses, and whether changes to nested objects update the parent's timestamp. The answer is frequently "no", "unspecified", "ours" and "no".
- "We read from the replica, so we are not loading the source." A replica is a full copy of the write workload plus your reads, and a heavy analytical scan on it can make replication lag, which makes the *application* read stale data if it also reads there (Read Replicas From the Application).
- "Event streams cannot lose data." They cannot lose what was published and is within retention. A producer that crashed before publishing, and a consumer that fell behind retention, both lose data, and neither is the broker's fault (Offsets and Commits).
- "File sources are the simple case." File sources have the weakest completeness semantics of the seven: no ordering, no delete concept, no way to know a file is finished unless the producer tells you, and no way to know a file was supposed to exist.
- Each source carries its own classification. A product database and a support-ticket API can contain the same person, but the ticket text is free-form and may contain anything a customer typed — including data you never intended to ingest (Data Classification).
- Credentials differ per source in blast radius. A read-only replication slot is narrow; a SaaS admin API key frequently grants far more than read, and is often the only credential the vendor offers (Least Privilege in Infrastructure).
- Cross-border movement is decided per source: pulling from a vendor whose data resides in another region is a transfer, and it happens the first time the connector runs rather than when someone approves it.
Operating it
- A per-source dashboard rather than a per-pipeline one: last successful window, extraction lag, rows or bytes moved, error rate by class, and current bookmark. Sources fail independently and aggregating them hides exactly the one that is broken.
- Rate-limit headroom for API sources — remaining quota as a fraction, over time. It is the leading indicator that turns a future outage into a scheduling change (The Rate-Limit Contract).
- Log or stream lag in *time*, not in records. "Nine hundred thousand messages behind" means nothing without the rate; "forty minutes behind" is immediately actionable (Depth Is Not an Emergency; Age Is).
- Expected-file arrival for drop-based sources, alerting on absence rather than on failure. Nothing fails when a partner does not upload; the only signal is a file that is not there (Freshness Monitoring).
- At 10x the number of sources, per-source hand-written connectors stop being maintainable and the trade becomes a managed connector platform: less control and less visibility into how bookmarks are committed, in exchange for not owning seven pagination bugs.
- At 10x volume from a single database, query-based extraction crosses from "noticeable" to "unacceptable" on the source, and log-based capture becomes the only viable read (Change Data Capture).
- At 100x event volume, partitioning and consumer parallelism dominate the design, and the ordering guarantee shrinks from "per source" to "per key within a partition" — a real weakening that downstream models must be written to tolerate (Event Keys and Partition Assignment).
- Some sources do not scale at all: an API with a fixed rate limit has a hard ceiling on how much you can extract per day, and no amount of engineering on your side moves it. Discovering that ceiling before designing around it is worth an afternoon.
- Query-based database sources cost the source's compute, which is the cost most likely to be paid by somebody else and therefore the one most likely to be over-consumed.
- API sources cost calls, which are usually quota-bounded rather than money-bounded from your side. The optimisation is fewer, larger requests, and the constraint is what the endpoint will let you ask for at once (Batch APIs and Partial Failure).
- Stream sources cost retention on the broker and consumer compute that runs continuously whether or not anything is happening. A rarely-updated source on a stream pays to be idle (Compute Waste).
- Source *count* is its own cost driver, paid in engineering attention rather than infrastructure. Each source is a schema to track, a credential to rotate, a failure mode to learn and a person to email.
- Log-based capture buys commit ordering, deletes and zero query load, and costs an operational dependency on the source database's replication configuration, plus a component whose failure is silent until it is far behind (CDC Failure Modes and the Retention Deadline).
- Per-source fact sheets and per-source checks buy real completeness answers and cost the uniformity that makes a connector fleet cheap to maintain. Above a certain source count you will accept a generic connector and a weaker guarantee, and the important thing is to know that is what you did.
- Full periodic snapshots buy delete detection and drift correction, and cost a heavy read on the source plus a decision about what to do when the snapshot and the incremental history disagree.
- Managed connectors buy time and cost visibility: when the question is "did this connector advance its bookmark before or after the write committed", the answer is in someone else's documentation and may change.
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.
- SOURCE-SPECIFICThe entire lesson is a comparison of source types, so almost nothing here transfers between them: the delete semantics of a Postgres logical slot, a MySQL binlog, a REST API with a modified-since filter and a nightly CSV drop have nothing in common beyond the word "source".
- GENERALThe three questions — how change is bounded, how deletes appear, what happens under load — are the right questions for any source including ones that do not exist yet. Only the answers are specific.
- ORG-SPECIFICWhether you can get a contract from a source depends on whether its owner is a colleague or a vendor. Internal sources can be negotiated with and given schema-change notice; SaaS sources change on their own release schedule and the only lever is monitoring.
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 what a consumer offset means when a broker rebalances partitions mid-read, and why a producer acknowledgement is not the same as durability.
- — DevOps / Production Engineering owns credential rotation for source systems without an ingestion outage, and the runbook for a connector that has fallen behind retention.