The short answer
Lead with processes and grains, then facts, then dimensions, then SCD.
Core processes
- 1Orders / order lines (sales)
- 2Inventory position (optional snapshot)
- 3Site engagement (clicks): often separate, rolled up
dim_date
dim_customer (SCD2 on city, segment)
dim_product (SCD2 on category)
dim_store / dim_channel
fact_order_item
grain: 1 row per order line
keys: date_key, customer_sk, product_sk, store_sk
degenerate: order_id, line_number
measures: qty, amount, discount, tax
fact_inventory_snapshot
grain: product × warehouse × day
measure: on_hand
fact_click_daily (rolled up)
grain: product × country × day
measures: clicks, approx unique usersSCD judgment
Customer city and product category → Type 2 if historical reporting matters. Email typo → Type 1.
Refunds
Prefer signed transactional rows (negative amounts) or a separate refund fact so SUM(amount) stays meaningful.
Clicks caveat
Raw clickstream is much higher volume and different grain. Do not casually join billion-row clicks into revenue queries; roll up or isolate.
Interview closing
> "Say the grain first. Everything else in a hangs from that sentence."