Six Sources, One Platform

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

Six source systems feed a new analytics platform: two emit events continuously, peaking at 5,000 per second combined, while four export nightly or weekly. About 40 finance analysts read the results through one BI tool. Design the end-to-end pipeline from the six sources to those finance dashboards.

ADF orchestrates. Unity Catalog governs. Nothing leaks.

Business requirements

• Six source systems have very different freshness needs, from continuous streams to weekly reference exports; pinning them all to one shared cadence under-serves the fast ones or over-spends on the slow ones.
• Finance teams need access to the data without seeing customer email, phone, or names, so all access has to funnel through one governed layer rather than each dashboard configuring its own permissions.
• When a finance dashboard number is questioned, the platform team has to be able to point to the source records that produced it.

Worked solution and explanation

Why this problem exists in real interviews

This problem has six source systems with very different cadences. Finance needs the data without customer identifiers, and lineage has to answer for a number when it gets challenged. Each of these is easy in isolation. Together they defeat the obvious design: one nightly orchestration that loads everything into one warehouse and handles permissions in the BI tool.

The first instinct is one nightly DAG that hits all six sources, normalises the data into a warehouse, and applies security in each dashboard. Three things go wrong. The fast sources go stale because the slowest source set the schedule. Customer email leaks because the seventh dashboard forgets its config. And when an audit asks where a number came from, someone reconstructs it from memory. None of these failures are exotic. They are the default outcome of putting schedule, access and lineage in the wrong layers.

Walk the requirements

Step 1: Run each source on its own cadence

When a continuous source and a weekly export share a schedule, one wastes compute and the other is silently stale. Give the continuous feeds a streaming path with a real-time SLA. Give the nightly extracts and weekly drops a daily batch path. Both paths converge on the same masking stage and warehouse, but each arrives on its own clock.

Step 2: Strip customer identifiers on the way in, not in each dashboard

Finance needs the dataset without customer email, phone or names. Put one masking stage on every path into the governed warehouse, so the tables finance reads never hold those columns in the clear. Then point every dashboard at that warehouse, and it no longer matters which dashboard finance opens. The wrong place for this is the BI tool. There, the boundary depends on someone configuring every new dashboard correctly, and the next leak comes from the one they miss.

Step 3: Index lineage off the warehouse

When finance points at a number and asks where it came from, the answer should be a lookup. That means lineage is captured as part of the load and fed from the warehouse into a lineage index such as OpenLineage, not written on a wiki page after the fact. The test is simple: can you take any dashboard figure and walk back to the source records without reading any code?

The shape that fits

Common follow-up questions

• If a finance number is challenged, what would a finance analyst click on to answer where it came from without asking the data team? (Tests whether lineage is genuinely queryable and self-serve, or whether it still requires a data engineer to interpret.)
• How would you onboard a seventh source without writing new pipeline code? (Tests whether the pattern is config-driven (declare a source, get a pipeline) or whether each source is bespoke.)