Real-Time Kafka and Database CDC Processing: A Complete Engineering Guide
An engineering guide to real-time Kafka and database CDC processing: per-operation capture, snapshot-to-streaming cutover, key and partition ordering, exactly-once requirements, log-retention failure modes, production monitoring, and an acceptance checklist.
Last refreshed: 13 November 2026. Connector properties, replication-slot behavior, and Kafka transaction semantics change between releases; verify version-specific details against the documentation for the versions you run.
This guide treats a CDC pipeline as a chain of guarantees rather than a single connector configuration. Capture, cutover, ordering, delivery, failure handling, and verification each establish something the next stage depends on, and each can fail quietly. The sections below work through the chain in order: what the source log actually contains per operation, how a snapshot joins the change stream without gaps or duplicates, how key and partition choices determine whether ordering holds, what "exactly-once" requires beyond the producer, which failures exist only because CDC reads a retention-bounded log, what to monitor so those failures surface before consumers do, and when a Kafka hop is the wrong tool. The closing checklist converts each guarantee into a test your team can run before calling the pipeline production-ready.
What CDC Captures Per Operation and Why Before-Images Matter
Every downstream guarantee rests on what the source log records for a given row change. Log-based CDC does not query your tables; it decodes the transaction log, so the event model is fixed by the engine and by the table's replication settings. An insert produces an after-image only. An update produces an after-image and, depending on configuration, a before-image. A delete produces a before-image or nothing at all — and when it produces nothing, no consumer can tell which row was removed.
SQL Server's change tables make the encoding explicit. Each row carries __start_lsn, the commit log sequence number that identifies and orders changes committed in the same transaction; __seqval, which orders multiple changes within one transaction; and __operation, where 1 = delete, 2 = insert, 3 = update (before image), and 4 = update (after image). __update_mask is a bit mask with one bit per captured column, all bits set for inserts and deletes and only changed columns set for updates (Microsoft Learn). Note that a single update appears as two rows with the same __start_lsn and __seqval, so any consumer that counts change-table rows as change events will double-count updates.
On PostgreSQL, whether a before-image exists is governed by REPLICA IDENTITY. DEFAULT records the prior values of the primary key columns; FULL records prior values for all columns; NOTHING records no row state at all (Debezium PostgreSQL connector). Deletes under NOTHING therefore cannot be routed to a key. The same source documents a second consequence: if a custom message key built from message.key.columns uses columns outside the primary key, REPLICA IDENTITY must be FULL, or the connector fails to emit tombstones with the expected null values after deletes. Table-level overrides can be applied through replica.identity.autoset.values, which accepts DEFAULT, INDEX indexName, FULL, and NOTHING per matched table.
MySQL's equivalent control is binlog_row_image, which determines whether the row image written to the binary log is minimal, full, or no-blob. The practical rule is the same across engines: decide per table whether deletes and key changes must be routable downstream, then configure the source so the before-image exists. This is a schema-level decision made before the connector starts, not a connector tuning parameter.
How the Snapshot-to-Streaming Cutover Works and How to Prove It
A snapshot reads table state at a point in time; the change stream begins at some log position. The cutover is the join between them, and it is the first place a pipeline can lose or duplicate rows without any component reporting an error. The mechanism that makes the join exact is an exported snapshot tied to the log position.
In PostgreSQL logical decoding, creating a replication slot through the streaming replication interface exports a snapshot showing exactly the database state after which all changes will be included in the change stream. A separate transaction can then use SET TRANSACTION SNAPSHOT to read that state, dump it, and afterwards apply the slot's contents without losing changes (PostgreSQL 18, §47.2). This is the reference design: the snapshot and the stream share one boundary.
Connector snapshot modes decide when that boundary is taken. Debezium's snapshot.mode defaults to initial, which snapshots only when no offsets have been recorded for the logical server name; always re-snapshots on every start; no_data never snapshots and, with no stored LSN, begins streaming from the point the slot was created — usable only when all data of interest is still in the WAL. when_needed snapshots when no topic offsets exist or the recorded position is no longer available on the server (Debezium PostgreSQL connector).
Isolation is the trade-off axis. Debezium's snapshot.isolation.mode defaults to serializable, which blocks concurrent DDL and index creation for the snapshot's duration; repeatable_read prevents row updates during the snapshot but allows records to appear twice — once in the snapshot, once in streaming; read_committed is the least restrictive and accepts minor inconsistencies for records added mid-snapshot. The connector documents these as consistency-versus-concurrency choices, and the duplicate-appearance risk under repeatable_read is stated explicitly, not incidental.
Proving the cutover means producing evidence rather than assuming the mechanism worked. A workable design: write a watermark row into each captured table immediately before the snapshot starts and another immediately after it completes; then assert that the sink contains the pre-snapshot watermark exactly once, the post-snapshot watermark exactly once, and no row whose source-side creation timestamp falls between them is missing. Duplicate detection is a separate check — count distinct primary keys against total rows per table in the sink for the snapshot window. If the connector is configured for repeatable_read or read_committed, expect and account for the documented duplicate window rather than treating it as a defect.
Keying Messages and Sizing Partitions for Ordering That Holds
Kafka guarantees order within a partition, not across a topic. That single fact makes ordering a design decision made at the message key: rows that must be applied in sequence must land in the same partition, and the key is what determines that. Everything else — partition count, consumer parallelism, compaction — follows from the key choice.
Debezium's default is the table's primary key. message.key.columns overrides it per table using the format <fully-qualified_tableName>:<keyColumn>,<keyColumn>, with entries separated by semicolons and table names treated as anchored regular expressions. The connector's own guidance is to use the minimum number of columns required to specify a unique key (Debezium PostgreSQL connector). A key that includes a mutable column is a trap: an update that changes the key moves the row to a different partition, and the pre-update and post-update events can then be consumed in either order relative to each other.
Key choice also sets the load distribution. A low-cardinality key — a status column, a tenant ID with one dominant tenant — concentrates traffic in a few partitions while the rest idle, and no amount of added partitions fixes it. High-cardinality keys spread load but multiply the number of distinct keys retained under compaction.
Partition count interacts with consumer parallelism in two directions. More partitions allow more consumers in a group to work concurrently, up to one consumer per partition; beyond that, additional consumers sit idle. But partition count is also the ceiling on per-key ordering scope, and rebalances reassign partitions between consumers, so a consumer that has buffered events must handle the handoff without reordering its own output. If a downstream sink applies events in the order it receives them, the sink's write path must preserve the order the consumer delivers — a parallel write pool inside the sink reintroduces the reordering that partitioning was meant to prevent.
Compaction deserves separate attention. Kafka's design documentation states that compaction never reorders messages, only removes some (Apache Kafka design). That makes compacted topics safe for keyed latest-state consumption, but it also means the last record for a key must be a complete state, not a delta — which is why delete handling depends on tombstones, covered in the failure-modes section.
What Exactly-Once Actually Requires End to End
"Exactly-once" is not a property a connector has. It is a property of a chain, and the chain has four links: producer idempotence, Kafka transactions, consumer offset commits inside the transaction, and sink-side idempotency or transactional support. Break any link and the end-to-end guarantee degrades to at-least-once — which is often acceptable, provided the sink is built for it.
The Kafka side is well defined. Kafka's design documentation notes that in Kafka the consumer and producer are separate and only the producer is transactional, but it can make transactional updates to the consumer's committed offset, and that is what gives the overall exactly-once behavior (Apache Kafka design). The offset commit must be part of the same transaction as the output records. A consumer that writes results and then commits offsets separately has created a window in which a crash produces either a duplicate write or a skipped offset, depending on which side landed first.
The chain typically breaks at the sink. A JDBC sink writing to a relational table is not transactional with the Kafka transaction unless the connector is configured to participate, and even then the database transaction and the Kafka transaction are separate systems that must be coordinated. Where the sink cannot join the transaction, the practical answer is idempotent writes: upsert on the primary key rather than blind insert, and a deduplication key derived from the source position (the LSN, the binlog coordinates, or the connector's offset) so that a replayed event overwrites rather than duplicates. The Confluent JDBC sink exposes pk.fields for exactly this purpose (Confluent JDBC Sink configuration).
External side effects are the hardest case. An email sent, a payment captured, or an API called cannot be rolled back by aborting a Kafka transaction. The workable pattern is to make the side effect itself idempotent — an idempotency key on the outbound call — or to move the side effect behind an outbox table that the sink writes transactionally and a separate process drains. Neither pattern makes the pipeline exactly-once in the abstract; they make a specific effect safe to attempt more than once.
Failure Modes Specific to Log-Based CDC
Log-based CDC reads a retention-bounded log, and that single dependency creates a family of failures that do not exist in query-based extraction. Each has a mechanism, an observable symptom, and a control. The controls matter more than the catalog, because most of these failures are silent until a consumer notices missing or stale data.
Slot retention and WAL growth. A PostgreSQL replication slot holds WAL until the consumer confirms it. max_slot_wal_keep_size caps how much WAL slots may retain in pg_wal at checkpoint time; when it is -1 (the default), slots may retain an unlimited amount (PostgreSQL 18, §19.6.1). A stalled connector therefore fills the disk. The observable symptom is pg_wal growth and wal_status moving from reserved to extended. The control is a non-negative max_slot_wal_keep_size plus alerting on safe_wal_size.
Slot invalidation. When WAL that a slot still requires is removed, the slot is no longer usable, and pg_replication_slots reports wal_status = lost for it. Where a slot is invalidated, invalidation_reason records why, as one of the possible values wal_removed, rows_removed, wal_level_insufficient, or idle_timeout, and is NULL if the slot is not invalidated (PostgreSQL 18, pg_replication_slots). The symptom is a connector that fails to start or restarts into a new snapshot. The control is monitoring conflicting and invalidation_reason, and setting idle_replication_slot_timeout deliberately rather than by default.
Log purging on other engines. SQL Server's CDC cleanup job discards change-table entries on a time-based strategy, computing a new low watermark by subtracting the retention period from the commit time of the last processed transaction (Microsoft Learn). A consumer that falls behind the retention window loses changes permanently. Oracle deployments face the equivalent with archive log retention, configured on RDS via archivelog retention hours (Debezium Oracle connector). The control is retention longer than the worst credible consumer outage, plus lag alerting well inside that window.
Schema drift and schema-history loss. A column rename or type change alters the event schema mid-stream. Debezium stores schema history in a Kafka topic; losing that topic forces a recovery snapshot, and the SQL Server connector's recovery mode exists specifically to rebuild a lost or corrupted history topic — with the documented warning not to use it if schema changes were committed after the last shutdown (Debezium SQL Server connector). The control is a replicated, compacted schema-history topic and a schema registry with a defined compatibility policy.
Tombstone loss under compaction. Debezium's tombstones.on.delete defaults to true, emitting a delete event followed by a tombstone so Kafka can remove all events for that key under compaction; setting it to false emits only the delete event (Debezium PostgreSQL connector). Disabling tombstones on a compacted topic leaves the deleted row's last state readable indefinitely. The control is to keep tombstones on any compacted topic and to ensure the sink treats a null value as a delete.
Monitoring and Verifying the Pipeline in Production
The signals that indicate health are not the ones that are easiest to collect. A connector that is running, a topic that has messages, and a consumer group that is stable can all be true while the pipeline is silently dropping rows. Verification needs to be layered, from per-event checks up to periodic full reconciliation.
Consumer lag is the first signal and the most misread. kafka-consumer-groups.sh --describe reports current offset, log-end offset, and lag per partition (Confluent). Lag tells you how far behind a consumer is, not whether the data it has consumed is correct, and a lag of zero on a topic that stopped receiving events looks identical to a healthy idle pipeline. Pair lag with a heartbeat: Debezium's heartbeat.interval.ms sends messages on a schedule regardless of source activity, which both confirms the connector is alive and, on PostgreSQL, lets the connector advance the slot's confirmed LSN during quiet periods so WAL is reclaimed.
Slot state is the second signal and is PostgreSQL-specific. Query pg_replication_slots for active, active_pid, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size, inactive_since, conflicting, and invalidation_reason. A slot that is inactive with a rising restart_lsn gap is the precursor to disk exhaustion; a slot with wal_status = unreserved is about to lose its WAL.
Connector metrics form the third layer. Debezium exposes streaming metrics including MilliSecondsBehindSource, which measures how far the connector's most recent event lags its source timestamp — a more direct freshness signal than Kafka lag, because it is measured against the source rather than the topic. The connector's statistics.metrics.enabled property controls whether quantiles are collected for that metric.
Reconciliation is the fourth layer and the only one that verifies correctness rather than liveness. A workable model: per-event checks that every consumed event's key exists in the source (or has a matching delete), periodic row-count comparison per table between source and sink, and a scheduled full comparison of a checksum over a stable column set for tables where exactness matters. Row counts alone will not catch an update that was applied to the wrong row; checksums will not catch a delete that was never propagated. Run both.
When Kafka-Based CDC Is the Wrong Choice
Kafka-based CDC is an infrastructure commitment, not a default. The pipeline adds a broker cluster, a Connect worker fleet, a schema registry, a connector to operate and upgrade, and a set of guarantees you now own end to end. Before adopting it, test whether the requirement actually needs a durable, replayable, ordered change log that multiple independent consumers read at their own pace. If the answer is a single downstream table refreshed every fifteen minutes, the added hop buys latency you are not using and operational surface you must staff.
Several conditions argue for a simpler path. At small scale — a few tables, modest write volume, one consumer — a scheduled incremental query or the destination's native sync keeps the same freshness with far less to run. When reporting tolerates batch latency, a periodic extract removes the ordering and cutover problems entirely. When consumers need consistent reads across several tables at one transaction boundary, a change stream split into per-table topics works against you: reassembling a transaction downstream requires transaction metadata and careful handling, and a direct read from a replica may serve the need more honestly. Teams without Kafka operations capacity should weigh the on-call burden of brokers, partitions, and consumer groups against the benefit. And where the destination already offers native incremental sync from the source, adding Kafka in the middle duplicates a capability you are paying for.
The trade-off is real in both directions. Kafka-based CDC earns its cost when many consumers need the same change stream, when replay from a retained log matters for rebuilding derived state, when ordering per entity must hold under load, and when the source must not be polled. Frame the decision as a comparison of requirements against operating cost, not as a verdict on the technology.
Putting It Together: An Acceptance Checklist
Run these checks against a staging copy of the production pipeline and record the evidence for each. A check passes only when its evidence is attached, not when the configuration looks correct.
Capture. Every captured table has a replica identity or equivalent that supplies the columns your keys and routing depend on; a delete on each table produces an event carrying the identifying before-image. Verify by deleting a row and inspecting the emitted record.
Cutover. A snapshot followed by streaming produces no missing and no duplicated rows at the boundary. Prove it with a watermark row written before the snapshot starts and a boundary reconciliation query comparing source and sink counts and checksums per table.
Ordering. Events for a single entity arrive in commit order at the sink. Test by updating one key repeatedly under concurrent load and confirming the final sink state matches the source.
Delivery. The sink is idempotent or transactional, and offset commits occur inside the same transaction as the writes they cover. Kill the consumer mid-batch and confirm no duplicate side effects and no lost offsets.
Failure containment. Replication slot retention is bounded and alerted; slot invalidation is detected and has a documented recovery path; schema history is durable; tombstones are emitted where compaction is enabled.
Verification. Per-event checks, periodic reconciliation counts, and a full reconciliation run on a schedule all exist, with owners and thresholds.
Frequently asked questions.
Does exactly-once remove the need for idempotent sinks? No. Kafka transactions cover Kafka writes and offset commits; a non-transactional external sink still needs its own idempotency.
Can I skip the snapshot and stream from the current log position? Only if every row you need is still reflected in retained WAL or log, which is rarely true for existing data.
What happens if the connector is down for a week? The slot retains WAL up to its configured limit; beyond that the slot can be invalidated and a new snapshot is required.
Is consumer lag a health signal? It shows how far behind a consumer is, not whether events were lost; pair it with reconciliation.
Where a unified lakehouse platform enters the evaluation, judge it on the same criteria used above: whether it can reconcile source and sink counts, how much operational surface it removes, and whether it gives you control over ordering keys. Those are testable questions, and the answers belong in the same evidence file as the rest of this checklist.