Data Warehousing
Intermediate 13 min read Data Warehousing

Facts and Measures

The numbers that add up — and the ones that must not

Facts are the events you measure, and measures are their numeric, additive properties. Distinguishing additive, semi-additive, and non-additive measures prevents dashboards that over-count.

factmeasureadditivesemi-additivenon-additivegrain
Prerequisites: Dimensional Modeling
Before we start

What you will learn

  • Name the fact types (transaction, periodic snapshot, accumulation)
  • Classify a measure as additive, semi-additive, or non-additive
  • Choose the fact type that matches the business event
Introduction

What is this?

A fact table records a business event; its measures are the numbers describing that event. Additive measures sum across any dimension; semi-additive measures (balances, headcount) sum across some but not time; non-additive measures (rates, averages) must not be summed at all.

Motivation

Why do you need it?

  • An over-aggregated semi-additive or non-additive measure silently doubles your totals.
  • Fact type choice — transaction vs snapshot vs accumulation — decides whether analysis asks the right questions.
  • Measure type decisions live in the model and travel with every metric.
Think about it

Mental model

Imagine

A stack of daily temperature readings

Think of

Semi-additive? No — non-additive

Averages across days make sense if re-weighted; summing temperatures is nonsense. That's the whole non-additive lesson in one image.

Mechanics

How it works

  • Transaction facts: one row per event (order, call) — additive, best for analyzing flow.
  • Periodic snapshot facts: one row per period per entity (account balance daily) — measures are semi-additive over time.
  • Accumulating snapshot facts: one row per flow (work order) with staged timestamps — supports elapsed-time analysis.
  • Additive: sum across any dimension. Semi-additive: sum across most, not time. Non-additive: only averages/ratios.
  • Measures without numeric meaning (IDs) belong in dimensions or as degenerate keys, not as measures.

Matching fact type to the event you want to answer.

Flow over time?
State at a moment?
Long-running process?
Additivity
Model
Hands-on

Examples

Semi-additive balance in the weekend run

Semi-additive balance in the weekend run
-- One row per account per day. Summing balances across accounts is fine:
SELECT sum(balance) FROM fact_account_balance WHERE day = '2026-03-01';
 
-- Summing across days is WRONG (double-counts multi-day balances):
SELECT sum(balance) FROM fact_account_balance WHERE day BETWEEN '2026-03-01' AND '2026-03-07';

Balances are semi-additive: they sum across accounts but not across time.

Real world

In production

  • An inventory model carries quantity-on-hand as semi-additive: dashboards sum across warehouses but not across snapshots.
  • A call-data warehouse uses an accumulation fact for ticket SLA — elapsed time needs staged timestamps.
  • A rates table stores non-additive measures and the BI layer computes weighted averages instead of sums.
Guidelines

Best practices

  • Store the underlying additive measures; derive rates/ratios later using correct denominators.
  • Document each measure's type and allowed aggregations in the model definition.
  • Use date_dim for time-based facts and time_dim when intraday matters.
Watch out

Common mistakes

  • Summing a semi-additive balance over a date range, doubling the number in every report.
  • Storing percentages as measures and summing them into >100% nonsense.
  • Modeling a periodic snapshot as a transaction fact and losing the 'as-of' semantics.
When things break

Debugging tips

  • A balance that looks 7x too big on a weekly view is the classic semi-additive sum — check the time aggregation.
  • Compare a ratio metric across a small window and a large one; divergence shows the non-additive appendage.
  • Ask 'does summing these across accounts AND time still make sense?' for every new measure.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which can be summed across time?

2

Non-additive measures should typically be derived from additive measures rather than stored and summed.

Go deeper

Official documentation

Kimball fact table techniques
docs
dbt measure model patterns
docs