The Schema That Could Not Answer Back

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
L5
Asked in
Stitch Fix

Problem

A personal styling service tracks shipments in a single wide denormalized table with one row per shipment, repeating item attributes across numbered columns. The business wants to add return reasons, calculate client lifetime value, and attribute revenue by brand. The current table cannot answer these questions without significant ETL changes. Redesign it.

Forty columns in. Zero useful answers out.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether you can detect repeating groups (item_1 through item_5) and normalize them into a child fact at the correct grain. The signal is naming the item-grain fact and recognizing that a new business question like "return reason" becomes a column on that child fact instead of an ETL rewrite.

Break down the requirements

Step 1: Declare the shipment grain on fact_shipments

One row per shipment holds shipment-level measures like total and item_count. Shipment count questions stay cheap.

Step 2: Unpivot item columns into fact_shipment_items

Each numbered column pattern becomes one row per item per shipment. was_kept and return_reason live here because they are item-level decisions.

Step 3: Conform client and stylist as dimensions

dim_client and dim_stylist hold stable attributes. Client lifetime value is computed from shipments, not stored as a frozen column.

Step 4: Put product as its own dimension for brand rollups

Brand and category live on dim_product. Revenue-by-brand questions become a single join instead of a string-parse.

Step 5: Isolate PII on dim_client

Client name and email stay on dim_client so GDPR erasure does not touch financial facts.

The solution

Below is one conceptually sound model. The grain split between shipment and shipment-item is the decision that pays for itself every time a new business question arrives.

`fact_shipment_items` grain = one row per item per shipment; `return_reason` is an item attribute, not a shipment column
  • dim_client: client_sk BIGINT PK, client_nk TEXT, state TEXT, age_bucket TEXT, signup_ts TIMESTAMP
  • dim_stylist: stylist_sk BIGINT PK, stylist_nk TEXT, specialty TEXT, hired_ts TIMESTAMP
  • dim_product: product_sk BIGINT PK, sku TEXT, brand TEXT, category TEXT, list_price DECIMAL
  • dim_date: date_key INT PK, calendar_date DATE, fiscal_month TEXT
  • fact_shipments: shipment_sk BIGINT PK, client_sk BIGINT FK, stylist_sk BIGINT FK, shipped_date_key INT FK, shipment_total DECIMAL, item_count INT
  • fact_shipment_items: shipment_item_sk BIGINT PK, shipment_sk BIGINT FK, product_sk BIGINT FK, price DECIMAL, was_kept BOOLEAN, return_reason TEXT
  • dim_client references fact_shipments
  • dim_stylist references fact_shipments
  • dim_date references fact_shipments
  • fact_shipments references fact_shipment_items
  • dim_product references fact_shipment_items

The analysis pattern

Net revenue and keep rate by brand
SELECT
    p.brand,
    SUM(si.price) AS gross_revenue,
    SUM(CASE WHEN si.was_kept THEN si.price ELSE 0 END) AS net_revenue,
    AVG(CASE WHEN si.was_kept THEN 1.0 ELSE 0.0 END) AS keep_rate
FROM fact_shipment_items si
JOIN fact_shipments s ON s.shipment_sk = si.shipment_sk
JOIN dim_product p ON p.product_sk = si.product_sk
JOIN dim_date d ON d.date_key = s.shipped_date_key
WHERE d.fiscal_month = '2025-03'
GROUP BY p.brand

Trade-offs and alternatives

Split shipment and item facts
Clean grain, easy brand rollups, additive item attributes. Requires a migration to unpivot the wide table into item rows. Writes touch two facts per shipment.
Wide shipment fact with JSON items
One row per shipment with items as a JSON array. Single insert per shipment. Brand questions require JSON parsing at query time and lose columnar pruning.

Common follow-up questions