Skip to content

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.