Home › Key concepts › Dimensional modelling (star schema)

Key concept · Walk (intermediate) · the same on every platform

Dimensional modelling (star schema)

Facts in the middle, descriptions around them: tables built for questions.

What it is

A fact table records events with numbers, such as each sale with its amount. Dimension tables describe the who, what, where and when: customer, product, store, date. The fact table holds keys to each dimension, forming a star. It is the classic shape for analytics, from Ralph Kimball's work.

fact_salesdate, product, store,customer, amountdim_datedim_productdim_storedim_customer
Star schema: numbers in the fact table, descriptions in the dimensions around it.

Why it matters

Business questions become simple joins, and every report agrees on what a 'customer' or a 'product' is.

An example

fact_sales(date_key, product_key, store_key, amount) joined to dim_product gives sales by category in one query.

The common mistake

One giant flat table per report, so a product's category is spelled three different ways.

Where you meet it on each platform

The idea stays the same; only the product name changes.

JobAWSAzureGoogle CloudDatabricksOpen source
SQL modellingturn raw tables into clean, tested, documented onesdbt on Redshift or Athenadbt on FabricDataform or dbt on BigQueryLakeflow Declarative Pipelines or dbtdbt with DuckDB
Warehousetables built for analysis, fast and governedAmazon RedshiftFabric WarehouseBigQueryDatabricks SQL warehouseDuckDB or Postgres

Related ideas

Where it shows up

Part of the Walk level on these paths: Data Engineering (shaping data).