Star schema

A star schema arranges data for analysis: a fact table of events, like sales, in the center, joined by keys to dimension tables (customer, product, date) that describe them.

Select any words here to ask your Maestro about them.

dim_customer is rebuilt every night from the CRM. A customer ordered in July while living in the Northeast, then moved West in August. In Q3 revenue by region, where do their July orders count?

An online coffee roaster, July to September. The fact table joins to dim_customer for the region. Example data.

What it's for: getting business questions answered with simple queries. Instead of joining ten tables from the source system, you join the fact table to the dimensions you need. It's the shape tools like Power BI recommend.

Facts and dimensions: the fact table holds events with numbers you add up (revenue, quantities) and a key for each dimension. Dimensions describe who, what and when. The first decision is the grain: what one row of the fact is.

You build it in your database with SQL, often with dbt. Learn it in depth in the course Data Modeling for AI Agents.

How it's used

The star

The roaster's sales, as a star

marked: fct_order_lines: one row per product in each orderThe center: what happened, with the numbers you add up.
→ dim_customer by customer_key: name, email, regionWhat you group and filter by.
→ dim_product by product_key: coffee, roast, bag sizeEach dimension describes one side of the fact.
→ dim_date by date_key: day, month, quarterOne row per day, so no query has to work out dates again.

Facts are what happened, and you add them up; dimensions are what you group and filter by. Every query joins the center to the points it needs.

A query on the star

revenue_by_region.sql

marked: select c.region, sum(f.net_revenue) as revenueThe number comes from the fact; the group, from the dimension.
from fct_order_lines f
join dim_customer c on c.customer_key = f.customer_keyOne join on a key, every time.
group by c.region

The same shape works for revenue by product or by month: you swap the dimension, not the fact table.

When to look closer

Shipping on every line

The quarter's shipping

marked: From the orders table: $3,578.20

Shipping is stored once per order. What happens if you add it up from a table with one row per product?

Ask your Maestro about Star schema

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "Star schema" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which question at work takes five joins today?

Asking about: Answers from the page "What is exploratory data analysis, and what do I do first with a new table?"

Only this browser keeps what you ask here. How it works

Learn more

Star schema (Wikipedia)

Grain (of a table)
What one row of a table stands for: a person, a transaction, a planet, a person per language. Counting rows counts what you want only if you know the grain.
Database
A database stores data in connected tables, with rules for each column, so many people and programs can read and change it without stepping on each other.
SQL
SQL is the language for asking a database for data: which columns, from which table, which rows and how to summarize them. You get a table back.
Power BI
Power BI is Microsoft's tool for connecting data from several sources and building interactive reports and dashboards your team opens online.

The whole glossary