Data modeling is the practice of organizing data for storage and access. The fundamentals — fact tables, dimensions, normalization, denormalization — have been stable for decades. The implementations evolve; the concepts don't.
This page covers the core concepts and the choices for different workloads.
The fundamental distinction:
The order is created once; read once or twice; updated occasionally. Data lives in a relational database (PostgreSQL, MySQL).
The data is read many times in different aggregations. Data lives in a warehouse (Snowflake, BigQuery, Redshift).
The two have different shapes. OLTP normalizes; OLAP denormalizes.
Splitting data across tables to eliminate redundancy.
Database normalization rules:
Most OLTP databases are in 3NF (or close to it).
The classic OLAP pattern:
Numerical measurements. Each row is an event or transaction.
fct_orders
├── order_id (key)
├── customer_id (FK to dim_customer)
├── product_id (FK to dim_product)
├── date_id (FK to dim_date)
├── amount (measure)
└── quantity (measure)
Columns are foreign keys to dimensions plus measures (the numbers).
Descriptive context.
dim_customer
├── customer_id (key)
├── name
├── email
├── signup_date
├── tier
└── city
dim_product
├── product_id (key)
├── name
├── category
├── brand
└── price
The "star" comes from the diagram: fact in center, dimensions around it.
Like star, but dimensions are normalized further.
dim_customer → dim_city → dim_country
Pros: less storage; cleaner. Cons: more joins.
For modern warehouses (cheap storage, fast joins), star usually wins. Snowflake schemas show up in older OLAP designs.
Dimensions change over time. A customer's tier upgrades. Their address changes.
How to handle the change:
Just update. No history.
Old row marked end-of-life; new row created with new values. History preserved.
customer_id | name | tier | valid_from | valid_to | is_current
1 | Alice | bronze | 2020-01-01 | 2022-06-01 | false
1 | Alice | gold | 2022-06-01 | (null) | true
For analytics that care about historical state, Type 2 is essential. "What tier was Alice when she made this purchase?"
Limited history (only the previous state).
For most warehouses, Type 2 is the default for dimensions where history matters.
A choice for fact tables:
date | revenue | costs | profit | margin
Easy queries; many columns; harder to add new measurements.
date | metric | value
Easy to extend; harder for some queries (need pivots).
For operational metrics, tall is flexible. For business KPIs with stable structure, wide is clearer.
Cloud warehouses change some traditional advice:
Denormalize aggressively. Don't pre-aggregate to save space; let warehouses store the detail.
Normalization concerns matter less. Star schemas in modern warehouses join quickly.
JSON columns in PostgreSQL, BigQuery, Snowflake. Sometimes the right answer; often a sign of underbaked modeling.
The staging-intermediate-marts pattern. Each layer applies more business logic; all transformations are SQL.
For mature business domains where you understand the entities. Designing OLTP schemas for a known domain.
For analytics where requirements emerge. Start with raw data; build dimensions and facts as needed.
For warehouses, iterative usually wins. The transformation layer in dbt makes restructuring relatively cheap.