Skip to content
BENIBIX Learn

Star Schema

Data🗄️ Storage & WarehousingDimensional Modeling

One fact table in the middle. Each dimension table joins straight to it.

Think of it like

It is like a wheel. The hub is in the middle, and each spoke goes straight out to the rim.

Example

A shop's star has a sales fact table in the middle. Date, shop, product and customer tables sit around it.

Why it matters

It is simple for people and fast for warehouses. Most BI tools are built to read it.

Definition

A star schema puts one fact table in the middle and dimension tables around it. Each dimension joins straight to the fact table with one key. Dimensions are not split further, so each one is a single wide table. Queries need only one join per dimension. Drawn out, it looks like a star.

Example code

sql
SELECT d.month, p.brand, SUM(f.amount)
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY d.month, p.brand;
Sales by month and brand. One join for each dimension.
A star keeps each dimension in one table. A snowflake splits some of them again.

Common mistake

Joining two fact tables to each other directly. Facts should meet only through shared dimensions.

Key takeaways

  1. 1
    Fact in the middle. One table of events.
  2. 2
    One join each. Dimensions join straight to the fact.
  3. 3
    Flat dimensions. Each dimension is one wide table.

Compare