Data Modeling

Data modeling is deciding how data is split into tables, what each row means, and how tables relate, so that the data is correct to load and easy to query. A good model makes the right query obvious and the wrong one hard. In analytics the model matters more than the engine: a fast warehouse cannot fix a fact table with an undefined grain.

Three levels of a model

Level Answers Example
Conceptual What things does the business care about? Customer places Order, Order contains Product
Logical Which entities, attributes and keys, independent of a database? order(order_id PK, customer_id FK, ordered_at)
Physical How is it stored in this engine? Types, partitioning, clustering keys, Parquet/Iceberg layout

Two goals that pull in different directions

  • Normalization (3NF) removes redundancy so each fact is stored once. Ideal for transactional systems where many small writes must stay consistent.
  • Denormalization repeats data so reads need fewer joins. Ideal for analytics where queries scan and aggregate.

Most analytical platforms do both: a normalized or raw integration layer, then denormalized models for consumers. See normalization vs denormalization.

What this section covers

  • Star Schema: Kimball’s fact and dimension tables, grain, surrogate keys and slowly changing dimensions (SCD Type 2). The default for BI.
  • Snowflake Schema: a star with normalized dimensions. Less redundancy, more joins.
  • Data Vault: hubs, links and satellites for auditable, change-friendly integration of many sources.

Choosing an approach

Situation Good fit
Dashboards and self-service BI on a handful of sources Star schema
Large shared hierarchies, normalized sources, semantic layer hides joins Snowflake schema where it helps
Many volatile sources, strict audit requirements, big team Data Vault for integration, stars on top
One dashboard that must be very fast One big table built from a star

A common 2026 layering, often called medallion (bronze, silver, gold) on lakehouses, maps onto these: raw data lands as-is, a cleaned and integrated layer (sometimes a vault) sits in the middle, and star schemas serve the gold layer.

Learning Resources

Books

Videos