extracttransformloadorchestrate
Scenario
3.Multi-sourcefan-inorchestration
Coordinate several independent sources (orders, payments, clickstream) into one downstream mart: fan them in, join on a shared key, and fail safely if a source is late or missing rather than publishing a half-built mart.
This pipeline must answer
- 01Are all the sources present and complete before we build the mart?
- 02How are orders, payments, and events joined into one correct row?
- 03If one source is late or missing, do we publish a half-built mart — or wait?
Guardrails
- At least two sources joined on a key
- Wait for all sources before publishing; alert on a late/missing source
- Idempotent mart write
- Avoid invalid cycles
Act 1 · Ask — answer all 11 and your spec flows11 / 11 answered
1Consumer & goal
Who reads this data, and what do they decide with it?
Name a real team, the decision they make, and the time they need it by.
- Who opens this first thing in the morning?
e.g.
The finance analyst who signs off yesterday’s revenue — a job title, not “the business”. - What decision changes because of this number?
e.g.
Reorder stock, chase a refund spike, close the books for the day. - What goes wrong if the number is wrong?
e.g.
A published revenue figure gets restated. That sets the bar: slightly late is survivable, wrong is not. - By when do they need it?
e.g.
“On my screen by 9am” is a deadline you can design against. “Fresh” is not.
◈ What this decides
New to this?What a Data Pipeline Is and Why It MattersThe output shape, how correct it must be, and how loudly it may fail.
Markdown: **bold** · *italic* · # heading · - bullet
signals:
✓ answered — flow continues through this valveAct 2 · Architect
Design review
The reviewer reads your pipeline.
Run the review to check your architecture against this scenario — a valid DAG, a trigger for the freshness SLA, a transform stage, a quality gate before publish, and a failure path — then defend it in a short interview.