Pipeline design rounds are usually lost in the first minute, by candidates who start drawing.
The prompt names a dashboard and a few sources, and the instinct is to sketch the architecture — ingestion, a lake, a warehouse, an orchestrator, a BI tool. Every box will be defensible. But the interviewer built the prompt around a question the boxes can't answer, and in this one it's the most ordinary word in the brief.
The brief
"Finance wants a daily revenue dashboard for our subscription business. Data lives in our billing provider, our product database and the CRM. Design the pipeline."
The first question isn't about pipelines
"Before anything else — which revenue? Cash collected, monthly recurring revenue, and recognised revenue are three different numbers, and for a subscription business with annual plans they can be a long way apart. I'd want to know which one this dashboard is for, because it changes what I model and what I have to keep."
Then show why it matters, with numbers. For the purposes of the round, a business with 2,400 customers on a $50 monthly plan and 600 on a $1,200 annual plan, in September:
| Cash collected | Monthly recurring revenue | |
|---|---|---|
| Monthly plans | $120,000 | $120,000 |
| Annual plans | $96,000 — the 80 annual invoices that fell in September | $60,000 — all 600, at $100 a month each |
| Refunds issued in September | −$3,400 | — |
| September | $212,600 | $180,000 |
"Both numbers are correct and they're $32,600 apart — that's the annual prepayments collected this month, $96,000, minus the $60,000 of monthly value they represent, minus the refunds. A dashboard that just says 'revenue' will be wrong in somebody's meeting. So I'd build both, label them, and recommend MRR as the headline for the business and cash for finance's own reconciliation."
Recognised revenue, the third definition, is what the accounts report. I'd say plainly that I wouldn't compute it in this pipeline — revenue recognition has rules finance owns, and a pipeline that half-implements them produces a third number that disagrees with the books.
This is the move the round is built to find. The prompt says "revenue" on purpose. Candidates who start drawing before asking which revenue will build a technically sound pipeline for an undefined number — and the interviewer will spend the rest of the hour finding out whether they ever notice. Asking in the first minute, and then showing the gap with arithmetic, is worth more than anything you can draw afterwards.
The volume check
"Next, how big is this? About 3,000 customers. Billing events — new subscriptions, renewals, upgrades, refunds — are in the low hundreds a day. That's small. It fits in a warehouse table without thinking about it, and a daily batch is enough for a number finance reads once a day.
So I'm not reaching for streaming, a message bus or a distributed processing framework. They'd add cost and failure modes to something that runs comfortably as SQL. The hard part of this pipeline is correctness, not scale."
That paragraph is a senior signal precisely because it removes architecture. Interviewers see a great many designs that are three times bigger than the problem, and noticing that the problem is small is rarer than knowing how to make it big.
The design, layer by layer
| Layer | What happens there | The decision that matters |
|---|---|---|
| Sources | Billing webhooks, plus a daily full export from the billing provider. Product database replica. CRM API | Webhooks get missed. The daily export is the reconciliation, not a backup |
| Raw | Everything lands as received, append-only, with a load timestamp | Never transform on the way in — when a number is wrong, this is the evidence |
| Staging | Typed, deduplicated on the source's own IDs — invoice ID, subscription ID | Dedup on source IDs, not on load time, or a re-sent webhook becomes a second payment |
| Model | Subscription changes as events — new, expansion, contraction, churn, reactivation — rolled into a daily MRR snapshot. Cash as its own fact table | MRR comes from changes in subscriptions, not from invoices. Annual plans are the reason |
| Serve | Two clearly named metrics, each with an "as of" date | The dashboard says which revenue it is, every time |
Two smaller decisions worth saying out loud, because each one is a classic source of wrong numbers:
- Currency. Store the original amount and currency, and convert at the rate on the invoice date. Converting at today's rate makes last month's revenue change every morning.
- Mid-month plan changes. An upgrade on the 14th is an expansion event dated the 14th. Treating it as a new subscription double-counts the customer.
When a closed month changes
"Here's the case that decides whether the design is right. A sales-assisted contract is signed on September 28th, but the account manager enters it in the CRM on October 2nd, effective September 28th. September's MRR was already on the dashboard. It's now wrong."
The design answer:
"So the pipeline doesn't treat a day as finished when it ends. Each run rebuilds a trailing window — say the last 35 days — from the staging layer, idempotently, keyed on the source IDs. September gets corrected on October 2nd's run. And the dashboard marks the current period as open, so nobody quotes a number that's still allowed to move."
Then the follow-up you should raise before they do:
"Refunds are the trap here, and they work the opposite way. A refund issued on October 3rd for a September charge is an October cash event — the money moved in October — so it never rewrites September's cash. For MRR, what matters isn't the refund but the cancellation it usually comes with. Mixing those up is how a dashboard ends up counting one churn twice."
How to make a rebuild like that safe to re-run, and how to backfill without taking production down with it, are their own questions — 14 and 16 in Data Engineer Interview Questions. In this round, naming that the window is rebuilt idempotently is enough; the interviewer will ask if they want more.
How you'd know it's right
"Every day, I'd reconcile the pipeline's cash total against the billing provider's own total for the same period, and alert if they differ by more than a small threshold. That one check catches missed webhooks, double-counted events and broken currency conversion — the three failures I'd most expect.
For MRR there's no external total to compare against, so I'd check that the daily snapshot equals the previous day plus that day's movements. If it doesn't add up, something was dropped or counted twice."
The follow-ups
"Finance wants it hourly." — "I'd ask what decision an hourly number feeds. Hourly cash is easy and occasionally useful around a launch. Hourly MRR mostly shows noise — a renewal at 2am and a cancellation at 3am — and I'd rather push back than build a number that invites people to react to it."
"Sales says the CRM is the source of truth for contract value." — "For contracted value, maybe. For revenue, the billing provider is, because that's where money actually moves. I'd keep both, show where they disagree, and treat the disagreement as a report worth having rather than a bug to hide."
"What would you build first?" — "Cash, reconciled against the billing provider. It's the simplest, it has an external check, and it earns trust in the pipeline before I ship MRR, which is the number people will argue about."
The architecture
“Events stream through Kafka into a data lake, Spark jobs transform them into a lakehouse, Airflow orchestrates, and the dashboard reads from the gold layer.”
A respectable diagram for a much bigger problem, and silent on which revenue it computes. The interviewer now has to ask the definition question themselves — and the candidate has already shown they wouldn't have.
The definition, then the smallest design
“Which revenue? Cash and MRR are $32,600 apart this month. It's 3,000 customers, so a daily batch in the warehouse — and the hard part is the month that changes after it closes.”
A question, a number, and a design sized to the problem. Everything afterwards is detail the interviewer can choose to explore, and every direction leads somewhere already considered.
Saying it out loud
Put the definition table on the whiteboard before any architecture, and let it sit there for the rest of the round. It anchors the conversation: every later decision — the model, the restatement window, the reconciliation — can be pointed at as serving one of those two columns.
The sentence worth rehearsing is the reconciliation one: "both are correct, and they're $32,600 apart because of annual prepayments and refunds." It's three facts in one breath, and if you can say it without stopping, the interviewer stops testing whether you understand the business and starts discussing the design with you.
The consumer side of this same problem — an analyst handed a number that's technically correct and quietly misleading — is worked through in the data analyst case study.
Practical target: take the main number from a pipeline you've built and write its definition in one sentence — the event that creates it, the date it counts on, and what reverses it. If that sentence takes more than one attempt, the pipeline was probably correct and its output ambiguous, and that ambiguity is exactly what this round is designed to surface.
