Ingestion & Change Data Capture

Data engineering starts with getting data in. It sounds mundane — “copy data from A to B” — but ingestion is where a surprising amount of pipeline pain originates: re-reading far too much data, missing deleted records, creating duplicates on retries, and hammering the source system. This lesson is the three ways to ingest, and the handful of ideas that make ingestion efficient and correct.

Three Ways to Ingest

Full snapshot — copy the entire table every run. Dead simple and always correct (you get the exact current state), but heavy: you re-read everything even though almost nothing changed. Fine for small or slowly-changing reference tables (a list of 500 stores), wasteful for a billion-row events table. Start here only when the table is small.

Incremental pull — copy only the rows that changed since last time, using a watermark: SELECT * FROM orders WHERE updated_at > :last_seen. You remember the maximum updated_at (or id) you’ve processed, and next run you pull only rows past it. This moves far less data and is the workhorse of batch ingestion. Two catches: it needs a reliable, monotonic column (an updated_at that is always set on every change, or an ever-increasing id), and — importantly — it misses deletes. A row that was deleted doesn’t show up in a WHERE updated_at > ... query (it’s gone), so a pure incremental pull silently keeps deleted rows forever unless you handle deletion separately.

Change Data Capture (CDC) — read the database’s own write-ahead log (the transaction log the database already writes for durability) instead of querying the table at all. Every insert, update, and delete is an entry in that log, so CDC captures all changes — including the deletes that incremental pulls miss — in near-real-time, with almost no load on the source (you’re reading a log it already produces, not running queries against its tables). Tools like Debezium stream these change events into Kafka (Lesson 9), giving you a live replica of your OLTP database in your lake or warehouse. CDC is how modern pipelines get fresh, complete, low-impact replication of production databases.

The Ideas That Make Ingestion Reliable

Across all three styles, a few principles separate ingestion that works from ingestion that bites you.

Use a watermark — don’t re-read what you don’t have to. The single biggest lever on ingestion cost (and on load against the source) is not moving data you’ve already moved. An incremental watermark turns a full-table re-read into a small delta. This same idea scales up to incremental processing (Lesson 10), where we’ll measure it doing ~200× less work. Whenever you catch a pipeline re-reading its entire history every run, ask whether a watermark could make it incremental.

Land data raw first (schema-on-read). Ingest the source data into a raw landing zone before transforming it — keep an untouched copy of exactly what arrived. This is the “E and L before T” that ELT is built on (Lesson 7). The payoff is huge: if a transformation has a bug, or requirements change, you re-run the transform on the raw copy you kept — you haven’t lost the original data by transforming it on the way in. Transforming during ingestion (“schema-on-write”) is tempting but brittle; landing raw and transforming later is the robust default.

Make ingestion idempotent. This is the one people learn the hard way. Networks time out and retry, jobs get re-run after a failure, a scheduler fires a task twice — so the same data will arrive more than once. At-least-once delivery is the norm (Lesson 9), which means your ingestion must be idempotent: running it twice produces the same result as running it once, with no duplicates. You achieve this by keying on a stable identifier and upserting (insert-or-update by primary key) or deduplicating downstream (Lesson 11), rather than blindly appending. A pipeline that appends on every run will double-count the first time a retry happens — and retries are not an edge case, they are Tuesday. (This is the same idempotency principle as the distributed-systems and idempotency material elsewhere on the site — it is foundational to all correct data movement.)

Put together: pull incrementally with a watermark (or use CDC for completeness and freshness), land the raw data untouched, and make the whole thing safe to re-run. Do that and ingestion becomes a reliable, cheap foundation; skip it and you get a pipeline that’s expensive, misses deletes, and double-counts on every retry. The next lessons assume data has landed correctly, and turn to storing it so it’s cheap to keep and fast to scan.

Pitfalls to Avoid

1. Full-snapshotting a large table every run. Re-reading a billion rows to capture a few thousand changes is slow, expensive, and loads the source heavily. Use an incremental watermark (or CDC) for anything but small reference tables.
2. Incremental pulls that miss deletes. WHERE updated_at > watermark never returns deleted rows, so they linger forever. Handle deletes explicitly (periodic reconciliation, soft-delete flags) or use CDC, which captures them from the log.
3. An unreliable watermark column. If updated_at isn’t set on every change (or clocks skew, or it’s not monotonic), incremental pulls silently miss rows. Verify the column is always updated; prefer a monotonic id or CDC when in doubt.
4. Transforming on ingest and discarding the raw. If you only keep the transformed data and the transform had a bug, the original is gone. Land raw first; transform later (ELT) so you can always re-derive.
5. Non-idempotent, append-only ingestion. Appending on every run double-counts the moment a retry or re-run happens — and those are normal. Key on a stable id and upsert/dedup so re-running is safe.
6. Hammering the production OLTP source. Heavy repeated queries against the live database compete with the application. Prefer CDC (reads the log, near-zero load) or read from a replica, and pull incrementally.

Quick Recap

• Three ingestion styles: full snapshot (whole table — simple, heavy, for small tables); incremental pull (WHERE updated_at > watermark — far less data, but needs a reliable column and misses deletes); CDC (read the DB’s write-ahead log — every insert/update/delete, near-real-time, near-zero source load; Debezium → Kafka).
• A watermark (max id/timestamp seen) is the biggest lever on ingestion cost — pull only what’s new. The idea scales to incremental processing (Lesson 10, ~200×).
• Land data raw first (schema-on-read): keep an untouched copy so a transform bug or changed requirement just means re-running the transform (this is the EL-before-T of ELT, Lesson 7).
• Make ingestion idempotent — re-running must not duplicate — because at-least-once delivery is the norm (retries, re-runs happen). Key on a stable id and upsert/dedup, never blindly append.

Next lesson: now that data has landed, how to store it so it’s cheap to keep and fast to scan — row vs columnar formats and compression, measured.