Change Data Capture From Operational Systems to Lakehouse

The problem

Analytics needs a current copy of an operational database.

The traditional approach is a scheduled query that pulls rows modified since the last run, which works until it doesn’t.

It misses hard deletes entirely, because a deleted row has no modified timestamp to find.

It misses updates that occur between the watermark and the end of the extract. It puts read load on a production database at whatever hour the job runs.

And it produces a table that is right most of the time and wrong in ways nobody can reproduce.

Change data capture reads the database’s own transaction log instead — the write-ahead log, the binlog, or the oplog — and emits every insert, update and delete as an event.

This is a strictly better source of truth because it is the same record the database itself uses for replication and recovery.

The demo works in an afternoon.

Production is where the difficulty lives, and it lives in specific places.

A connector restarts mid-snapshot and the recovery behaviour is not what anyone assumed. A replication slot on a paused consumer silently consumes hundreds of gigabytes of disk over a weekend. An ALTER TABLE lands on a Monday morning and the sink rejects every subsequent event.

None of these appear in a quickstart tutorial, and all three are routine.

When to use this

• Analytics needs source data fresher than a nightly batch, and the freshness requirement is real rather than aspirational.
• Deletes and updates must be reflected accurately downstream.
• The source database cannot absorb repeated full-table extracts.
• An accurate history of changes has value — slowly changing dimensions, audit, or point-in-time reconstruction.
• The source is a database you control, with logical replication or an equivalent transaction-log mechanism available.

When not to use this

A nightly full reload is genuinely sufficient and the table is small

For a few million rows with no delete semantics to preserve, a full reload is simpler, cheaper and has no replication slot to monitor.

Operational simplicity has real value.

The source is a SaaS application with no transaction log access

CDC needs the log.

Without it you are polling an API, which is a different pattern with different failure modes.

Nobody will own the replication slot

This is the honest disqualifier.

A replication slot is a stateful dependency on a production database that can continue retaining log data if the consumer stops.

It needs monitoring, an alert and a named person who responds.

Without that ownership, CDC can convert an analytics problem into a production database outage, and that failure is far more expensive than stale data.

The source schema changes constantly and without notice

CDC will surface every change as a downstream break.

The pattern is not the problem here; the absence of any change process upstream is, and this architecture will not fix it.

Sub-second latency is required

CDC into a lakehouse table is generally a minutes-scale architecture, not a milliseconds-scale serving layer.

Genuine real-time requirements need a streaming system that can serve queries directly rather than depending on a table on object storage.

Architecture

Events are captured from the transaction log by a connector and landed, unmodified, in an append-only bronze table.

Every insert, update and delete is retained with its operation type, its position in the log and its timestamp. Nothing is overwritten at this layer.

A periodic job then merges bronze into a silver table holding current state, deduplicating by primary key and keeping the latest version by log position.

The two-layer split is the part that is frequently skipped and should not be.

Writing CDC events directly into a merged target seems simpler and removes a table, but it discards the event history and with it the ability to rebuild the current-state table without re-snapshotting the source.

When the merge logic turns out to be wrong — which it will, the first time a composite key or an out-of-order event is encountered — the bronze layer is the difference between a re-run and an incident involving the production database.

Ordering deserves explicit attention.

Events for one primary key must be applied in log order, and an update arriving before the insert it depends on can produce a row that looks corrupt for reasons that are difficult to trace.

Deduplicate within each batch and order by log sequence number rather than event timestamp.

Timestamps are not reliably monotonic across transactions.

Components

Tradeoffs

Implementation notes

Monitor replication slot lag as a production alert

Monitor replication slot lag as a first-class production alert, routed to whoever owns the source database rather than only to the data team.

This is the single most dangerous component in the pipeline, and the people who need to act on the alert are often not the people who built the pipeline.

Choose the initial snapshot mode deliberately

Understand what happens when the connector restarts partway through a snapshot.

The defaults are reasonable but not universal.

A connector that decides to re-snapshot a large table during business hours can create an operational incident that is not immediately attributed to the analytics platform.

Plan for schema evolution before it happens

Decide what an added column does, what a dropped column does, and what a retyped column does.

Make each of those a detected, alerted and versioned event rather than a pipeline failure discovered from a broken dashboard.

Additive changes should normally flow through automatically. Destructive changes should stop and ask.

Schedule table maintenance from day one

Compaction, snapshot expiry and orphan-file cleanup are not optional on a CDC target.

A high-churn table accumulates small files and delete files quickly, and query performance degrades over weeks in a way that looks like a query-tuning problem and is not.

Reconcile against the source on a schedule

Run row counts by key range, plus checksums on a sample, on a regular schedule.

CDC drift is silent by nature, and reconciliation is how you discover it before a business user does.

Decide how deletes should appear in silver

A hard delete matches the operational source exactly.

A soft delete with a flag and deletion timestamp preserves the analytical history of things that used to exist, which is frequently what analysts actually want.

This is a modelling decision with real consequences and should be made explicitly rather than inherited from a connector default.

References

• Using Debezium to create a data lake with Apache Iceberg — the Debezium project’s own write-up, including how upsert mode uses equality deletes and why per-batch deduplication is needed.
  Debezium
• Debezium in production: PostgreSQL to Kafka to Iceberg patterns — covers snapshot modes, replication-slot risk and schema evolution breaking downstream sinks.
  BigData Boutique
• CDC to Apache Iceberg — treatment of equality deletes and the merge-on-read versus copy-on-write decision.
  Streamkap
• A guide to change data capture with Apache Iceberg — documents the Debezium event envelope including before, after and op, which the merge logic depends on.
  Dremio
• debezium-server-iceberg configuration reference — implementation reference for the direct-to-Iceberg path without Kafka.
  GitHub