Three Regions, One Report
A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.
- Domain
- Pipeline Design
- Difficulty
- hard
- Seniority
- L7
- Asked in
- PayPal
Problem
What daily batch pipeline should the on-call engineer run to turn yesterday's payment logs, which US, EU and APAC each land in their own object storage bucket on their own clock, into a merchant-level summary for global reporting? About 3 billion events arrive a day, and gateway retries resend 1 to 2 percent of them, sometimes after the day has ended.
Three regions, billions of payments, one merchant summary by 6 AM.
Business requirements
- Global reporting needs the consolidated merchant summary every morning by 6am UTC; missing it has been happening too often.
- Finance reconciles against the payment gateway and today the numbers never match because we drop or duplicate events.
- When one region's data lands late, the other regions still need to make progress; today everything waits for the slowest.
- When something fails partway through, operators rerun the day, and the rerun has to produce the same numbers, not different ones.
Worked solution and explanation
The tension under the prompt
This is three regional batch jobs that converge, dressed up as one daily report. Anyone can draw ingest, dedup, aggregate, publish. What separates candidates is where the waiting happens: put one 'all regions ready' check at the top and the US, which often lands near 4 AM UTC, holds APAC and EU hostage, so every slow US night is a missed 6 AM. The second trap is treating exactly-once as one property when it is two: dropping resends inside a run, and writing by date so a rerun replaces the day instead of stacking on top of it.
The shape that gets drawn first is one chain gated on all three regions. When it misses, the usual fix is to move the deadline to 7 AM, which holds until the next slow night. The shape is the bug: a region that is ready should not be able to wait on one that is not.
Walk the requirements
Step 1: Give each region its own readiness sensor
An orchestrator owns the daily run, and each region gets a sensor that fires the moment that region's files for the day are complete. APAC is in shortly after midnight UTC, EU around 2 AM, the US often near 4 AM. Each sensor pages if its region is still missing at 5 AM, so on-call has an hour to chase it instead of learning at 6 AM that the report is late.
Step 2: Drop resends before anything is counted
Gateway retries resend the same transaction ID, so each region's dedup keeps one row per transaction, keyed on the date the payment happened rather than the date its file landed. A straggler that arrives a day late still belongs to its original day.
Step 3: Write by date, delete then write
Both durable writes, regional_staging and merchant_summary, clear the target date before writing it. Whichever stage died, rerunning the date replaces the day rather than appending a second copy of what the first attempt half wrote. Dedup alone cannot give you that: it cleans one run, not two runs on top of each other.
Step 4: Reconcile before the summary is published
merchant_aggregator totals each merchant across the three staged regions, then gateway_reconciliation compares those totals with the gateway's per-merchant daily figures. Only a matching run is written to merchant_summary, so the dashboard never shows a number finance would reject.
The shape that fits
- us_payment_logs (source)
- eu_payment_logs (source)
- apac_payment_logs (source)
- us_readiness_sensor (transform, Airflow); alerting: page if US files missing at 5am UTC
- eu_readiness_sensor (transform, Airflow); alerting: page if EU files missing at 5am UTC
- apac_readiness_sensor (transform, Airflow); alerting: page if APAC files missing at 5am UTC
- us_dedup (transform, Spark)
- eu_dedup (transform, Spark)
- apac_dedup (transform, Spark)
- regional_staging (storage, object storage); idempotency: delete_write
- merchant_aggregator (transform, Spark)
- gateway_daily_totals (source, API)
- gateway_reconciliation (quality gate, dbt tests)
- merchant_summary (storage, warehouse); idempotency: delete_write
- global_report (consumer, dashboard)
- us_payment_logs -> us_readiness_sensor
- eu_payment_logs -> eu_readiness_sensor
- apac_payment_logs -> apac_readiness_sensor
- us_readiness_sensor -> us_dedup
- eu_readiness_sensor -> eu_dedup
- apac_readiness_sensor -> apac_dedup
- us_dedup -> regional_staging
- eu_dedup -> regional_staging
- apac_dedup -> regional_staging
- regional_staging -> merchant_aggregator
- merchant_aggregator -> gateway_reconciliation
- gateway_daily_totals -> gateway_reconciliation
- gateway_reconciliation -> merchant_summary
- merchant_summary -> global_report
Common follow-up questions
- Finance wants the morning report even when one region misses 6 AM. What changes? (Tests whether the candidate can publish a partial day with the late region flagged and filled in on rerun, without loosening the reconciliation check.)
- Finance teams in three timezones each want the number by 6 AM local time. What do you change? (Tests whether the candidate sees three deadlines against one summary: the pipeline barely moves, the alert thresholds do.)
- Stragglers trail up to a day. How do you correct the affected date without rerunning the whole week? (Tests whether the candidate reruns just that date and relies on delete-then-write by date to replace it cleanly.)