The star schema is how analytics data is shaped: one fact table of measurements surrounded by dimension tables of context. Learn facts vs dimensions, grain, and star vs snowflake.
Dimensional modeling (Kimball) organizes analytical data into two kinds of table. A fact table holds the numeric measurements of a business process — the things you sum and average — plus foreign keys. Dimension tables hold the descriptive context you filter and group by: who, what, where, when. Almost every analytics question is “a measure, sliced by some dimensions.”
Business process: "a product was sold."
FACT (what you measure — numeric, one row per event):
fact_sales(date_key, product_key, store_key, customer_key,
quantity, amount) <- the measures: quantity, amount
DIMENSIONS (the context — text you filter/group by):
dim_date(date_key, date, month, quarter, year, weekday)
dim_product(product_key, name, category, brand)
dim_store(store_key, store, city, region)
dim_customer(customer_key, name, segment)
Every question = a MEASURE from the fact, sliced by DIMENSION attributes.Arrange one fact table in the center with dimensions radiating out and you get a star. Its value is both performance (one join hop from fact to any dimension) and usability — a business user can read it without a data dictionary. This shape is why “revenue by category by region by quarter” is a single, obvious query.
-- "revenue by category by quarter" — one fact, join each needed dimension
SELECT d.year, d.quarter, p.category, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.year, d.quarter, p.category
ORDER BY d.year, d.quarter, revenue DESC;
-- Fact in the middle, dimensions one hop away = the "star".
-- Wide, denormalized dimensions mean few joins and fast, readable queries.The grain is what a single fact row represents — “one row per order line,” “one row per daily account balance.” Declaring the grain before anything else is the most important step in dimensional modeling: it fixes which dimensions and measures are valid and prevents the classic bug of mixing granularities (and double-counting) in one table.
Declare the grain in ONE sentence before you add a single column:
"One row per product per order." (order-line grain)
"One row per customer per day." (daily snapshot grain)
"One row per website session." (event grain)
Rules that follow from the grain:
- every measure must be true AT that grain (order-line qty: yes;
order-total on every line: no -> double counts when summed)
- every dimension must apply AT that grain
Mixed grains in one fact table = silent double counting. Grain first.A star keeps dimensions flat and denormalized; a snowflake normalizes a dimension into sub-tables (product → category → department). Snowflaking saves a little storage and can ease maintenance, but adds joins and hurts the readability that makes star schemas great. The modern default is star — storage is cheap, and columnar compression makes the redundancy nearly free.
STAR (denormalized dimension — preferred):
dim_product(product_key, name, category, department) <- flat, one table
SNOWFLAKE (normalized dimension — more joins):
dim_product(product_key, name, category_key)
-> dim_category(category_key, category, department_key)
-> dim_department(department_key, department)
Trade-off: snowflake saves storage + reduces update spots, but adds joins
and hurts readability. With cheap storage + columnar compression, STAR wins
for most warehouses. Snowflake only where a dimension is huge or shared.