What is change data capture (CDC) and why do teams use it?

Summary

• Change data capture (CDC) is a data integration pattern that captures only the changes made to data inserts, updates, and deletes instead of processing entire datasets repeatedly.
• CDC generates incremental change feeds containing only modified records, reducing the volume of data processed and enabling efficient, real-time synchronization between source systems and analytics platforms.
• Teams use CDC to synchronize operational databases with analytics systems, preserve full change history for auditing and regulatory compliance, and keep analytical tables in sync as upstream systems evolve.
• CDC enables deterministic ordering through sequence numbers or timestamps, ensuring that late or out-of-order arrivals are applied correctly and consistently.
• Databricks AUTO CDC APIs simplify CDC implementation with declarative patterns, allowing teams to specify keys, sequencing, delete handling, and whether to store results as SCD Type 1 (current state only) or SCD Type 2 (full history).

What is change data capture (CDC) and why do teams use it?

Change data capture is a data integration pattern that captures changes made to data in a source system: inserts, updates, and deletes. Rather than processing entire datasets, CDC generates feeds containing only the modified records. For example, if you have an employee table in Oracle with 50 rows and one employee's job title changes, the CDC feed contains a single UPDATE record for that employee, not the entire table.
Each record includes the operation type (INSERT, UPDATE, DELETE), data values, and a sequence number or timestamp for ordering. The sequence number ensures late or out-of-order arrivals are applied correctly.

Why teams use CDC

Data synchronization: Data engineers need to replicate data from upstream sources such as relational databases (Oracle, Postgres, and other relational databases) into Databricks for analytics, reporting, and machine learning. As operational systems change, analytical tables must stay synchronized with those changes. CDC enables this synchronization efficiently without full table scans.
Efficiency and incremental loading: CDC is typically much smaller than the full dataset. Changes can be processed by downstream queries as incremental updates to the data, eliminating the need for bulk-load updates. This reduces compute and improves latency.
Historical tracking and auditability: Some teams need to preserve a full history of changes for auditability, regulatory requirements, or customer analytics. CDC allows you to reconstruct records as they were at a specific point in time, giving you complete history for auditing, point-in-time reporting, or trend analysis.
Operational alignment: CDC allows data to flow continuously and reliably across data warehouses, lakehouses, and streaming platforms. Because every change is captured in the order it occurs, dashboards and applications remain synchronized with operational systems, ensuring decisions are made on current data.

CDC feeds and record structure

A CDC feed is a stream of change records from a source database. Each record specifies what changed, how it changed, and when. The schema typically includes:

• Operation type: INSERT, UPDATE, or DELETE.
• Before values: For UPDATE and DELETE operations, the values before the change.
• After values: The values after the change (includes all columns for INSERT and UPDATE).
• Sequence number or timestamp: Ensures deterministic ordering even if records arrive out of order or late.
• Change timestamp: When the change occurred.

What to look for in CDC implementation

Aspect	Why it matters
Change capture completeness	Does the CDC system capture all changes (inserts, updates, deletes) reliably?
Ordering guarantee	Are changes ordered consistently so late/out-of-order arrivals are handled correctly?
Performance efficiency	CDC processes only changes rather than scanning entire tables, reducing resource consumption.
Implementation complexity	Is the CDC approach straightforward to set up and maintain for your data sources?
Latency	How quickly do changes propagate from source to target? Does it meet your use cases?
Scalability	Can the CDC system handle growing change volumes without degradation?

CDC implementation patterns

CDC can be implemented in multiple ways:
Log-based CDC: Captures changes from the database transaction log or replication log. This is non-intrusive and captures all changes, but it's specific to each database system.
Query-based CDC: Compares snapshots of a table to detect changes. This is simple to implement but can be expensive for large tables.
Trigger-based CDC: Uses database triggers to record changes in a shadow table. This is reliable but adds overhead to write operations.
CDC via message queues: Change events are published to a message queue or streaming platform (Kafka, Pulsar) for consumption downstream.

Databricks AUTO CDC for simplified CDC

Databricks provides AUTO CDC APIs to simplify CDC implementation. Rather than hand-writing complex merge pipelines, teams can define CDC logic with declarative code, specifying:

• Keys: Which columns uniquely identify records.
• Sequencing: How to order changes (by timestamp, sequence number, etc.).
• Delete handling: How to handle DELETE operations (hard delete, soft delete, mark as inactive).
• SCD strategy: Store results as SCD Type 1 (current state only, overwrites history) or SCD Type 2 (full historical tracking with validity dates).

Auto CDC handles the complexity of merging changes correctly, detecting duplicates, and maintaining data quality.

Use cases for CDC

Real-time analytics dashboards: Replicate operational data with CDC to keep dashboards fresh without full table scans.
Data warehouse synchronization: Keep a data warehouse or lakehouse in sync with transactional databases as they change.
Streaming data pipelines: Feed CDC events to streaming platforms for real-time processing, alerting, or event-driven workflows.
Audit trails and compliance: Maintain immutable change logs for regulatory requirements or customer-facing audit trails.
Incremental ETL pipelines: Build efficient pipelines that process only changed data, reducing compute and improving performance.
Master data management: Synchronize master data across systems using CDC as the authoritative change feed.

FAQs

Is CDC the same as incremental loading?

CDC is one approach to incremental loading, but they're not identical. CDC captures changes from a source system. Incremental loading is any approach that loads only new or changed data rather than the full dataset. CDC enables efficient incremental loading.

How do I handle late arrivals or out-of-order records in CDC?

CDC records include a sequence number or timestamp for ordering. When processing CDC feeds, use this ordering to apply changes in the correct sequence, even if records arrive late or out of order. Databricks AUTO CDC handles this automatically.