How to model a data warehouse with a star schema

João Barros 20 de June de 2023 2 min read

A well-modelled data warehouse answers business questions in seconds. The star schema — a fact table surrounded by dimensions — is the most widely used pattern to achieve it.

Prerequisites

Basic knowledge of SQL and relational databases.
A database engine (SQL Server, PostgreSQL or equivalent).
A transactional data source to model (for example, sales).

Step 1: Identify the business process

Pick the process to analyse — for example, sales. Each row in the fact table will be a sales transaction.

How to model a data warehouse with a star schema

Step 2: Define the fact table

The fact table stores the metrics (measures) and the keys to the dimensions:

CREATE TABLE fact_vendas (
    data_key    INT NOT NULL,
    produto_key INT NOT NULL,
    cliente_key INT NOT NULL,
    quantidade  INT,
    valor       DECIMAL(12,2)
);

Step 3: Create the dimensions

Dimensions describe the context (who, what, when):

CREATE TABLE dim_produto (
    produto_key INT PRIMARY KEY,
    nome        VARCHAR(120),
    categoria   VARCHAR(60)
);

Repeat for dim_cliente and dim_data. Use integer surrogate keys (ending in _key) instead of business keys.

Step 4: Connect facts and dimensions

Each key in the fact table points to the primary key of the matching dimension. This simple link is what gives the star shape.

Verify the result

Run a test query joining the facts to the dimensions:

SELECT p.categoria, SUM(f.valor) AS total
FROM fact_vendas f
JOIN dim_produto p ON p.produto_key = f.produto_key
GROUP BY p.categoria;

Conclusion

With one fact table and a few dimensions you have the basis of any fast report. Which business process in your organisation would benefit most from a star model?