---
title: "What is a star schema? Facts and dimensions | Oplema"
description: "Learn how a star schema splits fact and dimension tables, when a flat table will do and how date dimensions, snowflakes and SCD Type 1 and 2 work."
url: "https://oplema.com/learn/business-intelligence/star-schemas"
updated: "2026-11-04"
---

Lesson 8 of 15 · Concept

# What is a star schema? Facts and dimensions

A star schema keeps events in one fact table and descriptions in dimension tables around it, so each fact is stored once and each description is too. You add up facts and group by dimensions. It earns its keep once several reports share the same definitions.

A small sales star
| Table | Kind | One row is | Holds |
| --- | --- | --- | --- |
| Sales | Fact | One sale | `date_key`, `product_key`, `store_key`, `amount` |
| Date | Dimension | One day | Month, quarter, year, weekday |
| Product | Dimension | One product | Name, category |
| Store | Dimension | One store | City, region |

The date dimension is the one nearly every star has. A row per calendar day makes "by quarter" one grouping everywhere, and it lets a report show a day with no sales, which the fact table can't.

## One fact table, one dimension

In this course's fictional example, [Vandelay Coffee Supply](https://oplema.com/learn/business-intelligence/what-is-bi) has a repeatable flow for invoices. The flow still has to say which region each invoice belongs to, because customer Dunder Mifflin moved from South to North on August 1.

Vandelay's star, with net\_eur as net value in euros
| Table | Kind | One row is | Columns |
| --- | --- | --- | --- |
| Invoices | Fact | A booked invoice | `invoice_id`, `customer_id`, `invoice_on`, `net_eur` |
| Customers | Dimension | A customer | `customer_id`, `customer`, `region`, `rebate_pct` |

Keep facts and dimensions apart and a region changes in one place. The price is that you must decide which region a fact belongs to when the region moved after the invoice.

## Current region or region at the time?

Dunder Mifflin's July invoice is €640, the only one dated before the move. The current figures are North €5,578 and South €5,900.

If that invoice counts where Dunder Mifflin was at the time, what is South's margin over North?

€1,602. South becomes €6,540 and North €4,938, because €640 moves across and counts twice in the gap. The current mapping gives €322.

Now choose one for the question at hand, which is where Max Thrust, the founder, should place an account manager. Write one sentence defending it before reading on.

Compare with a model answer

Current region. Max is placing a person to look after accounts from now on, and Dunder Mifflin is North's account now. As-was would suit a commission calculation, where the question is who earned it in July.

Neither mapping is wrong. Each answers a different question, and the choice belongs in the metric contract. Don Tremendous's report uses his old map, so his South still holds Dunder Mifflin, and that's part of why his numbers look as they do.

## Slowly changing dimensions: Type 1 and Type 2

A dimension that changes now and then is called a slowly changing dimension, or SCD. Two types cover most cases.

Two ways to record a move
| Type | What happens | Reports show |
| --- | --- | --- |
| SCD Type 1 | Overwrite the row | The current value, for all history |
| SCD Type 2 | Add a new row with dates | The value at the time |

Vandelay's register is Type 2 in shape, with a `valid_from` date on each Dunder Mifflin row. A plain lookup on customer ID then matches both rows, which is the [join fan-out](https://oplema.com/learn/business-intelligence/joins-integration) Sydney Swell hit. With Type 2, join on the key and the date, or pick the current row first.

## When a flat table is enough, and what a snowflake is

Apply the flat-table test. One report, one owner and one grain can sit happily in one wide table. Move to a star when several reports need the same customers and dates, or when history matters.

A snowflake splits a dimension further, with product pointing to category and category to department. It saves little in most BI work and adds joins. Keep dimensions in one table unless a shared table has its own owner. Power BI's modeling guidance recommends the star shape, and treats the one side of a relationship as the dimension and the many side as the fact. The [data preparation lessons on joins](https://oplema.com/learn/data-preparation/joins) show how the tables connect.

To join a fact table to a dimension yourself, try [Strata](https://oplema.com/strata), Oplema's browser app for data prep, with no account needed.
