Every Region Exports Its Own Way

A medium 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

medium

Seniority

L5

Problem

Design one pipeline to move transactional sales data from multiple regional databases into a centralized analytics layer on GCP, replacing the ad-hoc exports our regional teams run today. The five regions sit in different time zones and each adds a few million sales rows a day, with deals edited for weeks after they are created.

Sales data, BigQuery, Dataflow. Make it all sing.

Business requirements

• Regional sales managers expect same-day actuals on their dashboard each morning; the global executive view has to include all regions.
• The CFO has set a target to bring the warehouse bill down without slowing reports.
• Deal-level sales data is commercially sensitive; sales reps see only their own region while finance sees everything.

Worked solution and explanation

What this really is

This is multi-tenant warehouse consolidation dressed up as an ingestion project. Anyone can draw regional databases flowing into a warehouse. The real test is whether three asks from three rooms survive one pipe: morning actuals per region, a smaller bill, and region isolation on deals. The trap is one nightly job that pulls every region into a flat table. The slowest region then sets everyone's deadline. Every dashboard scans the whole table, and region filtering lives in the BI tool, one forgotten clause away from a rep seeing another region's deals.

Step 1: Replace ad-hoc exports with one extraction path

Regional extraction reads each database's change log and publishes to the ingestion queue, keyed by region. Every region ships the same way, and a stalled region only grows its own backlog.

Step 2: Normalize and upsert in the transform

Beam maps each region's schema to one shape and upserts on deal_id. A redelivered message overwrites a row instead of doubling revenue.

Step 3: Schedule each region on its own clock

The orchestrator runs one task per region against that region's local deadline, so a late region blocks no one else. The global executive view refreshes once every region has landed.

Step 4: Partition by sale_date, cluster by region

A manager's 'last 7 days' query prunes to 7 partitions and, inside them, to one region's blocks. Bytes billed track the question asked, not the table size. That is the CFO answer.

Step 5: Enforce the region boundary on the table

A row access policy filters region by the caller's group, and finance gets a policy that grants every row. The dashboard, a notebook and a CSV export all return the same rows.

The reference design

One nightly global job

Extracts every region in one run. One slow region delays everyone, and a rerun reloads the world, often appending duplicates.

Per-region close

Data arrives all day and each region closes on its own clock. A rerun overwrites one sale_date partition for one region.

Common follow-up questions

• A dashboard filters by date but still reads too many bytes. What do you check? (Whether the filter hits `sale_date` directly or through a function that defeats pruning, and whether `region` is in the predicate so clustering applies.)
• APAC's source schema adds a column mid-quarter. What breaks? (Schema evolution in the transform: absorb additive columns and quarantine unknown shapes instead of failing the region.)
• One region publishes a malformed record. Where does it go? (Add a dead letter path off the transform so a bad record is parked and replayed, rather than blocking that region's day.)