Analytical Data Modeling

Choosing the shape of the tables people query, so that the questions the business asks are easy to write, correct by construction and affordable to run.

Normalised, transaction-oriented schemas and fact/dimension, query-oriented schemas solve different problems. Neither is a degraded version of the other.

Tables of measurements of a business process, at a declared grain, with keys to context — and the measure types that decide whether SUM() means anything.

The single most important question in analytical modelling. Answer it in one sentence per table, or every aggregate downstream is a guess.

The descriptive context you filter and group by — customer, product, date, region — and the reason a calendar deserves a table of its own.

A warehouse-generated key with no business meaning, because with history the natural key stops being unique and because source ids change underneath you.

One fact table in the middle, dimensions one join away on every side. The shape that makes queries short, joins predictable and grain visible.

Dimensions normalised into their own hierarchies. Fewer repeated values, one place to correct a taxonomy, more joins in every query — and an honest comparison of when that trade pays.

A customer moves from Poland to Germany. Do last quarter's Polish revenue figures change? That question, answered per attribute, is the whole topic.

valid_from, valid_to, is_current — the columns that preserve history, the join predicate every fact must use, and the interval bugs that produce numbers reconciling against nothing.

Capture the state of every entity at the end of every period. `account_balance_daily` answers "what was it on the 14th" with a lookup instead of a fold over all history.

Events record what changed; snapshots record what was true at time T. Different storage curves, different query complexity, and different questions made easy.