Star schema basics: facts, dimensions and declaring the grain

What it is

Dimensional modelling divides data into measurements of business events and the descriptive context of those events: who, what, where, when, why and how. In a relational database this becomes a star schema: fact tables in the centre, each linked through foreign keys to dimension tables around it. The Power BI guidance (cited) puts it operationally: a fact table contains dimension key columns that relate to dimension tables, and numeric measure columns; the key columns determine the fact table's dimensionality and the key values its granularity; dimension tables are comparatively small, fact tables large and growing. The Kimball Group grain page (cited) makes the first design step explicit: declaring the grain establishes exactly what a single fact table row represents, becomes a binding contract, and must precede the choice of dimensions and facts; atomic grain, the lowest level captured by the process, is recommended because it withstands unpredictable queries. The facts page (cited) classifies measures: additive measures can be summed across any dimension; semi-additive ones, such as balances, across all dimensions except time; non-additive ones, such as ratios, not at all, so their additive components should be stored and the ratio computed after aggregation.

Why it matters

Analysts and BI tools can aggregate a star schema without knowing the source systems: filter on dimension attributes, sum measures, group by any dimension. A normalised operational schema forces multi-way joins whose semantics differ per query, and a table that mixes grains (order lines and order headers) double counts as soon as someone sums it.

How to apply

• Write the grain as a sentence ("one row per order line per day") and reject any measure or dimension that does not fit it; put a different grain into a separate fact table.
• Give each dimension a surrogate integer key, keep the source's natural key as an attribute, and include a date dimension with calendar attributes rather than deriving them in every query.
• Store degenerate dimensions (order number) directly on the fact; reserve dimension tables for entities with descriptive attributes.
• Define conformed dimensions once and reuse them across fact tables so that results from different processes align on the same rows.

Pitfalls

Snowflaking dimensions into normalised sub-tables buys little storage and costs joins. NULL foreign keys break filters; use an explicit "unknown" dimension member. Averages and percentages stored as facts cannot be re-aggregated correctly.

Surrogate keys in rerunnable pipelines

A key drawn from a sequence is assigned in load order, so rerunning or backfilling a dimension out of order changes keys and orphans the facts loaded in between. In pipelines built for idempotent reruns, derive the surrogate key deterministically from the natural key (and valid_from for type 2 rows), usually as a hash; dbt_utils.generate_surrogate_key is the common implementation. Dimension and fact loads then remain independently rerunnable and facts can be keyed at load time without a lookup. Keep integer sequence keys where loads are serialised and join performance on a narrow integer matters, and never mix the two schemes in one dimension.