Every Line Counts

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

Problem

Design the star schema that holds every item scanned at the registers of our 500 stores, one row per receipt line, so the merchandising analytics team can slice sales by store, product, date and the promotion applied, rolling up to region, department and fiscal period. A promotion covers many products and a product can sit in several running promotions at once, each pairing with its own discount, but the register applies at most one promotion to any given line.

Every beep at the register. Coupons, returns, all of it.

Worked solution and explanation

Why this problem exists in real interviews

Retail is the original Kimball use case, and interviewers use POS problems to probe grain declaration under a many-to-many wrinkle: a promotion can apply to many products and a product can participate in many promotions. The signal is whether the candidate reaches for a bridge table instead of flattening the relationship onto the fact.

Break down the requirements

Step 1: Fix the grain at the sales line

fact_sales is one row per item scanned per transaction. This is the atomic unit of retail analytics and supports every roll-up from basket to department to region.

Step 2: Conform dimensions across the warehouse

dim_store, dim_product, dim_date, and dim_promotion are reusable across inventory, returns, and sales facts. Conformity is what keeps "weekly revenue by department" returning the same answer in every dashboard.

Step 3: Model the promo-product relationship as a bridge

bridge_promo_product carries the eligibility and discount percentage per pair. The fact stores only the applied promotion_sk so you know which promo actually fired.

Step 4: Keep measures atomic and additive

quantity and extended_amount are both fully additive. Non-additive measures like unit price are derived at query time from extended amount divided by quantity.

The solution

Below is one defensible model. The bridge table is the anchor for the promo-product many-to-many; everything else is the canonical Kimball star.

`bridge_promo_product` resolves the many-to-many; `fact_sales` keeps only the promotion that actually fired
  • dim_store: store_sk BIGINT PK, store_code TEXT, region TEXT, district TEXT, store_format TEXT, open_date DATE
  • dim_product: product_sk BIGINT PK, sku TEXT, department TEXT, category TEXT, subcategory TEXT, brand TEXT
  • dim_date: date_sk INT PK, calendar_date DATE, fiscal_period TEXT
  • dim_promotion: promotion_sk BIGINT PK, promo_code TEXT, promo_type TEXT, start_date DATE, end_date DATE
  • bridge_promo_product: promotion_sk BIGINT FK, product_sk BIGINT FK, discount_pct DECIMAL
  • fact_sales: sales_line_id BIGINT PK, date_sk INT FK, store_sk BIGINT FK, product_sk BIGINT FK, promotion_sk BIGINT FK, quantity INT, extended_amount DECIMAL
  • dim_date references fact_sales
  • dim_store references fact_sales
  • dim_product references fact_sales
  • dim_promotion references fact_sales
  • dim_promotion references bridge_promo_product
  • dim_product references bridge_promo_product

The analysis pattern

Promotion lift by department
SELECT
    p.department,
    pr.promo_code,
    SUM(f.extended_amount) AS promo_revenue,
    SUM(f.quantity) AS units_sold,
    COUNT(DISTINCT f.store_sk) AS stores_participating
FROM fact_sales f
JOIN dim_product p ON p.product_sk = f.product_sk
JOIN dim_promotion pr ON pr.promotion_sk = f.promotion_sk
JOIN dim_date d ON d.date_sk = f.date_sk
WHERE d.calendar_date BETWEEN pr.start_date AND pr.end_date
GROUP BY p.department, pr.promo_code
ORDER BY promo_revenue DESC

Trade-offs and alternatives

Star with promo bridge
Clean many-to-many for eligibility, single-promo attribution on the fact, conformed dimensions across retail. Cost: analysts need to understand when to use the bridge versus the fact FK.
Denormalized sales with promo arrays
Single-table reads, no bridge joins. Cost: array columns kill most BI tools, stacked promos double-count revenue, and eligibility queries become full scans.

Common follow-up questions