Pattern community advanced
Postgres CDC to Lakehouse
Batch extraction from an OLTP database loads it and still misses deletes and mid-day changes.
At a glance
| What | Batch extraction from an OLTP database loads it and still misses deletes and mid-day changes. |
|---|---|
| When | OLTP → lakehouse with freshness < 15 min; You need deletes/updates |
| Where | CDC |
Problem
Batch extraction from an OLTP database loads it and still misses deletes and mid-day changes.
Solution
Read the write-ahead log, not the tables. WAL ─▶ Debezium ─▶ Kafka ─▶ [dedupe by LSN] ─▶ MERGE into lakehouse Rank events by (lsn, ts_ms) so out-of-order delivery resolves deterministically. Debounce/compact in a streaming job before the merge. Handle tombstones explicitly (hard or soft delete). Never read the source tables on a schedule.
When not to use
- A nightly batch is sufficient and the source is small
Security scan
Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.
Context
A Postgres/MySQL source whose changes must reach a lakehouse with low latency.
Forces
- OLTP must not be slowed
- Deletes and updates must be captured
- Ordering matters
- Exactly-once is hard
Tradeoffs
| Choose this when | Costs & caveats |
|---|---|
| low latency, captures updates and deletes, no query load on OLTP | operational complexity (slots, connectors, ordering) |
- watch: a dropped replication slot forces a full re-snapshot
Diagram
Postgres WAL ─▶ Debezium ─▶ Kafka topic ─▶ stream dedupe ─▶ MERGE ─▶ Delta/Iceberg
Registry stats
| Verification | community · Sep 25, 2026 |
|---|---|
| Completeness | 75 |
| Trust multiplier | ×0.75 |
| Composite | 34.25 · adequate |
| Freshness | 29 days since last source check |
| Difficulty | advanced |
| Version | 1.2.0 |
| License | CC-BY-4.0 |
| Security flags | 2 |
Did this solve your problem?
Postgres CDC to Lakehouse