Star Schema

The fact table holds the events being measured — a sale, a click, a shipment — with numeric measures and foreign keys to dimensions. Dimensions hold the descriptive attributes: product, customer, store, date.

Deliberately denormalised, which is the whole point. Normalising a dimension into a hierarchy of tables (a snowflake schema) saves storage that costs nothing and adds joins to every analytical query. Dimensions are small; facts are enormous. Optimise for the fact table's access path.

Why it has survived four decades of alternatives: it is comprehensible to business users, it maps directly onto how BI tools generate SQL, and query performance is predictable — one large table joined to several small ones is a shape every engine optimises well.

The design decisions that matter are grain and conformed dimensions. Grain — what one fact row represents — must be stated explicitly and never varied within a table; ambiguous grain is the source of most double-counting bugs. Conformed dimensions are shared across fact tables so that metrics from different processes can be compared on the same customer or the same date, which is what makes a warehouse a warehouse rather than a collection of marts.