The growth of corporate data and the need for more corporate applications and systems are not trends that will soon slow down. Data has become an essential component of commercial success and a measure of the value of a company. Investing in platforms, processes, and people that can effectively protect, transform, and leverage data is the hallmark of a modern data-driven enterprise.

Technology continues to move at the speed of light and so do the processes that power it. Any company dealing with data across multiple environments needs a solid data integration strategy to ensure that the data flow from conception to analysis is as smooth and error-free as possible. This is where change data capture (CDC) comes in, especially when combined with extract, transform, load (ETL). This article will define CDC, introduce several methods for implementing it, and discuss how it can be beneficial for IT professionals performing ETL.

Table of Contents

  1. What Is Change Data Capture?
  2. Change Data Capture Methods
  3. Why Is CDC Necessary for ETL?
  4. How Xplenty Can Help

What Is Change Data Capture?

Change data capture (CDC) is a method of keeping your data up to date so it can be accessed and assessed for a variety of purposes. CDC is a part of the data integration process — it "captures" changes in your data that occur in your source database, then populates the changes in the target database. It enables sophisticated analytical databases like data warehouses and data lakes to recognize and update data that is stored in a variety of other places like relational and NoSQL databases.

Outside of change data capture, the primary method of updating data across systems is to use batch processing. In batch processing, data is grouped and updated based on a schedule or other pre-determined criteria. It is a simple method that is easy to initiate, but it cannot be executed in near real-time and is not effective for large data sets. Situations where CDC is particularly necessary, include cases where companies need to:

  • Process frequent transactions.
  • Access near real-time dashboards.
  • Share data through APIs.
  • Build operational data stores.
  • Make time-sensitive decisions.
  • Integrate data from mergers and acquisitions.

CDC is a very resource-friendly method of updating data because only the changed data is moved from source to target, rather than a new copy of the entire database.

Change Data Capture Methods

Change data capture can operate using either a push or pull approach. Push means that changes are pushed from the source system to the target. Pull means that the target database periodically scans the source database for changes and then pulls in the changed data. There are many different ways to enable change data capture, from manual techniques to automated tools. The more data stored in the source database, the more complicated the effort. Multiple CDC solutions can be used in large, more complex IT environments. Here's a quick overview of the most common techniques, using push, pull, or a combination of the two:

Trigger-Based CDC

Triggers can be programmed to execute after specified events take place (such as an updated table), and then record the change in a changelog. The changelog is then used to update the target database.

Timestamp CDC

This technique relies on data in a column that identifies the time of the last change. A row that has a timestamp that is more recent than its previous timestamp is identified for capture.

CDC Scripts

Developers can code CDC at the application level by adding fields to the schema or using existing rows or even metadata to identify that data has been updated.

Log-Based CDC

This technique leverages transaction logs that record database events for use in database recovery. Inserts, updates, and deletes are read from the native logs, then applied to the target system.

Why Is CDC Necessary for ETL?

As company data grows and evolves, managing and maintaining the integrity of that data becomes increasingly important. Updated, real-time data is necessary in order for companies to fully leverage the capabilities of business intelligence applications, machine learning algorithms, and enterprise analytics. Extract, Transform, Load (ETL) is at the core of these capabilities because it performs the exchange of data between these different analytics systems and a company's core databases. An ETL process is completed by extracting data from the various source systems, transforming it to match the format of the destination, then storing it in staging tables in the target system.

CDC is a game-changer when it comes to ETL. Rather than running ETL in the traditional manner with batch loads, CDC allows a continuous exchange of information as data flexes, increases, and updates. It increases the efficiency of ETL while guaranteeing that data pulled into internal and external applications is fully updated and accurate, no matter when it is pulled. In addition, CDC benefits ETL in the following ways:

  • Reduces the resources needed for ETL: Because CDC identifies the rows and tables that have data that has changed, it only pulls a portion of the database rather than the whole thing.
  • Updates data in near real-time: Since ETL is executed continuously rather than in batches, data is always up to date and accurate.
  • Reduces data transferring costs over WAN because CDC sends only incremental changes.

For modern organizations using sophisticated tools for forecasting and trend analysis, competitive intelligence, and internal operational analysis, CDC is a vital component of core IT.

How Xplenty Can Help

Effectively using change data capture for ETL isn't easy without a sophisticated tool to recognize, pull, and store changed data in your target systems. That's where Xplenty comes in. Xplenty features the most effective methods of CDC: log-based and trigger-based. This enables you to keep the data in your target applications and systems continuously up to date using minimal resources and without manual intervention. In addition, Xplenty has the ability to integrate data from more than 100 data stores and SaaS applications.

Discover more about how Xplenty simplifies and speeds ETL by contacting our team of data integration professionals. Be sure to ask about how you can launch a 14-day pilot.