Food Truck Operations Data Model

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
Asked in
Amazon

Problem

We operate a fleet of food trucks. Each truck has a menu and moves between locations throughout the day. Customers order at a truck and pay. Operations wants to know: which items sell best at which locations? Which trucks are most profitable by day? Design the data model to answer these questions.

Mobile vendor, fixed menu, unpredictable locations.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate understands when to snapshot a measure onto a fact row instead of joining through a dimension. Price and location both drift constantly in this domain, and the design that looks normalized on day one quietly rewrites history on day ninety.

Break down the requirements

Step 1: Declare the grain

One row in fact_sales equals one line item on one order at one truck at one point in time. This is atomic and additive.

Step 2: Model the catalog vs the menu

menu_items is the global catalog. truck_menu_items is the junction expressing which truck sells which item at which price, valid between effective dates.

Step 3: Snapshot price_charged on the fact

Prices change. If a customer paid five dollars and the menu later says six, the sales total must still show five. The snapshot lives on fact_sales.price_charged.

Step 4: Snapshot location_id on the fact

Because trucks physically move, the location at sale time is a fact attribute, not a truck attribute. Joining through a schedule dimension is possible but adds latency and edge cases.

The solution

Below is one conceptually sound approach. The key decision is denormalizing price_charged and location_id onto the fact row, which trades a little storage for correctness under change.

`price_charged` and `location_id` are snapshotted on `fact_sales` so history never rewrites
  • trucks: truck_id INT PK, name TEXT, license_plate TEXT, capacity INT
  • locations: location_id INT PK, name TEXT, latitude DECIMAL, longitude DECIMAL, city TEXT
  • menu_items: menu_item_id INT PK, name TEXT, category TEXT
  • truck_menu_items: truck_id INT FK, menu_item_id INT FK, list_price DECIMAL, effective_from DATE, effective_to DATE
  • fact_sales: sale_id BIGINT PK, truck_id INT FK, location_id INT FK, menu_item_id INT FK, sold_at TIMESTAMP, price_charged DECIMAL, quantity INT
  • trucks references truck_menu_items
  • menu_items references truck_menu_items
  • trucks references fact_sales
  • locations references fact_sales
  • menu_items references fact_sales

The analysis pattern

Top items per location last week
SELECT
    l.name AS location,
    m.name AS item,
    SUM(s.price_charged * s.quantity) AS revenue,
    SUM(s.quantity) AS units
FROM fact_sales s
JOIN locations l ON l.location_id = s.location_id
JOIN menu_items m ON m.menu_item_id = s.menu_item_id
WHERE s.sold_at >= NOW() - INTERVAL '7 days'
GROUP BY l.name, m.name
ORDER BY l.name, revenue DESC

Trade-offs and alternatives

Snapshot on fact
price_charged and location_id live on fact_sales. * Reads are a simple SELECT SUM * History is immutable by construction * Slightly more fact storage
Type 2 SCD on menu
truck_menu_items versioned with effective_from/effective_to and surrogate keys. * Single source of truth for price * Every revenue query becomes a temporal range join * Backfills and corrections are delicate

Common follow-up questions