Change Data Capture (CDC) for ETL: 3 Easy Steps

In the ever-evolving landscape of data integration, Change Data Capture (CDC) is redefining how businesses can synchronize their increasing data volumes. According to a research report, the global data integration market size is projected to grow from USD 11.6 billion in 2021 to USD 19.6 billion by 2026. The main driving factor for this growth is the need for real-time data analysis and insights.

CDC ETL enables the identification and extraction of the modified data and then subsequently moves those changes to a downstream process. CDC is very critical to maintaining data consistency, efficiency, and decision-making accuracy during the ETL process. This blog is your comprehensive guide to change data capture, including how to implement it in ETL.

Let’s get started!

What is Change Data Capture?

Change Data Capture (CDC) is a data integration technique that tracks and captures changes made to data within a database in real-time. This continuous stream of data updates provides a powerful foundation for a wide range of applications, particularly in the realm of Artificial Intelligence (AI) and Machine Learning (ML).

Key Benefits of CDC:

• Real-Time Data Streaming: Enables continuous data flow, crucial for AI/ML models that require real-time insights and responsiveness.
• Enhanced Data Freshness: Delivers up-to-the-minute data updates, improving the accuracy and relevance of AI/ML models.
• Improved Model Performance: Provides a continuous stream of fresh data for model retraining and updates, leading to improved accuracy and performance.
• Reduced Data Latency: Minimizes data latency, enabling faster response times and more efficient decision-making in AI-driven applications.
• Streamlined Data Pipelines: Simplifies data integration and reduces the need for frequent full data refreshes, optimizing resource utilization.
• Improved Data Quality: Ensures data consistency and accuracy across different systems, crucial for the reliability of AI/ML models.
• Data integrity: CDC can be useful in ensuring data consistency and integrity in multiple systems.
• Real-time analytics: CDC can support real-time analytics and data science.
• Efficiency: CDC can minimize source data system load since CDC only deals with changed data.
• System reliability: CDC can enhance system reliability, particularly in cloud architectures.

Why Use Change Data Capture?

Change Data Capture is a potent technique to identify and capture changes in a database in real time. It allows streaming data from your primary database to other destinations, be it data warehouses, data lakes, or analytics dashboards. Here’s how CDC will benefit organizations:

Real-time Data Replication

• CDC ensures that any changes in your database are copied continuously to multiple destinations like data warehouses, data lakes, or secondary databases that provide real-time data consistency and availability.

Cost-Effectiveness

• Unlike traditional batch processing, which involves heavy provisioning on the network, CDC transfers data in smaller batches that are continuous.
  It saves network costs and eradicates spiky load behavior. This results in smooth and cost-effective data handling with real-time insights.
• With CDC, your data warehouse always captures the latest changes, giving you the ability to do real-time analytics and get a competitive advantage by making decisions with fresher data.

Higher Operational Efficiency

• CDC is cache invalidation-friendly since it automatically replaces outdated entries to ensure users always capture the latest data.
• Its ability also supports streaming updates to online data stores where production systems like Machine Learning models or search indexes are always current.

Compliance and Auditing

• Maintaining an entire change history, CDC supports data governance regulations and auditing.

Supplementary Applications

• Anomaly Detection & Streaming Analytics: Use CDC to detect anomalies in real-time
• Machine Learning Updates: Feed fresh data to online data stores for up-to-date predictions.
• Search Index Refreshes: Ensure search indexes are constantly updated with fresh data.

How Does Change Data Capture Work?

When you update a source database- usually a relational database such as PostgreSQL, MySQL, Oracle, or Microsoft SQL Server- you might have to update various related resources such as a search index and a cache. With CDC, you can easily avoid issues like dual writes to, as opposed to updating resources concurrently and precisely.

It can execute this by tracking row-level changes in database-source tables categorized as update, insert, and delete events- and then making those change notifications available to any other services or systems that depend on the same data. The change notifications are sent in the same order as they were generated in the original database.

This way, CDC ensures that all the interested parties of a given dataset are precisely informed of the change and can react accordingly, by either refreshing their own version of the data or by triggering business processes.

There are two flavors of CDC: push and pull.

• Push: The source database may push the updates to downstream services and applications.
• Pull: The downstream services and applications poll the source database at fixed intervals to pull the updated data.

Steps to Perform Change Data Capture

Change Data Capture (CDC) can be implemented using the following 3 steps:

• Step 1: Extract the Data
• Step 2: Transform the Data
• Step 3: Load the Data

Step 1: Extract the Data

Raw data is extracted from an array of sources and sometimes placed in a data lake. This data could be formatted in:

• JSON – Social media (Facebook, etc.)
• XML – Third-party sources
• RDBMS – CRM

Step 2: Transform the Data

The transformation stage is where you apply any business rules and regulations to achieve.

• Standardization
• Deduplication
• Verification
• Sorting

Step 3: Load the Data

Load this extracted transformed data into a new home by executing a task (job) from a CLI or GUI interface.

Change Data Capture vs Change Tracking

Change Tracking is the lesser-known counterpart of Change Data Capture. It’s a feature that allows the net changes made to data to be returned from a query easily. Change Tracking will let you know that a given row has changed since your last query. But, it doesn’t have an idea about:

• The different value changes that have been made to the row, or
• The number of times it was changed.

Change Data Capture, as the name suggests, tracks when the complete data has changed. You can capture an entire table or subset of columns through Change Data Capture.

TL;DR- Change Tracking is a subset of Change Data Capture.

Change Data Capture in ETL

In the era of Big Data, data has become more important for Business Intelligence and Enterprise Data Analytics. Data plays an important role in nearly every business operation. For your data to be valuable, you need a way to gather data from an unlimited number of sources, organize it together, and centralize it to a single repository. This means you need ETL or Data Integration processes. To understand the difference between the two, take a look at this resource.

Traditionally, data warehouses do not contain up-to-date data. Up-to-date data often resides in operational systems and are then loaded into the data warehouse in a set frequency. 

Discover the top CDC tools to efficiently capture and replicate real-time data changes across systems, ensuring seamless data integration and synchronization. Learn more at CDC Tools.

Change Data Capture in Distributed System

Even though CDC captures the changes in a database, it still needs a messaging service to send those change notifications to the applicable systems. One way to accomplish this is by considering the changes as individual events and sending them asynchronously. The technical term for such an architecture would be event-driven architecture (EDA).

Apache Kafka would be the ideal way to provide this communication between the database and consumers of the data that need a replayable, high-volume consumption pattern. Kafka has been designed to handle data streams from many sources and deliver the data to many destinations, with high scalability and throughput.

Change Data Capture would ensure that the events transmitted by Kafka are aligned with the changes in the original database or source system. Events are decoupled from the consumers in Kafka (asynchronous communication), allowing for a more robust delivery of changes.

Business Benefits of Change Data Capture

CDC Generates More Savings

90% of the world’s data was generated in just the past 2 years. The infrastructure of the internet, built in a few cases, a few decades ago, does not possess the bandwidth to move massive volumes of data instantaneously. This can pose a serious problem for businesses that wish to undertake projects with high data volumes, for instance, database migrations. These data transfers can severely congest network traffic. This might lead to cloud migrations that are costlier and slower in nature.

As opposed to this approach, CDC loads the data incrementally instead of all at once. Every time a data point gets modified in the source system, it gets updated on the target, needing minimal bandwidth. So, with CDC, businesses never have to worry about large data transfers that impact the network bandwidths.

CDC Generates More Revenue

With Change Data Capture, teams get provided with updated data that allow them to make decisions and perform actions with the best data available. CDC plays an important role in improving the accuracy and speed of the data so that the data is both accurate and faster. With CDC, you can act on opportunities sooner. It allows you to beat your competitors to deals while tackling a higher volume of opportunities.

CDC Protects Business Assets

Change Data Capture plays a pivotal role in protecting data, from destruction and deletion. By tracking changes, to metadata and data, CDC offers companies that face data loss a chance to rejuvenate affected datasets. Once data is gone, it can’t be regenerated. However, by protecting the data, Change Data Capture ensures that companies can recover their integral data to propel business growth.

CDC Gets Rid of Opportunity Costs

Change Data Capture, specifically, the log-based type, never burdens a production data’s CPU. Log-based CDC is modified directly from the database logs and does not add any additional SQL loads to the system. Apart from this, incremental loading ensures that data transfers have minimal impact on performance. In business terms, CDC eliminates the opportunity costs that pop up when a business is forced to slow down vital tech infrastructure.

Change Data Capture Techniques

At a high level, there are several techniques and technologies for handling the Change Data Capture processes (CDC process).

The top 4 CDC implementation techniques are: 

• Timestamp Based Technique
• Triggers Based Technique
• Snapshot Based Technique
• Log Based Technique

1) Timestamp Based Technique

This technique depends on a timestamp field in the source to identify and extract the changed data sets.

This example shows a snapshot of the table when a new record is created and a snapshot when the record with ID=101 is updated.

2) Triggers Based Technique

This technique requires the creation of database triggers to identify the changes that have occurred in the source system and then capture those changes into the target database.

The trigger functions are stored procedures that are automatically executed when a specific event, such as INSERT, UPDATE, or DELETE, occurs on a table. To capture any data changes, one trigger for each operation per table is needed. The data changes are stored in a separate table referred to as the event table or shadow table.

The implementation of this technique is specific to the database on which the triggers need to be created.

3) Snapshot Based Technique

This technique involves creating a complete extract of data from the source table in the target staging area.

Therefore, the next time the incremental data needs to be loaded, a second version or snapshot of the source table is compared to the original one for spotting the changes.

4) Log Based Technique

Almost all Database Management Systems have a transaction log file that records all changes and modifications in the database made by each transaction. 

In general, every DML operation such as CREATE, UPDATE, DELETE is captured in a log file in the database, along with the time-stamp or a database-specific unique identifier indicating when each of these operations was incurred.

This log-based technique depends on this log information to spot the changes and perform CDC operations.

When it comes to choosing the right CDC method, there is no clear-cut answer. It all boils down to the requirements for capturing changes and what the data in the target system will be used for.

If the use cases for the target system are based on the data being up-to-date at all times, then you should definitely implement a push-based CDC solution. If a push-based solution isn’t possible, you can always look to implement pull-based solutions. However, pull-based solutions are dependent on a large number of factors. First, if you can amend the source schema before adding update timestamps or row versions should be pretty simple by generating some database triggers. The overhead of managing an update timestamp system is far less than a row-versioning system, so using update timestamps is the way to go.

On the other hand, if modifying the source system isn’t possible then the only options at your disposal are: change scanning or leveraging any in-built change log capabilities of the source database.

When it comes to choosing the right CDC method, there is no clear-cut answer. It all boils down to the requirements for capturing changes and what the data in the target system will be used for.

Change Data Capture Real-World Use Cases

Following are the major use cases for implementing CDC in ETL:

• Transactional Analysis
• Data Duplication

1) Transactional Analysis

• Fraud detection
  You want to analyze transactions in some sort of batch manner to see if credit cards are being used from multiple locations at the same time.
• Kafka pipeline
  You want some sort of analysis done on a transaction level and not an aggregated level.

2) Data Duplication

• Database mirroring
  Database mirroring is a strategy used in High Availability (HA) and Disaster Recovery (DR) database deployments. It involves two or three SQL Server instances where one acts as a primary instance (principal), the other as a mirrored instance (mirror), while the third instance acts as the witness.
• Database replication
  Database replication is the process of copying data from a database in one server to a database in another server so that all users share the same level of information without any inconsistency. It can be a one-time operation or an ongoing process.

At this point, you might be wondering which is the better option, hand-coding the CDC infrastructure for ETL or investing in a tool that can handle this out of the box?

Hand coding comes with many challenges:

• Managing, supporting, and reusing code is complex.
• Having many coders onboard results in high maintenance costs.
• Custom-code developers are scarce.

Therefore, the opportunities become endless when you empower yourself and your team with the right data pipeline platform.

Apart from these, here are a few more real-world use cases of Change Data Capture:

• Microservices Integration: You can use CDC to sync microservices with traditional applications. This allows smooth transfer of data changes from legacy systems to microservices-based applications.
• Compliance and Auditing: You need to save a history of data changes to follow the strict data compliance requirements. You can also use CDC to save data changes for archiving or auditing requirements.
• Analytics Dashboards: You can use CDC to feed data changes to analytics dashboards to support effective decision-making for business intelligence.
• Cache Invalidation: Capture Data Change can also be used to invalidate caches. This ensures that the outdated entries in a cache are removed or replaced to make way for the latest versions.
• Full-text Search: You can use CDC Change Data Capture to automatically keep a full-text search index aligned with the database.
• CQRS Model Updates: Change Data Capture can be used to keep Command Query Responsibility Separation (CQRS) read models aligned with the primary models.

Why CDC is Essential for Data Warehouses

Change Data Capture (CDC) enhances data warehouses by:

• Supporting Real-Time Analytics: Enables real-time insights by transferring changes instantly.
• Keeping Systems in Sync: Maintains consistency between source systems and data warehouses.
• Enabling Cloud Migrations: Efficiently moves data across networks for seamless transitions.
• Optimizing Data Resources: Reduces operational overhead and improves resource efficiency.

CDC transforms data warehouses into dynamic hubs for real-time insights and seamless integration.

Change Data Capture Tools

Here are a few popular open-source Change Data Capture tools that you can consider for your workforce:

• Maxwell: Maxwell reads the MySQL binlogs and writes row updates as JSON to Kafka and Kinesis, to name a few platforms. Since Maxwell has low operational overhead, needing nothing other than MySQL and a space to write, it should be on the top of your ‘Change Data Capture Tools to Keep a Lookout For’.
• Debezium: Debezium is an open-source CDC platform that’s built on top of Apache Kafka. It has connectors to pull a change stream from the databases like MySQL, PostgreSQL, and MongoDB, and send that to Kafka. Kafka Connect is used for change propagation and detection here. You can either use Debezium as a standalone server or you can embed it into your application code as a library.

Change Data Capture Best Practices

Here are a few best practices for Change Data Capture that ensures peak efficiency:

• Make sure that it supports asynchronous pub/sub style change propagation to consumers.
• The order of changes for change data capture needs to be preserved. This allows the data to be propagated to the target system in the same format.
• Make sure that it supports light-weight message transformations as the event payload needs to match with the input format of target systems.

Now that we’ve highlighted all the major aspects of Change Data Capture, let’s round off the discussion with a bunch of frequently asked questions on the topic:

Conclusion

This blog introduced you to Change Data Capture (CDC) and explained the steps to implement it. Moreover, the blog discussed the relationship between the CDC and ETL process and also listed the various use cases of both of them. Also, it mentioned the limitations that you will face if you will perform the CDC process manually using the ETL method. Here’s another blog that can help you understand the benefits of CDC.

Learn about slowly changing dimensions and how to manage changes in your data with our in-depth guide on the subject.

Change Data Capture FAQs