Nobody really chooses the nightly copy from the application database into the warehouse. It was the easiest thing to build in year one, and now it’s year five. The dashboards are a day behind, so when someone in operations needs today’s number, they query a production replica instead. I’d do the same.
With change data capture, that data can reach the warehouse minutes after the write. People worry about Kafka and the table format. What tends to hurt later is more mundane: tiny files, and the night the stream loses changes without telling anyone.
Start from the log
Every serious relational database already logs each change: the write-ahead log in Postgres, the binlog in MySQL. A CDC reader such as Debezium follows that log the way a replica would and turns each insert, update and delete into an event.
Many teams poll first, querying updated_at every few minutes. I wouldn’t start there. Polling never sees a hard delete. It trusts every code path to set the column, including the fix someone ran by hand during an incident. And it puts a repeating scan on the very database you meant to protect.
The log does have one cost that catches people out. In Postgres the reader holds a replication slot, and if the reader stops, the server keeps WAL for it until the disk fills. Capping that with max_slot_wal_keep_size only trades it for an invalidated slot and a lost place in the log. Alert on slot lag from the first day.
The route a row takes
- The application database, which doesn’t know any of this exists.
- The CDC reader, which snapshots each table once and then follows the log.
- One Kafka topic per table, keyed by primary key, so every change to a row lands in the same partition, in order.
- A streaming job that wakes every minute or so and upserts the batch by key.
- A lakehouse table in Iceberg, Hudi or Delta Lake. Any of them works. Don’t let the format debate eat a quarter.
- The query engine, now reading a table that’s minutes old.
Topic per table is Debezium’s default, and it’s the right call here. Separate topics let you replay one table, and if each table gets its own job, a stuck one on orders doesn’t hold up customers.
The front half of this route, change data capture into Kafka, is what the CRM integration platform I led at LinkedIn ran on. That was integration rather than analytics, and it has its own post.
One change, end to end
This is an order update as Debezium emits it for Postgres, trimmed a little. You only get a full before image if the table has REPLICA IDENTITY FULL. By default, before is null on updates and holds just the key on deletes.
{
"before": {
"order_id": 81723,
"customer_id": 4410,
"status": "pending",
"total_cents": 12950,
"updated_at": "2026-07-07T14:02:11.482Z"
},
"after": {
"order_id": 81723,
"customer_id": 4410,
"status": "shipped",
"total_cents": 12950,
"updated_at": "2026-07-07T14:06:37.105Z"
},
"source": {
"connector": "postgresql",
"db": "shop",
"schema": "public",
"table": "orders",
"txId": 902114,
"lsn": 4815162342,
"ts_ms": 1783433197105
},
"op": "u",
"ts_ms": 1783433197391
}
The streaming job flattens each event into a row: the after columns, the op code, and the log position as source_lsn. For a delete, after is null, so the key comes from before. It skips the null-value tombstone Debezium sends after each delete. Each micro-batch becomes a temporary view called changes_batch, and the job runs this:
MERGE INTO lake.orders AS t
USING (
SELECT order_id, customer_id, status, total_cents, updated_at, op, source_lsn
FROM (
SELECT c.*,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY source_lsn DESC) AS rn
FROM changes_batch AS c
) AS ranked
WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd' AND s.source_lsn > t._source_lsn THEN
DELETE
WHEN MATCHED AND s.op <> 'd' AND s.source_lsn > t._source_lsn THEN
UPDATE SET
customer_id = s.customer_id,
status = s.status,
total_cents = s.total_cents,
updated_at = s.updated_at,
_source_lsn = s.source_lsn
WHEN NOT MATCHED AND s.op <> 'd' THEN
INSERT (order_id, customer_id, status, total_cents, updated_at, _source_lsn)
VALUES (s.order_id, s.customer_id, s.status, s.total_cents, s.updated_at, s.source_lsn);
That’s Spark SQL, which Delta and Iceberg accept as written once their Spark extensions are on. Hudi’s MERGE INTO has rules that shift between versions, and its precombine field usually does the newest-wins step for you.
Two pieces carry most of the weight. ROW_NUMBER() keeps one change per key, because a MERGE fails when two source rows match the same target row. The source_lsn guard stops a duplicated or replayed event from overwriting a newer version of a row, so you can rewind the consumer after an incident and replay calmly.
This leaves a gap: after a hard delete, the guard has nothing to compare against, so a replayed insert or update brings the row back until the replay reaches the delete again. Keeping deleted rows in the lakehouse closes it. The delete branch sets a _deleted flag and the new _source_lsn instead of deleting, the other branches set it to false, and a view filters flagged rows out.
Wider tables have another Postgres catch. Without REPLICA IDENTITY FULL, an update that doesn’t touch a large TOASTed column, such as a long text or jsonb value, arrives with a placeholder instead of that column’s value. The UPDATE SET has to keep the lakehouse’s value whenever it sees the placeholder, or it overwrites good data.
Compaction is the price of fresh data
Every commit writes a few small files, and merge-on-read tables also write delete or log files that each query has to fold in. After a week of one-minute commits, a busy table can be tens of thousands of tiny files, and queries slow down in a way nobody connects to the pipeline.
So compaction belongs in the design from the start. Hudi has compaction and clustering built in, Iceberg has rewrite_data_files, and Delta has OPTIMIZE. Point it at the partitions that are actually changing. Expire old versions on the same schedule (expire_snapshots in Iceberg, VACUUM in Delta), or storage keeps every file compaction replaced.
It’s also why I resist second-level freshness for analytics. A few minutes is cheap. Ten seconds buys far more commits and compaction for a dashboard someone refreshes twice an hour.
Counting rows every night
Streaming pipelines fail quietly. A slot gets dropped and recreated during maintenance, and the changes in between are gone. Or someone truncates and reloads a table, and the connector, which skips truncates by default, passes along only the reload. Nothing throws an error. The numbers are just a little wrong, and they stay wrong.
Once a night, count each table’s rows in a read replica and in the lakehouse. Exact equality is hard on a table that’s still being written, so allow a small tolerance and alert past it. For tables that hold money, compare a sum too.
When a table is off, re-snapshot just that table. Debezium can do that incrementally, through its signaling channel, without stopping the stream. That only repairs rows the source still has, though. If the lakehouse count is high, deletes went missing, and an anti-join against the replica finds the extra keys.
Tables that can stay on the nightly copy
You don’t need this for every table. A lookup table that changes twice a year can keep its nightly copy, and every streaming pipeline is one more thing that can page someone.
The whole approach may not scale down, either. For a small team with one database and a few dashboards, a replica and a scheduled job can be the better trade for years. I’d reach for CDC once the big tables change all day and people have started going around the warehouse for today’s numbers.