System designMediumsd-g178

Subject EtlLevel Mid–Senior~40 minCommon in Distributed systems interviewsIndustries Technology, Software development

Question

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.

Learn the concepts

Narrate your design

Loading whiteboard…

Run or narrate your approach, then ask the coach.