The Table That Lies

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
Stitch Fix

Problem

Our operational database has a single wide table with one row per stylist-client session. It contains client attributes, stylist attributes, session metadata, and item-level details all flattened together. Analysts struggle to write correct queries against it and frequently produce wrong aggregations. Design a normalized data model to replace it.

Every query comes out wrong. The data is all there.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether you can diagnose fan-out as the root cause of wrong aggregations. The signal is separating session-level measures from item-level measures into two facts at different grains so a count of sessions is not silently multiplied by the number of items per session.

Break down the requirements

Step 1: Declare two distinct grains

fact_sessions sits at one row per session. fact_session_items sits at one row per item per session. Any query that needs only session measures never joins the child fact.

Step 2: Normalize clients, stylists, and items into dimensions

Attributes that were repeating across sessions move to dimensions. This is where every textbook Kimball diagram lives, but the hiring signal is calling out why it matters: stable attributes do not belong on the fact.

Step 3: Keep booking_fee on the session

Session-level measures stay on fact_sessions. Item purchase revenue lives on fact_session_items. These never go on the same row.

Step 4: Provide a compatibility view

A view that mimics the old wide table can keep legacy reports alive during migration. Strong candidates volunteer this without being asked.

The solution

Below is one defensible model. The conceptual anchor is grain separation: session-level measures never share a row with item-level measures.

`fact_sessions` grain = one row per session; `fact_session_items` grain = one row per item per session
  • dim_clients: client_sk BIGINT PK, client_nk TEXT, size_preference TEXT, city TEXT
  • dim_stylists: stylist_sk BIGINT PK, stylist_nk TEXT, specialization TEXT, tenure_months INT
  • dim_items: item_sk BIGINT PK, sku TEXT, brand TEXT, category TEXT
  • fact_sessions: session_sk BIGINT PK, client_sk BIGINT FK, stylist_sk BIGINT FK, session_ts TIMESTAMP, session_duration_min INT, booking_fee DECIMAL
  • fact_session_items: session_item_sk BIGINT PK, session_sk BIGINT FK, item_sk BIGINT FK, price DECIMAL, was_purchased BOOLEAN
  • dim_clients references fact_sessions
  • dim_stylists references fact_sessions
  • fact_sessions references fact_session_items
  • dim_items references fact_session_items

The analysis pattern

Stylist revenue and session duration without fan-out
SELECT
    st.stylist_nk,
    COUNT(DISTINCT s.session_sk) AS sessions,
    AVG(s.session_duration_min) AS avg_duration,
    SUM(CASE WHEN si.was_purchased THEN si.price ELSE 0 END) AS revenue
FROM fact_sessions s
JOIN dim_stylists st ON st.stylist_sk = s.stylist_sk
LEFT JOIN fact_session_items si ON si.session_sk = s.session_sk
GROUP BY st.stylist_nk

Trade-offs and alternatives

Grain-split two-fact star
Correct aggregations by construction. Requires writers to maintain two facts. Legacy reports need a compatibility view during cutover.
Single fact with DISTINCT-aware reports
No migration needed. Every downstream query must remember the fan-out. Silent errors are common and hard to detect at review time.

Common follow-up questions