CDC Pipeline into a Data Warehouse

Replicate operational databases into analytics within minutes

Design the pipeline that copies every insert, update and delete from hundreds of sharded PostgreSQL and MySQL databases into a warehouse within minutes, with history, without loading the primaries.

Overview

The company's product runs on many operational databases: PostgreSQL for some services, MySQL for others, each split into shards. Analysts want those tables in the data warehouse so they can join orders with payments with support tickets. Today they get a nightly dump that is a day old, misses rows deleted during the day, and slows the databases while it runs.

The goal is a change data capture (CDC) pipeline. Instead of copying whole tables, it reads each database's own change log, the write-ahead log (WAL) in PostgreSQL or the binary log (binlog) in MySQL, and turns every committed insert, update and delete into an event. Those events are applied to warehouse tables in small batches every minute or so. Analysts see data a few minutes old, deletes included, and they can also ask what a row looked like last Tuesday.

The problem has three hard parts. First, events can arrive twice or out of order (after a crash, a replay or a re-snapshot), and the warehouse must still end up with exactly the newest version of each row. Second, the change log lives on the production database, and a capture process that stops reading makes the database keep that log, which can fill its disk. Third, the schema keeps changing under you.

For the general ideas, see the Change Data Capture (CDC) topic and the Data Warehousing and Dimensional Modeling topic. For how a service publishes its own events through a table, see the Outbox Pattern topic.

Questions to ask first

How many sources and tables? — 200 databases: 10 services, each sharded 20 ways, about 25 tables each, so 5,000 physical tables that become 250 warehouse tables
How much change? — 50,000 row changes a second on average, 3 times that at peak; about 1 KB per change event
How fresh must data be? — Within 5 minutes for p99 of changes (a target we chose)
Are deletes real? — Yes. Analysts must not see deleted rows as current, and audit needs them
Do we need history? — For audited tables (about 10% of change volume), every version of every row
How big is the warehouse? — About 100 TB today
May we load the primaries? — No full-table scans on primaries during business hours; capture reads the log, and snapshots read replicas where possible
Is there personal data? — Yes: emails, phone numbers, addresses. Masked or hashed before it reaches the warehouse

What you will be able to do

Explain why log-based capture beats polling with an updated_at column, and what each database must be configured with.
Hand over from an initial snapshot to the live stream without losing or double-applying a change.
Write an idempotent apply that survives duplicate and out-of-order events, with soft deletes and SCD type 2 history.
Re-snapshot or backfill one table while the stream keeps running.
Size the event log and the apply work from 50,000 changes a second, and say how a stalled replication slot can fill a primary's disk and how to stop it.

Key concepts

Log-based CDC
Replication slots and binlog
Snapshot and stream handover
Event log keyed by primary key
Source position (LSN)
Idempotent MERGE
Soft deletes and tombstones
SCD type 2 history
Incremental snapshots
Slot lag and WAL retention

Functional requirements

Capture every committed insert, update and delete from registered PostgreSQL and MySQL tables, including sharded copies of the same table
Load each new table with an initial snapshot, then switch to the change stream without gaps or duplicates in the result
Keep one current table per logical source table in the warehouse, with deleted rows marked or removed
Keep full row history (SCD type 2) for tables marked as audited
Apply additive schema changes automatically and hold breaking ones for review
Re-snapshot or backfill one table on request while the rest keep streaming
Mask or hash columns tagged as personal data before events are written to the event log
Show freshness per table: how far behind the source each warehouse table is

Non-functional requirements

Freshness: p99 of changes visible in the warehouse within 5 minutes of the source commit (a target we chose)
Correctness: after any crash, replay or re-snapshot, each warehouse row equals the source row at some recent position; no lost deletes, no resurrected rows
Source safety: capture never scans primaries in business hours, and a stalled consumer can never fill a primary's disk
Throughput: 150,000 changes a second at peak without falling behind
Isolation: one table's backfill or broken schema never stops other tables
Privacy: unmasked personal data never reaches the event log or the warehouse

Interview questions

Why not just query each table for rows with updated_at newer than the last run?
How do you get existing data in, and switch to the stream without gaps or duplicates?
Events can arrive twice or out of order. How does the warehouse still end up correct?
Soft deletes or hard deletes in the warehouse?
How would you keep full history for audit?
What happens to the source database if your pipeline stops?
A product team adds a column, then later renames another. What does the pipeline do?
One warehouse table is wrong. How do you fix it without stopping everything?