Change Data Capture (CDC) — Real-Time Database Replication

This lesson on Change Data Capture (CDC) — Real-Time Database Replication is hands-on and example-driven. You will master the architecture and operational mechanics of Change Data Capture (CDC) to replicate database changes to downstream targets in real time. You will evaluate log-based, trigger-based, timestamp-based, and query-based extraction methods to design low-impact, scalable data replication pipelines.

What You'll Be Able To Do

• Differentiate the four primary CDC implementation methods based on source database overhead, portability, and accuracy.
• Architect a log-based CDC pipeline connecting OLTP transaction logs, stream processors, message queues, and analytical targets.
• Select appropriate CDC tooling such as Debezium, Oracle GoldenGate, or AWS DMS based on target infrastructure requirements.
• Formulate mitigation strategies for operational CDC bottlenecks including schema evolution, initial bootstrapping, and transactional consistency.

Detailed Concept Walkthrough

1. CDC Core Pipeline Architecture

Change Data Capture (CDC) is a pattern that tracks row-level modifications (INSERT, UPDATE, DELETE) in source databases and delivers them downstream with minimal source system latency.

• Change Capture: The ingestion layer observes source systems via transaction log parsing, database triggers, or state polling to record data mutations as discrete change events. This decouples the mutation event from downstream processing without requiring continuous, heavy analytical queries.
• Change Processing: The stream processing engine transforms raw change records into standardized schemas, filters out unneeded operational metadata, and enriches payloads with external context. This ensures that downstream targets ingest structured, typed, and normalized change streams.
• Change Delivery: A distribution component publishes processed events to message brokers (e.g., Apache Kafka) or direct target interfaces for real-time streaming or micro-batch integration. Reliable delivery mechanisms guarantee message ordering and buffer spikes in downstream consumption.

{
  "name": "mysql-cdc-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "tasks.max": "1",
    "database.hostname": "source-db.internal",
    "database.port": "3306",
    "database.user": "cdc_user",
    "database.password": "${file:/secrets.properties:db_pass}",
    "database.server.id": "184054",
    "topic.prefix": "cdc_stream",
    "database.include.list": "inventory",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-changes.inventory"
  }
}

Key Takeaway: CDC decouples analytical and event-driven consumers from OLTP databases by continuously converting raw mutations into an ordered stream of change events.

2. Log-Based vs. Trigger-Based Capture

Log-based CDC parses native write-ahead transaction logs out-of-process, whereas trigger-based CDC executes database functions synchronously within the transaction runtime.

• Mechanism: Log-based CDC reads the write-ahead log (e.g., MySQL binary log, PostgreSQL WAL) directly from disk or replication slots without executing queries against table storage. Trigger-based CDC registers AFTER INSERT/UPDATE/DELETE triggers that insert change rows into an auxiliary audit table.
• Under the Hood: Log readers run asynchronously and introduce virtually zero CPU or lock contention on live transaction tables. Triggers execute within the original transaction's execution boundary, increasing transaction commit latency and write amplification.
• Best Practice: Use log-based CDC whenever you have infrastructure access and native engine support; reserve trigger-based CDC only for heterogeneous or legacy databases where binary log access is restricted by security policies.

-- Trigger-based CDC shadow table recording updates
CREATE TRIGGER trg_customers_cdc
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO customers_audit_log (
        customer_id, old_status, new_status, change_type, changed_at
    )
    VALUES (
        OLD.id, OLD.status, NEW.status, 'UPDATE', CURRENT_TIMESTAMP
    );
END;

Key Takeaway: Log-based capture provides superior performance and full fidelity with zero schema modifications, while triggers add write overhead directly into source transactions.

3. Timestamp and Query-Based Polling

Timestamp and query-based extraction methods poll tables periodically using standard SQL queries, offering high portability across diverse engines at the expense of fidelity.

• Mechanism: Timestamp CDC extracts records where a last_modified column is greater than the watermark from the previous extraction run. Query-based snapshotting executes full or partial table comparisons between consecutive poll intervals.
• Under the Hood: Timestamp extraction fails to capture physical hard DELETE operations because deleted rows no longer exist to hold a modified timestamp. Query comparison introduces high I/O overhead on large tables as entire datasets must be scanned to detect diffs.
• Best Practice: Enforce soft-deletes (e.g., is_deleted = TRUE) and create indexed timestamp columns if forced to use polling CDC, and tune extraction frequencies to balance latency against source query load.

-- Watermark-based polling query
SELECT id, customer_id, order_total, status, updated_at
FROM orders
WHERE updated_at > :last_extracted_watermark
ORDER BY updated_at ASC
LIMIT 5000;

Key Takeaway: Timestamp polling cannot natively detect hard deletes and creates index overhead, making it suitable only as a lightweight fallback when log access is unavailable.

4. CDC Implementation Challenges and Tooling

Production CDC systems must manage distributed consistency, continuous schema evolution, and the initial table snapshot without downtime or message loss.

• Mechanism: Enterprise tools like Debezium, Oracle GoldenGate, AWS DMS, and Qlik Replicate handle the end-to-end lifecycle from reading logs to publishing structured change records into messaging backbones.
• Under the Hood: Bootstrapping requires taking a consistent point-in-time snapshot of the database while simultaneously buffering subsequent log changes from that exact log offset to prevent missing in-flight writes.
• Best Practice: Deploy a centralized schema registry and enforce backward-compatible DDL migrations so downstream stream processors do not fail when source table schemas evolve.

-- PostgreSQL logical replication setup for log-based CDC
ALTER SYSTEM SET wal_level = 'logical';
-- Set replica identity to full to capture old and new values on update
ALTER TABLE customers REPLICA IDENTITY FULL;
-- Create publication for downstream CDC consumers
CREATE PUBLICATION cdc_publication FOR TABLE customers, orders;

Key Takeaway: Managing initial table snapshots and schema evolution gracefully requires pairing log-based replication slots with structured schema registries.

Topics Covered in Change Data Capture (CDC) — Real-Time Database Replication

1. CDC Fundamentals (0:00 - 0:45) — The instructor defines Change Data Capture and its role in tracking real-time database modifications.
2. Three Core Components (0:45 - 1:20) — The lesson breaks down change capture, change processing, and change delivery mechanisms.
3. Log vs Trigger Methods (1:20 - 2:30) — The video evaluates the operational trade-offs between log-based parsing and database triggers.
4. Timestamp and Polling Methods (2:30 - 3:25) — The instructor explains the mechanics, limitations, and delete-tracking deficiencies of query-based CDC.
5. Log-Based Architecture (3:25 - 4:15) — A reference architecture is presented showing transaction logs flowing through CDC services to message queues and targets.
6. CDC Core Use Cases (4:15 - 5:10) — The presentation surveys use cases including data warehousing, microservices sync, cache invalidation, and auditing.
7. Tools and Challenges (5:10 - 6:00) — Popular tools like Debezium and AWS DMS are reviewed alongside challenges like schema evolution and bootstrapping.

Data Engineering Cheat Sheet

• WAL Level Logical — Enables logical decoding for CDC in PostgreSQL

  ALTER SYSTEM SET wal_level = 'logical';
  

• Replica Identity — Captures complete pre-change row values for updates

  ALTER TABLE orders REPLICA IDENTITY FULL;
  

• Create Publication — Exposes specified tables to CDC replication stream

  CREATE PUBLICATION app_cdc FOR ALL TABLES;
  

• Debezium Rest API — Registers a new CDC connector to Kafka

  curl -X POST -H "Content-Type: application/json" --data @connector.json http://kafka-connect:8083/connectors
  

• Timestamp Watermark Query — Extracts changed rows since prior high-watermark

  SELECT * FROM users WHERE updated_at > :high_watermark;
  

Comparison Table

Common Pitfalls

• Mistake: Relying on timestamp CDC when the source application performs hard deletes. Avoid: Implement soft-deletes with an is_deleted flag or switch to log-based replication.
• Mistake: Altering source table schemas without coordinating with CDC connectors. Avoid: Use schema registries and backward-compatible DDL migrations to prevent pipeline parsing crashes.
• Mistake: Overlooking initial data snapshot buffering during log initialization. Avoid: Read from a synchronized log position established at the exact moment of the snapshot.

FAQs

• Why does log-based CDC have minimal performance impact on the database? Log-based CDC reads the write-ahead log directly from the file system or replication stream outside the query execution path, preventing table locks and query engine overhead.
• How does CDC facilitate microservices synchronization? CDC extracts data modifications directly from service databases and publishes them as domain events to message queues, maintaining eventual consistency without tight coupling.
• What happens if a CDC pipeline crashes during high transaction volume? The log reader resumes streaming from the last committed offset stored in the message queue, replaying unprocessed log segments without data loss.