Skip to main content

Facts, Dimensions, and Grain

The grain is the promise made by each row. A transaction-grain fact table can represent one event, while a periodic snapshot might represent one account per day. Dimensions provide descriptive context. A natural key comes from the source domain; a warehouse-managed surrogate key identifies a dimension version and insulates facts from mutable source identifiers.

Prerequisites​

Complete Dimensional Modeling and Joins Without Duplicate Surprises.

Learning goals​

  • Declare the grain before selecting facts or dimensions.
  • Distinguish natural, durable, and surrogate keys.
  • Classify facts as additive, semi-additive, or non-additive.
  • Test that a table actually satisfies its stated grain.

Worked example​

At one row per event, uniqueness of event_id is part of the model contract. Row count and distinct key count must agree; the amount can be summed across these events because the fixture defines signed event amounts in one currency.

SELECT
  COUNT(*) AS rows_at_declared_grain,
  COUNT(DISTINCT event_id) AS distinct_events,
  SUM(amount_cents)::INTEGER AS amount_cents
FROM fct_event;

The result is 6, 6, 4550. This is evidence for the fixture, not proof that upstream delivery will always be unique. Enforce or test the key at publication.

Counts and transaction amounts are usually additive over compatible dimensions. An end-of-day balance is semi-additive: it can be summed across accounts at one instant, but summing daily balances across time is usually meaningless. Ratios such as conversion rate are non-additive; aggregate their numerator and denominator, then divide.

Facts may use several dates—event time, arrival time, settlement date. Name each role. If a dimension lookup fails, choose an explicit unknown-member policy or quarantine the fact; do not silently drop it in an inner join.

Limitations and common mistakes​

  • A surrogate key does not guarantee business uniqueness.
  • A composite natural key may change; document durability assumptions.
  • Joining two fact tables directly can multiply rows unless both are first aggregated to a compatible grain.
  • A null foreign key can mean unknown, not applicable, or not yet resolved; these states are not interchangeable.
  • Summing averages, percentages, inventory levels, or distinct counts usually gives invalid results.

Practice​

Classify order-line quantity, daily account balance, unit price, and conversion rate by additivity. Specify the correct aggregation path for each.

Next steps​

Continue with Star Schemas to query a fact table through reusable dimensions.

Primary sources​

Checked 2026-10-15: Kimball Group grain declaration, additive facts, and dimension surrogate keys.