Change Data Capture (CDC) for Data Engineers

To sustain my writing, I've put this article behind a paywall. If you have a Medium membership, I'd really appreciate it. If you prefer to read it for FREE, please visit here and subscribe to my newsletter. Either way, you'll help keep my work going.

Intro

Change Data Capture (CDC) is a term that you can't find who coined it or when, unlike "lakehouse, "data lake"or "data warehouse."

It's more like an industry-standard data integration design pattern.

I just know that CDC has been there for a long time, backing many companies' data architecture, both operational and analytical.

—

This week, join me to learn about CDC.

I wrote an article about CDC before, but it was quite rigid and focused mainly on the technical details. This article is my effort to add more of my observations, insights, and thoughts.

Motivation

CDC is the process of tracking and extracting data changes in the source database(s) (INSERT, UPDATE, and DELETE operations) and then delivering those changes to whatever system needs them.

Instead of scanning the whole table to detect changes, mechanisms (we'll cover them later) help the system track the "deltas," or incremental changes to the table.

The "deltas" are then delivered downstream in batch or stream.

Something changed in a source, and something else needs to find out. That's the whole idea of CDC.

—

I used to think CDC was an exclusive data engineering technique.

Operational databases have been doing it to themselves for decades.

A primary MySQL or PostgreSQL instance accepts writes, and many replicas serve reads. Read replicas don't knock on the primary's door and ask it to export data every time they need to sync.

Instead, it reads the primary's transaction log (MySQL's binlog or PostgreSQL's WAL) and replays every change in strict transaction order.

I wrote about PostgreSQL's WAL here.

That's basically CDC. The database just doesn't call it that.

This pattern has also been used in many places: cache invalidation, search indexing, or cross-region sync.

—

Analytical use cases came later.

Imagine your company has a PostgreSQL database that backs the company's application.

As a data engineer, you have to extract data from the database, then load and process it in BigQuery.

The most straightforward approach is to ask the software engineer to grant you access to the database so you can export a snapshot, land it in object storage, and load it into BigQuery later.

However, the use case evolved:

• The company's app growth causes more data to come in; the snapshot dump process gets slower
• The dump process now contends for resources with the database's main operations.
• End users require lower latency. They can't tolerate the delay between snapshot exports.

—

In a case like this, CDC can help us.

Something changes in a source, and something else needs to know. It's like database replication, but instead of PostgreSQL-to-PostgreSQL, it's PostgreSQL-to-your preferred OLAP systems.

Implementation

There are many ways to implement CDC; they differ in how they capture changes, and in the form the changes take (batch or stream).

—

The first method involves periodically executing SQL queries to check for changed records since the last run. It commonly relies on a dedicated column in the source table, such as an updated_timestamp.

The CDC process maintains a "checkpoint" (the timestamp of the last run) and queries for all rows where the timestamp is greater than this checkpoint. It then updates the checkpoint for the next run, and so on.

If updated_timestamp > checkpoint → changes

This is the easiest method to implement, but it has some disadvantages:

• It can't track deletions. Because it relies on the query's result, and the deleted rows don't appear in the result
• It overwhelms the source. Frequent scanning increases the source's resource utilization. If the changes between runs are huge, the source must process a lot of data.
• Latency: The freshness of the data depends on the polling interval

This method is called Query-Based CDC.

—

The next method relies on database triggers; stored procedures run in the background to capture specific data-modification events on a table. For CDC, INSERT, UPDATE, and DELETE triggers are created on the source tables

When a change happens, the associated trigger fires and writes a record of that change into a separate table within the same database. The CDC process periodically reads new entries from the shadow table to capture changes.

Now, deletion can be tracked, but the limitations are still there:

• Performance impact: Every transaction to the table incurs additional writes (to the changes table).
• The delay remains: the CDC process must periodically read the changes table.
• Managing a large number of triggers can become complex

This method is called Trigger-Based CDC.

—

The next method is the most common one right now.

It operates by reading changes directly from the database's transaction "log".

Implementation varies by database. However, the high-level idea is the same.

When a process wants to process data, it brings it into memory, changes it there, and eventually writes it back to disk. But memory is volatile; if the machine crashes before that flush happens, the change is gone.

To prevent this, most OLTP databases force the writers to write down their "intention" to a log (MySQL's binlog or PostgreSQL's WAL).

The log is an append-only structure on disk that records every change as it happens.

If the machine dies before the modified data in RAM reaches disk, the database replays the log and reconstructs the change.

—

For CDC, the log becomes the reliable source of changes instead of a recovery tool. At a high level, the CDC process streams changes from the log to the OLAP system. No more querying any table.

Compared to the batch-oriented method, log-based CDC has some advantages:

• Minimal Performance Impact: This is its most significant advantage. Because it reads log files rather than executing queries, log-based CDC enables continuous change capture with minimal performance overhead.
• Completeness: The "log" is the ultimate source of truth for all data modifications.
• Low Latency: By streaming changes as they commit to the log, this method ships near-real-time changes downstream.

Because of this, log-based CDC is the most common approach today. However, the method is quite complex to implement: enabling logical replication, granting permissions for the CDC process, and infrastructure to absorb the stream (e.g., Kafka or even Flink to process the changes)

In the rest of this article, we will focus only on log-based CDC.

—

We will spend a little more time looking at a typical log-based CDC pipeline:

At the high level, it will look like this:

• Changes in Source Database: The process begins when a user commits a transaction (INSERT, UPDATE, or DELETE) to the database (e.g., PostgreSQL, MySQL).
• Transaction Log Write: The database writes the transaction's records to its transaction log before applying the changes in the data files.
• The log reader: A pipeline's component reads this log. It continuously monitors for new log records.
• The log publisher: This component will publish the new log record to the message system. Again, you rarely build this instance from scratch. The CDC connector will also handle this task.

You will rarely handle the log reading and publishing yourself; instead, you will rely on the available CDC connector. (e.g., Debezium Connector For PostgreSQL)

• Absorbing the log stream: The message system (e.g., Apache Kafka) then absorbs the log records. This step ensures reliable log delivery. It also decouples log publishers and log consumers, allowing each to operate at its own pace.

For example, the source can generate hundreds of changes per minute, but the consumer doesn't need to keep up with that rate, as it only needs to changes in the last 1 hour)

• Downstream Consumers: Downstream systems and applications subscribe to the message system, read the change events from there, and process them according to their specific needs

Next, we'll cover the challenges. As mentioned, we focus only on the log-based method.

Operation

For its robustness, log-based CDC is complex to set up correctly.

First, you have to manage several software components for the end-to-end pipeline: a log reader/publisher, a message system, a stream processing engine (optional), and a consumer.

It's quite a lot compared to exporting the data to object storage periodically.

As discussed, you rarely handle everything yourself; CDC connectors can abstract some of the complexity.

However, you still need to configure, scale, and monitor each component carefully.

—

Next is the permission grant.

The log reader/publisher must have the right permissions to read the database's change log.

Missing permissions could give you a headache at first. (I've been there)

—

Offset management is also a thing worth keeping in mind. At which position in the log change does the pipeline reset after it fails?

Again, CDC connectors will handle this for you, but you still need to be aware of it because it plays a crucial role in the correctness of your whole pipeline.

Gap offsets between re-run results can cause missing data, or re-consuming an already-processed offset can cause data duplication.

—

Bootstrapping is also a problem.

CDC is about capturing changes.

But these changes must be applied to the "base" state to be meaningful.

Your destination must match the source table's state when CDC begins.

To do that, you have to stream log changes from the very beginning up to the point where CDC would begin.

Or, you can dump a snapshot of that table once, load it to the destination, and start the CDC from there.

Exporting the base state is usually the common way for bootstrapping, given its one-time nature.

—

Someone adds a column in the source table.

Someone renames a column or changes its type.

Usually, the log-based CDC pipeline can capture these schema evolution events.

But how to apply them to the destination is 100% your call.

Schema evolution is a classic problem when you move data, whether it's traditional ETL or CDC.

—

Finally, you have to decide when to merge the "changes " into the "base state". The base state, after being merged with changes T, will result in a new base state.

When changes T+1 come in, the merge process must happen again to create a new base state. In theory, the process goes on forever.

You have to decide the delay between the merging of T and T+1.

If merges happen too frequently, you get lower latency, but you pay more for compute resources.

If merges happen infrequently, you pay less for compute resources, but you get higher latency.

CDC is a stream

And streaming ingestion is a problem in a lakehouse.

Open table formats like Iceberg or Delta Lake were originally designed for batch workloads.

When a process writes data to the lakehouse, it has to create associated metadata on top.

One data file, one transaction log (Delta Lake), or three pieces of metadata (Iceberg), then the final commit. These table formats rely on the metadata to ensure ACID, schema evolution, query optimization, or time travel.

Each metadata write is an object storage request.

This overhead is acceptable in a batch workload, given its benefit.

However, with streaming, where small data and frequent ingestion are the pattern, it's not. The commit process will add latency to each write request. The overhead adds up when writes happen frequently in stream ingestion.

—

Another problem with "small data + frequent ingestion" is that it creates many small data files.

Small files degrade read performance. Each file read requires an object storage call to open, read, and close the file. Many files accumulate overhead.

You can increase file size by batching data before writing to the lakehouse, but that adds latency.

Open table formats rely on compaction to merge small files into bigger files. This compaction happens in the background, asynchronously, and it needs careful configuration and monitoring because it consumes a lot of resources and can compete with the table's read/write operations.

—

If you're using a data warehouse solution like BigQuery or Snowflake, you won't need to worry much about these, since the vendors manage everything behind the scenes.

But if your company chooses to go with the lakehouse, you must keep these things in mind when deploying the CDC pipeline (or any streaming use case)

Outro

In this article, we discuss Change Data Capture motivation, how it's gonna be implemented, and its challenges.

Compared to the original ETL approach, CDC, more specifically, the log-based approach, is a very different paradigm because we have to deal with streams rather than batches.

Dealing with streams is always hard.

—

Thank you for reading this far; see you in my next article.