Marketplace Sales Warehouse

A hard 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
hard
Seniority
L4
Asked in
Amazon

Problem

On our two-sided marketplace any account can buy or sell, and every item line in an order has both a buyer and a seller, so the analyst building our self-service warehouse needs GMV, commission and seller performance sliceable by account region, product category and its parent category, and date. Sellers reprice constantly, yet booked GMV and commission must never change after the sale. No schema exists yet: establish the entities, their relationships and the dimensional model from scratch.

No schema given. The interviewer is watching.

Worked solution and explanation

What this is really asking

This is a star schema with one twist hiding in the word marketplace: every sale has two parties drawn from the same population. Anyone can lay out a fact and four dimensions. What separates candidates is noticing that buyer and seller are both accounts, so the fact points at the account dimension twice. Miss it and you have a warehouse that can report GMV by buyer region and cannot answer the question the prompt names: how are sellers performing.

Build it in order

Step 1: Declare the grain first

Analysts drill to individual item lines and nothing finer, so fact_sale_line holds one row per item in an order, keyed by sale_line_id with order_id kept as a plain attribute. Quantity, GMV and commission are all additive at this grain.

Step 2: Give the fact both parties

An account can buy today and sell tomorrow, so a separate seller table would duplicate every account that does both. buyer_sk and seller_sk both reference dim_user.user_sk: one dimension, two roles, two edges.

Step 3: Freeze the money on the line

Sellers reprice constantly. price_at_sale and commission_amount live on the fact row, written once at the moment of sale, so a later price change on the product never reaches booked GMV.

Step 4: Snowflake the category and link it to its parent

Products carry brand and SKU plus a category_sk into dim_category. That table references itself: parent_category_sk is a foreign key to its own category_sk, so every category row points at its parent row. The flattened parent_category label sits beside it so rollup queries need no recursive walk.

The reference design

`fact_sale_line` references `dim_user` twice (buyer and seller) and freezes `price_at_sale` and `commission_amount`; `dim_category` points at its own parent through `parent_category_sk`.
  • dim_user: user_sk BIGINT PK, user_id TEXT, region TEXT, signup_date DATE
  • dim_product: product_sk BIGINT PK, product_id TEXT, category_sk BIGINT FK, brand TEXT, sku TEXT
  • dim_category: category_sk BIGINT PK, parent_category_sk BIGINT FK, category_name TEXT, parent_category TEXT
  • dim_date: date_sk INT PK, full_date DATE, year INT, quarter INT, day_of_week INT
  • fact_sale_line: sale_line_id BIGINT PK, order_id BIGINT, buyer_sk BIGINT FK, seller_sk BIGINT FK, product_sk BIGINT FK, date_sk INT FK, quantity INT, price_at_sale DECIMAL, commission_amount DECIMAL
  • fact_sale_line.buyer_sk references dim_user.user_sk
  • fact_sale_line.seller_sk references dim_user.user_sk
  • fact_sale_line.product_sk references dim_product.product_sk
  • fact_sale_line.date_sk references dim_date.date_sk
  • dim_product.category_sk references dim_category.category_sk
  • dim_category.parent_category_sk references dim_category.category_sk

Reading it back

Seller GMV and commission by parent category and region
SELECT
    c.parent_category,
    s.region AS seller_region,
    SUM(f.price_at_sale * f.quantity) AS gmv,
    SUM(f.commission_amount) AS commission
FROM fact_sale_line f
JOIN dim_user s ON s.user_sk = f.seller_sk
JOIN dim_product p ON p.product_sk = f.product_sk
JOIN dim_category c ON c.category_sk = p.category_sk
JOIN dim_date d ON d.date_sk = f.date_sk
WHERE d.year = 2026
GROUP BY c.parent_category, s.region
ORDER BY gmv DESC

The seller role joins through seller_sk; swap in buyer_sk to slice by buyer region instead.

Star with role-played account
One dim_user, joined through buyer_sk or seller_sk depending on the question. Account changes land in one place; the fact stays narrow and additive.
One big denormalized table
Buyer and seller attributes copied inline on every line. Scans are fast on a columnar engine, but every region change means rewriting history and the table widens with each attribute.

Common follow-up questions