ModelingGENERALWAREHOUSE-SPECIFICSIMPLIFIED

Fact Tables

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.

What actually happens How to build it Can I trust it?▶ Run the lab

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

What belongs in a fact table, at what grain, and which of its columns are safe to add up?

Who needs this

Every aggregate anyone runs. A BI tool will offer SUM on every numeric column in the table without knowing which ones are additive, and a business user will use it. The fact table's column choices are therefore a safety design, not just a storage one.

What one row is

One row is one occurrence of the business process being measured, at the grain the table declares: one order, one order line, one payment, one account-day, one shipment through its whole lifecycle. The grain is the table's most important property and the one least often written down (Grain: What Does One Row Represent?).

The obvious build

Put the transactional table in the warehouse, keep the source ids as columns, keep every numeric field the source had, and call it a fact table. Analysts get what they expect and the model looks like a star from a distance.

Why it breaks

The source had a discount_percent column. It is numeric, so the BI tool offers SUM(discount_percent), and someone puts the result on a slide. A summed percentage is not a quantity of anything (Grain: What Does One Row Represent?).

How it breaks with real data

The source had a discount_percent column. It is numeric, so the BI tool offers SUM(discount_percent), and someone puts the result on a slide. A summed percentage is not a quantity of anything (Grain: What Does One Row Represent?).
The order table carries shipping_fee and the analyst joins it to lines to get product mix. shipping_fee now repeats once per line and the total shipping cost reported is several times the real one, with no error.
account_balance is stored as a fact and someone sums it across a month. Balances are semi-additive: they add across accounts and are meaningless added across time (Snapshot Tables).
Source ids were kept as the join keys, so when the CRM migration renumbered customers, half the fact rows stopped resolving and the join silently dropped them (Surrogate Keys).
Order status keeps changing after the fact row is written. The table is treated as immutable history, so the fact says pending forever, and the "orders awaiting payment" metric never comes down.

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

A fact table has three kinds of column and nothing else. Foreign keys to dimensions, which are what you group and filter by. Measures, which are numeric quantities of the process. Degenerate dimensions — identifiers like order_id that have no attributes of their own and so live on the fact rather than in a dimension of one column.
Facts come in three shapes. A transaction fact has one row per event and is append-only — the natural shape and the one to prefer. A periodic snapshot has one row per entity per period and exists because some measures are states rather than events. An accumulating snapshot has one row per pipeline instance whose columns are filled in as it progresses, and is the only one of the three that is deliberately mutable (Snapshot Tables).
Measures come in three additivity classes, and this is the distinction that decides whether a dashboard is meaningful. Additive measures — revenue, quantity — can be summed across every dimension including time. Semi-additive measures — balances, inventory levels, headcount — sum across entities but not across time; the right time aggregation is a point-in-time value or an average. Non-additive measures — rates, percentages, ratios, unit prices — cannot be summed across anything.
The fix for non-additive measures is always the same and always worth stating explicitly: store the components, compute the ratio at query time. Store discount_amount and gross_amount, not discount_percent. Then SUM(discount_amount) / SUM(gross_amount) is correct at any grouping, and no column in the table is dangerous to add up.
Facts are usually sparse rather than dense: a row exists only where the event happened. That is why "zero sales for a product in a week" requires a date dimension and an outer join rather than a row in the fact — the absence of a row is the zero (Dimension Tables).

What a fact table holds, and what one row is

The canonical example is small enough to hold in your head and complete enough to argue about. fct_orders has an order identifier, keys into the dimensions that give context, and the numbers that measure the order. Nothing else.

Every column falls into exactly one of the three categories, and being able to say which one a column is in is the test of whether the table is well formed. If a column is none of the three, it is a descriptive attribute that has drifted onto the fact, and it belongs in a dimension.

The grain table underneath shows what happens to that same order as the grain changes. This is the fact-table version of the journey lesson's point: each row is legitimate, and confusing two of them produces a number that looks entirely normal.

The same order, in four fact tables

Stage	One row is	Breaks if
`fct_orders`	One order. Measures are order-level totals.	You need product mix. There is no product key here that means anything, and adding one forces a choice about which product "the" product is.
`fct_order_lines`	One product line within one order.	You sum an order-level measure such as `shipping_fee` that was copied onto every line — the total is multiplied by the line count.
`fct_payments`	One payment attempt against one order.	You treat it as one row per order. A retried card payment is several rows and one order, and counting rows counts attempts.
`fct_order_fulfilment` (accumulating)	One order's whole lifecycle, with a timestamp column per milestone, updated in place.	You load it incrementally by insert time. Rows are updated after insertion, so an insert-time watermark misses every advance (The High-Water Mark).

Four legitimate facts about one order. A question is answerable only against the table whose grain matches it, and joining two of them without aggregating one to the other's grain first is the most common way a metric silently inflates.

A transaction fact at order grain

1CREATE TABLE fct_orders (
2  -- degenerate dimension: an identifier with no attributes of its own
3  order_id       VARCHAR   NOT NULL,
4 
5  -- foreign keys to dimensions: what you GROUP BY and FILTER on
6  customer_key   BIGINT    NOT NULL,   -- surrogate, points at a VERSION
7  product_key    BIGINT,               -- null at order grain: see the note
8  date_key       INT       NOT NULL,   -- yyyymmdd into dim_date
9  region_key     INT       NOT NULL,
10 
11  -- measures: quantities of the process, all additive at THIS grain
12  quantity       INT       NOT NULL,   -- additive
13  revenue        DECIMAL(18,2) NOT NULL, -- additive
14  shipping_fee   DECIMAL(18,2) NOT NULL, -- additive AT ORDER GRAIN ONLY
15  discount_amount DECIMAL(18,2) NOT NULL, -- additive; the ratio is derived
16 
17  -- NOT stored, deliberately:
18  --   discount_percent  -> non-additive; = SUM(discount_amount)/SUM(revenue)
19  --   customer_country  -> belongs in dim_customer, where it can be versioned
20  --   customer_name     -> descriptive; would freeze at load time
21 
22  -- grain: ONE ROW PER ORDER. Enforced by test, not by the engine.
23  PRIMARY KEY (order_id)
24);

product_key is the tell. An order can contain several products, so at order grain there is no single product key — leaving it nullable is how order-grain and line-grain facts get quietly merged into one broken table. The correct move is a second fact table at line grain (Grain: What Does One Row Represent?).

Three fact types, three different relationships to time

ENGINE-SPECIFICAccumulating snapshots assume affordable row-level updates. On an append-oriented lake table with copy-on-write semantics each update rewrites whole files, so the same model that is routine in a warehouse becomes a compaction problem on a lake — which is why lake platforms often express the same thing as an event fact plus a derived current-state view.

The taxonomy is worth learning because it predicts operational behaviour rather than just naming shapes. Transaction facts append; periodic snapshots grow by a fixed amount per period; accumulating snapshots are updated in place. Those three behaviours have completely different load, cost and recovery profiles.

The mistake this taxonomy prevents is trying to answer a state question with an event table. "How many open orders are there right now" is not a transaction question — it is a state question, and answering it from a transaction fact means folding every status change from the beginning of time. Either fold it once into a snapshot, or accept the cost on every query (Event vs Snapshot Modeling).

The mirror mistake is trying to answer an event question with a snapshot. "How many orders changed status yesterday" cannot be answered from daily snapshots at all if two changes happened between snapshots — the intermediate state was never observed and is not recoverable.

Fact type	One row is	Grows by	Write pattern	Answers well	Cannot answer
Transaction	One event, at the moment it happened	Events that occurred	Append only, immutable	"How much revenue in March", "how many orders per hour"	"What is the current status" without folding the whole history
Periodic snapshot	One entity in one period — an account on a day	Entities × periods, whether or not anything changed	Append one full period per run	"What was the balance on 14 March", trends, point-in-time cohort states	Anything that happened between two snapshots, including changes that were reverted
Accumulating snapshot	One instance of a multi-step process, from start to finish	New process instances only	Insert then repeatedly update in place	"How long between order and shipment", funnel and lag analysis	The state of a process as of a past date, unless you also version the row

Additivity: the property no type system checks

Every numeric column in a fact table will eventually be summed by someone, because that is what BI tools offer and what business users expect. The column's type does not distinguish a quantity from a rate, so the model has to.

The rule is simple and the discipline is not: additive measures may be summed across all dimensions; semi-additive measures may be summed across every dimension except time; non-additive measures may not be summed at all. The design response to the last two is the same in both cases — store components, derive the rest.

The failure table below is the one to show a stakeholder who is arguing for a margin_percent column because "it is easier for the dashboard". Each row is a real, shipped mistake, and each one produced a number that no reconciliation, uniqueness test or freshness check would ever have flagged.

What summing the wrong kind of measure produces

Trigger	Symptom	Cause	Response
A `discount_percent` column exists and a user drags it into a total.	A discount rate of 4,182%. Or, worse, a plausible-looking 23% that happens to be the sum over a small group.	Percentages are ratios of two quantities. Summing them adds denominators that are not the same, which is not an operation with a meaning.	Drop the column. Store `discount_amount` and `gross_amount`; expose the ratio in the metrics layer as `SUM(discount_amount)/SUM(gross_amount)` (The Metrics Layer).
A monthly report sums `account_balance` from a daily snapshot fact.	Total assets roughly thirty times reality, and it scales with the number of days selected.	Balances are semi-additive: they add across accounts, not across time. Thirty daily rows of one account are thirty observations of one balance.	Define the time aggregation explicitly — closing balance, average balance, or balance as of a chosen date — and encode it in the metrics layer rather than leaving `SUM` available (Snapshot Tables).
Order-level `shipping_fee` copied onto a line-grain fact for convenience.	Shipping cost is inflated by exactly the average number of lines per order, so the error is large, stable, and looks like a real trend.	A measure that is true at order grain is repeated once per line. Summing it counts the same fee once per line (Grain: What Does One Row Represent?).	Keep order-level measures on the order-level fact. If they must be on the line fact, allocate them across lines so the sum is still correct, and document the allocation rule.
A `unit_price` column summed to get "total price".	A number that is close enough to revenue to pass a glance and wrong for every order with quantity above one.	Unit price is a rate per item, not a quantity. The additive measure is `quantity * unit_price`, which is why it should be stored.	Store the extended amount as its own measure. Keep `unit_price` only if a consumer genuinely needs it, and name it so nobody sums it.
A quality suite passes on all of the above.	Full confidence in a wrong dashboard, which is worse than no checks at all.	Uniqueness, referential integrity, freshness and volume tests all describe row-level structure. Additivity is a property of meaning and no structural test can see it.	Treat measure semantics as documentation plus a metrics layer, and review new numeric columns for additivity class the way you review a migration (Dataset Documentation).

How to build it

Most important first.

Declare the grain first and in one sentence, then admit only columns that are true at that grain. This one rule prevents most fact-table defects, including the order-level-measure-on-a-line-grain-table trap (Grain: What Does One Row Represent?).
Model at the finest grain the process supports. A line-grain fact answers order-grain questions by aggregation; the reverse is impossible and discovering it late costs a rebuild (Backfills).
Never store a ratio, percentage or rate as a measure. Store numerator and denominator. If a consumer insists on the ratio, expose it in the metrics layer where the aggregation rule can travel with it (The Metrics Layer).
Use surrogate keys for dimension references, not source ids, so dimension versioning and source-key churn are both absorbed (Surrogate Keys).
Keep facts narrow and numeric. Every descriptive attribute you copy onto the fact is a value frozen at load time that you can no longer restate (Dimension Tables).
Decide explicitly whether the fact is immutable. If status must be current, either keep a mutable status column and say so, or model status changes as their own transaction fact and derive current state (Event vs Snapshot Modeling).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A transaction fact table guarantees, if loaded idempotently, that one row corresponds to one business event. That is the guarantee every aggregate silently relies on, and it holds only as long as the uniqueness test on the business key passes (Idempotent Data Pipelines).
Nothing in the schema guarantees additivity. The type system says NUMERIC; only documentation, naming and the metrics layer say "do not sum this".
Sparseness means a fact table guarantees nothing about absence: no row can mean the event did not happen, or that ingestion missed it. Distinguishing the two requires an external completeness check (Reconciliation).
An accumulating snapshot explicitly does not guarantee immutability. Rows are updated in place as a process advances, so any consumer that cached a row has a stale copy, and any incremental load keyed on insert time will miss the updates.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

Assert uniqueness on the declared business key of the fact — (order_id) at order grain, (order_id, line_number) at line grain — on every load. This single test is the direct guard on every metric the table serves (Data Tests).
Assert referential integrity: every dimension key in the fact resolves to exactly one dimension row. A failure here means either a late dimension load or a fan-out waiting to happen (The Dimensions of Data Quality).
Reconcile a summed measure against the source for a closed period. It catches missing and duplicated rows in one number (Reconciliation).
What all three miss: additivity. Every test passes on a table containing discount_percent, and the first dashboard that sums it produces a number that is wrong by an amount nobody can compute after the fact.

Freshness

Transaction facts can be appended as events arrive, so their freshness is bounded only by ingestion. They are the cheapest fact type to keep fresh because nothing has to be recomputed.
Periodic snapshots are as fresh as their period, by construction. A daily snapshot cannot answer an intraday question no matter how fast the pipeline runs, and consumers routinely misread this (Snapshot Tables).
Accumulating snapshots are the most expensive to keep fresh: every advance in the process is an update to an existing row, so freshness costs a merge rather than an append (Upserts and Merges).

When the schema or meaning changes

Adding a measure is safe and additive to the schema: existing queries ignore it. Backfilling it for history is the real work, and leaving it null for old periods is a decision consumers must be told about (Nullability & Defaults).
Adding a dimension key is safe. Removing one breaks every query that grouped by it, loudly, which is the good case (Breaking Schema Changes).
Changing what a measure includes — adding tax to revenue, switching to net of refunds — is the dangerous change: no column changes, every historical comparison silently breaks, and no compatibility test fires (Semantic Changes).
Changing grain is not evolution. It is a new table with a new name, built in parallel, with the old one deprecated on an announced schedule while consumers migrate (Impact Analysis tells you who they are).

How to re-run this safely

A transaction fact rebuilt from retained raw events is fully recoverable, partition by partition, provided the load is idempotent — write with a merge on the business key or replace whole partitions atomically (Atomic Publish).
Accumulating snapshots are the hard case. Their current state is the product of a sequence of updates, so recovery means replaying the sequence rather than re-reading a partition; if the update history was not retained, the state cannot be reconstructed (Replay from the Log).
Re-running a fact load over a period that contains late-arriving events changes numbers that were already reported. That is usually correct and always needs to be announced, because a silently restated month destroys more trust than a known gap (Late-Arriving Data).

What can go wrong

Failure modes

A non-additive measure stored as a column, summed by a BI tool that had no way to know better.
An order-level measure on a line-grain table, multiplied by line count on every aggregation (Grain: What Does One Row Represent?).
A duplicate load appending a second copy of a day's events, doubling every metric for that day (Duplicate Rows).
A dimension key that stopped resolving after a source migration, so an inner join dropped rows and the trend looked like a business decline (Missing Rows).
The mitigation failing: a uniqueness test on a surrogate row id rather than on the business key, which passes on every duplicate because each duplicate got its own row id.

Misreads

"Facts are events, dimensions are things." A useful first approximation that breaks on periodic snapshots, which are facts about states rather than events, and on factless facts that record that something was possible rather than that it happened.
"Any numeric column is a measure." Identifiers, version numbers, postal codes and percentages are all numeric and none is a measure. Numeric type says nothing about additivity.
"The fact table should carry the customer's country so we do not need the join." That freezes country at load time and duplicates it on every row. It is sometimes the right call and it is always a decision, not a convenience (Dimension Tables).
"We can add the missing measure later." You can add the column later; you can only backfill it if the raw data that computes it was retained (Keeping Raw History: The Recovery Position and the Liability).

Operating it

How you see it in production

Row count and summed measures per load, versus the same period historically. Doubling and halving both show up immediately here (Volume Anomalies).
Unresolved-key rate per dimension, per load. It is the earliest signal that a dimension is late or that an id space changed.
Distinct count of the business key versus row count. When they diverge, the grain has been violated, and this is cheaper to watch than to test after the fact (Distribution Tests).

What changes at 10x and 100x

At 10x, partitioning the fact by its event date stops being an optimisation and becomes the thing that makes queries feasible (Partitioning).
At 100x, the width of the fact matters as much as its length, and denormalised descriptive columns you added for convenience are now paid for on every scan.
High-cardinality dimension keys change join strategy: a dimension small enough to broadcast is nearly free, and one that is not forces a shuffle on every query (Broadcast Joins).

What drives cost here

Fact tables are the biggest objects in the warehouse and scan cost is proportional to their row count times the width of the columns a query touches. Grain is therefore the dominant cost lever in the whole model (Scan Cost).
Every extra column costs storage always and scan cost only when queried — which is the argument for keeping facts narrow but not the argument for dropping columns that are cheap to carry (Projection Pushdown).
Accumulating snapshots cost merges rather than appends, and merge cost scales with how much of the table the merge has to rewrite (Upserts and Merges).
Periodic snapshots cost entities times periods, forever, whether anything changed or not. That is a storage curve with no natural ceiling (Snapshot Tables).

What this approach costs

The finest grain answers the most questions and costs the most to store and scan. The honest position is to model fine and serve coarse aggregates from derived tables, which costs a second model to maintain.
Storing components instead of ratios costs two columns and a small amount of consumer education, and buys immunity from an entire class of wrong dashboard.
Immutable transaction facts are simpler and cannot express current status. Adding a mutable status column is convenient and removes the guarantee that a partition, once written, never changes.

Modeling lab — one grain, ten questions

Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.

Modeling lab — one grain, ten questions

Pick the grain of the fact table. The questions do not change; what the table can honestly say about them does.

Fact grain

The transaction and its total. The individual products are gone.

Answered

With care

Confidently wrong

Unanswerable

4 of these questions get an answer at this grain that is wrong, and none of them raise an error. That is the whole difficulty: the unanswerable ones announce themselves, and these do not.

Business question	At this grain	Why
What was revenue by country last month? Wants: One order, or one order line — either works, provided the measure is additive at that grain and is not summed twice.	answered	The measure is additive at this grain and each order is counted once.
What is the average order value? Wants: One order. An average over lines answers a different question entirely.	answered	The denominator is orders, which is exactly what one row is.
What was revenue by customer country at the time of each order? Wants: One order, joined to the version of the customer that was current when the order was placed.	WRONG no history	With a Type 1 dimension every historical order is attributed to the customer's current country. A customer moving from Poland to Germany silently rewrites last year's regional reports, and last month's report no longer reproduces.
What is net revenue after refunds? Wants: One order, with refunds either netted into the measure or held as a separate signed fact at the same grain.	answered	Refunds net into the measure, or sit beside it as a signed fact at the same grain.
What was the total account balance on each day last year? Wants: One account-day. A balance is a state, not an event, and cannot be reconstructed by summing transactions unless every transaction since account opening is retained.	WRONG	Summing transactions per day gives the daily change in balance, not the balance. The chart has the right shape and the wrong y-axis.
What is month-three retention by signup cohort? Wants: One user-month of activity, joined to the user's signup month.	WRONG	Users who were active but did not buy are invisible, so retention is understated by exactly the non-buyers.
What share of sessions ended in a purchase? Wants: One session — which requires a session window over events, because no source system emits a session.	unanswerable	No source system emits a session. Without a session window over events there is no denominator to divide by.
Which products are most often bought together? Wants: One order line, with the order key retained so lines can be grouped back into baskets.	unanswerable	The most instructive failure in this lab: the model is not wrong, it is at the wrong resolution, and no query can recover what was aggregated away.
What was yesterday's revenue, asked at 06:00 this morning? Wants: One order, in a period that is not yet closed.	with care	The grain is right and the period is not closed. Orders that happened yesterday and arrive later today are still missing at 06:00.
What was global revenue, across markets that bill in different currencies? Wants: One order, with both the transaction amount and the converted amount stored, plus the rate and the date the rate applied.	WRONG no history	Converting at query time with today's rate makes every historical report change daily. Converting once with no record of the rate makes the number unreproducible. Both pass every type check there is.

answeredThe grain is the thing the question is about.

with careIt works, and there is one specific way to get it wrong.

WRONGIt returns a plausible number that is not the answer, and nothing raises.

unanswerableThe resolution needed was aggregated away. No query recovers it.

SIMPLIFIEDA single fact table against ten questions. A real model has several, and a question that one answers badly another may answer exactly — which is the argument for more than one fact table, not for a finer one.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe fact/measure/degenerate-dimension decomposition and the three additivity classes are properties of the data, not of any engine, and transfer unchanged between warehouses, lakehouses and single-node analytical engines.
WAREHOUSE-SPECIFICWhether a mutable fact is cheap depends on the engine's update mechanism: warehouses with copy-on-write table formats rewrite whole files for a single row update, while merge-on-read formats defer that cost to query time, so an accumulating snapshot is affordable in one and painful in the other.
SIMPLIFIEDThe three fact types are a teaching taxonomy from dimensional modelling practice, not a partition of reality — factless fact tables, bridge tables for many-to-many relationships and aggregate facts all exist and none fits neatly into the three.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databaseaggregation-and-grouping joins relationships-and-keys normalization

Backendcommands-vs-events event-naming

Observabilityjoin-performance cardinality averages-lie

DSAprefix-sum

Domains that do not exist yet

— DevOps / Production Engineering owns the review process that should catch a new numeric column with no declared additivity class before it reaches a dashboard, the same way a schema migration gets reviewed.