Data modelling · Medium
What are fact and dimension tables in a star schema?
Short answer
A fact table records measurable events at a declared grain, such as one row per order line with quantity and amount, plus foreign keys. Dimension tables describe the context of those events: customer, product, date and channel attributes used for filtering and grouping. Arranging one fact around several dimensions forms a star, which keeps BI queries simple and fast.
Detailed answer
Mapping our shop data to a star schema:
- Fact:
fact_order_line– grain: one row per product per order. Measures: quantity, revenue, cost. Keys: order, customer, product, date. - Dimensions:
dim_customer(city, signup date, premium flag, referral source),dim_product(name, category),dim_date(day, month, quarter, year, weekday, festival-season flag).
-- A "fact view" at order-line grain, with margin calculated once
SELECT oi.order_item_id,
o.order_date,
o.customer_id,
oi.product_id,
oi.quantity,
oi.quantity * oi.unit_price AS revenue,
oi.quantity * (oi.unit_price - p.cost) AS gross_margin
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status NOT IN ('cancelled', 'returned')
ORDER BY o.order_date
LIMIT 10;
Key ideas to mention:
- Declare the grain first. Mixing grains in one fact table causes double counting.
- Additive vs non-additive measures: revenue can be summed across any dimension; ratios such as conversion rate cannot — store the numerator and denominator instead.
- Slowly changing dimensions: if a customer moves from Pune to Bengaluru, do historical orders report under the old city or the new one? Type 1 overwrites; Type 2 keeps history with validity dates.
- A snowflake schema normalises dimensions further (product → category table); it saves space but adds joins.