Dimensional Modeling
Intermediate 15 min read Dimensional Modeling

Fact Tables

The measurements, at one declared grain

Fact tables store measurements of business events at a declared grain. Additive measures sum safely; semi-additive and non-additive measures need special handling.

fact tablesadditivesemi-additivenon-additivegrainmeasures
Prerequisites: Dimensional Modeling Overview
Before we start

What you will learn

  • Classify measures as additive, semi-additive and non-additive
  • Choose grain for common fact patterns (transaction, periodic snapshot, accumulating)
  • Design fact tables that aggregate without double-counting
Introduction

What is this?

A fact table records measures of a business event — one row per order, per click, per daily account snapshot. The grain is its identity, and measures fall into three classes: additive (SUM across any dimension — sales), semi-additive (SUM across some dims but not time — balances), and non-additive (never SUM meaningfully — ratios, unique counts). Getting these classes right is what keeps dashboard math sane.

Motivation

Why do you need it?

  • Almost every wrong BI total traces to a measure summed at the wrong class/grain.
  • Periodic snapshot facts (balances) are the only honest home for inventory/account levels.
  • Fact design decides whether a dashboard can even express its question.
  • Atomic grains make every later aggregation derivable — cheap optionality.
Think about it

Mental model

Imagine

A gardener's yield journal

Think of

A fact table

Each entry records one harvest event with date, crop and weight (one row per event). Adding August weights means something to a cook (additive), but a 'current water level in the rain barrel' is a snapshot — summing those would flood the metaphor (semi-additive).

Mechanics

How it works

  • Transaction facts: one row per event (order, click) — all measures additive.
  • Periodic snapshot facts: one row per entity per period (account, day) — balances are semi-additive.
  • Accumulating facts: one row per process with lifecycle dates (order-to-cash) — timing measures non-additive.
  • Factless facts: rows for events with no measure (attendance, coverage), counting rows instead.
  • FK + measure + degenerate key columns; tests assert grain uniqueness.

Choosing the fact pattern by event type.

Event stream
State over time
Process lifecycle
Coverage
Dashboards
Hands-on

Examples

Adding balances vs flows

Adding balances vs flows
-- Transaction: safe to SUM across everything
SELECT d.month, SUM(f.sales_amount) FROM fact_sales f JOIN dim_date d ON f.date_key=d.date_key GROUP BY d.month;
 
-- Periodic snapshot: SUM over time is wrong
SELECT d.month, AVG(f.balance) AS avg_balance FROM fact_account_daily f JOIN dim_date d ON f.date_key=d.date_key GROUP BY d.month;
-- (use AVG/LAST, not SUM, for semi-additive balances)

Sales flow sums across months; daily balances should be averaged or last-in-pop taken, never summed.

Real world

In production

  • An e-commerce transactional fact (one row per order line) powers sums of GMV over any dimension.
  • A bank's daily account-balance snapshot supports 'average balance by branch this quarter' — computed as AVG, not SUM.
  • An order-to-cash accumulating fact tracks each order's lifecycle dates so aging reports never double-count.
Guidelines

Best practices

  • Classify every measure explicitly in the model documentation.
  • Keep additive measures in transaction facts; push balances into periodic snapshots.
  • Degenerate facts (order numbers) stay in the fact; attributes move to dimensions.
  • Add grain tests to catch accidental row doubling.
Watch out

Common mistakes

  • SUM-ing an account balance across time — reporting 'oh, balances of 40M' from a million-week average.
  • Putting customer 'current region' into the fact, corrupting history.
  • Storing a ratio in the fact and summing it later.
  • Duplicate keys from a source re-send silently multiplying measures.
Root cause analysis

Common issues

Error
Inventory 'total' is astronomically high
Root cause

Periodic snapshot summed across all days

Fix

Use AVG or LAST_VALUE over time; keep endpoint snapshots for 'current'.

Error
Grain test fails after a source re-send
Root cause

Duplicate event rows from retries

Fix

Add an idempotent source key (event_id + load_id) and dedupe in staging.

When things break

Debugging tips

  • Wrong total? Classify the measure: if semi- or non-additive, sum is the probable bug.
  • Fact row count vs source event count should match at atomic grain.
  • If balances 'jump' at aggregation, check whether it's SUM vs AVG/LAST across time.
Interviews

Interview checkpoint

Practice

Hands-on lab

Classify Measures and Fix a Broken Balance Report
35 min · intermediate
In progress
Objectives
  • Build a transaction fact and a balance snapshot
  • Compare SUM vs AVG for a semi-additive measure
  • Fix a report that sums balances across time

Classify Measures and Fix a Broken Balance Report

35 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

An account balance from a daily snapshot is best classified as:

2

Unique active-user counts are safe to SUM across all rows of a fact table.

Go deeper

Official documentation

Kimball fact table techniques
docs