Database Ingestion and Change Data Capture

In plain terms

An operational database is often an important source for analytics, while its first responsibility is serving the application. It holds the orders, the customers, and the subscriptions as they are right now, it is busy serving the application, and a badly written extraction can slow the checkout for every customer at once. Copying from it is therefore a negotiation: how much of its attention the platform may take, how far behind the copy may be, and what the copy is allowed to miss.

Three common approaches are full snapshots, timestamp-based increments, and log-based CDC. Read the whole table and replace the copy. Ask for rows that changed since last time. Or consume the database’s supported change stream and apply the captured changes. Recovery logs need engine-specific decoding or a separate replication log; their raw contents are not a ready-made table of changes. The pipeline patterns article named these as full snapshot, watermark, and change data capture and gave the table that chooses between them. This article is about how each one actually works, what each one silently misses, and what has to be built around it so that the copy can be trusted.

What a source database gives you to work with

Four capabilities to inspect before choosing an ingestion design.

• A stable row identity. A primary key or another enforced unique key lets updates and deletes find the same row. Capture without a primary key is sometimes possible with an engine-specific replica identity or full row image, but duplicate rows and changing keys make application harder. Define how key changes remove the old identity.
• A modification timestamp, usually updated_at, maintained by the application. It is what a watermark extraction filters on, and its reliability is exactly the application’s discipline in setting it.
• Consistent reads. A transaction-wide snapshot requires suitable isolation and read semantics. Under READ COMMITTED, successive statements can see different committed states even within one transaction.
• A supported change stream. PostgreSQL logical decoding derives changes from WAL. MySQL CDC commonly reads the binary log, which is distinct from InnoDB’s recovery redo log. Configure the required logging format, captured tables, and row images; a physical recovery log is not already a universal stream of complete committed rows. See the MySQL documentation for the binary log and redo log.

A read replica can move scan CPU and I/O away from the primary, but replication and long reads still have costs. Record observed replay lag and source identity; lag has no universal seconds-to-minutes bound. Logical CDC from a replica depends on engine version, topology, connector support, and failover configuration. Choose the source with the database operator rather than assuming every extraction can use the same replica.

Full snapshots, done properly

A snapshot reads the whole table and replaces the copy. It can suit affordable full reads of small or reference tables, initialize a copy, and provide an aligned reference for reconciliation. Done carelessly it is also the mechanism most likely to hurt the source, so three details matter.

One shared snapshot. Use the engine’s snapshot semantics for all chunks. In PostgreSQL REPEATABLE READ, the first non-transaction-control statement establishes the snapshot, not simply BEGIN; READ COMMITTED takes a new snapshot per statement. In SQLite WAL mode, a deferred read transaction pins its snapshot on the first read. Record snapshot identity or source position and capture start/end times; a wall-clock label alone is not an exact database boundary. See PostgreSQL isolation and SQLite isolation.

Parallel readers over a split column. Disjoint ranges must cover the snapshot’s full key domain, including a separate rule for NULL values. Use indexed, stable columns and measured row distributions; quantile-based ranges or hash buckets can help with skew. Independent worker transactions do not automatically share a snapshot. PostgreSQL can export a snapshot for workers to import before reading, while the exporting transaction remains open. Without engine-supported coordination, describe the output as a moving extract, not a point-in-time snapshot. PostgreSQL documents snapshot synchronization. Sqoop’s boundary-query option supplies two outer bounds, not an arbitrary list of quantile edges.

A source load budget. Agree connection limits, query timeouts, scan rates, and abort thresholds using measurements on the actual source. Four workers and a ten-minute timeout are example settings, not universal safe values. Long snapshots can delay version cleanup or WAL checkpoint progress and increase storage pressure; monitor this along with application latency and replica lag.

Watermarks, and the rows they miss

A watermark extraction reads rows whose updated_at is later than the highest value seen last time. With a selective indexed timestamp and stable key, it can reduce scan cost; it also requires a reliable timestamp contract. Three failure modes need explicit handling.

Commit lag. A row’s updated_at is set by the application when it decides to write, and the row becomes visible when the transaction commits. Between the two can lie seconds, or for a batch job that opened its transaction at 23:00 and committed at 00:02, an hour. An extraction that ran at 23:59 could not see the row and set its watermark past 23:00. The next run asks for updated_at later than the watermark, and the row, whose timestamp is 23:00, is never returned again. That update remains missed until another qualifying write or reconciliation repairs the row. The same happens on a smaller scale whenever many rows share one timestamp at the watermark boundary and some of them commit after the read.

Overlap re-reads from before the last watermark and merges the selected rows again. Its bound must account for late commits, replica visibility, timestamp granularity, and clock error; without a measured bound it is a mitigation, not a guarantee. It cannot capture an intermediate state that has already been overwritten. Timestamps can move backward: a strict updated_at guard can reject a backdated correction even when a later extraction captures it. Use a trustworthy version or serialized, ordered runs, and reconcile remaining gaps.

Deletes. A deleted row has no updated_at to filter on because it has nothing at all. Overlap alone cannot detect the removal. Use captured deletes, a reliable soft-delete field, or reconciliation.

Writes that skip the timestamp. A bulk correction run by a DBA in SQL, a column changed by a trigger, an ORM path that forgot the field. The row changed and updated_at did not. These rows are stale in the copy until a snapshot happens to replace them, and there is no way to count them from the copy alone.

Store progress only after durable output, preferably in the same destination transaction. Use a fixed extraction snapshot or upper bound and keyset pagination: ORDER BY (updated_at, primary_key) must be paired with a predicate that resumes after that pair. Sorting alone does not implement a cursor. A tie-break key prevents pagination skips within a stable read; it does not fix a row that commits late behind the cursor. Re-read the overlap on subsequent runs, and leave the watermark unchanged for an empty batch.

Change data capture: reading the log

Log-based CDC decodes a configured source change stream. Debezium is one implementation, commonly deployed with Kafka Connect. The simplified payload below illustrates an update; field availability and ordering metadata depend on the connector and source configuration.

{
  "op": "u",
  "source": {"table": "orders", "lsn": 8813420, "txId": 55021, "ts_ms": 1772937720000},
  "before": {"order_id": 1746, "status": "paid",    "amount_cents": 8522, "updated_at": "2026-03-06T23:40:16Z"},
  "after":  {"order_id": 1746, "status": "shipped", "amount_cents": 8522, "updated_at": "2026-03-06T23:00:00Z"}
}

Operations distinguish creation, update, deletion, and snapshot reads. Do not assume one LSN uniquely identifies every row event or orders independent databases and Kafka partitions. Follow the connector’s source-offset and transaction-order contract; preserve per-key order and buffer transaction boundaries when atomic multi-row visibility is required. Before-images can be partial. PostgreSQL replica identity settings affect what is available.

CDC can retain captured deletes and intermediate changes that periodic row reads miss. It still consumes decoding CPU, network bandwidth, and log storage, and usually needs an initial snapshot. Latency and completeness depend on capture scope, retained logs, and operation. Monitor both connector progress and downstream consumer progress: when a connector writes to a broker, a database slot can advance before the analytics table catches up. Retention exhaustion requires an explicit recovery path rather than silently skipping ahead.

A trigger log writes capture records in the source transaction, adding write cost and retention work. Its sequence is not automatically commit order on a database with concurrent writers: transaction A may allocate ID 10, B commit ID 11, and A commit later. Polling above 11 would miss A. Only under a single-writer transactional-log protocol can a visible maximum sequence represent that restricted log’s committed progress. Triggers record statement-time information, not automatically a database commit timestamp.

Joining snapshot and stream. Establish a consistent source snapshot and a corresponding change-stream boundary, retain changes while scanning, publish the complete snapshot, then apply the retained suffix without gaps. A snapshot and a log position sampled independently can miss or double changes. Use the connector’s supported protocol, not a wall-clock guess. PostgreSQL documents exported snapshots tied to a logical slot. Do not generalize a visible maximum trigger-ID shortcut to concurrent sequence allocation.

Do not assume every after-image is complete. With some PostgreSQL replica-identity settings, Debezium uses an unavailable-value placeholder for an unchanged TOAST value. That placeholder is not SQL NULL: preserve the prior value under the connector’s rules or stop if the required prior state is unavailable. Blind whole-row replacement can corrupt a previously correct copy.

A logical replication slot retains PostgreSQL decoding state independently of a client connection. Its progress does not establish that the final analytical destination is caught up.

Applying changes at the destination

A stream of change records is not a table. Turning it into one is a merge, in position order, with three decisions made in advance.

Current state. Creates and updates replace a key with the captured after-image; deletes remove it. Replaying an old segment over newer state can temporarily or permanently regress rows or resurrect deleted ones. Commit ordered batches and their checkpoint atomically, or use source-version guards with retained delete tombstones. A checkpoint is the largest fully applied contiguous source prefix, not just the largest position observed. Keep only the last event per key for a current-state batch when full after-images are available; retain earlier events separately when history is required.

Deletes. Choose physical deletion or a filtered deleted flag per table and consumer. A technical delete is not automatically a refund or permission to retain personal history forever. Define retention and deletion propagation for raw logs, replicas, and history. If per-key version guards are used, keep enough deletion metadata to reject older replays without retaining unnecessary payloads.

History. First decide whether the question is about source observation time or business effective time. CDC is useful for captured state transitions, but a backdated business change may take effect before its commit. Use effective dates for that question, and source positions for deterministic capture order. Half-open [valid_from, valid_to) intervals require an explicitly defined time basis; a recorded observation time is not automatically the commit or business-effective time. Same-time changes need an explicit position tie-break; writes within one transaction need a policy about whether intermediate states should be visible. Deduplicate captured events before maintaining persistent history.

Reconciliation: checking the copy

Every mechanism above can drift from the source without any error being raised: a watermark that skipped a transaction, a connector that restarted from the wrong position, a delete nobody captured. The copy needs scheduled reconciliation against the source, and the check has to be cheap enough to run often.

Chunked checksums compare counts and hashes over the same key ranges, columns, and source boundary. Matching hashes provide evidence, not mathematical proof of equality. Computing hashes still reads the covered data unless maintained summaries already exist; the main saving can be network transfer and targeted repair. Include the union of source and destination key domains so a deleted highest key or an empty source does not hide stale destination rows. Align snapshots or reconcile at a known applied CDC boundary; comparing a live source with a lagging target confuses normal lag with drift.

Canonicalize column order, NULL markers, decimal scale, timestamp zone, and row ordering before hashing. A Python serialization recipe is not automatically identical to a warehouse SQL hash expression; test both implementations with shared fixtures.

Schema changes

Schema evolution needs an explicit contract for each ingestion path. Do not assume CDC sends DDL events before changed rows: the Debezium PostgreSQL connector does not report DDL change events, and before-image availability depends on replica identity. Capture permitted additions only when storage and readers support them; a new field may also contain sensitive data. Detect incompatible changes, preserve recoverable input, stop affected publication and progress, and coordinate migration. For documents, profile missing fields and types; flattening nested arrays needs an explicit grain and stable child key.

Failure and recovery

Failure	What is left behind	Recovery
A snapshot worker fails on chunk three of four	two chunks in staging, two missing	clear the staging area and rerun the whole snapshot; a retry that appends to what is there doubles the first two chunks
Watermark run fails after extraction, before load	a batch in staging, watermark unchanged	rerun; the watermark was never advanced, so the same window is read again and the merge absorbs it
Watermark run fails after load, before recording the watermark	rows loaded, watermark unchanged	retry retained input or perform a newer serialized extract; prevent an older retry from overwriting newer output
CDC consumer crashes mid-batch	uncommitted writes may roll back; separate checkpoints may lag durable writes	recover from a durable contiguous checkpoint; use atomic batches or version guards to prevent regressions
CDC connector or downstream consumer down for days	unread data accumulates in source logs or the broker, depending on the stalled stage and retention	restart and catch up; if the required log range is unavailable or continuity cannot be established, re-snapshot and hand over through a supported boundary
Source restored from backup	the copy holds rows the source no longer has, and positions that no longer exist	re-snapshot; validate source identity and lineage; rebootstrap unless a supported recovery protocol establishes continuity

Recovery depends on durable output, a checkpoint that never skips unapplied input, and replay behavior suited to the destination. Snapshot replacement, current-state merge, and history append need different implementations. With separate data and progress stores, failed acknowledgements can cause retries; make those retries safe before advancing the checkpoint.

Choosing per table

Consider a hypothetical subscription commerce company, an illustrative case rather than a description of a real organization. Its Postgres database has a few hundred tables, and three of them show the whole decision. The mechanisms and cadences in the table are proposed examples for this case, not measured results.

Table	Shape	Mechanism	Why
orders	large, append-mostly, reliable updated_at, rows never deleted, latest status needed rather than every intermediate transition	watermark with a one-day overlap, nightly, from the replica; weekly chunked reconciliation	acceptable under measured lag and timestamp bounds; reconciliation detects residual drift
customers	medium, closed accounts are deleted, address and segment history matters	log-based CDC into a current table with a deleted flag and a type 2 history	captured deletes and intermediate transitions are needed; polling current rows cannot reliably preserve both
countries, plans, products	small reference tables	full snapshot, nightly, in one transaction	a measured affordable full read can simplify incremental logic; validation and publication are still required

Choose a watermark when current-state copies, timestamp behavior, visibility delay, and deletion lag meet the consumer’s requirements. Choose CDC when the supported capture stream is needed for deletes or intermediate history and its operational cost is acceptable. Choose snapshots when a complete consistent read is affordable or for initialization. Design for known failure modes before launch, then adjust bounds and cadence from measurements.

Anti-patterns

• Extracting from the primary at peak. The nightly job that became an hourly job that now runs at 09:00 against the checkout database.
• Chunks outside a transaction. A “snapshot” whose first and last chunks are forty minutes apart, with no as_of anyone could state.
• Splitting on a skewed column. Four workers, one of them doing half the work, the job timed by the slowest.
• The strict watermark. updated_at > last_seen with no overlap, missing writes that become visible behind the saved cursor.
• Ordering CDC by timestamp. Records applied in updated_at order because it “looked right”, leaving rows in stale states.
• An unwatched replication slot. The connector stopped in March; the source disk filled in April.
• Retrying by appending. Chunk three failed, the retry ran all four again on top of the two that succeeded.
• No reconciliation. The copy has drifted for a year and the first sign is a finance number that does not match.

References

• MySQL 8.4 Reference Manual — binary log
• MySQL 8.4 Reference Manual — redo log
• PostgreSQL isolation
• SQLite isolation
• PostgreSQL documents snapshot synchronization
• Apache Sqoop 1.4.7 User Guide — boundary-query
• exported snapshots tied to a logical slot
• Debezium — PostgreSQL connector