The Hunger Shift

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5

Problem

We run a food-delivery marketplace, and product wants a live board of the dishes surging in each city right now, the kind of thing that reads 'pad thai is up 40 percent in Austin this week.' A single order usually bundles several different dishes, and the same dish shows up under many spellings across restaurant menus. The board refreshes every 15 minutes across hundreds of cities.

What's everyone eating? The answer changes hourly.

Worked solution and explanation

Why this problem exists in real interviews

This is a pre-aggregation and dimensional-conformance problem wearing a food-delivery costume. Anyone can draw a star schema. The signal is two decisions: normalizing dish names into dim_dishes so counts do not fragment across menu variants, and materializing agg_dish_city_hourly so a 15-minute refresh never scans raw orders. Miss the first and 'Pad Thai' trends nowhere because its orders scatter across 'pad thai noodles' and 'Thai Noodles'. Miss the second and the dashboard blows its SLA the moment real volume arrives.

Break down the requirements

Step 1: Normalize dish names with dim_dishes

Restaurants call the same thing by different names. A canonical dimension with a canonical_name and a synonyms map prevents trending signals from fragmenting.

Step 2: Keep fact_orders at order-item grain

An order averages 2.5 items, so the atomic fact is one row per order item, each with its own surrogate key and a dish_sk. Grouping items back to a parent order rides on order_id. This is the source of truth for every downstream rollup and the input to the aggregation job. Leaving it at 'order grain' while carrying dish_sk breaks the primary key.

Step 3: Pre-aggregate at dish-city-hour

agg_dish_city_hourly is built on a schedule. A 15-minute refresh reads this table, never the raw fact.

Step 4: Materialize trending_scores

Trending ratio is recent_count / baseline_count. Storing the computed values with computed_at lets the dashboard read a single row per dish and city. Storing the raw counts alongside the ratio is the more defensible choice because it makes the score auditable, but a table that carries only the derived percent change at (dish, city) grain is still a valid trending materialization.

The solution

Below is one defensible model. The conceptual anchor is the pre-aggregation layer: raw order items feed agg_dish_city_hourly, which feeds trending_scores.

`fact_orders` sits at order-item grain; `agg_dish_city_hourly` is the pre-aggregation layer; `trending_scores` is the materialized ratio computed on a schedule
  • dim_dishes: dish_sk BIGINT PK, canonical_name TEXT, cuisine TEXT, tags JSON
  • dim_cities: city_sk BIGINT PK, city_name TEXT, metro_area TEXT, timezone TEXT
  • fact_orders: order_item_sk BIGINT PK, order_id BIGINT, dish_sk BIGINT FK, city_sk BIGINT FK, order_ts TIMESTAMP, quantity INT, amount FLOAT
  • agg_dish_city_hourly: dish_sk BIGINT FK, city_sk BIGINT FK, hour_bucket TIMESTAMP, order_count INT, gross_sales FLOAT
  • trending_scores: dish_sk BIGINT FK, city_sk BIGINT FK, computed_at TIMESTAMP, recent_count INT, baseline_count INT, trending_ratio FLOAT
  • dim_dishes references fact_orders
  • dim_cities references fact_orders
  • dim_dishes references agg_dish_city_hourly
  • dim_cities references agg_dish_city_hourly
  • dim_dishes references trending_scores
  • dim_cities references trending_scores

The analysis pattern

Currently trending dishes per city
SELECT
    d.canonical_name,
    c.city_name,
    t.trending_ratio
FROM trending_scores t
JOIN dim_dishes d ON d.dish_sk = t.dish_sk
JOIN dim_cities c ON c.city_sk = t.city_sk
WHERE t.computed_at = (SELECT MAX(computed_at) FROM trending_scores)
  AND t.trending_ratio > 1.2
ORDER BY c.city_name, t.trending_ratio DESC

Trade-offs and alternatives

Pre-aggregation layer plus canonical dish dimension
Refresh SLA is achievable. Atomic fact remains for backfills. Requires an aggregation job and a dish resolution pipeline. Storage cost is low relative to raw orders.
On-demand query over raw orders
No aggregation job to maintain. Simpler to reason about. Cannot meet a 15-minute refresh at high volume without a columnar engine and aggressive caching.

Common follow-up questions