Data modeling interview questions and how to answer them

One sentence describing exactly what one row means. Say it before anything else in a design exercise:

• "One row per line item on an order." (transaction fact)
• "One row per account per day." (periodic snapshot)
• "One row per order, updated as it moves through fulfilment." (accumulating snapshot)

Mixed grain is the most common modeling bug: order-level shipping cost stored on a line-item fact, then summed — and shipping revenue triples.

Facts are numeric and additive things you measure: quantity, net_amount, duration_seconds. Dimensions are the context you slice by: customer, product, store, date, channel.

The trap question: is unit_price a fact? It's numeric but non-additive — summing prices is meaningless. Store it, but note that only quantity * unit_price is additive. Ratios and percentages should be stored as their numerator and denominator, and divided at query time.

dim_customer
customer_key | customer_id | region | valid_from | valid_to   | is_current
1001         | 42          | LATAM  | 2023-01-01 | 2025-01-15 | false
1002         | 42          | EMEA   | 2025-01-15 | 9999-12-31 | true

• Type 2 history needs multiple rows per natural key — only a surrogate key stays unique.
• Source systems change or merge; a surrogate key insulates the warehouse.
• Compact integer keys join faster than composite natural keys.
• Handles unknown/late-arriving members with a -1 "Unknown" row.

OLTP normalizes to keep writes correct and small. Analytics denormalizes to keep reads simple and fast. In a warehouse, default to a star schema and only normalize a dimension when it's shared, enormous, or governed centrally. Say that out loud — interviewers are testing whether you have a default plus an exception rule, not whether you memorized normal forms.

This is the SCD question in disguise. Answer: "Depends on whether historical reports should change. If revenue by region must stay accurate as-of the transaction date, Type 2 with valid_from/valid_to and a point-in-time join. If it's a correction of bad data, Type 1 overwrite." Naming the decision criterion is the answer; naming only the type is not.

-- Not idempotent: a retry duplicates every row
insert into fact_orders select * from staging_orders;

-- Idempotent A: merge on the business key
merge into fact_orders t
using staging_orders s on t.order_line_id = s.order_line_id
when matched then update set net_amount = s.net_amount, status = s.status
when not matched then insert values (s.*);

-- Idempotent B: replace the whole partition being reprocessed
delete from fact_orders where order_date = '2026-07-30';
insert into fact_orders
select * from staging_orders where order_date = '2026-07-30';

Walk it in this order — out loud:

1. Questions first. "Revenue per city per day, driver utilization, surge effectiveness, cancellation rate."
2. Grain. "One row per completed trip" for fact_trips.
3. Facts. fare_amount, surge_multiplier, distance_km, duration_seconds, driver_payout, platform_fee.
4. Dimensions. dim_rider, dim_driver, dim_city, dim_vehicle, dim_date, dim_payment_method.
5. SCDs. Driver rating tier and vehicle class change over time → Type 2.
6. Second fact table. fact_driver_shift_daily, a periodic snapshot for utilization — different grain, deliberately separate.
7. Edge cases. Cancellations (separate fact or status flag?), late-arriving tips, timezone per city, refunds as negative rows.
8. Loading. Incremental by trip_completed_at with a MERGE, plus a lookback window for late events.

Want to see how a real company solved this? Our architecture deep dives walk through Uber's actual pipeline interactively.

• Factless fact table — what is it and when? (attendance, promotions eligibility)
• Degenerate dimension — order number stored on the fact with no dimension table.
• Junk dimension — collapsing several low-cardinality flags into one dimension.
• Bridge table — handling many-to-many (a trip with multiple promo codes).
• Late-arriving dimension — insert a placeholder row, backfill when it arrives.
• Conformed dimension — one dim_date, dim_customer shared by all facts.
• Data vault vs Kimball — when raw auditability beats query ergonomics.