Change Data Capture Essentials

Hey, and welcome back to Deltaplex Live — The Real-Time Enterprise Show. I'm Alex.

And I'm Maya. And if you are new here... welcome. You picked a good one to start with, honestly.

Yeah, no, I think so. Because today we are getting into something that I feel like comes up in basically every data architecture conversation we have had in the last year. Like, almost every single one.

Change Data Capture.

Change Data Capture. CDC. The thing that... okay, how do I put this. It sounds like it should be simple? Like, just move the data. But then you actually try to do it in production and you're like... oh. Oh, this is a whole thing.

[laughs] Yeah. Just move the data. Famous last words.

Exactly. So that's what we're digging into today — what CDC actually is, the different architectural approaches, why log-based is sort of the gold standard right now, and then we're going to get into the stuff people don't always talk about, like delivery semantics, edge cases, schema changes...

The fun stuff.

The fun stuff. Right. Okay, Maya — let's actually start with the problem statement. Because I think it's worth spending a minute on why this is even hard. Like, why can't you just... query the database?

Yeah, so... okay. The core requirement, right, is pretty simple to say out loud: replicate every committed change from a production database to downstream systems — your warehouse, your data lake, feature stores, whatever — in near real time, without slowing down the source system, without touching application code.

Right.

And that last part is the kicker. Without slowing down the source system. Because the production database is usually, like... the most sensitive system in the entire enterprise. You do not mess with it.

It's the thing keeping the lights on.

Exactly. So you've got this system that might be under heavy transactional load. The application team is not going to let you add triggers or schema modifications or any kind of custom write-path logic. And expensive extraction queries against production tables? Just... not acceptable.

And on top of that, downstream systems need everything, right? They don't just need inserts and updates. They need deletes. They might need schema changes. And the pipeline has to recover from failures without data loss or duplication.

Which, I mean... that's actually a distributed systems problem. Like, this is not a connector problem. It's database internals, state management, delivery semantics, schema evolution —

Operational reliability...

Right. So. With all that framing... let's talk about the three main approaches. Because not all CDC is created equal, and I think it's worth understanding why.

Yeah, absolutely. So the first one — and this is the one people usually reach for first because it feels simple — is polling. Query-based CDC.

Mmhm.

And the idea is straightforward, right? You just... query the source table periodically for records that changed since the last sync time. You've got some timestamp column or an incrementing ID, and you say give me everything that changed after this point.

And it can work. Like, for simple analytical extracts, low-frequency stuff, it can totally work.

Sure. But the problems start to pile up fast. So first —

Deletes.

Deletes! [laughs] Yeah. If a row gets deleted, it's... gone. The query can't find it anymore. So you just miss it entirely.

Which is a huge deal if you're trying to keep a downstream system in sync. Like, your warehouse has a record that no longer exists in production. That's a real correctness problem.

Right. And then there's the source load issue. As the table grows, or as you shorten the polling interval to get lower latency — which, by the way, you'll want to do — the query load goes up proportionally. You're basically adding work to the system you were trying not to touch.

And latency is always bounded by the polling interval. Like, you cannot get below whatever your cycle time is.

Yeah. And there are correctness issues too — clock drift, late commits, batch updates that all look like they happened at the same time...

It gets messy. Okay, so polling's out for serious production workloads. What about triggers?

Right. Trigger-based CDC. So the idea here is you set up a database trigger that fires on inserts, updates, and deletes, and it writes change records into an audit or staging table. And... it does capture more. You get deletes, you get low latency, you get a pretty complete picture.

The trade-off is the transaction path.

Yeah, trigger logic executes inside the transaction. Which means every single write to the source table now has extra overhead attached to it. Every. Single. One.

And under high throughput, that overhead is... just not acceptable. Like, you're adding deadlock risk, transaction failure risk, you need schema changes to add the audit table...

And then when the application schema evolves — which it will — upgrading becomes really painful because the trigger is coupled to it.

Yeah. So triggers are a step up from polling, but for high-throughput production systems, they're still not the right answer.

Which brings us to the good stuff. Log-based CDC.

Log-based CDC. Okay. So this is the one. And the core insight is actually kind of elegant, which is... the database is already maintaining a transaction log. For its own recovery, its own replication. Oracle has redo logs, MySQL has the binary log, Postgres has the WAL — the Write-Ahead Log — SQL Server has its transaction log.

And these logs already have everything you need. Every committed change, in order, with metadata.

Right. So instead of asking the database to re-read tables — which adds query load — you're just... observing a stream that's already there. The CDC engine reads the log, parses committed changes into structured events, and turns them into a downstream event stream.

And the benefits are significant. Minimal query load on production tables. No triggers, no application code changes. You capture inserts, updates, deletes, and even certain DDL events. Lower latency because you're seeing changes as commits appear in the log.

And crucially — better recoverability. Because log positions and checkpoints give you a precise way to know where you are and where to resume from.

Okay, so let's walk through how this actually works, step by step. Because I think this is where people's eyes either light up or... glaze over.

[laughs] Yeah. Okay. So step one is establishing a consistent starting point. Before you start continuous capture, you need a baseline. Usually that means an initial snapshot of selected tables combined with a precise source log position. And the key thing here is coordination — the system has to know exactly where the snapshot ends and where log-based streaming begins. Those two things have to connect perfectly.

Because if there's a gap —

You lose data. Or you duplicate it. Either way, bad.

Right. Okay, step two —

Reading committed changes from the log. So the CDC engine is parsing the transaction log and turning every committed change into a structured event. And that event includes... a lot. The source metadata, the operation type — insert, update, delete — the primary key, before and after values, commit timestamp, transaction ID, and the log position.

That before and after is really powerful, actually. Because now downstream systems can see what changed, not just what the current state is.

Exactly. Okay, step three — ordering. This is one that I feel like doesn't get enough attention.

Yeah.

Ordering is critical. Some downstream systems need events in commit order. Others need table-level or key-level ordering. A production CDC system has to define its ordering guarantees explicitly, and then actually deliver on them.

Because if events arrive out of order and the downstream system isn't ready for that... you can end up with really subtle correctness bugs that are hard to trace.

Right. And then step four — transformation and routing. Raw database changes aren't always in the format you need downstream. So CDC pipelines handle things like data type conversion, schema mapping, filtering sensitive columns, masking PII...

Routing different tables to different targets...

Right. And step five — reliable delivery. Which... is kind of the whole game, actually. Let's talk about that.

Yeah. Let's talk about exactly once. Because this is a phrase that gets thrown around a lot and I think it deserves some scrutiny.

[sighs] Okay, yes. Exactly once. So... here's the thing. In distributed data systems, exactly once is not something a connector can just... guarantee unilaterally. Like, you can't just flip a switch.

It's more of an architectural outcome than a feature.

Yes. Exactly. It requires the whole system to be built for it. So what does that actually mean? It means checkpointing source log positions only after you have durable downstream acknowledgement. It means using deterministic event identifiers or primary keys. It means applying idempotent upserts, merges, replace-by-key operations at the target.

Handling deletes explicitly...

Coordinating retries with target write semantics. Detecting and resolving partial batch failures.

It's a lot.

It's a lot. And if any one of those pieces is missing... you don't have exactly once. You have at-least-once on a good day.

Which, honestly, at-least-once with idempotent writes is... probably fine for most use cases. But you have to be intentional about it.

Right. You have to know what you're building. Okay — let's talk implementation specifics, because this is one I think practitioners really care about. The architecture is broadly consistent across databases, but the details are very different depending on what you're running.

Yeah, let's go through them quickly. Oracle first.

Oracle uses online and archived redo logs. One thing to be aware of — you may need to enable supplemental logging so that change records contain enough information for downstream reconstruction. The CDC engine needs to track the System Change Number — the SCN — which is Oracle's way of ordering changes. And large transactions can span multiple redo logs, so archive log retention has to be sized carefully for your expected downtime window.

MySQL?

MySQL uses the binary log — the binlog. And you really want row-based binlog format for reliable change reconstruction. Position-based or GTID-based checkpointing depending on your setup. Binlog retention has to exceed your maximum expected pipeline outage window — which, by the way, this is true for all of these databases —

Yeah.

And large BLOB or JSON fields can require memory and batch-size tuning. Also failover handling is interesting in MySQL because you have to account for replica topology and binlog continuity.

Postgres.

Postgres uses the Write-Ahead Log through logical decoding. The big thing with Postgres — and this is one I see bite people — is replication slot lag. Logical replication slots retain WAL until it's consumed. So if your pipeline goes down for a while... disk usage can increase significantly.

That's a dangerous one if you're not monitoring it.

Very. You need to be watching slot lag closely. And LSN checkpoints — Log Sequence Numbers — define your recovery position, so those need to be managed carefully.

And SQL Server?

SQL Server CDC is enabled at the database and table level. You've got cleanup jobs and retention windows that need to align with your pipeline recovery requirements. LSN checkpoints again. And permissions — this is true across all of them, but especially here — least-privilege access to required CDC artifacts. Don't give the pipeline more than it needs.

Okay, I want to spend a few minutes on edge cases, because I think this is where production CDC either holds up or falls apart. And there are a handful that come up constantly.

Yeah. Large transactions first.

Large transactions. So the pattern here is stream them in bounded chunks, preserve transaction metadata for downstream consistency — so consumers know this is all part of the same transaction — apply backpressure when targets can't keep up, use durable intermediate state if you need it, and resume from the last safe checkpoint after failure. The key thing is you're not just dumping everything in one shot.

Schema changes. This one is... [sighs] ...this one is nuanced.

It really is.

So you need to detect added, dropped, and renamed columns. You need to classify data type changes and primary key changes. For compatible changes — like, you added a nullable column — you might be able to auto-apply. But destructive changes? Dropped columns, type changes that break downstream schemas? Those need to pause and escalate for review.

You don't want the pipeline to just silently swallow a schema change and start producing wrong data downstream.

Exactly. Okay — deletes and tombstones.

Yeah, this is one that I feel like gets underestimated.

Deletes are first-class events in log-based CDC. Which is one of the huge advantages over polling, right? But you still need a contract for how they're handled downstream. Tombstones, soft-delete flags, merge logic — it depends on the target. And consumers need to know what they're getting.

And then log retention. This is a big operational one. You need to define your maximum acceptable pipeline downtime, set source log retention above that recovery window, and alert on lag and remaining retention risk. Because if the pipeline is down longer than your log retention window...

You've lost continuity. And now you're looking at a resnapshot. Which is painful.

Very. Okay, I also want to touch on security and governance quickly, because I think people underestimate how sensitive these pipelines are.

Yeah. CDC pipelines are carrying operational data. Real data. Possibly with PII, financial records, health records... You need to govern this the same way you govern the source systems. Least-privilege access for source connections. Encryption in transit, at rest where applicable. Role-based access control for pipeline management. Audit logs for configuration changes and data access.

Column-level masking or filtering for sensitive fields.

Right. And lineage metadata — knowing where data came from and where it went — which matters for compliance. And environment separation. Dev, test, production should not be sharing pipeline configurations.

Yeah. Okay. I feel like we've covered a lot of ground here. Let me see if I can bring it back down to earth. Like, what's the... what's the actual takeaway?

I think the takeaway is... don't underestimate this. Real-time replication looks simple on the surface. Read rows from here, write them over there. But as soon as production realities hit — deletes, schema changes, large transactions, failover, slow targets, partial writes, audit requirements, recovery after downtime —

It's a distributed systems problem.

It's a distributed systems problem. And log-based CDC is the architecture best suited for it because it separates data capture from the application transaction path entirely. But even log-based CDC requires careful engineering. Checkpoints, ordering, schema evolution, delivery semantics, operational controls.

The goal — and I think this is a nice way to put it — is to capture committed changes with minimal source impact, deliver them reliably, and govern the full data flow from source to destination. That's the foundation for production-grade real-time analytics, operational reporting, AI systems, event-driven applications...

All of it. Yeah.

Alright. I think that's a really good place to land. This has been a great conversation.

It has! I feel like we could do a whole episode just on Postgres WAL...

[laughs] Oh, don't tempt me. Maybe we will.

Listeners, if you want to go deeper on any of this — the delivery semantics stuff, the database-specific implementation details, the checklist for production readiness — we have a link to the full technical brief in the show notes. Seriously, it's worth a read.

And if you enjoyed this episode, subscribe wherever you get your podcasts. Leave a review if you're feeling generous — it genuinely helps. And send us your questions. If there's a topic you want us to dig into, we want to hear it.

Find us on LinkedIn, find us at deltaplex dot ai... Alex, what are we calling this one?

Change Data Capture Architecture: Zero-Impact Real-Time Replication.

Love it.

I'm Alex.

And I'm Maya.

Thanks for listening to Deltaplex Live — The Real-Time Enterprise Show. We'll see you next time.