Two Million Boxes by Monday Morning
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
- L5
- Asked in
- HelloFresh
Problem
We ship weekly meal-kit boxes to subscribers across multiple countries. Orders are placed online, fulfilled through regional warehouses, and delivered via third-party carriers. The analytics team needs a warehouse that tracks subscription performance, delivery rates, ingredient waste, and weekly cohort behavior. Design the pipeline and warehouse model.
Shipped, maybe. Delivered, debatable.
Business requirements
- The weekly business review runs Monday morning and analytics needs prior-week data ready by 7am; pipeline failures have to alert on-call by 5am Monday.
- Carriers confirm delivery a day or two after the box arrives; the warehouse has to update delivery status when late data lands without rebuilding the fact table.
- EU subscriber name and address are PII under GDPR; tokenization has to happen before any record reaches the analytics warehouse.
Worked solution and explanation
Why this problem exists in real interviews
Weekly meal-kit subscription analytics with a Monday-morning deadline that has to alert on-call by 5am, late-arriving carrier delivery confirmations that update the warehouse without rebuilding the fact, and EU PII tokenized before any warehouse landing. The trap is treating the carrier confirmation as a separate batch or letting PII land before tokenization.
The default reach is one weekend run that loads orders, fulfillment, and what's available of carrier confirmations. Late carrier confirmations land Monday afternoon and the team rebuilds the entire fact table; the warehouse goes offline for hours. PII lands and is tokenized in a downstream view; one direct query exposes raw values.
Walk the requirements
Step 1: Land prior-week analytics by 7am Monday with on-call alert by 5am
The orchestrator runs the weekly DAG with sensors firing by 5am Monday if any stage is at risk. On-call has hours, not minutes, to address pipeline failures before the 7am business review. Without orchestration the deadline isn't owned; without the warehouse the analytics layer has nowhere to live.
Step 2: Late carrier confirmations partition-overwrite the affected row
Carriers confirm delivery a day or two after the box arrives. Each confirmation arrives keyed on order id and partition-overwrites that order's delivery status row in the fact. The rest of the fact stays as is. A 'rebuild the fact every time a carrier confirmation lands' approach takes the warehouse offline for hours; targeted overwrite is the contract.
Step 3: Tokenize EU PII at ingest; warehouse holds tokens
EU subscriber name and address are GDPR PII. Tokenization runs at the boundary; the warehouse and downstream stores hold tokens. The mapping vault sits in a restricted environment with audited access. A 'mask in BI' approach is the version where one direct query exposes raw values; tokenize-at-ingest is the contract that keeps PII out of queryable tables.
The shape that fits
- orders_oltp (source, CDC)
- fulfillment_events (source, Kafka)
- carrier_confirmations (source, S3)
- tokenizer (transform, Spark); on error: alert
- tokenization_vault (storage, PostgreSQL)
- orchestrator (transform, Airflow); on error: alert; alerting: Stage at risk vs 7am Monday SLA, alerting by 5am
- fact_loader (transform, dbt); backfill: partition_overwrite; idempotency: upsert
- subscription_warehouse (storage, Snowflake); freshness: < 24h; backfill: partition_overwrite
- analytics_team (consumer, Tableau); freshness: < 24h
- orders_oltp -> tokenizer
- fulfillment_events -> tokenizer
- carrier_confirmations -> tokenizer
- tokenizer -> tokenization_vault
- tokenizer -> fact_loader
- orchestrator -> fact_loader
- fact_loader -> subscription_warehouse
- subscription_warehouse -> analytics_team
- Sources + tokenize: orders_oltp, fulfillment_events, carrier_confirmations, tokenizer, tokenization_vault
- Load + warehouse: orchestrator, fact_loader, subscription_warehouse
- Consumers: analytics_team
Common follow-up questions
- A carrier delivers two confirmations for the same order (a delivery and a later correction). What does this design do, and what does the warehouse show? (Tests whether the candidate sees the partition-overwrite by order id as the contract: the second confirmation overwrites the first; the warehouse reflects the corrected status. The fact's other rows are unaffected.)
- A GDPR deletion request arrives for an EU subscriber. What in this design lets the deletion happen without removing the order's existence? (Tests whether the candidate sees the tokenization vault as the boundary: deletion removes the subscriber's mapping; the order rows hold tokens that are now orphaned. The order facts stay intact; the link to the person is gone.)