A pull-ingestion job extracts from a Postgres orders table with 200M rows that grows by 1M per day

A medium Pipeline Design interview practice problem on DataDriven. Write and execute real pipeline design code with instant grading.

Domain

Pipeline Design

Difficulty

medium

Problem

A pull-ingestion job extracts from a Postgres orders table with 200M rows that grows by 1M per day. The full-table nightly scan takes four hours and is starting to fight 6am application traffic. The section's pattern is incremental pull with a high-water mark: WHERE updated_at >= last_watermark AND updated_at < this_run_started_at, ORDER BY updated_at, advance only after a successful write. Pick the high-water-mark column by replacing the full-load transform with one whose name states the bookmark column with inclusive lower bound, exclusive upper bound fixed at run launch, and ORDER BY, plus a bookmark-state node that persists the watermark between runs.

Practice This Problem

Solve this Pipeline Design problem with real code execution. DataDriven runs your solution and grades it automatically.