Change Data Capture (CDC): Syncing Search, Caches and Warehouses

Change data capture (CDC) turns every committed change in a database into an event stream by reading the database's own transaction log. Search indexes, caches, analytics warehouses and other services subscribe to that stream and stay in sync, without the application writing to each of them and without extra queries against the primary database.

The dual-write problem

Product search must reflect every product update: new prices, changed descriptions. The naive approach has the application write to both the database and the search engine. Then one day the search service times out: the database write succeeds, search misses thousands of products, and the two silently diverge. Writing to several systems from application code fails in three ways:

• Partial failures: the database write succeeds and the search write fails, so the product exists in one system but not the other.
• Race conditions: two updates reach the database as A→B but reach search as B→A, so the final states differ.
• Tight coupling: every write path must know every downstream system; adding analytics means changing application code.

How CDC works

Databases already keep a transaction log for durability and replication (MySQL's binlog, PostgreSQL's WAL, MongoDB's oplog). CDC taps that existing log: a product price update writes the row and appends to the log; the CDC connector reads the entry, produces an event with the new price and publishes it; a search indexer consumes it and updates the index.

1. The database writes each change to its transaction log.
2. A CDC connector (e.g. Debezium) tails the log and publishes change events.
3. A message broker (e.g. Kafka) stores the events durably, in order per key.
4. Consumers update search, caches, analytics and other services' read models.

A Debezium change event carries the row before and after the change, the operation and the source position:

{
  "op": "u",                                        // c = create, u = update, d = delete
  "before": { "id": 812, "name": "Trail shoe", "price": 59.00 },
  "after":  { "id": 812, "name": "Trail shoe", "price": 49.00 },
  "source": { "db": "shop", "table": "products", "lsn": 24023128, "ts_ms": 1760950000000 }
}

# A consumer that keeps Elasticsearch and Redis in sync with product changes
import json
from kafka import KafkaConsumer
from elasticsearch import Elasticsearch
import redis

es, cache = Elasticsearch("http://localhost:9200"), redis.Redis()
consumer = KafkaConsumer("shop.public.products", bootstrap_servers="localhost:9092",
                         group_id="product-sync", enable_auto_commit=False,
                         value_deserializer=lambda v: json.loads(v) if v else None)

for msg in consumer:
    event = msg.value
    if event is None:                                  # tombstone after a delete
        consumer.commit(); continue
    row = event["after"] or event["before"]
    if event["op"] == "d":
        es.options(ignore_status=404).delete(index="products", id=row["id"])
    else:
        es.index(index="products", id=row["id"], document=event["after"])   # idempotent upsert
    cache.delete(f"product:{row['id']}")              # invalidate; next read refills
    consumer.commit()                                  # commit offset only after applying

Why CDC beats dual writes

• Decoupling: the application knows nothing about downstream systems. A new consumer is a new subscription, not a change to every writer.
• Ordering: changes flow in commit order per partition (usually per key or shard), so downstream state converges to the same result.
• Completeness: every committed change is in the log, so partial failures can't drop events, as long as the log is retained long enough and consumer offsets are tracked correctly.
• Efficiency: tailing a log is cheap. Only modified rows stream: no polling, no full scans, no extra queries on the primary.

Implementation approaches

Related: the outbox pattern writes business events into an outbox table in the same transaction, and CDC publishes that table. It's the standard way to emit domain events reliably.

Use cases and downstream systems

• Product search: the primary database keeps transactional integrity while Elasticsearch, Algolia, Typesense or Meilisearch handle full-text search and faceting. A merchant edits a description, and the index updates within seconds without search queries touching the primary. See full-text search.
• Analytics pipelines: each sale streams to BigQuery, Snowflake or ClickHouse, so analysts query a warehouse that's minutes behind instead of loading production. See OLTP vs OLAP.
• Cache invalidation: a price change event makes the cache service delete that product's key, coordinating invalidation across cache nodes with no polling. See cache invalidation.
• Microservice data replication: the Order Service keeps a local, denormalized read model of product data fed by CDC from the Product Service database, instead of calling its API on every request, which cuts latency and coupling. See database per service and materialized views.

Trade-offs

• Eventual consistency: commit, capture, publish and apply take milliseconds to seconds. Meanwhile the database shows the new price and search shows the old one. Most products tolerate this; for critical reads (inventory counts at checkout), read the source database.
• Operational complexity: connectors, brokers and consumers must be deployed and monitored; failures and restarts handled; at-least-once (or effectively-once) delivery ensured; and lag between source and destination tracked. See delivery semantics.
• Schema changes need coordination: a new column may require changes to CDC configuration, the event schema and every consumer. See data contracts and schema evolution.
• For a simple app with one downstream system, dual writes plus periodic reconciliation may be enough. CDC becomes essential with several consumers, when ordering matters, or when you can't modify application code.

Common tools

Using CDC in an interview

Asked to add search to an e-commerce system, explain that full-text queries on the transactional database would hurt its write performance, then stream product changes via CDC into Elasticsearch or Algolia, reading the log without adding database load. State the eventual consistency explicitly (search may lag by seconds, fine for product search, not for inventory at checkout). Mention other consumers of the same stream: analytics, caches, materialized views. Close on the trade-off: more infrastructure, but at, say, 10 million searches a day you can't run those queries against the primary.