Fact Tables: Measuring the Business
A fact table is where the numbers live — but not every number sums the way you think. Anatomy, the additivity taxonomy, and building fct_order_items in dbt on Snowflake.
Every dashboard the bookshop will ever ship comes down to a number and a way to slice it — revenue by genre, units by month, margin by customer segment. The number is a measure, the slice is a dimension, and the table that stores the measures and points at the dimensions is a fact table. It’s the center of the star, and it’s where the business keeps score.
The grain work you did last post already decided what one row means: one order line. Now we fill that row in — and the filling is where most of the subtle, expensive mistakes in a warehouse get made, because a fact table looks trivial and isn’t. It’s three kinds of column and a fistful of rules about what’s allowed to sum.
What actually lives in a fact row
A fact table row has exactly three kinds of column, and the discipline is refusing to let anything else in.
- Foreign keys to dimensions. The context of the event — which book, which customer, which date. Every one is a surrogate key pointing at a
dim_table. These are how the row gets sliced. - Measures. The numbers the event produced — the quantity ordered, the amount charged. This is what you sum, average, and count.
- Degenerate dimensions. An identifier that behaves like a dimension key but has no table behind it. More on this in a moment.
That’s it. No descriptive text, no genre name, no customer email — those are attributes and they live in the dimensions. A fact table is deliberately narrow and deep: a few keys, a few numbers, and a great many rows. Keep it that way and it stays fast; let a book_title leak in and you’ve smeared a dimension across a billion rows, and every time an author’s name is corrected you’re rewriting the fact instead of one dimension row.
The narrowness isn’t aesthetic. A star schema is fast because the fact is small per row and the joins are all key-to-key; the moment you widen the fact with descriptive columns you’ve paid for a dimension’s worth of storage on the largest table in the warehouse and gained nothing a join wouldn’t have given you. When you’re tempted to add a column to a fact, ask which of the three kinds it is. If the honest answer is “it’s descriptive,” it belongs in a dimension.
Not every number sums
Here’s the part that quietly wrecks reports. You’d think a measure is a measure — put it in a fact, sum it, done. But measures come in three flavors, and only one of them is safe to add across everything. Kimball calls this the additivity of a measure, and it isn’t a label you attach for tidiness; it’s a rule about which aggregations are allowed to touch which columns, and violating it produces numbers that are wrong without being broken — no error, no null, just a confident figure that means nothing.
Fully additive: sum across anything
Additive measures sum across every dimension, in any combination, with no caveats. Quantity is additive: units sold by book, by month, by customer, by all three at once, grand total — all just sum(quantity). Extended price — quantity * unit_price on the line — is additive too, and so is margin_amount. These are the measures you actually want, and a well-designed transaction fact is mostly made of them, because “sum it along any axis” is exactly the freedom that makes a fact table worth building.
-- fully additive: every one of these is legitimate
select sum(extended_price) from fct_order_items; -- grand total
select genre, sum(extended_price) from fct_order_items f
join dim_book b on b.book_sk = f.book_sk group by genre; -- by genre
select date_sk, sum(quantity) from fct_order_items group by date_sk; -- by dayThe test for full additivity is simple: can you add this number across time and get something meaningful? Units sold in January plus units sold in February is units sold across both months — yes. Hold that test in your head, because the next two categories are exactly the measures that fail it.
Semi-additive: sum across space, never across time
Semi-additive measures sum across some dimensions but not time. A running inventory level is the classic case: if you stock 40 copies of a title on Monday and 40 on Tuesday, you have 40 copies — not 80. You can add stock across books (total inventory in the shop today) but never across days, because a level isn’t a flow. It’s the state of the world at an instant, and adding two instants together is nonsense with units of “copy-days” that a dashboard will cheerfully print as “copies.”
Semi-additive measures show up whenever you snapshot a level rather than record a flow — inventory on hand, account balances, headcount, open tickets. We build the bookshop’s daily inventory snapshot next chapter; here’s the query-time consequence you inherit the moment one exists. Summing across books is fine:
-- LEGAL: one point in time, summed across the space dimension (books)
select sum(stock_level) as copies_in_shop
from fct_inventory_daily
where snapshot_date = '2026-05-13';Summing across time is the lie:
-- WRONG: adds a level across three days → copy-days, not copies
select sum(stock_level)
from fct_inventory_daily
where snapshot_date between '2026-05-11' and '2026-05-13';The cure isn’t “don’t aggregate over time” — reports need a time range. The cure is to pick a non-additive aggregation over the time grain: take the level at the last date in the window rather than the sum of the levels in it. last_value (or a qualify on the max date) is the tool:
-- CORRECT: the level as of the latest day in the window, then sum across books
with latest as (
select
book_sk,
last_value(stock_level) over (
partition by book_sk
order by snapshot_date
rows between unbounded preceding and current row
) as level_as_of
from fct_inventory_daily
where snapshot_date <= '2026-05-13'
qualify snapshot_date = max(snapshot_date) over (partition by book_sk)
)
select sum(level_as_of) as copies_in_shop from latest;Sum across books, MAX(date) across time. That split — additive on one axis, “take the latest” on the other — is what “semi-additive” means, spelled out in SQL. Get it into your fingers now so that next chapter’s snapshot doesn’t surprise you.
Non-additive: don’t sum at all, recompute from parts
Non-additive measures don’t sum across anything, and ratios are the trap. Margin percent is the one everyone reaches for: it’s tempting to store margin_pct right on the line and let the dashboard avg() it. But the average of per-line margins is not the business’s margin. A 50%-margin line for one dollar and a 10%-margin line for a thousand dollars don’t average to 30% of anything real — the true blended margin is (0.50 + 100.00) / (1.00 + 1000.00), about 10.05%, nowhere near the 30% the naïve avg reports. Ratios are non-additive because their denominators differ per row, and averaging them weights a one-dollar line and a thousand-dollar line equally when the business does not.
Unit price is non-additive for the same reason from the other direction: sum(unit_price) adds the sticker prices of unrelated books into a number with no meaning, and avg(unit_price) gives you the average shelf price, not the average price paid — those differ the moment quantities vary. Neither is the number anyone wants.
The rule for non-additive measures is blunt: don’t store the ratio — store its components. Put the numerator and the denominator in the fact as their own additive measures — margin_amount and extended_price — and compute the ratio at query time, after aggregation:
-- CORRECT: aggregate the additive parts, divide once at the end
select
b.genre,
sum(f.margin_amount) as margin_dollars,
sum(f.extended_price) as revenue,
sum(f.margin_amount) / sum(f.extended_price) as margin_pct -- weighted, and true
from fct_order_items f
join dim_book b on b.book_sk = f.book_sk
group by b.genre;Aggregate first, divide second. The margin percent that falls out is weighted by dollars automatically, because you summed the dollars before you divided. Pre-aggregating a ratio is how a dashboard ends up confidently wrong; storing components is how it stays right at every grain, because sum(margin_amount)/sum(extended_price) is correct whether you group by genre, by month, or not at all.
The taxonomy in one line: additive sums anywhere, semi-additive sums everywhere but time, non-additive doesn’t sum — and the fix for the last two is the same instinct, keep the additive parts and defer the collapse. When a stakeholder asks for “average margin,” they’re asking you to store two additive columns, not one non-additive one.
Zero, null, and “did not happen”
Measures have a second subtlety that outranks additivity for how often it burns people: the difference between a measure that is zero, one that is null, and an event that simply produced no row at all. They are three different facts about the world, and conflating them silently corrupts aggregates.
- Zero is a real, known value. A line with
discount_amount = 0had no discount — the discount happened and it was nothing. Zero is additive; it sums and averages like any number and belongs in the fact as a plain0. - Null is unknown or not applicable. If the source didn’t send a cost for a line,
unit_costis genuinely unknown — and here’s the sharp edge:sumandavgskip nulls.avg(unit_cost)over ten lines where two are null averages the other eight, quietly narrowing the denominator;count(unit_cost)returns eight whilecount(*)returns ten. A null measure isn’t a zero, and if you want it to read as zero you must say so withcoalesce(unit_cost, 0)— on purpose, having decided that “unknown cost” means “assume zero cost,” which for a margin calculation overstates profit. Usually null is the more honest choice and the report should surface the coverage gap rather than paper over it. - “Did not happen” is the absence of a row. A book that never sold has no line in
fct_order_items— not a zero-quantity line, no line. This is the one people trip over: you cannotsumyour way to “titles that sold nothing,” because the fact has nothing to sum. Questions about absence need a different structure entirely — the factless coverage fact and the anti-join, which the next chapter builds. For now, just hold the distinction: a zero is a row that says nothing sold; “did not happen” is the row that isn’t there.
The practical discipline: decide, per measure, whether a missing value should be stored as 0 or left null, and write that decision into the model with an explicit coalesce or an explicit absence of one. An implicit null that some queries skip and others coalesce differently is how two dashboards over the same fact disagree.
Foreign keys are never null
The null discussion flips completely when we move from measures to foreign keys. A measure is allowed to be null — it means “unknown,” and that’s sometimes the truth. A foreign key must never be null. This is one of the few genuinely non-negotiable rules in dimensional modeling, and the reason is the join.
Suppose an order line arrives with no book_id — a data-entry gap, a bulk import, a gift-card sale that never had a book. If you let book_sk be null and the revenue dashboard inner-joins to dim_book, that row vanishes — real revenue, silently dropped, and the grand total no longer reconciles with finance. Switch to a left join and the row survives but groups under a blank nobody can click. Either way an analyst loses an afternoon discovering that “revenue by genre” and “total revenue” disagree by exactly the dirty rows.
Kimball’s fix is old and firm: when the truth is “we don’t know which dimension member,” the fact points at a designated unknown member — a placeholder row that every dimension carries for this purpose. In classic sequence-keyed warehouses it’s the famous -1 row (book_sk = -1, everything labelled “Unknown”); with the hashed keys this series uses, the same idea wears a hash instead of a -1, and it takes one deliberate coalesce — generate_surrogate_key(["coalesce(book_id, '-1')"]) routes a null to the sentinel key, so a keyless line is already pointing at one specific address, and the dimension just has to build a row that lives there. We construct that unknown member in chapter 6; the obligation it satisfies starts here. The fact’s job is to guarantee the key is never null — route the unknowns to the unknown member rather than letting a null slip through — and the dimension’s job is to have a row waiting.
Header amounts at line grain: allocation
The bookshop charges shipping once per order — a $6 fee that lives on the order header, not the line. But the fact’s grain is the line. So where does the $6 go? You cannot copy it onto every line (three lines × $6 = $18 of phantom shipping when you sum), and you cannot leave it off (now line-level revenue doesn’t roll up to the order total the finance team knows). This is the allocation problem, and it’s the everyday form of a multi-valued fact — one header value that has to be spread across many lines without changing the total.
The fix is to allocate the header amount down to the lines in proportion to something already at line grain — usually each line’s share of the order’s extended price:
-- allocate a header-level shipping fee across an order's lines by revenue share
select
oi.order_id,
oi.line_number,
oi.quantity * oi.unit_price as extended_price,
o.shipping_fee
* (oi.quantity * oi.unit_price)
/ sum(oi.quantity * oi.unit_price) over (partition by oi.order_id)
as shipping_allocated
from {{ ref('stg_order_items') }} oi
join {{ ref('stg_orders') }} o on o.order_id = oi.order_idEach line gets a slice of the $6 sized to its share of the order, and the slices sum back to exactly $6 per order — so sum(shipping_allocated) across the whole fact equals total shipping charged, at every grain. That “sums back to the header total” property is the whole point of allocation: it lets a header measure live at line grain and stay additive. (Watch the one edge case: a fully-discounted order with sum(extended_price) = 0 divides by zero — guard it with a nullif/coalesce and fall back to an even split across the lines.) Allocate by revenue share, by quantity share, or by weight for freight — the method is a business call, but the invariant never changes: the parts must sum to the whole.
The degenerate dimension
Look at order_id. It’s clearly dimensional — it groups the lines of a single order, and you’ll filter and count by it. But there’s no dim_order table worth building: an order has no interesting attributes of its own that don’t already live on the customer or the date. Its only real attribute is its identity.
So the key stays in the fact with no dimension behind it. That’s a degenerate dimension — a dimension that has degenerated to just its key. Order numbers, invoice numbers, transaction IDs, ticket numbers: all classic degenerates. It sits in the fact row as a plain column, and count(distinct order_id) gives you order counts straight from the transaction fact without a join — which matters, because “how many orders” and “how many order lines” are different questions and the degenerate key is what lets one table answer both. Don’t build an empty dimension table to hold it; that’s ceremony, not modeling.
Three rhythms, one process — a preview
Before we build, one bit of vocabulary, because it decides which shape this table even is. Kimball names three fact types, distinguished entirely by their rhythm — when a row is born and whether it’s ever touched again:
- Transaction fact — one row per event, born when the event happens and never updated. Grain: the event (here, one order line). This is what
fct_order_itemsis, and it’s the right default when unsure. - Periodic snapshot — one row per entity per period, whether or not anything changed. Grain: entity × period (one book per day). Its measures are the semi-additive levels from above.
- Accumulating snapshot — one row per process instance, updated in place as the instance hits milestones. Grain: the process (one order’s fulfillment, ordered → shipped → delivered).
Same business process — selling books — wants more than one of these, each answering questions the others structurally can’t. The next chapter builds all three for real; here we finish the transaction fact, and knowing it’s the transaction one is what justifies the append-only, ever-growing, incremental build below.
Building fct_order_items
The grain is one order line. The build reads from staging, looks up each dimension’s surrogate key with the same expression the dimension will use to mint it, and keeps only keys, measures, and the degenerate order_id.
One column deserves a note before the SQL: unit_cost. Margin needs cost, and the right cost is the one that was true when the sale happened, not today’s cost looked up from the book — a wholesale price change next month must not silently rewrite last month’s margin. So unit_cost is snapshotted onto the line at sale time in the source and carried through staging as a line-level measure, right beside unit_price:
-- models/staging/stg_order_items.sql (the columns the fact leans on)
select
order_id,
line_number,
book_id,
quantity,
unit_price, -- price charged on this line
unit_cost -- cost as of the sale, snapshotted at the line
from {{ source('shop', 'order_items') }}With cost living at the grain, the fact can compute a truthful, additive margin_amount per line and never has to join back to the book to do it:
-- models/marts/fct_order_items.sql
{{
config(
materialized='incremental',
unique_key='order_item_sk',
incremental_strategy='merge'
)
}}
with order_items as (
select * from {{ ref('stg_order_items') }}
),
orders as (
select * from {{ ref('stg_orders') }}
)
select
{{ dbt_utils.generate_surrogate_key(['oi.order_id', 'oi.line_number']) }} as order_item_sk,
-- coalesce to the '-1' sentinel BEFORE hashing, so a null natural key lands on
-- the same surrogate key the dimension minted for its unknown member.
{{ dbt_utils.generate_surrogate_key(["coalesce(oi.book_id, '-1')"]) }} as book_sk,
{{ dbt_utils.generate_surrogate_key(["coalesce(o.customer_id, '-1')"]) }} as customer_sk,
{{ dbt_utils.generate_surrogate_key(["coalesce(o.order_date::varchar, '-1')"]) }} as date_sk,
oi.order_id, -- degenerate dimension
oi.line_number, -- the other half of the grain
o.order_date, -- plain date, kept for incremental filtering
oi.quantity as quantity,
oi.unit_price, -- kept: it's what extended_price is derived FROM
oi.quantity * oi.unit_price as extended_price,
oi.quantity * (oi.unit_price - oi.unit_cost) as margin_amount
from order_items oi
join orders o on oi.order_id = o.order_id
{% if is_incremental() %}
where o.order_date > (select max(order_date) from {{ this }})
{% endif %}Two of those columns look redundant and are not, so let’s defend them before someone “tidies” them away.
line_number stays, even though it’s already baked into order_item_sk. The hash is an opaque key: it identifies a row, but you cannot read the grain off it, and you cannot test the grain with it. unique on order_item_sk proves the hash is unique; it does not prove that (order_id, line_number) is. Those differ in exactly the case you care about — an upstream fan-out that duplicates a line — and the only test that catches it is unique_combination_of_columns on the natural pair, which needs the pair present in the table. A grain you can’t test is a grain you’re hoping for.
unit_price stays, even though extended_price is derived from it. Storing the components alongside the derived measure is what lets you assert the derivation: extended_price = quantity * unit_price is a one-line test that catches a whole class of arithmetic and type-coercion bugs, and it’s impossible to write if you threw the inputs away. It also means an analyst can answer “what did this actually sell for per unit” without reverse-engineering a division. The storage is a rounding error; the auditability isn’t. Just remember what you were told above: unit_price is non-additive. It sits in the fact as an input and an audit trail, not as something anyone should ever sum().
The general rule, and it’s worth carrying past this chapter: a fact table should carry the columns its tests need. If you find yourself unable to test the grain or verify a computed measure, the fix is usually not a cleverer test — it’s a column you dropped too early.
A few more decisions worth naming. The primary key of the fact is its own surrogate — order_item_sk, hashed from the grain (order_id + line_number) — which gives the merge a clean unique_key and a stable handle on each row. The three dimension keys are built with generate_surrogate_key over the natural keys, and the expression matches exactly what dim_book, dim_customer, and dim_date will hash on their side — that’s the whole trick to making the join line up. quantity, extended_price, and margin_amount are all additive; there is deliberately no margin_pct column, because — per the taxonomy above — the ratio is recomputed as sum(margin_amount) / sum(extended_price) and gets a number that’s actually true.
The incremental materialization is here because a transaction fact is the table that grows without end — order lines only ever accumulate. You built the machinery for this in dbt’s incremental models; on Snowflake the merge strategy upserts on the unique_key so a late-arriving correction to a line replaces its row instead of duplicating it. The fact carries a plain order_date alongside the hashed date_sk precisely so the incremental filter has a real, range-comparable column to test against — a surrogate hash can’t be filtered with >.
Testing fct_order_items
A fact table’s tests aren’t decoration — they’re the tripwires for the exact failures this chapter has been circling: a fanned-out grain, a null key that should have been routed to the unknown member, a foreign key pointing at a dimension row that doesn’t exist. Four tests, and they map one-to-one onto the anatomy.
# models/marts/_facts.yml
models:
- name: fct_order_items
columns:
- name: order_item_sk
data_tests:
- unique # the grain: one row per order line, never two
- not_null
- name: book_sk
data_tests:
- not_null # never-null-FK, enforced mechanically
- relationships:
to: ref('dim_book')
field: book_sk
- name: customer_sk
data_tests:
- not_null
- relationships:
to: ref('dim_customer')
field: customer_sk
- name: date_sk
data_tests:
- not_null
- relationships:
to: ref('dim_date')
field: date_skRead that as three assertions the chapter earned:
unique+not_nullonorder_item_skis the grain test. The grain is one row per order line; the surrogate hashed from(order_id, line_number)is unique exactly when that’s true. If an upstream join tostg_ordersfans out — say a duplicate order header — two lines collide on the key anduniquegoes red before asum(extended_price)doubles on the dashboard. A grain violation isn’t a data glitch to shrug at; it’s a silent factor-of-two in every revenue number, and this test is what makes it loud.not_nullon every foreign key enforces the never-null rule mechanically. It cannot pass while a keyless line carries a nullbook_sk— which is precisely why the unknown-member routing has to be in place: the test demands that the null got turned into the unknown member’s real hash. (Withgenerate_surrogate_key, a null natural key already hashes to a non-null sentinel key, so this passes by construction — the test guards against a future refactor that “optimizes” the coalescing away.)relationshipsto each dimension is the referential-integrity tripwire. It fails the build if any fact key points at a dimension row that doesn’t exist — catching both the gross failure (a hash-expression mismatch between fact and dimension, which orphans every key at once) and the precise one (a singlebook_idthat hasn’t loaded intodim_bookyet). That second case is the late-arriving data problem — a real key with no member behind it, which the unknown member does not cover — and until it gets its own chapter, thisrelationshipstest is the alarm that it happened.
The unknown member handles null keys; relationships catches present-but-orphaned ones. Together they convert the two ways a fact join can silently return wrong numbers into two ways the build turns red. That’s the whole value proposition of testing a fact: the failure mode is never an error, always a plausible-looking figure, so you buy the alarm at build time or you pay for it in a meeting.
Final thoughts
A fact table is easy to describe and easy to get subtly wrong. The anatomy is three columns deep — keys, measures, degenerates — and the hard part is none of it: it’s knowing that the innocent-looking margin_pct you were about to store is a landmine, that the inventory level you’ll snapshot next post can’t be summed over time no matter how much a chart wants to, that a null measure and a null foreign key are opposite obligations, and that the header’s shipping fee has to be split before it can live at line grain. Additivity isn’t a footnote to fact-table design. It’s the thing that decides whether the numbers on the dashboard mean what they say.
Comments