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 in several countries, fulfilled from regional warehouses and delivered by third-party carriers. Design the pipeline and analytics warehouse that track subscription performance, delivery rates, ingredient waste and weekly cohort behavior.

Shipped, maybe. Delivered, debatable.

Business requirements

Worked solution and explanation

This looks like a big meal-kit warehouse design, but it is really three small contracts stacked on one pipe: tokenize before anything lands, update late facts by key instead of rebuilding, and make something own the Monday clock. Anyone can draw sources, a loader and a warehouse. What separates candidates is where the tokenizer sits and what the late carrier file does when it arrives. Get the first wrong and raw EU names are one query away. Get the second wrong and every Tuesday confirmation forces a full fact rebuild.

Walk the requirements

Step 1: Tokenize every feed before the loader

Orders, fulfillment and carrier files all pass through one tokenizer, and only tokens go downstream. The name and address mapping lives in a separate vault. Masking in the BI layer is not this: the raw value is already in a table someone can query.

Step 2: Write the delivery fact by order key

The loader runs with upsert idempotency and partition_overwrite backfill. A confirmation a day or two late rewrites only its own order's delivery row, and a rerun lands the same result. The rest of the fact table is untouched.

Step 3: Let the orchestrator own the Monday deadline

Airflow schedules the weekly run and carries a monitorAlert that pages on-call by 5am if a stage is at risk against the 7am review. The alert is tied to risk, not to failure, because a failure noticed at 6:50 is too late to fix.

Every feed is tokenized before the loader. The loader writes by order key, so late carrier files never rebuild the fact. The orchestrator pages on-call by 5am.
  • orders oltp (source, CDC)
  • fulfillment events (source, Kafka)
  • carrier confirmations (source, API)
  • tokenizer (transform, Spark)
  • tokenization vault (storage, PostgreSQL)
  • orchestrator (transform, Airflow); alerting: Stage at risk vs 7am Monday SLA, alerting by 5am
  • fact loader (transform, dbt); backfill: partition_overwrite; idempotency: upsert
  • subscription warehouse (storage, warehouse); freshness: < 24h
  • analytics dashboard (consumer, Metabase)
  • 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 dashboard
  • Source Systems: orders oltp, fulfillment events, carrier confirmations
  • Ingestion Layer: tokenizer, tokenization vault
  • Transformation Layer: orchestrator, fact loader
  • Data Warehouse: subscription warehouse
  • Serving Layer: analytics dashboard
Rebuild on every late file
The carrier file lands Tuesday and the whole delivery fact is recomputed. The warehouse is busy for hours and the Monday numbers can shift under analysts.
Keyed write per order
The loader upserts one order's delivery row with partition_overwrite. Cost scales with the late rows, a rerun is safe, and everything else stays stable.

Common follow-up questions