The Sales Architecture

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

Problem

We sell physical products through an online marketplace, and our normalized transactional database is too slow to power the BI dashboards analysts depend on. They need to break down revenue by product, seller, customer, geography, and time. Design a schema that makes those slices fast to query at scale.

Numbers are easy. Making them queryable at scale is the real job.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate can execute a textbook star schema without cutting corners: one atomic fact, five conformed dimensions, surrogate keys, additive measures. The interviewer is testing discipline, not creativity. The signal is how quickly the candidate locks grain and resists ad-hoc attributes on the fact.

Break down the requirements

Step 1: Declare grain

fact_sales is one row per product per transaction. Atomic grain is what makes the star answer roll-ups at any level.

Step 2: Conformed dimensions per slice

Each stakeholder asks for a different cut: product team wants category, sales team wants region, finance wants quarter. Five dimensions cover the common vocabulary.

Step 3: Surrogate keys on dimensions

Each dimension has a BIGINT surrogate. Source system keys change; surrogates do not. This is the main defense against upstream churn.

Step 4: Keep measures fully additive

quantity and net_amount sum cleanly across any dimension combination. Derived metrics (discount %, ASP) are computed at query time.

The solution

Below is one conceptually sound star. Five conformed dimensions and one atomic fact is the canonical retail analytics shape; the discipline is in stopping there.

One atomic fact, five conformed dimensions, surrogate keys throughout; extend only when new questions demand it
  • dim_product: product_sk BIGINT PK, sku TEXT, category TEXT, list_price DECIMAL
  • dim_customer: customer_sk BIGINT PK, customer_id TEXT, segment TEXT, signup_date DATE
  • dim_seller: seller_sk BIGINT PK, seller_id TEXT, region TEXT, hire_date DATE
  • dim_date: date_sk INT PK, calendar_date DATE, fiscal_quarter TEXT
  • dim_geography: geography_sk BIGINT PK, country TEXT, region TEXT, city TEXT
  • fact_sales: sales_line_id BIGINT PK, date_sk INT FK, product_sk BIGINT FK, customer_sk BIGINT FK, seller_sk BIGINT FK, geography_sk BIGINT FK, quantity INT, net_amount DECIMAL
  • dim_date references fact_sales
  • dim_product references fact_sales
  • dim_customer references fact_sales
  • dim_seller references fact_sales
  • dim_geography references fact_sales

The analysis pattern

Seller performance across product categories
SELECT
    s.region,
    s.seller_id,
    p.category,
    SUM(f.net_amount) AS revenue,
    SUM(f.quantity) AS units,
    COUNT(DISTINCT f.customer_sk) AS unique_customers
FROM fact_sales f
JOIN dim_seller s ON s.seller_sk = f.seller_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 s.region, s.seller_id, p.category
ORDER BY revenue DESC

Trade-offs and alternatives

Flat five-dimension star
Conformity, evolvability, fast BI on columnar warehouses. Cost: joins at query time and dimension-maintenance pipelines.
Snowflake with nested hierarchies
Smaller dimension storage, independent hierarchy updates. Cost: more joins per query, more ETL surface area, and most BI tools prefer flat dimensions.

Common follow-up questions