The ETL process, which stands for ‘extract, transform, load,’ involves extracting raw data from its source system, transforming it into the necessary structure, and then loading it into the destination system, typically a data warehouse. ETL helps to ensure that data is organized, clean, and ready for downstream use in analytics or applications.

Traditionally, ETL runs in large, scheduled batches, often overnight. This means the data in the target system is only as fresh as the most recent batch run.

The key difference is in how CDC extracts data. Instead of extracting the dataset in batches, CDC captures the changes as they happen, making it possible to always query the most current data instead of relying on periodic batch processing. This can help you respond faster to changes and gain more accurate insights.

Change data capture is primarily a process focused on the efficient extraction of all incremental changes from a source database, often in near real time, to minimize the load on the source system.

In contrast, slowly changing dimensions (SCD) is a data modeling technique applied in the target data warehouse or analytical system, focusing on how to store and manage the historical state of dimension data (like customer records or product attributes) over time.

While CDC is the tool for efficient data transport of the changes, these changes are typically applied using a specific SCD strategy to maintain a complete and accurate history within the destination system.

CDC can unlock a wide range of critical use cases, from ensuring data continuity to powering real-time analytics and AI.

• Replication: Secondary databases or backups remain consistent with the primary system by capturing real-time changes. This is critical for disaster recovery and high-availability architectures.
• Migration: Database migrations are simplified by tracking ongoing changes and ensuring the destination system reflects the source accurately, even during live migrations.
• Real-time analytics: By feeding live data into analytics systems, CDC helps you respond to trends and changes as they occur, offering up-to-the-minute insights.
• Data for AI training: CDC supplies the most current data, enabling AI and machine learning models to be trained with accurate, relevant information, improving predictions and decision-making.
• Building a data cloud: CDC is an essential service for creating a unified data cloud, streaming real-time changes from diverse, siloed sources (like transactional databases and SaaS applications) into a central analytics platform and back. This helps break down data barriers and creates a single source of truth.

Databases are the most common and reliable source for change data capture because of a core component they all share: the transaction log. This log is an immutable, ordered record of every change, making it the perfect foundation for capturing data modifications accurately and efficiently without impacting database performance. Here’s a deeper look at the key concepts.

Before any data is written to the actual database tables, the change (an insert, update, or delete) is first recorded in a transaction log. Modern CDC tools tap into this log to capture changes non-intrusively. This is a core part of database replication technology, and while the concept is universal, the name for this log varies across popular databases. Some notable examples:

• PostgreSQL: write-ahead log (WAL)
• Oracle: redo log
• SQL Server: transaction og
• MySQL: binary log (Binlog)

For large, existing databases, reading the entire history from the transaction log can be impractical due to network performance and other constraints. A common real-world pattern is to first perform a bulk load and then use CDC to synchronize. The process involves:

1. Taking a consistent snapshot or backup of the database at a specific point in time.
2. Loading that large snapshot into the destination system.
3. Starting the CDC stream from the precise log position where the snapshot was taken.

This “snapshot and catch-up” method ensures the destination is fully seeded with historical data and then kept perfectly in sync with all subsequent live changes. This is how managed services like Google Cloud's Datastream and Database Migration Service perform seamless and reliable backfills.

To manage this process reliably, CDC relies on unique identifiers within the transaction log, typically called log sequence numbers (LSNs). These numbers act as precise bookmarks for the stream of changes. They are critical for:

• Positioning: Knowing exactly where in the log to start or stop a stream
• Recovery: If a CDC process fails, it can restart from the last successfully processed LSN, guaranteeing that every change is captured exactly once without data loss or duplication

These mechanisms—the transaction log, the initial snapshot, and the log sequence number—work in concert to make database CDC both highly efficient and reliable.

Several technologies overlap with or complement CDC, offering varied approaches to managing data changes and enabling real-time processing. Here’s how CDC compares to other prominent approaches:

HTAP combines transactional (OLTP) and analytical (OLAP) workloads into a single system, providing immediate analytics on the data. While this makes HTAP well suited for unified analytics in a self-contained environment, it can struggle to synchronize with the diverse and distributed data sources that exist across an organization. CDC, on the other hand, excels at maintaining data consistency across multiple systems and enabling distributed workflows.

Federated queries (and a similar approach called ‘external tables’) allow a data warehouse or query engine to read data directly from an external source system, such as a transactional database, at the moment a query is run. This approach avoids the need to copy or move data, providing live access to the information where it resides.

This method can be excellent for ad hoc analysis or occasional queries, as it offers immediate access without the setup of a replication pipeline. However, for regularly used or performance-sensitive queries, it can be inefficient, as it puts a direct load on the source operational system with every query. Unlike CDC, which proactively pushes a stream of changes to a destination for optimized analytics, federation pulls data on demand, which may be unsuitable for high-frequency workloads.

Kafka is an open source platform designed for high-throughput data streaming and processing. While it efficiently manages large-scale data flows, Kafka complements CDC rather than replacing it. CDC focuses on capturing precise database changes in real time, while Kafka streams broader event data, such as a website click, an IoT sensor reading, or an item being added to a shopping cart. Together, these technologies can create a powerful combination for real-time workflows.

While CDC uses a no-code approach to replicate data, integration technologies that connect at the application layer connect systems and SaaS platforms using a low-code approach. If you want the ability to transform data as it moves between platforms, then application-level integration products, such as Google Cloud’s Application Integration, can be a better tool for the job.