Change Data Capture (CDC) is a technique for tracking changes in source systems and propagating them to downstream targets. This guide explains how CDC works and when it's the right choice.
What is Change Data Capture?
CDC identifies and captures data changes (inserts, updates, deletes) as they occur in source systems. Rather than comparing entire datasets, CDC efficiently processes only what has changed.
Traditional Approach (Batch):
- Extract entire dataset from source
- Compare with previous extract
- Identify differences
- Process changes
CDC Approach:
- Monitor source for changes in real-time
- Capture changes as they happen
- Propagate to targets immediately
- Maintain change history
CDC Implementation Methods
Log-Based CDC
Reads database transaction logs to identify changes.
Advantages:
- Minimal impact on source systems
- Captures all changes including deletes
- Preserves change order
- Very low latency
Considerations:
- Requires access to database logs
- Log format varies by database
- May require database configuration
Trigger-Based CDC
Uses database triggers to capture changes.
Advantages:
- Works with most databases
- Full control over captured data
- Doesn't require log access
Considerations:
- Performance impact on source
- Must manage trigger code
- May miss bulk operations
Timestamp-Based CDC
Queries for records modified since last check.
Advantages:
- Simple to implement
- Works with read-only access
- No database changes required
Considerations:
- Cannot detect deletes
- Requires timestamp columns
- May miss rapid changes
Diff-Based CDC
Compares current and previous snapshots.
Advantages:
- Works with any data source
- No source system requirements
Considerations:
- Higher resource consumption
- Latency depends on comparison frequency
- Scales poorly with data size
When to Use CDC
Real-Time Requirements
When downstream systems need fresh data:
- Operational dashboards
- Real-time analytics
- Event-driven applications
- Synchronization between systems
High-Volume Sources
When full extracts are impractical:
- Large transaction tables
- Systems with continuous activity
- Bandwidth-constrained environments
Audit and Compliance
When change history is required:
- Regulatory requirements
- Audit trails
- Point-in-time analysis
Efficient Integration
When minimizing source impact matters:
- Production systems sensitive to load
- Limited extract windows
- Systems with SLA requirements
When NOT to Use CDC
Small Datasets: If full extracts are fast and cheap, CDC adds unnecessary complexity.
Infrequent Changes: If data rarely changes, batch processing may be simpler.
Complex Transformations: If changes require significant business logic, batch may be easier to implement.
Source Limitations: If you can't access logs or implement triggers, CDC may not be feasible.
CDC Architecture Patterns
Streaming to Data Lake
CDC → Kafka → Data Lake (append changes)
Use for historical analysis and ML training data.
Real-Time Sync
CDC → Change Stream → Target Database
Use for keeping systems synchronized.
Event-Driven Processing
CDC → Event Bus → Microservices
Use for triggering downstream processes.
Hybrid Pipeline
CDC for real-time + Batch for reconciliation
Use when you need both speed and reliability.
Popular CDC Tools
- Debezium: Open-source, works with Kafka
- AWS DMS: Managed service for AWS
- Oracle GoldenGate: Enterprise solution for Oracle
- Qlik Replicate: Cross-platform commercial option
- Fivetran: Managed CDC for data warehouses
Best Practices
Monitor Source Impact: Even log-based CDC has some overhead. Monitor and tune.
Plan for Failures: Design for recovery when CDC processes fail.
Validate Completeness: Periodically reconcile to ensure no changes were missed.
Manage Schema Evolution: Plan for handling source schema changes.
Consider Ordering: Ensure changes are processed in correct sequence.
Conclusion
CDC is a powerful technique for keeping data synchronized with minimal latency and source impact. Choose it when real-time data freshness, change history, or efficient processing of high-volume sources is required. For simpler scenarios, traditional batch processing may be more appropriate.
Frequently asked questions
Does CDC replace source-to-target reconciliation?
No. CDC captures or propagates changes, while reconciliation evaluates whether the resulting populations and values meet the comparison requirements. Define checks for inserts, updates, deletions, late events and recovery behavior. A running capture process does not independently establish that every required target state is correct.
Can timestamp-based CDC miss deleted records?
Yes. A query that selects rows using a modified timestamp may not find a row after it has been physically deleted. The implemented design needs a supported way to represent or detect deletions if they matter to the target. Verify that behavior for the specific source and capture mechanism.
What should a CDC recovery test verify?
Test the expected target state after an interruption and restart, including missed changes, replayed events, ordering assumptions and deletions where relevant. Retain source and target observation boundaries. Validate both population and material values so a resumed process cannot be mistaken for evidence that recovery preserved the required business state.
