Retailer Data Warehouse Design

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're a mid-size e-commerce company processing about 10,000 orders per day. The analytics team needs to build dashboards for sales performance, customer segmentation, and product trends. Right now all the data lives in a normalized Postgres OLTP database and queries are slow. Can you design a dimensional model for the analytics warehouse?

Queries are crawling. The analysts are not happy.

Worked solution and explanation

Why this problem exists in real interviews

This is a minimalist star schema probe. Interviewers use it to test whether a candidate can resist the urge to over-engineer: can they declare a clean atomic fact, three conformed dimensions, and stop there? The signal is proportionality between the model and the stated requirements.

Break down the requirements

Step 1: Fix the grain

fact_orders is one row per product per order. Atomic grain supports every roll-up without pre-aggregation and keeps the model answerable to questions the product team has not yet asked.

Step 2: Choose the minimum conformed dimensions

Customer, product, and date cover the stated reporting needs. A fourth dimension like geography is cheap to add later if required; adding it upfront costs maintenance without delivering value.

Step 3: Use surrogate keys

customer_sk, product_sk, and date_sk decouple the fact from source system churn. When a customer merges accounts or a product gets relaunched, the surrogate absorbs it.

Step 4: Keep measures additive

quantity and extended_amount roll up cleanly across any dimension combination. Derived measures (ASP, unit price) are computed at query time.

The solution

Below is one conceptually sound design. The restraint is the point: a three-dimension star with an atomic fact answers the stated questions without over-committing.

Atomic grain with three conformed dimensions; expand the model only when new questions demand it
  • dim_customer: customer_sk BIGINT PK, customer_id TEXT, segment TEXT, signup_date DATE
  • dim_product: product_sk BIGINT PK, sku TEXT, category TEXT, brand TEXT, list_price DECIMAL
  • dim_date: date_sk INT PK, calendar_date DATE, day_of_week INT, fiscal_quarter TEXT
  • fact_orders: order_line_id BIGINT PK, order_id BIGINT, customer_sk BIGINT FK, product_sk BIGINT FK, date_sk INT FK, quantity INT, extended_amount DECIMAL
  • dim_customer references fact_orders
  • dim_product references fact_orders
  • dim_date references fact_orders

The analysis pattern

Top categories by customer segment
SELECT
    c.segment,
    p.category,
    SUM(f.extended_amount) AS revenue,
    COUNT(DISTINCT f.order_id) AS orders,
    SUM(f.quantity) AS units
FROM fact_orders f
JOIN dim_customer c ON c.customer_sk = f.customer_sk
JOIN dim_product p ON p.product_sk = f.product_sk
JOIN dim_date d ON d.date_sk = f.date_sk
WHERE d.fiscal_quarter = '2025-Q1'
GROUP BY c.segment, p.category
ORDER BY c.segment, revenue DESC

Trade-offs and alternatives

Minimal three-dimension star
Clean evolvability, proportional to requirements, fast ETL. Cost: new analytical requests may require adding dimensions over time.
Pre-built warehouse template
Every dimension you might need is already present. Cost: ETL complexity and storage grow ahead of demand, and many dimensions stay empty for months.

Common follow-up questions