Sixty Minutes, Every Hour

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain
Pipeline Design
Difficulty
medium
Seniority
L6

Problem

The OLTP database behind our customer-facing app commits about 2 million row changes a day and already runs near its CPU limit, so the DBA who owns it will not accept any new reads. Design the pipeline that gets those changes into the analytics warehouse without loading the live database.

Every hour, on the hour. No excuses.

Business requirements

Worked solution and explanation

The trap

This is change data capture dressed up as a scheduling problem. "Hourly" makes everyone reach for a cron job that SELECTs the last hour and appends. That one move breaks all four requirements. It loads the production database. Dashboards read a half-written hour. A rerun double-counts what a partial run already wrote. A hard delete leaves no row to select, so the customer lives on in the warehouse. Anyone can name CDC, a swap, a checkpoint and a delete handler. What separates candidates is seeing that they are one pattern, not four fixes.

Walk the requirements

Step 1: Capture off the log, never the tables

Log-based CDC tails the write-ahead log the database already writes, so the app sees zero extra query load. Polling SELECTs are the design the DBA rejects. Polling also cannot see a hard delete.

Step 2: Build the hour in staging, then swap

A scheduler kicks off each hour's build. The hour's changes are merged into a staging table that is a separate node from the warehouse. Then one atomic swap publishes it: a partition overwrite, a transactional rename or a table-format commit. All three dashboards see the old hour or the new one, never a mix.

Step 3: Replay from the retained position

After an outage, the merge restarts from its last committed log position. The stream still holds the missed windows, so they replay in order. The merge is keyed on the change id, and a partial run only ever touched staging. The rerun converges instead of double-counting.

Step 4: Treat deletes as events

A source delete becomes a DELETE event on the stream. The merge sets is_deleted and deleted_at, and consumers filter on is_deleted. A compaction job purges those rows once retention expires, so GDPR erasure completes on schedule and leaves an audit trail.

The shape that fits

CDC tails the OLTP write-ahead log onto a retained stream, an hourly merge builds staging, then an atomic swap publishes to the warehouse the dashboards read. No query ever touches the live tables.
  • oltp write ahead log (source, PostgreSQL)
  • log based change data capture (source, CDC)
  • retained change stream (queue, Kafka)
  • hourly scheduler (transform, Airflow)
  • merge changes and deletes (transform, Spark)
  • staging table (storage, object storage)
  • atomic hourly swap (transform, dbt)
  • analytics warehouse (storage, warehouse)
  • analytics dashboards (consumer, dashboard)
  • oltp write ahead log -> log based change data capture
  • log based change data capture -> retained change stream
  • hourly scheduler -> merge changes and deletes
  • retained change stream -> merge changes and deletes
  • merge changes and deletes -> staging table
  • staging table -> atomic hourly swap
  • atomic hourly swap -> analytics warehouse
  • analytics warehouse -> analytics dashboards
  • Capture: oltp write ahead log, log based change data capture, retained change stream
  • Hourly build: hourly scheduler, merge changes and deletes, staging table
  • Publish: atomic hourly swap, analytics warehouse
  • Consumers: analytics dashboards

Common follow-up questions