Prep RoomSign inStart free
Code RoomMulti-source order consolidation
MediumPrep Room Coding #3301

Multi-source order consolidation

System designDistributed systemsMid–Senior~40 min

Design a nightly ETL that consolidates orders, refunds, and shipping data from 6 source systems (3 Postgres OLTP DBs, a Stripe export, a CSV drop from a 3PL logistics partner, and a Salesforce API) into a unified `fact_orders` table powering finance dashboards. Volumes: ~5M orders/day total. Finance needs the table fresh by 6am and trusts it for board reporting, so a silently-wrong number is worse than a late table. Design the extraction, the transform/conform layer, and the quality gates that decide whether to publish.

What a strong answer looks like

Clarify scale and constraints first. Propose a clean component breakdown, then go deep on the hard parts (data model, bottlenecks, consistency, failure modes) and name the trade-offs you are making.

Clarify4:30 left
Estimate4:30 planned
Design13:30 planned
Deep dive10:30 planned
Failure7:00 planned
0:00
Which questions mattered is sealed until you submit. Telling you now would just be handing over the edge cases.