Change Data Capture (CDC) is the practice of turning the changes made to a database (inserts, updates, deletes) into a stream that other systems can consume, instead of having those systems re-query the tables. Interviewers probe it because it is the honest answer to "how do I keep a search index, a cache, a warehouse, or an embeddings store in sync with my database without dual writes?", and because the follow-ups (ordering, duplicates, schema changes, what happens when the consumer is down) separate people who have read a blog post from people who have thought about failure. For a Backend or AI engineer it is the plumbing behind derived data: whenever a document row changes and its vectors or its Elasticsearch copy must change with it, something is doing CDC.
1. POLLING / QUERY-BASED 2. TRIGGER-BASED 3. LOG-BASED
SELECT ... WHERE AFTER INSERT/UPDATE/DELETE read the database's own
updated_at > :last_seen trigger writes to a write-ahead log / binlog
change_log table (what replication reads)
app tables app tables --trigger--> app tables WAL / binlog
^ change_log | |
| periodic query ^ | (normal | tail
consumer | poll | writes) v
consumer consumer
misses: deletes, and every cost: extra write inside cost: needs log access,
intermediate update between EVERY writing transaction; a slot/retention to manage;
two polls; needs an indexed triggers must be maintained output is physical row
updated_at that is always for each table; adds lock and changes, not business events
set correctlyPolling is the simplest and the leakiest. It sees only the current state of rows whose timestamp moved, so a row deleted between polls is invisible (unless you use soft deletes) and two updates between polls collapse into one. It also depends on every writer remembering to bump updated_at, and on that clock not going backwards. Trigger-based CDC fixes those gaps because the trigger fires for every change, but the trigger does extra writes inside the user's transaction, so it slows the write path and holds locks longer, and a forgotten trigger on a new table silently loses changes. Log-based CDC reads the same log the database already writes for crash recovery and replication (the PostgreSQL WAL through logical decoding, the MySQL binlog), so it adds no work to the writing transaction and sees every committed change in commit order, including deletes. The cost moves elsewhere: you need permission to read the log, you have to manage retention, and what you get out is a row-level change, not a business event.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT, updated_at INTEGER NOT NULL);
CREATE TABLE change_log (seq INTEGER PRIMARY KEY AUTOINCREMENT, op TEXT NOT NULL, row_id INTEGER NOT NULL,
old_email TEXT, new_email TEXT);
CREATE TRIGGER customers_ai AFTER INSERT ON customers BEGIN
INSERT INTO change_log(op,row_id,old_email,new_email) VALUES ('I',NEW.id,NULL,NEW.email);
END;
CREATE TRIGGER customers_au AFTER UPDATE ON customers BEGIN
INSERT INTO change_log(op,row_id,old_email,new_email) VALUES ('U',NEW.id,OLD.email,NEW.email);
END;
CREATE TRIGGER customers_ad AFTER DELETE ON customers BEGIN
INSERT INTO change_log(op,row_id,old_email,new_email) VALUES ('D',OLD.id,OLD.email,NULL);
END;
INSERT INTO customers VALUES (1,'Asha','[email protected]',100),(2,'Ravi','[email protected]',100);
-- a polling consumer runs "WHERE updated_at > 100" after these changes:
UPDATE customers SET email='[email protected]', updated_at=200 WHERE id=1;
UPDATE customers SET email='[email protected]', updated_at=200 WHERE id=1;
DELETE FROM customers WHERE id=2;
.headers on
.mode column
.print polling (updated_at > 100) sees:
SELECT id, email FROM customers WHERE updated_at > 100;
.print trigger-based change_log sees:
SELECT * FROM change_log ORDER BY seq;
-- Real output (sqlite3, run locally):
-- polling (updated_at > 100) sees:
-- id email
-- -- --------------
-- 1 [email protected]
-- trigger-based change_log sees:
-- seq op row_id old_email new_email
-- --- -- ------ ------------ --------------
-- 1 I 1 [email protected]
-- 2 I 2 [email protected]
-- 3 U 1 [email protected] [email protected]
-- 4 U 1 [email protected] [email protected]
-- 5 D 2 [email protected]Polling reports one row: the final state of customer 1. It never learns that customer 2 was deleted (the row is simply gone, and there is nothing to match updated_at > 100) and it never sees the intermediate email. The trigger log has all five changes, in order, with old and new values, which is what a downstream system needs to stay correct. That is the essential difference: polling gives you "state now", change capture gives you "what happened". Log-based CDC gives you the same completeness as the trigger log without the trigger's write cost.
DUAL WRITE (broken) OUTBOX (correct)
BEGIN; UPDATE orders ...; COMMIT; BEGIN;
-- crash here, or broker is down -- UPDATE orders ...;
broker.publish(OrderPlaced) INSERT INTO outbox(aggregate_id, type, payload) ...;
=> DB says PLACED, no event exists COMMIT; -- both or neither
=> downstream is wrong forever -- separate relay (or CDC on the outbox table):
-- read unpublished rows, publish, mark/ignore
Reverse order (publish, then commit) => event exists iff the business change committed
fails the other way: an event for a => delivery is at-least-once, so consumers dedupe
change that then rolls back.A database and a message broker cannot be committed atomically by application code (short of distributed transactions, which most brokers and managed services do not offer cleanly). Any order of "write DB, publish event" has a crash window in which one happened and the other did not. The outbox pattern removes the window by making the event itself a row in the same database, inserted in the same local transaction as the business change, so atomicity comes from the one thing that can provide it. A relay process then moves outbox rows to the broker. Two common relays exist: a poller that reads rows where published = 0, or log-based CDC tailing the outbox table (the Debezium outbox event router works this way), which avoids polling load and lets you delete or partition outbox rows freely. Either way the relay can crash between "publish" and "mark published", so the same event can be delivered twice. The next section shows exactly that.
import sqlite3, json
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT NOT NULL);
CREATE TABLE outbox (seq INTEGER PRIMARY KEY AUTOINCREMENT, aggregate_id INTEGER NOT NULL,
type TEXT NOT NULL, payload TEXT NOT NULL, published INTEGER NOT NULL DEFAULT 0);
""")
def place_order(order_id):
with db: # business change + outbox row in ONE transaction: both or neither
db.execute("INSERT INTO orders VALUES (?, 'PLACED')", (order_id,))
db.execute("INSERT INTO outbox(aggregate_id,type,payload) VALUES (?,?,?)",
(order_id, "OrderPlaced", json.dumps({"order_id": order_id})))
def ship_order(order_id):
with db:
db.execute("UPDATE orders SET status='SHIPPED' WHERE id=?", (order_id,))
db.execute("INSERT INTO outbox(aggregate_id,type,payload) VALUES (?,?,?)",
(order_id, "OrderShipped", json.dumps({"order_id": order_id})))
broker = [] # stand-in for Kafka: an append-only list
def relay(crash_before_mark=False):
rows = db.execute("SELECT seq, aggregate_id, type FROM outbox WHERE published=0 ORDER BY seq").fetchall()
for seq, agg, typ in rows:
broker.append((seq, agg, typ)) # 1) publish
if crash_before_mark:
print(f" relay CRASHED after publishing seq={seq}, before marking it")
return
db.execute("UPDATE outbox SET published=1 WHERE seq=?", (seq,)) # 2) mark
db.commit()
applied = {} # aggregate_id -> last seq applied (consumer's dedupe state)
read_model = {} # aggregate_id -> status
def consume(msg):
seq, agg, typ = msg
if applied.get(agg, 0) >= seq:
print(f" consumer: skip duplicate/old seq={seq} ({typ})")
return
applied[agg] = seq
read_model[agg] = "PLACED" if typ == "OrderPlaced" else "SHIPPED"
print(f" consumer: applied seq={seq} ({typ}) -> order {agg} = {read_model[agg]}")
place_order(1); ship_order(1)
print("relay run 1 (crashes after first publish):")
relay(crash_before_mark=True)
print("relay run 2 (restarts, re-reads unpublished rows):")
relay()
print("broker contents:", broker)
print("consumer processing the broker log:")
for m in broker: consume(m)
print("read_model:", read_model)
print("orders table:", db.execute("SELECT * FROM orders").fetchall())
print("unpublished outbox rows:", db.execute("SELECT COUNT(*) FROM outbox WHERE published=0").fetchone()[0])
# contrast: the naive dual write (commit DB, then publish)
print("\nnaive dual write:")
db.execute("INSERT INTO orders VALUES (2, 'PLACED')"); db.commit() # DB commit succeeds
print(" DB committed order 2; process crashes before broker.append(...)")
print(" orders:", db.execute("SELECT id FROM orders").fetchall(), " broker has order 2 event:",
any(m[1] == 2 for m in broker))
# Real output (python3, run locally):
# relay run 1 (crashes after first publish):
# relay CRASHED after publishing seq=1, before marking it
# relay run 2 (restarts, re-reads unpublished rows):
# broker contents: [(1, 1, 'OrderPlaced'), (1, 1, 'OrderPlaced'), (2, 1, 'OrderShipped')]
# consumer processing the broker log:
# consumer: applied seq=1 (OrderPlaced) -> order 1 = PLACED
# consumer: skip duplicate/old seq=1 (OrderPlaced)
# consumer: applied seq=2 (OrderShipped) -> order 1 = SHIPPED
# read_model: {1: 'SHIPPED'}
# orders table: [(1, 'SHIPPED')]
# unpublished outbox rows: 0
#
# naive dual write:
# DB committed order 2; process crashes before broker.append(...)
# orders: [(1,), (2,)] broker has order 2 event: FalseThe broker holds the first event twice, because the relay crashed after publishing but before marking. Nothing is lost, which is the point of at-least-once, and the consumer copes because it tracks the highest seq applied per aggregate and ignores anything not newer. Contrast the last block: with a dual write the row exists and the event never does, with no record that anything went wrong. Note the limits of the demo: it is a single-process simulation, the broker is a list, and the dedupe state lives in memory. A real consumer must persist its dedupe state or offset atomically with its own side effect, otherwise it has just moved the dual-write problem downstream.
PostgreSQL / MySQL
| WAL (logical decoding, pgoutput) / binlog (binlog_format=ROW)
v
Debezium connector (runs in Kafka Connect)
| 1. initial SNAPSHOT of existing rows (op = "r") 2. then STREAM the log from a saved position
v
Kafka topic per table key = primary key => per-row order preserved within a partition
|
+--> search indexer +--> cache invalidator +--> warehouse loader +--> embeddings re-indexer
Illustrative change event (Debezium-style envelope, values made up):
{ "before": { "id": 1, "email": "[email protected]" },
"after": { "id": 1, "email": "[email protected]" },
"source": { "table": "customers", "lsn": 2450912, "txId": 771 },
"op": "u", // c = create, u = update, d = delete, r = snapshot read
"ts_ms": 1760000000000 }
-- PostgreSQL (not run here): prerequisites for logical decoding
-- wal_level = logical
-- CREATE PUBLICATION app_pub FOR TABLE customers, orders;
-- the connector creates a replication SLOT so the server keeps WAL it has not yet consumed
-- ALTER TABLE customers REPLICA IDENTITY FULL; -- otherwise UPDATE/DELETE "before" may hold only the keyThree mechanisms make this work. First, the connector remembers its position in the log (an LSN in PostgreSQL, a binlog file and offset in MySQL), so after a restart it resumes from there, and it takes an initial snapshot so a new consumer starts from a complete picture and then switches to streaming. Second, ordering is per key: keying each Kafka message by the row's primary key sends all changes to one row to one partition, in order, but there is no global order across partitions or generally across tables. Third, the replication slot is a promise from the database to retain WAL until the consumer confirms it. That is what makes the pipeline reliable, and also its main operational hazard: a stalled or abandoned connector makes the primary retain WAL indefinitely, and disk fills up. Monitoring slot lag is not optional. Delivery from such a pipeline is generally at-least-once: after a connector restart it can re-emit events from the last committed offset, so consumers must be idempotent.
Timeline in the source database:
t1 INSERT ... (customers has columns id, name, email) -> event with 3 fields
t2 ALTER TABLE customers ADD COLUMN phone TEXT; -> DDL
t3 INSERT ... (now id, name, email, phone) -> event with 4 fields
Safe (backward-compatible) changes Risky changes
add a nullable / defaulted column drop or rename a column
widen a type (int -> bigint) change a type incompatibly (text -> int)
change the primary key
Countermeasures:
* schema registry (Avro/Protobuf/JSON Schema) with compatibility rules enforced at publish time
* expand -> migrate -> contract: add new column, dual-populate, move consumers, then drop the old
* consumers ignore unknown fields (tolerant reader) instead of failing on them
* for renames: treat as add + backfill + drop, never as one atomic stepA CDC stream couples every consumer to the shape of a table you thought was private. An added nullable column is harmless if consumers ignore unknown fields; a dropped or renamed column breaks anyone reading it, and it breaks them at consume time, in production, possibly after events already published in the old shape are still sitting in the topic. Log-based connectors detect table structure changes and update the emitted schema (how they discover it differs by database and connector), but they cannot make an incompatible change compatible. The durable answer is process: additive changes first, a schema registry that rejects breaking publishes, and a rule that events published to other teams are a contract. This is also the argument for the outbox pattern over raw table CDC: the outbox payload is a deliberately designed event you can version, while the raw row stream leaks your internal table layout.
updated_at (misses deletes and intermediate updates), triggers (complete but adds work to every write), and log-based (reads WAL or binlog after commit, sees everything in commit order, no write-path overhead).# 1) Save the SQL from "Trigger vs Polling" to a file and run it:
# sqlite3 :memory: < cdc.sql
# Expected: polling returns 1 row (id 1, [email protected]); change_log returns 5 rows (I, I, U, U, D).
#
# 2) Save the Python from "At-Least-Once Delivery" as outbox.py and run: python3 outbox.py
# Expected: the broker contains (1, 1, 'OrderPlaced') twice, the consumer skips the second copy,
# read_model ends as {1: 'SHIPPED'}, and the naive dual write ends with "broker has order 2 event: False".
#
# Exercises:
# a. In the SQL, add a soft-delete column (deleted_at) and change the polling query to see the delete.
# What still goes missing? (the intermediate email: polling only ever shows the latest state)
# b. In the Python, remove the "applied" check in consume() and confirm the duplicate is applied twice.
# Then change the payload to a counter increment and observe the read model drift.
# c. In the Python, publish rows in a shuffled order and add a version guard so an older seq
# never overwrites a newer one.
#
# -- PostgreSQL (not run here): inspect a logical replication slot on a real server
# SELECT slot_name, active, restart_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
# FROM pg_replication_slots;
# -- An inactive slot with a growing "retained" value is the classic CDC incident.Q: Compare log-based CDC with trigger-based and polling-based CDC. When would you pick each?
A: Polling is simplest, needs no database privileges, and is fine for low-volume, idempotent syncs where missing deletes or collapsed updates are acceptable (with soft deletes it can be made adequate). Triggers capture every change including old values and work where you cannot read the log, but they add work and lock time to every write and must be maintained per table. Log-based CDC reads the WAL or binlog after commit, so it adds no write-path cost and sees deletes and every intermediate change in commit order; the price is log access, retention management (slots), and dealing with row-level rather than business-level events. For anything high-volume or where completeness matters, log-based is generally the default choice.
Q: What is the outbox pattern and what problem does it solve?
A: It solves the dual-write problem: you cannot atomically update a database and publish to a broker, so a crash between the two leaves them inconsistent. You insert an event row into an outbox table in the same local transaction as the business change, so either both exist or neither does, and a separate relay (a poller or CDC on the outbox table) publishes it. The trade-off is at-least-once delivery, an extra table and relay to operate, and the need for idempotent consumers.
Q: What ordering and delivery guarantees does a Debezium-style pipeline give, and what must the consumer do about them?
A: Per key, events are in commit order because they are keyed by primary key into one partition; there is no reliable global order across partitions or tables. Delivery is generally at-least-once, so after a restart duplicates are possible. The consumer must therefore be idempotent (upsert by key, skip events with a version or offset not newer than applied) and must not assume cross-table ordering, for example that a child row's event arrives after its parent's.
Q: A column is renamed in the source table. What happens downstream and how do you avoid an incident?
A: Consumers that read the old field name break or silently get nulls, and old events already in the topic still carry the old name, so you have two shapes in flight. Avoid the incident by never doing a rename in one step: add the new column, populate both, migrate consumers, then drop the old one, with a schema registry enforcing compatibility. Better still, publish deliberate events through an outbox so internal table changes do not leak to consumers at all.
Q: Design a pipeline that keeps a search index and an embeddings store in sync with a PostgreSQL documents table.
A: Enable logical decoding and run a Debezium-style connector on the documents table (or on an outbox table if you want designed events), publish keyed by document id, and have one consumer per derived store. Each consumer upserts or deletes by document id and skips stale versions, and the embeddings consumer batches and rate-limits calls to the embedding model. Handle initial load with the connector's snapshot, monitor slot lag and consumer lag, and keep a backfill job that can rebuild either index from the source table, because CDC pipelines eventually drift and you need a repair path.
Follow-up: The connector was down for six hours. What happens on a PostgreSQL primary, and what do you do?
A: The replication slot keeps every WAL segment the connector has not confirmed, so the primary's WAL directory grows for those six hours and can fill the disk, which stops the database. Recover by restarting the connector so it drains the backlog, or, if disk is at risk, dropping the slot, at the cost of losing your position and having to re-snapshot. Prevent it with alerts on retained WAL per slot and, on newer PostgreSQL versions, a cap such as max_slot_wal_keep_size, accepting that hitting the cap invalidates the slot.
Follow-up: Can you get exactly-once end to end?
A: Not generally as a property of the transport; what you can build is effectively-once processing: at-least-once delivery plus idempotent or transactional application of each event. Some systems offer transactional writes that commit the consumer offset and the output together (for example Kafka transactions between topics), but once the effect is an external side effect such as an email or a payment call, you need an idempotency key on that side effect. Any claim of exactly-once should be tied to a specific boundary.
Follow-up: Why not just use the outbox table with polling and skip Debezium?
A: You can, and for modest volume it is often the right call: fewer moving parts and no slot to manage. Its costs are polling load on the database, added latency equal to the poll interval, contention if several relays run (you need SELECT ... FOR UPDATE SKIP LOCKED style claiming, in PostgreSQL), and per-row published-marker updates that create table bloat. Log-based CDC on the outbox avoids the polling and marker writes and lets you keep the outbox table small, in exchange for operating Kafka Connect and the connector.
CDC is the connective tissue whenever a design has a system of record plus derived stores. "Keep Elasticsearch in sync", "invalidate the cache when the row changes", "feed the warehouse without a nightly full dump", and "re-embed a document when it is edited" are all the same shape: one authoritative database, one ordered stream of what changed, several idempotent consumers. In an interview, state the pipeline explicitly (outbox or log-based CDC, keyed topic, idempotent consumers), state the freshness you accept for each consumer, and say what happens on failure: the connector restarts from its offset, duplicates are absorbed, and a backfill job repairs drift. Mention the operational hooks a strong candidate would think of: slot lag alerts, consumer lag, a dead-letter path for poison events, and schema compatibility checks. It is also the practical implementation of the read-model synchronization described in the CQRS page.
You are trading a simple, synchronous, single-database system for decoupled consumers that learn about changes without adding load or coupling to the write path. The costs are real: an asynchronous pipeline with lag, at-least-once semantics that force idempotent consumers, a log-retention hazard on the source database, per-key rather than global ordering, and a schema contract you now share with every consumer. Row-level CDC also exposes your internal table design and gives you "row changed" rather than "order was cancelled because the customer requested it", so business semantics often need an outbox or explicit events. It is the wrong choice when a nightly batch or a plain read replica meets the freshness requirement, when there is a single consumer in the same database that a view or trigger could feed, or when nobody on the team is ready to run and monitor the pipeline. Start with the simplest mechanism that meets the freshness and completeness you actually need, and move to log-based CDC when volume, deletes, or ordering requirements make the simpler options leak.