Change Data Capture

Change Data Capture and event sourcing both revolve around an append-only log of changes, which — especially if you've just come from the last chapter — makes them sound like one idea in two hats. They are not one idea. Event sourcing makes the log the source of truth: you give up mutable rows, your application appends domain events on purpose, and current state becomes something you compute. CDC assumes the reverse. Your database stays exactly as it is — mutable rows, ordinary UPDATEs, an application that has never heard of an event — and CDC reads the log the database already keeps for its own crash recovery. Event sourcing is a decision about how you model your data; CDC is a decision about how you observe data you've already modeled the normal way.

That distinction is the thing to hold onto, because everything below is about the second path. There's a gap between what your database knows and what the rest of your system knows. It opens the moment you commit a write, and it widens every millisecond until something carries the news.

A user changes their preferred genre from Thrillers to Science Fiction. That change lands in the users table at 14:00:00.001. The recommendation engine, which has spent the morning building their queue, still thinks it's serving a thriller fan. The analytics pipeline doesn't know a preference shift happened at all. The marketing service will cheerfully send a thriller discount email tomorrow, because nobody told it otherwise. Three systems, one database, and a gap the user never agreed to.

This is not a hard problem. The database knows about the change — it has to, because it wrote it. The only question is how that knowledge escapes the database and reaches the systems that need it.

The naive answer is: your application publishes an event. You do the UPDATE, and then you send a message to Kafka, and every service subscribed to that topic updates its own state. Clean, obvious, and wrong about half the times you ship it — for reasons we'll get to, and that nobody enjoys discovering at scale.

That's where CDC parts ways with the naive approach. Instead of asking the application to remember to publish, it asks the application to do nothing at all. CDC reads the database's transaction log — the low-level record of every write, committed before the database even acknowledges success — and turns those entries into events. The application doesn't participate. It writes to the database the way it always has, blissfully unaware that anything is reading over its shoulder, which is about the most you can ask of application code.

The transaction log is the most durable record in the system, and it isn't close. It exists because the database itself can't live without it: before any write counts as committed, it's written to the log, so that a crash becomes a recovery instead of a data-loss incident. CDC parasitizes that durability for free. The change was going to be in the log no matter what you did. CDC just makes it legible to the rest of your infrastructure.

That's the whole idea, and it's worth saying plainly because it's the thing that makes CDC click: the database changes, and every interested system finds out — not because the application remembered to announce it, but because the log recorded it anyway, and CDC reads what's already there.

Here's the ground we'll walk together:

• The dual-write problem — why "write the row, then publish the event" is a quiet lie that sails through code review, survives staging, and only breaks in production once you've stopped watching for it
• The three flavors of CDC — log-based, query-based, and trigger-based — and exactly where each one breaks, because they break in different places and those differences are the decision
• How Airbnb pulled real-time data into their warehouse without building a second write path — and the schema-change gate they had to invent so the pipeline stopped failing silently
• The operational realities of running Debezium for real — replication-slot lag, schema evolution, and the routine ALTER TABLE that turns an ordinary Tuesday into a 2 AM page
• The one question a principal engineer answers first — capture the database's state, or publish the application's intent? — which is also the fork between CDC and the Outbox pattern of the next chapter

Consider the purchase flow. User clicks Buy. Your OrderService writes a row to the orders table. That's the moment the purchase becomes real: durable, consistent, the database said so.

Now count the systems that need to know:

The inventory service needs to reserve the item. The recommendation engine needs to mark this product as purchased so it stops suggesting the thing you just bought. The analytics pipeline needs the event for revenue reporting. The fraud system wants to update its risk model. The email service needs to send a confirmation. The loyalty service wants to credit points.

Six systems. One write. And the write itself told none of them.

The obvious fix is to have the OrderService publish an OrderCreated event to Kafka right after it writes. Every downstream service subscribes, reacts, updates its own state. You've seen this architecture. It's in every microservices tutorial written since 2015, and it works — right up until the OrderService process dies in the gap between the database write and the Kafka publish.

The database write committed. The event never left the building. The order exists in orders, but inventory isn't reserved, analytics has no record of it, and the customer is sitting there waiting for a confirmation email that will never come. The system is now silently inconsistent, in the specific way that surfaces three days later as a support ticket, a line that doesn't reconcile in the weekly revenue report, or — on a good day — a failed idempotency check downstream that lets someone reverse-engineer what happened.

This isn't a theoretical race condition you only hit on exotic hardware. It happens on deployments. It happens when your Kafka producer hits a timeout. It happens when the pod gets OOM-killed three lines above the producer.send() call. The window is a few milliseconds per request — but a few milliseconds times a few thousand orders a day is a number, and at that volume you don't avoid the window. You live in it.

Here's the part worth sitting with, because it's the reason this bug outlives the people who ship it: nobody writes this design because they're careless. They write it because it survives everything that's supposed to catch a bug. The logic is obviously correct on the whiteboard — write the row, publish the event, what could possibly go wrong between two adjacent lines. It passes code review for the same reason. It passes staging, because you cannot hit a three-millisecond crash window on purpose no matter how hard you try. It passes the first year in production, until traffic quietly raises the odds and the window finds you on its own schedule. The dual-write bug isn't something a senior engineer doesn't know about. It's something the entire development process is structured not to see.

The failure mode has a name: the dual-write problem. You're writing to two systems — the database and the message broker — that share no transaction boundary. Absent a distributed transaction coordinator (which you don't want, for reasons Chapter 8 covered at length), one write can succeed while the other fails, with nothing to put it back together. You've planted a consistency hazard at precisely the point in the system where consistency matters most.

CDC closes the window by making the transaction log the only write that counts. The application writes to the database. Period. CDC reads the log and publishes the event. The log and the database are the same system — you cannot commit to one without committing to the other — so there's no seam for a crash to slip through. The event can't be less durable than the data, because the event is the data, read back out.

Before accepting CDC's complexity, most teams try two things first. Both are reasonable. Both have a timer on them.

Application-level event publishing, the approach above, fails at the dual-write seam. Wrapping both operations in a try-catch doesn't help — a crash doesn't throw an exception, it just stops, and finally blocks don't run for a process that no longer exists. Kafka transactions can genuinely help (beginTransaction(), commitTransaction() spanning both the DB write and the Kafka produce), but only if you're using Kafka's transactional producer, and your database write happens inside the same client, and your consumers are configured for read_committed isolation. Most teams are running three different services with three different Kafka clients, which means this architecture is simply not on the menu for them. The teams that can assemble it often discover the transactional producer degrades throughput enough to earn its own capacity-planning exercise — which is to say, the cure has a line item.

The "we'll just be idempotent about it" strategy — publish the event, retry on failure, design consumers to tolerate duplicates — solves the duplicate problem and leaves the missing-event problem untouched. Retries assume a process that's still alive to retry. The crash scenario produces no retry. It produces silence, and silence is the one thing your monitoring can't alert on.

CDC sidesteps both by refusing to involve the application in the event pipeline at all. The log records the commit. Debezium reads the log. The event lands in Kafka. If Debezium crashes mid-read, it restarts and re-reads from its last checkpoint — at-least-once delivery, never zero. The gap that kills application-level publishing doesn't exist here, for the simple reason that the log doesn't depend on your application staying healthy to do its job.

There's one genuinely different answer that isn't on this naive list, and it's the subject of the last chapter: event sourcing. It's tempting to file "publish an event after you write" right next to it, but they're opposites. The naive approach keeps the database as the source of truth and bolts events on the side — which is exactly why the two can drift apart. Event sourcing makes the log itself the source of truth, so there's no second write to drift from; the dual-write problem doesn't get solved so much as it stops existing. That's not a naive move, just a heavier one, and it earns its weight where the log needs to be the system of record — audit and compliance, complex workflow state, many read models fed from one history (the cases the last chapter argued for). CDC takes the opposite bet: leave your ordinary database as the source of truth and read the log it already keeps. Same problem, two prices — rebuild your write path, or run a capture pipeline.

The catch — the price of that capture pipeline — is operational overhead, and it's real, not rhetorical. Running Debezium in production means running another service, managing connector configs, watching replication lag, handling schema evolution, and understanding just enough about your database's internal log format to not be surprised by it at the worst possible moment. We'll come back to that — it's most of the rest of the chapter.

This is the real thing. Log-based CDC reads the database's own internal record of every change — the Write-Ahead Log in Postgres, the binary log (binlog) in MySQL — and converts those entries into events.

The logic is direct: the database writes to its log before acknowledging any commit, so if Debezium reads that log, it sees every committed change, in the exact order it happened, with no cooperation from the application that made it. A DBA running UPDATE users SET tier='gold' across a few thousand rows at midnight? Shows up in the log. A migration script that rewrites a million rows? In the log. Your application's own writes? Also in the log, indistinguishable from the rest — which is either a feature or a problem, depending entirely on whether you wanted to capture that midnight migration as a flood of events your downstream consumers now have to process.

Debezium is the dominant open-source implementation. It runs as a Kafka Connect plugin: you point a connector at your database, tell it which tables to watch, and it starts reading the log and publishing change events to Kafka topics — one topic per table, by default. Each event carries the full row state before and after the change, the operation type (c for create, u for update, d for delete), and the log position. The log position is the whole trick behind the checkpoint: if Debezium restarts, it picks up exactly where it left off instead of starting over.

One subtlety that surprises engineers: Debezium publishes your schema, not just your data. Every change event carries the row's column names and types, and Kafka Schema Registry keeps track of the shape. The moment you ALTER TABLE orders ADD COLUMN discount_code, the schema changes, and if your downstream consumers are deserializing against the old shape, you have a compatibility problem you didn't schedule. This is manageable — Avro's schema-evolution rules and Schema Registry's compatibility modes exist precisely for it — but "manageable" means "someone manages it." Teams that run log-based CDC without a schema-registry strategy eventually ship a deployment that breaks a consumer and then blame Debezium, which is a bit like blaming the smoke detector.

The other thing that quietly ruins afternoons: Postgres's logical replication slots hold a reference to the WAL position. If Debezium falls behind — stops consuming, gets stuck, gets accidentally turned off — the WAL grows without bound until the slot is dropped or the consumer catches up. This is not a hypothetical. A Debezium connector that's offline for a week on a busy Postgres instance can pile up tens of gigabytes of WAL, and the way you find out is that the disk fills and Postgres stops accepting writes — all writes, to your actual production database, because a downstream analytics pipeline got behind. Monitor your replication-slot lag. Put a real alert on it, not a dashboard nobody opens. This is the CDC failure that reliably requires a 2 AM page, because by the time it's visible, the symptom isn't "analytics is stale," it's "the site is down."

The simpler alternative: poll the database on a timer with SELECT * FROM orders WHERE updated_at > :last_checkpoint ORDER BY updated_at. Find everything newer than your last run, process it, advance the checkpoint. Repeat forever. (The full polling loop — with checkpointing, paging, and op-type detection inferred from timestamps — is in Appendix A.2.)

No Debezium. No replication slots. No reading the internal log format of a database that would rather you didn't. Just SQL and a cron entry.

The tradeoffs are real, and for high-throughput use they're mostly fatal. First, you can't capture hard deletes — a deleted row is gone, and updated_at doesn't exist on a thing that no longer exists. You can route around this with soft deletes (deleted_at IS NOT NULL), but now you're bending the application's data model to accommodate your capture strategy, which is the tail wagging the dog. Second, latency is bounded by your polling interval: poll every 30 seconds and your downstream systems are permanently 0–30 seconds behind, which is fine for a nightly report and a non-starter for anything that calls itself real-time. Third, updated_at has to exist, be indexed, and be religiously maintained — and the day some code path updates a row without touching updated_at, that change becomes invisible to your pipeline, with no error and no log line to tell you it vanished.

Where query-based CDC earns its keep: managed databases that won't expose the transaction log (some RDS configurations, older MySQL hidden behind connection proxies), legacy systems where installing Debezium is a six-month political negotiation, and any pipeline where the delay genuinely doesn't matter. Hydrating a nightly analytics table? Poll every five minutes and pocket the operational simplicity — a cron job running a SELECT is a thing you can actually sleep next to.

Where it has no business being: anything that has to be fast, complete, or delete-aware. A real-time fraud system can't sit thirty seconds behind the fraud. A pipeline that has to record deletions can't lean on a strategy that's structurally blind to them — updated_at doesn't fire on a row that no longer exists. A consumer that needs every state a row passed through can't poll at all, because each poll sees only the latest value and silently skips whatever changed between two ticks. And any table without a reliable, indexed updated_at will quietly drop the writes that forgot to stamp it. Feeding any of those? You're reading the wrong section — that's what log-based CDC is for.

Database triggers fire in-transaction: you write a row, the trigger fires, and it writes to an audit table — or, in the more adventurous architectures, calls an external API — before the transaction is allowed to commit. It's synchronous, it's exhaustive, and it parks the event-publishing logic right in the database tier, where the application physically cannot forget to call it. On paper, that's the dream: the capture can't be skipped, because it's welded to the write.

The overhead is the catch, and it's structural. Every write to a trigger-enabled table pays for the trigger firing inside the transaction. On a low-write table — an admin config, a feature-flag store — that cost is a rounding error. On a table absorbing ten thousand writes a second, the trigger overhead is measurable, it compounds, and it shows up as write latency on the path you least wanted to slow down. Triggers also can't easily reach external systems (Kafka, an HTTP endpoint) without either staging through an intermediate table or leaning on database-specific extensions that aren't portable and frequently aren't available in managed environments anyway.

The worst trigger-based CDC I've watched fail was exactly that cautionary variant: a trigger on a high-volume events table publishing directly to RabbitMQ through a Postgres extension. RabbitMQ had a brief, unremarkable network partition — the kind of thing message brokers do on a Tuesday. Every insert into events started failing. Not because anything was wrong with the database, but because the trigger couldn't complete its publish, and the trigger was inside the transaction, so the transaction couldn't commit. Someone had accidentally promoted a message broker to a hard dependency of the primary database write path, and nobody knew until the broker sneezed and the application caught a cold. It took an afternoon to untangle and an architecture review to make sure nobody rebuilt it from memory six months later.

Triggers make far more sense when the target is a database-local staging table — write the change event to an outbox table inside the same transaction, then let a separate process read the outbox and publish to Kafka on its own time. Which is precisely the subject of Chapter 11.

The choice comes down to a handful of axes. Here they are side by side; the paragraphs after fill in the nuance a table can't hold.

Log-based CDC is the right choice when you need completeness, low latency, and you don't want to touch the application code. It captures everything — inserts, updates, deletes, and the changes made directly to the database by tools and humans the application never knew about. The operational burden is the price of admission: replication slots, schema evolution, connector management, WAL-lag monitoring. Budget for a named owner. This is not a "set it and forget it" component, and the teams that treat it like one are the teams writing the postmortem.

Query-based CDC is the right choice when you can't reach the transaction log, when polling latency is acceptable, and when the data model has reliable updated_at columns you'd trust with money. The simplicity is a genuine advantage, not a consolation prize — a cron job running a SQL query is dramatically easier to operate than a Debezium cluster, and easier-to-operate has saved more systems than clever ever has. Just sign the terms: no hard-delete capture, no sub-second latency, and any writer that skips updated_at is invisible and won't tell you.

Trigger-based CDC sits in an uncomfortable middle seat: more complex than query-based, less capable than log-based, and carrying the unique risk of coupling your write path to whatever the trigger decides to do. Use it when you're doing the Outbox pattern (writing change events to a local outbox table inside the same transaction) and you have no access to the WAL. Do not use it to publish directly to external systems from inside a transaction, unless you've always wanted your database's uptime to depend on your message broker's mood.

One tradeoff cuts across all three and outranks the rest: CDC publishes internal database state, not business events. A UserUpdated CDC event says "the row changed; here's the before and after." A business event says "the user upgraded to premium." Both carry information — different information, at different altitudes. CDC is the low-altitude view. If you need business semantics — the why of a change, not just the what — CDC hands you raw material and leaves the interpretation to something downstream. Application events built on the Outbox pattern hand you the meaning directly. The real question underneath the tradeoff is who owns the publishing logic: the application (reliable only if no developer ever forgets) or the infrastructure (reliable by construction, but fluent only in a lower-level dialect that consumers have to translate).

The canonical CDC pipeline: Debezium runs as a Kafka Connect worker, connected to your database over its logical replication protocol. You configure a connector that names the database, the tables to capture, the topic-naming convention, and the Schema Registry endpoint. Debezium reads the WAL in real time and publishes change events. Downstream services consume from the Kafka topics at whatever pace they can manage.

The thing the diagram makes visible that the prose glosses over: there are two lag points, not one, and conflating them is how you get fooled. Replication-slot lag tells you how far Debezium is behind the database. Consumer-group lag tells you how far each consumer is behind Debezium. A fraud system that's 5ms behind Debezium is not a real-time fraud system if Debezium is 45 seconds behind the WAL — it's a 45-second-late fraud system with excellent self-esteem. Monitor both. Alert on both. Separately.

Airbnb's data-infrastructure problem in 2015 was a common one wearing an uncommon scale: production databases that couldn't absorb analytics queries without falling over, and a data warehouse (Hive, at the time) that was only ever as fresh as the last ETL job — which ran every 12 to 24 hours. Pricing analysts were looking at yesterday's market. Listing-health metrics were stale by half a day. Anomaly detection was hunting anomalies that had already had time to become the new normal.

They built a CDC pipeline they named Spinal Tap — yes, that Spinal Tap, this one goes to eleven — on top of MySQL binlog replication. The binlog was replicated from production to a dedicated analytics replica, which fed a pipeline that wrote to Hive. The move that mattered was conceptual, not technical: they treated the binlog as a first-class data stream instead of an implementation detail of MySQL replication. The mental model shifted from "copy the database to the warehouse" to "stream the changes and apply them to the warehouse," and the warehouse stopped being a nightly photocopy and became a near-real-time projection of production, updated as writes happened.

The payoff landed hardest in pricing. Dynamic pricing at Airbnb leans on signals that move by the hour — competitor rates, local events, demand spikes — and with 24-hour data lag, the pricing models were busy optimizing for a market that had already moved on. With CDC, lag dropped to under a minute, and the pricing team could watch the effect of a rate change inside the hour instead of finding out the next morning. For a marketplace where supply and demand are both volatile, the gap between 24 hours and 60 seconds isn't a latency improvement. It's the difference between reacting to the market and reading about it.

What Airbnb learned the hard way, and wrote up publicly so the rest of us could learn it the easy way, was schema evolution. Every time a production schema changed — a column added, a type widened, a table renamed — the pipeline had to be updated before the change landed, or it would fail silently: consuming events it couldn't parse and dropping them on the floor, no error, no alarm, just a slow leak of data nobody noticed until a report looked wrong. Their fix was a schema-change review gate: the data-engineering team had to sign off before any production DDL could merge. It added friction, and the people shipping migrations felt it. It also retired a recurring genre of 2 AM incident, which is the kind of trade most on-call engineers will take every single time.

LinkedIn had reached the same conclusions years earlier with Databus, their own CDC framework built before Debezium existed. Databus treated the Oracle and MySQL transaction logs as a durable, replayable source and put a relay layer in front of them, so secondary consumers could subscribe without piling additional load onto the production databases. Their core observation — that the transaction log is already the most reliable thing the database produces, and that CDC's only job is to make it observable — is the conceptual foundation Debezium later productized for everyone else.

Debezium is the de-facto open-source standard. It supports Postgres (logical replication), MySQL (binlog), MongoDB (oplog), Oracle, SQL Server, and more. It runs as a Kafka Connect plugin, so you inherit Connect's offset management, connector lifecycle, and REST API for free. The community is active and the documentation is unusually good for open-source infrastructure — a sentence that doesn't get written often, which is exactly why it's worth writing. If you're building log-based CDC on a supported database, start here. (Full configuration reference: Appendix A.1.)

AWS Database Migration Service (DMS) is the managed option. You configure source and target endpoints, pick a replication-task type (full load, CDC, or both), and DMS handles the rest. The appeal is obvious: no connector management, no replication-slot babysitting, AWS owns the operational burden. The cost is flexibility — DMS is built for database-to-database migration and warehouse loading, not for publishing rich change events with full before/after row state to a message broker. If your use case is "stream Postgres changes into Redshift or Kinesis," DMS is worth a serious look. If you need fine control over event schemas, topic routing, or transformation logic, you'll find DMS's ceiling fast, usually with your head.

Fivetran and Airbyte live in a different tier: primarily query-based CDC, optimized for the data-pipeline use case (production DB → warehouse). Easy to configure, pleasant to operate, and completely wrong for real-time event streaming — a fact teams tend to discover one to three minutes at a time, which is roughly the latency they didn't realize they were signing up for. They're worth naming here precisely because teams go looking for "CDC," find these, and adopt them without registering that "polling-based" and "real-time" are not the same product.

For Postgres specifically: pglogical is worth knowing. It's an extension that exposes logical replication in a structured form that tools like Debezium lean on under the hood — but which you can also consume directly, if your architecture doesn't run Kafka Connect and you'd rather not add it just for this.

Your production Postgres database has a CDC pipeline, via Debezium, feeding an analytics warehouse and a real-time inventory service. A developer removes the quantity_reserved column from the orders table — it's been superseded by a separate reservations table, and nothing in the application reads it anymore. Clean cleanup. Good hygiene. Ship it.

The migration runs. Debezium sees the schema change in the WAL and starts publishing events without the column. Now work through what happens in each downstream system:

1. The analytics warehouse schema still has quantity_reserved, declared not-nullable. How does the warehouse consumer react when it tries to write a row that's missing a column its own schema insists on?
2. The inventory service has been consuming quantity_reserved to reconcile stock. It never formally declared a dependency on the field — it was just always there, the way the floor is always there until someone removes it. What happens to its state now?
3. A third consumer you didn't know existed — a nightly audit job written six months ago by someone who's since left the company — also reads quantity_reserved. It runs tonight, at 2 AM, while you're asleep. What's in the audit output in the morning?

The uncomfortable answer is that you probably can't enumerate all the consumers of a Debezium topic without auditing every consumer group registered against it — and the one that hurts is always the one that wasn't in anybody's head. This is the schema-governance problem in its purest form. A column dropped from a database has effectively been deleted from the API that every CDC subscriber consumes, with no deprecation window, no version bump, and no announcement to the people who depended on it.

The principle the exercise is trying to drive into your bones: a CDC topic is a public API. The instant you point Debezium at a table, that table's schema becomes a contract with every consumer of the topic — including the consumers you've forgotten about and the ones written by people who've left. Deprecation windows, backwards compatibility, versioning: these stop being optional niceties and become the line between CDC as a durable engineering primitive and CDC as an incident with a future start date.

← Chapter 9 (Event Sourcing). Event sourcing stores the event log as the source of truth; CDC reads an existing database's log and turns it into events. The conceptual overlap is real — both treat history as a first-class record — but the relationship is layered, not either/or. You can run Debezium against an event-sourced system (reading the event store's own log for replication), and you can feed CDC events into an event-sourced downstream consumer. They compose more often than they compete.

→ Chapter 11 (Outbox Pattern). The Outbox pattern is the direct answer to the dual-write problem this chapter opened with. Write the event to an outbox table inside the same transaction as the business write, then use CDC (or a polling reader) to publish from the outbox to Kafka. You get application-semantic events with the reliability guarantees of log-based CDC — the best of both altitudes. If this chapter left you uneasy about application-level event publishing, that unease is the point, and Chapter 11 is where it resolves.

← Chapter 7 (Stream Processing). CDC events are a stream — a durable, ordered, replayable log of database changes. Everything from Chapter 7 about consumer lag, partition assignment, exactly-once semantics, and stream transformation applies directly the moment those events hit Kafka. The Debezium pipeline ends at the topic; stream processing begins there. The seam between the two chapters is a Kafka topic, which is fitting.

The intuition to carry out of this chapter: the transaction log is the most reliable thing your database produces. It exists before the commit is acknowledged. It exists whether your application is healthy or face-down. CDC doesn't invent a new event stream — it makes visible one that was always there, recording faithfully, waiting for someone to read it. So the question is never whether to capture your database's changes; they're already captured. The question is whether you capture them deliberately, through a pipeline you operate and monitor and understand, or accidentally — through the gaps in an application that was supposed to publish events and, on a long enough timeline, sometimes simply doesn't.

The chapter keeps the code out of the narrative on purpose. Here it is, collected and annotated — read the prose for the idea, come here for the literal shape. Both are illustrative rather than production-hardened: they're here to make the structure concrete, not to be pasted into a connector config and pointed at your production database before lunch.

A.1 — Debezium Connector Configuration (Postgres)

1{
2  "name": "orders-connector",
3  "config": {
4    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
5    "database.hostname": "postgres.internal",
6    "database.port": "5432",
7    "database.user": "debezium",
8    "database.password": "${file:/opt/kafka/external.properties:postgres.password}",
9    "database.dbname": "orders_db",
10    "database.server.name": "orders",
11    "table.include.list": "public.orders,public.users",
12    "plugin.name": "pgoutput",
13    "slot.name": "debezium_orders_slot",
14    "publication.name": "debezium_publication",
15    "key.converter": "io.confluent.kafka.serializers.KafkaAvroSerializer",
16    "value.converter": "io.confluent.kafka.serializers.KafkaAvroSerializer",
17    "key.converter.schema.registry.url": "http://schema-registry:8081",
18    "value.converter.schema.registry.url": "http://schema-registry:8081",
19    "transforms": "unwrap",
20    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
21    "transforms.unwrap.drop.tombstones": "false",
22    "heartbeat.interval.ms": "10000"
23  }
24}

A few things worth noting in this configuration that the documentation buries. slot.name must be unique per connector — running two connectors against the same slot will corrupt both. heartbeat.interval.ms is not optional: without it, an idle table will never advance the replication slot's confirmed LSN, and WAL will accumulate even with no actual changes to capture. The ExtractNewRecordState transform flattens Debezium's envelope (which wraps before and after in a container structure) into a flat record; most consumer schemas expect this, but if you need the before-image for your consumers, remove it. And table.include.list is a safelist, not an afterthought — if you don't specify it, Debezium captures everything, including system tables you don't want and internal tables you'd prefer not to publish.

A.2 — Query-Based CDC: Polling Loop

1-- Schema assumption: orders has updated_at timestamp, maintained by app
2-- Soft-delete pattern: deleted_at IS NOT NULL for deleted rows
3 
4-- Initialize: store last checkpoint (e.g., in a pipeline_checkpoints table)
5-- SELECT checkpoint FROM pipeline_checkpoints WHERE pipeline = 'orders_sync';
6 
7-- Poll query (runs on interval, e.g., every 30 seconds)
8SELECT
9  id,
10  status,
11  total_cents,
12  user_id,
13  created_at,
14  updated_at,
15  deleted_at,
16  CASE WHEN deleted_at IS NOT NULL THEN 'd'
17       WHEN created_at = updated_at THEN 'c'
18       ELSE 'u'
19  END AS op_type
20FROM orders
21WHERE updated_at > :last_checkpoint
22ORDER BY updated_at ASC
23LIMIT 10000;
24 
25-- After processing, advance checkpoint:
26-- UPDATE pipeline_checkpoints SET checkpoint = :max_updated_at_seen
27-- WHERE pipeline = 'orders_sync';

The LIMIT 10000 is non-negotiable — without it, a backfill or a batch update that touches millions of rows will make your polling query run for minutes and hold locks while it does. Process in pages, advance the checkpoint incrementally. The CASE expression detecting operation type from timestamp comparison is an approximation: if an updated_at trigger has a bug and doesn't fire on every write, your op classification is wrong. Query-based CDC is only as reliable as the mechanism that maintains updated_at, which is why it's the second thing to check when results look wrong.

Next: Chapter 11 — The Outbox Pattern: writing events into the database alongside your business data, so the dual-write problem from this chapter becomes a non-problem, and CDC becomes the reliable bridge it was always supposed to be.