Every Line Remembered

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
Nintendo

Problem

Our sales reports cannot be trusted: when a customer moves, last year's sales by city follow them to the new address, and when a retired product relaunches under its old identifier it inherits the retired item's sales. Redesign the retail chain's transactional orders, customers, products, stores and employees as a dimensional warehouse where every line item of an order is its own sales record, still traceable to its order and tied to the customer, product, store, employee and date of the sale. Each sale must keep the address its customer had on the order date while we can still see where every customer lives now, and digital storefronts with no physical location must report as stores alongside the physical ones.

Customers move, products relaunch, and some stores have no address. Reshape the tables so nothing gets forgotten.

Worked solution and explanation

What this problem really is

Underneath the retail costume this is a grain-and-history problem. Anyone can list five dimension tables and a fact; what separates candidates is declaring the fact at the order line rather than the order, and deciding which dimensions have to carry history. Model the fact at order grain and every product-mix question needs a fan-out back to line detail. Model dim_customer as Type 1 and the first time a customer moves you silently rewrite every past geo report. The reused product identifiers are the tell that forces surrogate keys, and the digital storefronts are the tell that the store dimension cannot assume a physical address.

Break down the requirements

Step 1: Declare the fact grain as the order line

One row per order line lets analysts compute units, gross sales, and product mix by simple aggregation. Order-grain would force fan-out joins back to line detail for any product question.

Step 2: Introduce surrogate keys on every dimension

Natural keys stay as *_nk columns but are not the PK. This lets a product_id renumber in the source (a retired item relaunched under the same id) without cascading to fact rows.

Step 3: Apply SCD Type 2 on dim_customer address

Customer city and state move. Tracking the as-of address via effective_from, effective_to, and is_current lets a past sale attribute to the address it shipped to.

Step 4: Conform dim_date across any time-based lookup

One calendar dimension is shared by every fact and by date-typed columns on dimensions via role-playing keys.

Step 5: Keep dim_store flexible enough for digital

Digital storefronts have no physical address. A nullable address is one defensible choice; a store_type flag lets reporting partition physical vs digital cleanly, all within one dim_store.

The solution

Below is one defensible model. The grain anchors the rest of the design, and Type 2 on dim_customer is the one SCD lever worth defending out loud.

`fact_sales` grain = one row per order line; `dim_customer` carries as-of address via SCD Type 2
  • dim_customer: customer_sk BIGINT PK, customer_nk TEXT, city TEXT, state TEXT, effective_from TIMESTAMP, effective_to TIMESTAMP, is_current BOOLEAN
  • dim_product: product_sk BIGINT PK, product_nk TEXT, sku TEXT, category TEXT, brand TEXT
  • dim_store: store_sk BIGINT PK, store_nk TEXT, store_type TEXT, region TEXT
  • dim_employee: employee_sk BIGINT PK, employee_nk TEXT, role TEXT, hire_date DATE
  • dim_date: date_key INT PK, calendar_date DATE, fiscal_week TEXT
  • fact_sales: sales_line_sk BIGINT PK, order_nk TEXT, customer_sk BIGINT FK, product_sk BIGINT FK, store_sk BIGINT FK, employee_sk BIGINT FK, date_key INT FK, quantity INT, extended_price FLOAT
  • dim_customer references fact_sales
  • dim_product references fact_sales
  • dim_store references fact_sales
  • dim_employee references fact_sales
  • dim_date references fact_sales

The analysis pattern

Weekly gross sales by region and category
SELECT
    d.fiscal_week,
    s.region,
    p.category,
    SUM(f.quantity) AS units,
    SUM(f.extended_price) AS gross_sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_store s ON s.store_sk = f.store_sk
JOIN dim_product p ON p.product_sk = f.product_sk
GROUP BY d.fiscal_week, s.region, p.category

Trade-offs and alternatives

Kimball star with line-grain fact
Predictable joins, conformed dimensions, good BI tool support. Writes are heavier because dimensions require SCD processing. Storage grows with dimension history.
One Big Table on columnar storage
Single wide fact with dimension attributes flattened onto every row. No joins at query time. Schema evolution is trickier and reprocessing history on a dimension change rewrites millions of rows.

Common follow-up questions