Blueprint · Reference Scenario
SaaS CDC Lakehouse
Analytics on a production Postgres fleet without touching OLTP performance — change-data-capture into a medallion lakehouse with 5-minute freshness.
01 · PROBLEM
Analytics queries are fighting production for the same database
A SaaS company's product runs on Postgres. As the business grows, analytics needs grow faster: dashboards, customer-facing reports, data science. Every analytical query lands on the OLTP database or its lonely read replica — slow dashboards during peak hours, and engineers afraid to add indexes that might help analytics but hurt writes.
This blueprint moves analytics to a lakehouse fed by change-data-capture: row-level changes stream off the Postgres write-ahead log into Iceberg tables, dbt models the gold layer, and the OLTP database never sees an analytical query again.
02 · SCALE
Illustrative scale (scenario assumptions)
- Source
- ~2 TB across 40 tables in 3 Postgres databases; ~15k writes/sec peak
- Freshness SLA
- 5 minutes, source commit to queryable gold
- Consumers
- ~120 BI users, embedded customer dashboards, 6-person data science team
- History
- Full history retained in bronze (immutable); 13 months hot in gold
- Growth
- 2× data volume expected in 18 months — the design must absorb it without re-architecture
03 · CONSTRAINTS
What the design had to respect
- Zero OLTP impact: no analytical queries on primaries; CDC reads the WAL, not the tables.
- Exactly-once semantics: financial reporting downstream — duplicates are not a rounding error.
- Schema evolution: product ships weekly; columns appear and disappear without a data-team ticket.
- Team reality: small data team, strong SQL skills, limited streaming-ops experience — managed services over self-built.
- Cost: the read-replica fleet being replaced sets the budget ceiling.
04 · ARCHITECTURE
System design
Debezium captures row-level changes from the Postgres WAL into Kafka topics (one per table, schema-registry governed). A streaming writer lands bronze Iceberg tables; silver jobs deduplicate, type, and conform. dbt owns the gold layer — versioned SQL with tests and docs. BI reads gold through a semantic layer; data science works from silver/gold. Quality gates watch freshness, row counts, and null rates; late or out-of-order events are handled with watermarks and idempotent merges.
05 · DECISIONS
Key tradeoffs
| Decision | Chosen | Why |
|---|---|---|
| Capture method | Log-based CDC (Debezium), not query-based polling | Zero query load on OLTP, true row-level changes with before/after images, sub-minute latency |
| Table format | Iceberg | Multi-engine reads (Spark, Trino, Snowflake), schema evolution, hidden partitioning — and the team wasn't on Databricks |
| Silver processing | Micro-batch streaming, not per-event | 5-minute SLA doesn't need per-event; micro-batch is simpler to operate and exactly-once is easier to reason about |
| Gold modeling | dbt on the lakehouse | Team's SQL strength becomes leverage; tests and docs ship with the models |
| Backfill | Snapshot + CDC merge with idempotent keys | New tables onboard without downtime; replay is a normal operation, not an incident |
06 · IMPLEMENTATION
Phased delivery
07 · SECURITY & GOVERNANCE
Controls
- CDC credentials are read-replication scoped; the capture user can't write to anything.
- PII columns are tagged at the schema registry; masking policies apply in silver/gold — analysts never see raw PII.
- Kafka and the lakehouse live in private subnets; no public ingress to the data plane.
- dbt models carry tests (uniqueness, not-null, referential) as deployment gates — a failing test blocks the gold refresh.
- Lineage from gold dashboard back to source table is queryable in the catalog.
08 · RESULTS
Success criteria (illustrative targets, not achieved outcomes)
- Freshness SLO: 99% of gold tables within 5 minutes of source commit, measured continuously.
- Zero analytical queries on OLTP primaries; replica CPU reserved for failover headroom, not reporting.
- Dashboard migration: parity-verified (row counts and key metrics match legacy within tolerance) before cutover.
- Total platform cost at or below the retired read-replica fleet within two quarters of full migration.
These are acceptance thresholds for the scenario design. We publish measured client outcomes only with permission — which is why this page carries the reference-scenario label.
09 · LESSONS LEARNED
What generalizes
- Start with 5 tables, not 40. CDC's failure modes (schema changes, large transactions, replica lag) show up fast on a small scope — learn them cheap.
- Idempotent merges are the whole game. At-least-once delivery with idempotent sinks beats fragile exactly-once plumbing.
- dbt tests are deployment gates. Data quality enforced in CI prevents the 2 AM "the dashboard is wrong" call.
- Keep the replica as a safety valve through the migration — decommission it on evidence, not on schedule.
10 · TECHNOLOGIES
11 · RELATED
Lakehouse pattern
The pattern this blueprint instantiates — medallion zones on open table formats.
Read pattern →Engineering
Our data engineering practice builds streaming platforms like this one.
Explore practice →Retail Demand Forecasting
The companion reference blueprint: ML on top of a lakehouse foundation.
Read blueprint →Start here
Talk to an Architect
Bring your hardest AI, data, or modernization problem. We'll tell you plainly whether we can help — and what it takes.