Contents
This is sample teaching content. Publication dates and transaction records are illustrative.
A star schema separates measurable events from descriptions used to group them. In our sample shop, a sale has a date, product, quantity, and amount. Before choosing columns, write the grain: one fact row represents one order line, identified by order_id and line_number.
Keep the grain explicit
A product dimension contains one row per product version; a date dimension contains one row per calendar date. A fact stores keys to those rows. If a product category changes and historical reports must retain the old category, create a new product dimension version and choose the key valid at sale time. Overwriting a category instead changes how old sales are grouped.
| Table | Unique key | Meaning |
|---|---|---|
| fact_sales | order_id, line_number | One sold order line |
| dim_product | product_key | One descriptive product version |
| dim_date | date_key | One calendar date |
Join without multiplying money
Assume PostgreSQL 18, dimension keys enforced as unique, and fact keys referencing existing dimensions:
SELECT d.calendar_date, p.category,
SUM(f.net_amount) AS net_revenue
FROM fact_sales AS f
JOIN dim_date AS d ON d.date_key = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
GROUP BY d.calendar_date, p.category
ORDER BY d.calendar_date, p.category;
If dim_product contains two rows with the same product_key, the join doubles matching sales. Check key uniqueness and foreign-key coverage before trusting a total. An inner join also drops unmatched keys, so compare fact amounts before and after enrichment. A documented unknown dimension member can retain incomplete facts for investigation.
Measures have limits
Our net_amount is additive across order lines in the same currency. A unit price is not: summing prices across lines answers no useful revenue question. Summing a daily account balance across dates repeats stock rather than measuring flow.
Review a concrete transaction containing two lines. Confirm it creates two facts, verify each dimension join matches once, and compare the aggregate with the input amount. A schema diagram explains relationships; these checks prove the grain survives loading.