Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Design a daily batch pipeline from an OLTP database

Pipelines & scenarios · System Design Questions

Design a daily batch pipeline from an OLTP database

Hardpipelines-32
scenariopostgresbatchincrementalreplica

Question

Design a pipeline that copies data from a production Postgres database into the warehouse every day for reporting.

Solution

First rule: never run reporting extracts against the primary production database. Use a read replica, or capture changes from the log. Then decide per table how to extract, land the raw data untouched, and transform in the warehouse.

Postgres replica -> extract job -> object storage (raw, dated)
                                      -> warehouse raw -> dbt/Spark -> marts

Choosing full or incremental per table

Small tables (a few thousand rows, such as countries or plans) are simplest as a full copy each day. Large append-mostly tables (events) can load by an id or created_at high watermark. Large mutable tables (orders, whose status changes) need updated_at with a small overlap and a MERGE by key, or CDC. A watermark cannot see hard deletes, so if deletes matter, use CDC or compare key lists once a week.

Landing and loading

Write the extracted rows as Parquet files to a dated path such as raw/orders/load_date=2025-03-01/, and keep the files. That gives you a replay source and an audit trail. Then load into raw tables in the warehouse unchanged (bronze). Transform from there, so a logic bug never needs a re-extract from production.

Idempotent runs

Key every run on a logical date. Re-running 2025-03-01 should overwrite that day's partition or MERGE by key, never append blindly. Then retries and backfills are safe.

Things that bite

  • Schema change in the source: compare the extracted schema with the expected one. Allow new nullable columns into raw, and fail or quarantine on a type change.
  • Long transactions on the replica: a heavy extract can conflict with replication and get cancelled. Use short queries in chunks by primary key range.
  • Time zones: store timestamps in UTC and define the business day once.

Operations

Orchestrate with Airflow or a similar tool, with retries and a task per table. Run data quality checks (row counts versus source, null keys, duplicate keys) before publishing marts. Set an SLA, say the marts are ready by 7 a.m., and alert when a run is late, not only when it fails. Keep a backfill command that takes a date range.

If freshness needs grow from daily to minutes, the same design moves to CDC with little change to the transform layer.

PreviousNext