The Retail Blueprint

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
L4

Problem

We're a mid-size online retailer selling about 50,000 SKUs. The BI team needs self-service dashboards to analyze sales by product category, customer segment, geography, sales channel, and time period. Design the dimensional model.

One business. A thousand transactions. Only one layout survives the analytics layer.

Worked solution and explanation

Strip the costume: this is a grain declaration

"Analyze sales by category, segment, geography, channel, and time" is a dimensional model wearing a BI-requirements costume. The real skill being probed is whether you declare the fact grain as atomic, additive order-line events and push every descriptor into surrogate-keyed conformed dimensions. Anyone can list six tables. What separates candidates is refusing to stamp category, brand, or loyalty_tier onto the fact. Do that and you're fine until the product team relabels a category: now every historical fact_order_lines row is wrong, and dashboards drift silently with no error to catch it. A coarser order-level grain would lose product-level slicing entirely, and a single flat table can't absorb catalog or segment changes without rewriting the fact.

Building it

Step 1: Pin the grain at the order line

One row per product per order in fact_order_lines. This atomic grain supports basket analysis, per-line discounting, and category roll-ups without any pre-aggregation. An order-level grain would collapse the products in a basket and you could never slice by category again.

Step 2: Force every descriptor into a dimension

category and brand live in dim_products; loyalty_tier lives in dim_customers; region and country live in dim_geography. Keeping these OFF the fact is exactly what lets an analyst add a new attribute later without touching a single fact row.

Step 3: Key dimensions with surrogates, not source IDs

product_sk and customer_sk decouple the warehouse from operational keys. When a SKU is relaunched or a product changes category mid-year, the surrogate absorbs the new version as a new dimension row while old fact rows keep pointing at the old product_sk. That's how point-in-time history survives.

Step 4: Stamp channel and geography per line too

Channel is captured per order, but you stamp channel_sk on every order line so revenue slices by channel without joining back to an order table. dim_channel and dim_geography keep name strings off the fact, exactly like every other descriptor.

One row per order line; `fact_order_lines` carries only additive measures, every descriptor lives in a conformed dimension
  • dim_customers: customer_sk BIGINT PK, customer_id TEXT, email TEXT, signup_date DATE, loyalty_tier TEXT
  • dim_products: product_sk BIGINT PK, sku TEXT, category TEXT, brand TEXT, list_price DECIMAL
  • dim_dates: date_sk INT PK, calendar_date DATE, fiscal_week INT, is_holiday BOOLEAN
  • dim_geography: geography_sk BIGINT PK, country TEXT, region TEXT, postal_code TEXT
  • dim_channel: channel_sk BIGINT PK, channel_name TEXT, channel_type TEXT
  • fact_order_lines: order_line_id BIGINT PK, order_id BIGINT, customer_sk BIGINT FK, product_sk BIGINT FK, date_sk INT FK, geography_sk BIGINT FK, channel_sk BIGINT FK, quantity INT, net_amount DECIMAL
  • fact_order_lines.customer_sk references dim_customers.customer_sk
  • fact_order_lines.product_sk references dim_products.product_sk
  • fact_order_lines.date_sk references dim_dates.date_sk
  • fact_order_lines.geography_sk references dim_geography.geography_sk
  • fact_order_lines.channel_sk references dim_channel.channel_sk
Weekly revenue sliced across every conformed dimension
SELECT
    d.fiscal_week,
    p.category,
    c.loyalty_tier AS segment,
    g.region,
    ch.channel_name,
    SUM(f.net_amount) AS revenue,
    SUM(f.quantity) AS units,
    COUNT(DISTINCT f.order_id) AS orders
FROM fact_order_lines f
JOIN dim_products p ON p.product_sk = f.product_sk
JOIN dim_dates d ON d.date_sk = f.date_sk
JOIN dim_customers c ON c.customer_sk = f.customer_sk
JOIN dim_geography g ON g.geography_sk = f.geography_sk
JOIN dim_channel ch ON ch.channel_sk = f.channel_sk
WHERE d.calendar_date >= '2025-01-01'
GROUP BY d.fiscal_week, p.category, c.loyalty_tier, g.region, ch.channel_name
ORDER BY d.fiscal_week, revenue DESC

Five FK joins fan out to the dimensions; the fact supplies only additive measures

Classic Kimball star
Conformed dimensions, surrogate-key evolvability, additive facts. An attribute relabel is a single dimension row. Cost: joins at query time and per-dimension SCD handling, both cheap on columnar engines.
One Big Table
Flat denormalized single-table reads. Fast to scan, but every attribute change forces a full fact rewrite, there's no conformity across facts, and storage balloons as descriptors repeat on every row.

Common follow-up questions