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.
See it happen: Problem Lab: an order before its customer, and inferred members (simulation), recorded from a real run.
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.
| Job | AWS | Azure | Google Cloud | Databricks | Open source |
|---|---|---|---|---|---|
| SQL modellingturn raw tables into clean, tested, documented ones | dbt on Redshift or Athena | dbt on Fabric | Dataform or dbt on BigQuery | Lakeflow Declarative Pipelines or dbt | dbt with DuckDB |
| Warehousetables built for analysis, fast and governed | Amazon Redshift | Fabric Warehouse | BigQuery | Databricks SQL warehouse | DuckDB or Postgres |
Related ideas
Where it shows up
Part of the Walk level on these paths: Data Engineering (shaping data).