A star schema: fact tables at the grain of a business event, surrounded by dimensions that describe the who, what, where and when.
Facts, each at one grain, stated explicitly:
fact_order_items: one row per line item. Keys to order, customer, product, date, promotion. Measures: quantity, unit price, discount, tax. Everything additive.
fact_orders: one row per order, for order-level facts like shipping cost and payment method; or derive it from items and keep only one.
fact_returns, fact_sessions, fact_inventory_snapshot (a periodic snapshot, daily stock per SKU per warehouse, since stock is a level, not an event).
Dimensions:
dim_customer as SCD Type 2, because segment and address change and historical orders must join to the attributes current at the time.
dim_product with category hierarchy; SCD2 if price history matters, otherwise price on the fact.
dim_date, one row per day with fiscal calendar, holiday flags, week start.
dim_promotion, dim_geography, dim_channel.
CREATE TABLE fact_order_items (
order_item_key BIGINT PRIMARY KEY,
order_id BIGINT,
customer_key BIGINT REFERENCES dim_customer, -- version key, not natural id
product_key BIGINT REFERENCES dim_product,
order_date_key INT REFERENCES dim_date,
quantity INT,
unit_price NUMERIC(12,2),
discount_amount NUMERIC(12,2)
);
The decisions to say out loud: the grain of each fact, which dimensions are SCD2, that facts reference surrogate keys so history is preserved, and that ratios (conversion rate, average order value) are computed from additive measures at query time rather than stored.
What they are checking: grain, SCD2 on the customer, and additive measures.
Common mistake: one wide "orders" table with customer attributes copied onto it, which cannot answer what the customer's segment was last year.