The Revenue That Was Wrong for Two Weeks
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
- Asked in
- Shopify
Problem
Our transformation layer has grown to over 200 models, and we're seeing silent data quality failures slip into production reports. The data team wants a pipeline design that enforces quality gates, prevents bad models from promoting downstream, and gives analysts confidence in the output. Design the pipeline.
Nobody caught it until the CFO asked a question. Design the system that catches it first.
Business requirements
- Revenue was double-counted for two weeks before anyone noticed; bad data has been quietly reaching production reports.
- Business consumes the mart at 7am every morning; the nightly run has to finish in time.
- Order data carries customer email and shipping address; analysts can't see those fields in the mart.
Worked solution and explanation
This is a promotion-control problem dressed up as a data quality story. Anyone can bolt tests onto a dbt project. What separates candidates is where the tests sit in the flow: if a failing model can still land in the mart, the tests are a dashboard, not a gate. Get that wrong and you rebuild the exact incident: revenue double-counted for two weeks and found by the CFO.
Walk the requirements
Step 1: Put the quality gate on the only road to the mart
Row-count tolerance, null rates, uniqueness on order keys and referential integrity run after the models build and before anything loads the warehouse. A double-counted join fails the uniqueness check and the load never happens. Analysts read yesterday's mart: stale, but right.
Step 2: Mask PII in a transform upstream of the mart
Customer email and shipping address are hashed or dropped by a pii masking transform before anything is stored for analysts. The mart never holds the raw values, so a direct SQL query, a notebook or a brand-new BI tool has nothing to leak. Masking placed after the mart protects only the one path it sits on.
Step 3: Give the 7am deadline an owner
The orchestrator runs the nightly DAG and alerts when a stage is at risk against the 7am deadline or when the gate fails. That alert has to fire hours before 7am, while on-call can still fix things. A pipeline that merely finishes late tells you at 7:05.
- source tables (source, PostgreSQL)
- orchestrator (transform, Airflow); alerting: Stage at risk vs 7am SLA or quality gate failed
- dbt transformations (transform, dbt)
- pii masking (transform, SQL)
- quality gate (quality gate, dbt tests)
- mart warehouse (storage, PostgreSQL)
- dashboard (consumer, Metabase)
- source tables -> dbt transformations
- orchestrator -> dbt transformations
- dbt transformations -> pii masking
- pii masking -> quality gate
- quality gate -> mart warehouse
- mart warehouse -> dashboard
- Mask in the BI layer
- The warehouse holds raw email and address. One saved query, notebook or new tool skips the BI view and returns them.
- Mask before the mart
- The mart only contains masked values, and the gate can assert that no raw email pattern survives. There is no unmasked path to forget about.
Common follow-up questions
- A model that 200 reports depend on fails its gate at 5am. What happens, and what do analysts see at 7am? (Tests the fail-closed contract: no promotion, yesterday's mart stays, and the alert names the model.)
- An analyst legitimately needs a customer's email to debug a ticket. What in this design allows it? (Tests whether the candidate keeps raw PII out of the mart and routes exceptions through a separate audited path.)