Change Data Capture (CDC)

CDC captures only the delta—the inserted, updated, or deleted rows—since the last capture point. This is fundamentally different from batch-based full table extracts, which move entire datasets.

• Efficiency: Drastically reduces network load, compute resource consumption, and storage I/O compared to full refreshes.
• Low Latency: Enables near real-time data propagation by processing only new changes.
• Example: Instead of transferring a 1TB customer table nightly, a CDC system streams only the 50MB of changes that occurred in the last 5 minutes.

CDC can be implemented using several low-level techniques, each with different trade-offs in performance, reliability, and implementation complexity.

• Log-Based CDC: Reads the database's native transaction log (e.g., MySQL's binlog, PostgreSQL's WAL). This is the most efficient and non-intrusive method, as it captures changes made by any application.
• Trigger-Based CDC: Uses database triggers to write changes to a separate shadow table. This adds overhead to the source database's write operations.
• Timestamp/Version-Based CDC: Queries a table for rows where a last_modified timestamp or version column has changed. This is simple but cannot capture hard deletes and may miss rapid, overlapping updates.

A robust CDC system is stateful, meticulously tracking its position in the source change stream to guarantee exactly-once or at-least-once delivery semantics.

• Checkpointing: The CDC process persistently stores a log sequence number (LSN) or timestamp to resume from after a restart, preventing data loss or duplication.
• Ordered Delivery: Changes are typically emitted in the order they were committed in the source database, preserving transactional integrity for downstream consumers.
• Failure Recovery: Statefulness enables the system to recover from network interruptions or consumer failures and continue processing from the last acknowledged offset.

CDC doesn't just move raw data; it captures and propagates the schema of the change event. This is essential for downstream systems to correctly interpret the data.

• Event Envelope: A CDC record typically includes metadata like the operation type (INSERT/UPDATE/DELETE), source table, commit timestamp, and often the before and after state of the row.
• Schema Evolution: As source tables evolve (e.g., a column is added), the CDC stream must adapt. Advanced systems integrate with a schema registry to manage compatibility and notify consumers of schema changes.
• Format: Events are commonly serialized into formats like Avro, JSON, or Protobuf for transmission via message brokers like Apache Kafka.

CDC is the foundational pipeline for modern, event-driven data architectures, moving data ecosystems from periodic batch updates to continuous streams.

• Data Lake/Lakehouse Ingestion: Feeds raw change events into a bronze layer for immutable historical storage.
• Real-Time Analytics: Powers up-to-the-second dashboards and monitoring systems by streaming changes directly to analytical databases like Apache Pinot or ClickHouse.
• Cache Invalidation & Search Index Updates: Keeps external caches (e.g., Redis) and search indices (e.g., Elasticsearch) synchronized with the source of truth.
• Microservices Integration: Facilitates the outbox pattern, allowing services to publish reliable, ordered events based on database transactions.

The CDC stream itself becomes a primary source of operational metadata and a backbone for data observability platforms.

• Data Freshness Monitoring: The constant flow of CDC events provides a direct, real-time signal for data freshness and pipeline health.
• Lineage Generation: By tracking the flow of change events from source to destination, CDC is instrumental in automating data lineage mapping.
• Impact Analysis: Understanding which downstream systems consume a CDC feed allows for precise impact analysis before making schema changes to the source table.
• Audit Trail: The immutable stream of change events creates a reliable audit trail for compliance, showing exactly when and how any record was modified.

The most robust and performant CDC method, which reads the database's native transaction log (e.g., MySQL's binlog, PostgreSQL's Write-Ahead Log). It captures changes as they are committed, providing low latency, minimal performance impact on the source, and a complete, ordered history. This method is non-intrusive and does not require schema modifications.

• Examples: Debezium (reads binlog/WAL), AWS DMS (using log reader), Oracle GoldenGate.
• Advantages: High performance, point-in-time recovery, captures deletes.
• Considerations: Requires database configuration to enable and retain logs.

Uses database triggers to capture changes. When an INSERT, UPDATE, or DELETE occurs, a trigger fires to write a record of the change to a separate shadow table or queue. This method is synchronous with the transaction.

• Implementation: A trigger on each source table writes change events to a staging table.
• Advantages: Works on databases without accessible transaction logs. Guarantees capture.
• Drawbacks: Adds overhead to every write transaction, can impact source performance, and increases complexity through trigger management.

An application periodically polls source tables for changes by querying based on incremental markers like an updated_at timestamp or an auto-incrementing ID. This is a simple, application-level approach.

• How it works: SELECT * FROM orders WHERE updated_at > LAST_POLL_TIME.
• Advantages: Simple to implement, universal compatibility.
• Drawbacks: High latency, misses hard deletes, significant read load on source, and can miss intermediate states if multiple updates occur between polls.

A database-engine-native CDC feature, notably in Microsoft SQL Server and Oracle. The database automatically records changes to tracked tables in system-managed change tables. This is a hybrid approach where the database provides a queryable CDC interface.

• Mechanism: Enable CHANGE_TRACKING or CHANGE_DATA_CAPTURE at the database/table level.
• Advantages: Reliable and integrated, provides easy SQL querying of changes, handles net changes.
• Drawbacks: Vendor-locked, adds internal storage overhead, management is database-specific.

The tracking of data's origin, movement, transformations, and dependencies across systems and processes over its lifecycle. For CDC, lineage is critical to understand impact analysis and debug issues by tracing a changed record from the source database through all replication and transformation steps to its final destination.

• Column-Level Lineage provides granular visibility, showing how individual fields propagate.
• Essential for data observability, allowing teams to quickly assess the blast radius of a source schema change or a corrupted CDC stream.

The practice of managing changes to a data schema—such as adding columns or modifying data types—over time while maintaining compatibility with existing data and consumers. CDC tools must handle schema evolution gracefully to avoid breaking downstream pipelines.

• Involves strategies like backward and forward compatibility.
• Often managed in conjunction with a Schema Registry, which stores and enforces compatibility rules for data in motion (e.g., Avro, Protobuf schemas).

A centralized inventory and management system that stores, organizes, and provides access to technical and business metadata about an organization's data assets. A catalog ingests metadata from CDC pipelines, databases, and BI tools to enable data discovery, governance, and lineage tracking.

• Platforms like DataHub, OpenMetadata, and Apache Atlas are modern examples.
• Catalogs provide the single pane of glass for understanding what data exists, where it comes from (including CDC sources), who owns it, and how it can be used.

A formal, versioned agreement between data producers and consumers that specifies the schema, semantics, quality, and service-level expectations (e.g., freshness, latency) for a data product. In a CDC context, a contract defines the reliable interface for a change stream.

• Acts as an API contract for data, ensuring producers do not break downstream consumers during schema evolution.
• Critical for implementing Data Product thinking within architectures like Data Mesh, where domains own their data outputs.

The measurement of the timeliness and delivery speed of data from source to consumer. For CDC pipelines, freshness is a key Service Level Objective (SLO), measuring the lag between a change occurring at the source and its availability in the target system.

• Metrics include end-to-end latency and pipeline processing delay.
• A core component of Data Observability and Quality Posture, ensuring that real-time analytics and decisions are based on current data.

A continuous, ordered log of data change events (inserts, updates, deletes) emitted by a source system. CDC mechanisms create these streams, which are the primary transport for real-time data integration.

• Often built on database write-ahead logs (WAL) like MySQL's binlog or PostgreSQL's logical decoding.
• Consumed by stream-processing frameworks (e.g., Apache Kafka, Apache Flink) for real-time transformation, enrichment, and loading into sinks like data warehouses or caches.