ETL and Data Pipelines · Capstone Case Studies
Capstone: From Nightly Batch to Near-Real-Time CDC
Plan the migration of a nightly full-copy pipeline to change data capture with 5-minute freshness, without losing data or breaking existing reports.
Scenario
Example company: a logistics company copies 40 tables from a PostgreSQL production database to a cloud warehouse every night with full SELECT * exports. Exports take 4 hours and load the primary database heavily. Operations now needs shipment status within 5 minutes for a live control-room dashboard, and the database team wants the nightly export load gone.
Facts:
- The largest table,
shipment_events, has 2 billion rows and grows 15 million rows a day. - Some tables are hard-deleted by a cleanup job.
- 25 dbt models and 40 dashboards depend on the current warehouse tables.
- The team has no streaming experience.
Requirements
- Shipment status in the warehouse within 5 minutes of the change in production.
- Deletes must be reflected.
- No extra load on the primary database during business hours.
- Existing reports keep working through the migration.
Constraints
- Production runs PostgreSQL with a read replica; logical replication is allowed.
- Budget for at most one new managed service.
Deliverables
- Architecture: CDC source, transport, landing format, how changes are applied in the warehouse, and how often.
- The initial snapshot and cut-over plan for the 2-billion-row table.
- How you will verify correctness (reconciliation) before switching consumers.
- Operational plan: monitoring (lag, slot size), failure handling and the on-call runbook.
- What stays batch, and why.
Hints
- Log-based CDC (for example Debezium or a managed CDC connector) reading PostgreSQL logical replication, landing change events in the warehouse, then merging them into tables every few minutes is micro-batch, which the team can operate.
- Apply changes in log order per key; handle deletes as deletes or soft-delete flags.
- Run old and new pipelines in parallel and reconcile counts and checksums per day before cut-over.
- Watch replication slot lag: a stopped consumer makes PostgreSQL retain WAL and can fill the disk.
Deliverables
What architecture would you propose, and why?
Model answer
Log-based CDC from PostgreSQL logical replication to a landing table (raw change events), with a merge every few minutes into current-state tables, plus the existing batch path for low-freshness tables.
Rubric
Look for log-based CDC, ordered idempotent merges, delete handling and a pragmatic micro-batch apply instead of full streaming, given the team's experience.
How do you migrate the 2-billion-row table safely?
Model answer
Take a consistent initial snapshot (from the replica or the CDC tool's snapshot mode) tied to a log position, then apply changes from that position; run both pipelines in parallel and reconcile per day before switching consumers.
Rubric
Strong answers tie the snapshot to a log position so no change is missed or applied twice, and verify with reconciliation before cut-over.
What operational risks does CDC add, and how do you monitor them?
Model answer
Replication slot lag and WAL retention filling the source disk, schema changes breaking the stream, and consumer failures. Monitor lag, slot size and merge freshness, alert early, and document how to resync a table.
Rubric
Look for slot/WAL monitoring, schema-change handling and a resync procedure.
Open in the app to mark lessons complete, take notes and track your progress.