Three Regions, One Finance Team

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

L6

Problem

Design the platform that turns payment events from our US, EU and APAC regions, each running its own infrastructure, into one consolidated daily view of global payment volume. The finance controller who signs off on that view every morning currently gets three regional silos and no single number to trust.

Payments from everywhere. One consistent report.

Business requirements

• Finance reads yesterday's consolidated totals every morning, so a daily run has to land all three regions in one central store before that read.
• EU payment data is regulated; raw personal information and card data can't cross borders, only aggregated values can.
• Historical transactions are valued at the daily FX rate for their original date, not today's rate.
• Finance has to see which regional totals are still incomplete before signing off; today late or missing data shows up as one final number.

Worked solution and explanation

What this really is

This is a residency boundary problem wearing a reporting costume. Wiring three regional feeds into one warehouse is the easy part. The trap is centralizing first and enforcing residency at query time: by then raw EU card data already sits in a central store, which is an audit failure on day one. It also leaves finance staring at one consolidated number that hides which region never arrived.

Walk the requirements

Step 1: Ingest and aggregate in-region

Each region keeps its own Kafka log, and that log is where residency starts. eu_dedup_fx_aggregate removes duplicates and rolls payments up to totals on EU compute. What leaves it is a regional total per currency and day: no card numbers, no customer identifiers.

Step 2: Convert at the transaction's date

Each aggregator looks up the daily rate for the transaction's original date from fx_rate_history and keeps the original amount, the rate and the converted amount. Apply today's rate instead and every rebuild silently rewrites last quarter.

Step 3: Gate on completeness per region

region_completeness takes one input per region, compares expected and received counts, and tags each region 'complete', 'late' or 'missing'. The tag lands beside the totals in central_warehouse, so finance signs off knowing what is inside the number.

Step 4: Schedule against the morning read

daily_orchestrator runs the cycle once a day so the three regional totals and their status are in central_warehouse before finance opens finance_dashboard each morning.

The reference design

Centralize, then filter

Raw EU rows already sit in the central warehouse, and a filter on read does not change where they are stored. FX looked up at today's rate makes totals drift on every rebuild. A late region vanishes into one number.

Aggregate in-region, then ship

Only aggregates cross the border. The rate for the transaction's date is fixed on the row. A late region shows up as 'late' next to its total instead of hiding inside it.

Common follow-up questions

• EU now forbids even some aggregated breakdowns from leaving the region. What changes, and where? (Only the aggregation logic in `eu_dedup_fx_aggregate` changes. Downstream of the border, nothing does.)
• Last quarter's USD total moved slightly between two runs a month apart. How does finance find out why? (The rate stored on each row, compared with the history in `fx_rate_history`, shows exactly which dated rate was corrected.)
• The APAC feed is still short an hour before finance reads. How would you make sure someone hears about it before finance does? (Tests whether the candidate adds alerting off `region_completeness` rather than off job failure alone.)