Streaming ETL With CDC and Materialized Views: Real-Time Without a New Data Stack

Nightly batch is fine until it isn't. The signals that you've outgrown it:

• Business asks for 'today's numbers' before your batch runs.
• A dashboard needs freshness under 15 minutes.
• ML features need to reflect events within seconds.
• You're triggering webhooks or workflows off DB changes without a clean pattern.

If none of these apply, don't stream — the operational tax is real. If several apply, CDC is usually the lowest-friction upgrade.

Every relational database maintains a write-ahead log (Postgres WAL, MySQL binlog, SQL Server transaction log). CDC tools read the log and emit each change as an event to a message system. Nothing in your application changes — the DB is the source of truth, the log is a natural event stream, and you're just consuming it.

# debezium connector config
{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "prod-db",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "...",
    "database.dbname": "app",
    "plugin.name": "pgoutput",
    "publication.autocreate.mode": "filtered",
    "table.include.list": "public.orders,public.line_items",
    "topic.prefix": "prod",
    "snapshot.mode": "initial",
    "tombstones.on.delete": "true"
  }
}

Debezium first takes a consistent snapshot of every included table, then tails the WAL. Every row change becomes a Kafka message on prod.public.orders. Consumers subscribe and update whatever downstream state they need.

A consumer reads the Kafka topic and maintains a downstream table you can query. For simple cases (denormalization, single-table filtered views), a Postgres materialized view refreshed on a schedule is enough:

create materialized view active_customers as
select customer_id, count(*) as orders, max(created_at) as last_order
from orders
where status = 'active'
group by 1;

-- refresh incrementally with pg_ivm, or fully every N minutes
refresh materialized view concurrently active_customers;

For complex real-time joins across streaming tables, standard MVs aren't enough. That's where streaming warehouses come in.

Streaming warehouses (Materialize, RisingWave, ksqlDB) maintain fully incremental materialized views over streams. You define a SQL query joining multiple CDC topics; the engine keeps the result up-to-date within milliseconds of each source change.

They shine when you need:

• Real-time joins across 3+ streams (order + customer + product = an enriched view).
• Sub-second freshness on aggregates.
• Consistent snapshots — you never see a partial update mid-query.

They cost more per GB than a batch warehouse and are trickier to operate. Use them when you have a real real-time product, not for dashboards.

Your OLTP app renames a column. CDC dutifully emits messages with the new schema. Downstream consumers explode. Every serious streaming setup needs:

• A schema registry (Confluent, Apicurio) with backward-compatibility enforcement.
• PR review on OLTP migrations that touch published tables — with data engineers on the reviewer list.
• A 'streaming contract' doc for each replicated table naming which downstream systems consume it.

Skip this and every DB migration becomes a downstream incident.

What happens when a downstream system is broken for a week? When you launch a new consumer that needs historical data? When Kafka retention is 7 days but the outage was 10?

1. Set Kafka retention long enough to cover recovery scenarios (7-30 days is common).
2. For historical data past retention, use Debezium's incremental snapshot to re-read a table without a full reload.
3. For new consumers, backfill from the warehouse (batch) up to a chosen timestamp, then subscribe to the stream from that timestamp. Bootstrap + tail pattern.
4. Document your recovery playbook. Rehearse it. It's the least fun tabletop until it's the most important one.