Design your first star schema

Nobody told me which columns go in the fact table and which belong in a dimension.

BeginnerRead 6 minDoing: about an hour6 steps

▶ Start

What you'll have at the end

A fact table and its dimension tables that correctly answer one real business question

Before you start

You need

A handful of raw transaction rows you want to turn into a report, such as an export or a database table, and one specific business question you need those rows to answer.

Not covered

Handling a dimension whose attributes change over time, such as a customer's address, which needs its own procedure once you're ready for it.

Leans on

Frame the report before touching a table

Name the business process and the exact question
Declare the grain before anything else

Shape the fact and dimension tables

Turn each row's context into dimension tables
Pick the measures and label their additivity

Load it and prove the answer

Load the fact table and check the total
Query the star to answer your question

Where to go next

OLTP vs OLAP: decide when a report needs its own warehouse

Read this first if you're not sure a separate warehouse table is even worth building yet.

Star schema or one big table: which does your new dashboard need?

Come back here once your dashboard's joins get heavy and you're weighing a flatter, single wide table instead.

Checked 25 Aug 2026

Part of the Data Warehousing cookbook