Table of Contents

“Businesses that manage their data effectively derive unique insights from it and tend to move quicker and be leaders in their industries.” That’s according to Kim Stevenson, Senior Vice President and General Manager of NetApp Foundational Data Services However, that data is only helpful if you can find what you need when you need it and put it to good use. This guide discusses CDC Change Data Capture to provide up-to-date information for real-time analytics.

How CDC Improves the ETL Process

According to International Data Corporation, "IDC estimates there will be 55.7 billion connected IoT devices (or “things”) by 2025, generating almost 80B zettabytes (ZB) of data."

Applications often start with a small footprint where a single database suffices to support the application. As the application grows the amount of information stored and the complexity of the data change.

These difficulties are compounded by a lack of a common architecture, with information stored in different formats across the company and in multiple public clouds. Once the database grows, it becomes increasingly vital to capture changes.

By design, ETL handles massive amounts of information. It is impractical to update the entire database for each change. CDC change data capture is more efficient and takes up fewer server resources. Additional advantages include:

Efficient Use of Resources

CDC limits the amount of information pushed through the pipeline. The result is a faster and more efficient ETL process.

Real-Time Updates

Before CDC, batch processing was the norm. Organizations would run their update jobs at once. The risk for stale metrics increased as users waited for the next batch run to see new input. CDC, Change Data Capture runs updates in smaller batches and more frequently. 

Replication

Disaster recovery efforts benefit from CDC. Using this approach dompqni3w can replicate the data to disaster recovery environments.

Integrate.io’s extensive catalog of pre-built code templates makes it easy to integrate systems to provide robust information for your ETL pipeline.

Stages of CDC

CDC involves a series of steps that ensure all updates are identified, captured, and stored in the target sources.

Detection

Applications make updates to the source database as users work in the system. Updates, inserts, and deletes are all captured as these events occur. The system logs these transactions into a transaction log. The detection system monitors the log for changes. Changes to the log are then pushed to the capture process.

Capture

This process is usually a messaging queue that receives updates from the detection process.

Propagation

The ETL process pushes the information to the target system systems, thus providing real-time figures to users.

Integrate.io’s no-code tools help you integrate your operational systems. With a CDC process, you can update your operational systems with the latest information from these systems to support real-time decision-making. 

CDC Data Capture Methods

CDC needs criteria to identify changes as they occur. There are several approaches. 

Timestamp

Applications track metadata for each row. That information includes the user associated with the change, the transaction's create date, and the transaction's last modified date and time. This approach to CDC keeps track of the date column and only captures rows that were changed since the last extract. Although this method is straightforward, there are drawbacks:

  • Deletes - Capturing deletes is a problem because there is no metadata for deleted records.
  • Availability - The date must be available on all tables and must follow the same format to ensure consistency in locating the correct records.
  • Resources - Using the timestamp field uses significant resources which puts strain on the transactional system.

Triggers

Triggers are stored procedures that “trigger” or executes when a defined event occurs. For example, the trigger may execute when a record is inserted, deleted, or updated. They can be called before or after these events and write the changes to a shadow table. The problems with this approach involve:

  • Negative Impact on Performance - Triggers run for each detected event, which can slow the application.
  • Issues with Truncate - A trigger won’t fire if the application performs a truncate. As a result, changes will not be recorded.

Table Differencing

The most resource-intensive approach to CDC Change Data Capture involves table differencing. This approach to CDC change data capture copies an entire table from the source to the target system and uses queries to compare differences between the tables. 

Copying entire tables requires significant computing power. This method cannot offer real-time figures as the comparison takes too long.

Log-Based

Database changes are stored in a log. These logs are used to help recover the database in the event of a failure. The challenges with this approach include:

  • No Documented Standards - Extracting changes from logs can be difficult as there are often no documented standards on how the information is stored. 
  • No Interface - Some vendors don’t provide an interface to the logs, which makes them inaccessible.

Integrate.io's suite of pre-built integrations helps you create a holistic picture of your business. Using CDC ensures this information remains available and reliable.

How Integrate.io Can Help

To learn more about Integrate.io’s approach to change data management or how it can help with your data management, reach out to a representative today. Don't forget to sign up for a trial to determine if these services are a good fit for your company.