Fact Table Patterns: Transaction, Periodic Snapshot & Accumulating Snapshot Facts

fact table patterns are the three shapes every measurement table in a dimensional model can take — and choosing the right one is not a stylistic preference, it is a correctness decision that determines whether your SUM returns a number a business will trust. A fact table is the centre of a star schema: a wide, mostly-numeric table whose columns are foreign keys to dimensions plus the numeric measures you actually aggregate. But "one fact table" hides a fork in the road. Are you recording each individual event as it happens? Sampling a balance at the end of every day? Or tracking a single order as it crawls through ordered, shipped, and delivered? Those are three different tables with three different grains, three different loading rhythms, and three different rules about which aggregations are legal.

Ralph Kimball reduced this to a discipline: declare the grain before you choose columns. The grain is the business meaning of a single row — "one line item on one order," "one account balance at end of one day," "one order's whole fulfillment lifecycle" — and once you have stated it in a sentence, the fact-table type, the measures, and the additivity rules all fall out of it. This guide walks the three canonical patterns an interviewer will actually probe — transaction facts, periodic snapshot facts, and accumulating snapshot facts — plus the additive / semi-additive / non-additive measure taxonomy that governs how you may aggregate them, and closes on the factless fact as a teaser. Each section pairs the theory with a Solution-Tail interview answer: SQL, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the grain-declaration decisions on the fact-grain practice set →, rehearse balance-and-snapshot modelling on the periodic-snapshot practice set →, and model process lifecycles on the accumulating-snapshot practice set →.

---

On this page

• Why fact table patterns start with the grain
• Transaction fact tables — one row per event
• Periodic snapshot facts — one row per entity per period
• Accumulating snapshot facts — one row per process instance
• Additive, semi-additive & non-additive measures
• Cheat sheet — fact table pattern recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why fact table patterns start with the grain

The grain is the sentence "one row means…" — every fact-table choice is downstream of it

The one-sentence invariant: you declare the grain of a fact table before you pick a single column, because the grain decides which of the three patterns you are building and which aggregations will be correct. Kimball's four-step dimensional design runs in a fixed order — choose the business process, declare the grain, identify the dimensions, identify the facts — and skipping straight to columns is the single most common way a warehouse ends up with fact tables nobody can safely SUM.

What "grain" actually means.

• Grain is a business statement, not a technical one. "One row per order line," "one row per account per day," "one row per insurance claim" — you should be able to say it in one sentence with no ANDs that hide a mixed grain.
• Every row in the table must mean exactly the same thing. If some rows are per-line-item and some are per-order-total, the grain is mixed and every aggregate double-counts. A clean grain is the precondition for trustworthy math.
• Grain fixes the dimensionality. Once you say "per line item per day per store per product," you have named the foreign keys the fact must carry. Dimensions attach at or above the grain, never below it.

The three patterns are three answers to "what is one row?"

• Transaction grain — one row per event. The finest grain: a row exists only when a measurement event occurs (a sale, a click, a payment). Sparse, additive, append-only.
• Periodic snapshot grain — one row per entity per period. A row for every entity at every regular interval, whether or not anything changed (a daily balance, a monthly inventory level). Dense, semi-additive.
• Accumulating snapshot grain — one row per process instance. A row per long-running workflow that you update in place as it hits milestones (an order from placed to delivered). Many date keys, lag measures.

The anatomy shared by all three.

• Foreign keys. Surrogate keys pointing at conformed dimensions (date_key, product_key, store_key), forming the star.
• Measures. The numeric columns you aggregate (amount, quantity, balance, lag_days). Their additivity is a property you must document.
• Degenerate dimensions. Identifiers like an order number that live on the fact with no dimension table of their own.

What interviewers listen for.

• Do you declare the grain first, in one sentence, before naming columns? — the senior tell.
• Do you name all three fact-table types and match each to its grain and loading rhythm? — required breadth.
• Do you reason about additivity — "balances are semi-additive, so I cannot SUM them across time" — unprompted? — depth signal.
• Do you keep the grain as fine as the process allows, resisting pre-aggregation, because you can always roll up but never drill down below the grain? — the Kimball reflex.

Worked example — declaring the grain for retail sales

Detailed explanation. Before any DDL, walk Kimball's four steps out loud. The business process is retail selling; the natural grain is one row per product per sales-transaction line (the most atomic thing the POS records); the dimensions that attach at that grain are date, store, product, promotion, and cashier; the facts are the numbers on that line. Declaring the grain as "one row per line item on one receipt" is what tells you this is a transaction fact, and that its sales_amount will be fully additive.

Question. For a retail chain that wants to analyse sales by day, store, and product, state the grain and classify the fact table.

Input.

design step	decision
business process	retail sales at the register
grain (declare first)	one row per product line on one receipt
dimensions	date, store, product, promotion, cashier
facts	quantity_sold, sales_amount, discount_amount

Code.

-- Grain: ONE ROW PER PRODUCT LINE ON ONE SALES RECEIPT (transaction grain)
CREATE TABLE fact_sales (
    date_key        INT     NOT NULL REFERENCES dim_date(date_key),
    store_key       INT     NOT NULL REFERENCES dim_store(store_key),
    product_key     INT     NOT NULL REFERENCES dim_product(product_key),
    promotion_key   INT     NOT NULL REFERENCES dim_promotion(promotion_key),
    cashier_key     INT     NOT NULL REFERENCES dim_cashier(cashier_key),
    receipt_no      VARCHAR NOT NULL,           -- degenerate dimension
    quantity_sold   INT     NOT NULL,           -- additive
    sales_amount    NUMERIC(12,2) NOT NULL,     -- additive
    discount_amount NUMERIC(12,2) NOT NULL      -- additive
);

Step-by-step explanation. The comment line pins the grain so no future engineer widens it by accident. Each foreign key attaches at that grain — a line item happens on one day, in one store, for one product, so those are single-valued keys, not lists. receipt_no is a degenerate dimension: it identifies the receipt but has no attributes worth a dimension table, so it rides on the fact. The three measures are all counts of money or units at line grain, which is exactly what makes them additive across every dimension.

Output.

property	value
grain	one row per receipt line
pattern	transaction fact
additivity	fully additive (sum across all dimensions)
load rhythm	insert-only, one row per event

Rule of thumb. If you cannot state the grain in a single sentence without the word "and," the grain is mixed — split it into separate fact tables before you write a column.

---

2. Transaction fact tables — one row per event

A transaction fact is the immutable event log of the warehouse — one row the instant a measurement happens

The transaction fact table is the workhorse and the default. Its defining property in one line: there is exactly one row for each measurement event, at the finest grain the business process produces, and once written the row is never updated. A sale, a payment, a web click, a sensor reading — each becomes a single row the moment it occurs, keyed to whichever dimensions were in force at that instant.

The defining characteristics.

• Grain = one event. The row represents a single atomic occurrence. This is the finest grain available, which is exactly why transaction facts are the most flexible — you can always aggregate up to daily or monthly, but a pre-aggregated table can never be drilled back down.
• Sparse, not dense. A row exists only when something happened. A product that did not sell today generates no row. This is the opposite of a periodic snapshot, and it is why transaction tables can be enormous yet contain no "empty" rows.
• Fully additive measures. Because each row is an independent event quantity, the measures (amount, quantity) sum correctly across every dimension, including time. This is the easiest additivity story in dimensional modelling.
• Append-only. Transaction facts are insert-only. You do not update a past sale; a correction is a new compensating row. This makes the table idempotent-friendly and audit-friendly.

Degenerate dimensions live here.

• What they are. An operational identifier — order number, receipt number, ticket id — that you want to keep for grouping and drill-through but which has no descriptive attributes of its own.
• Where they go. Directly on the fact row as a plain column, with no join to a dimension table. Grouping "all lines on order 5501" is then a simple WHERE receipt_no = ....

When to reach for a transaction fact.

• The business question is about individual events or fine-grained rollups ("revenue by product by day," "clicks by campaign by hour").
• You need maximum analytical flexibility and can afford the row volume.
• History must be immutable and auditable — every event preserved exactly as it happened.

Worked example — loading sales events into a transaction fact

Detailed explanation. The everyday transaction-fact load is an insert of new events since the last run, each row carrying the surrogate keys resolved from the operational identifiers. No row that already exists is touched; the load only grows the table. This is what makes a transaction fact the natural landing zone for an append-only event stream.

Question. Two sales events arrive. Show them landing as two new immutable rows in fact_sales and confirm that a SUM over them is correct across day and product.

Input.

receipt_no	date	product	qty	amount
R-100	2026-03-01	SKU-A	2	40.00
R-101	2026-03-01	SKU-B	1	15.00

Code.

INSERT INTO fact_sales
    (date_key, store_key, product_key, receipt_no, quantity_sold, sales_amount)
SELECT d.date_key, s.store_key, p.product_key,
       e.receipt_no, e.qty, e.amount
FROM   staging_sales_events e
JOIN   dim_date    d ON d.full_date   = e.event_date
JOIN   dim_store   s ON s.store_code  = e.store_code
JOIN   dim_product p ON p.sku         = e.product_sku
WHERE  e.event_ts > :last_loaded_ts;   -- only new events, append-only

Step-by-step explanation. Each staging event is matched to its dimension surrogate keys (date, store, product) so the fact stores compact integers, not business strings. The WHERE e.event_ts > :last_loaded_ts filter makes the load incremental and insert-only — already-loaded events are never re-inserted or updated. receipt_no is written straight onto the fact as a degenerate dimension. After the insert the two events are two independent rows.

Output.

date_key	product_key	receipt_no	quantity_sold	sales_amount
20260301	11	R-100	2	40.00
20260301	12	R-101	1	15.00

Rule of thumb. Keep the transaction grain as fine as the source allows and never update a landed row — corrections are new rows, so the table stays an auditable log.

Dimensional modeling interview question on transaction-fact grain

Question. A colleague proposes a fact_sales table with one row per order (summing all line items) to save space. You need per-product analysis. Explain why that grain is wrong, and write the transaction fact at the correct grain plus a query that proves both product-level and order-level analysis are possible from it.

Solution Using the finest transaction grain with a degenerate dimension

Code.

-- Correct grain: one row per ORDER LINE (finest), not per order.
CREATE TABLE fact_sales (
    date_key      INT NOT NULL,
    product_key   INT NOT NULL,
    order_no      VARCHAR NOT NULL,   -- degenerate dimension (identifies the order)
    quantity_sold INT NOT NULL,
    sales_amount  NUMERIC(12,2) NOT NULL
);

-- Per-product analysis: aggregate up from the fine grain.
SELECT product_key, SUM(sales_amount) AS revenue
FROM   fact_sales
GROUP  BY product_key;

-- Order-level analysis is STILL possible: roll up on the degenerate key.
SELECT order_no, SUM(sales_amount) AS order_total
FROM   fact_sales
GROUP  BY order_no;

Step-by-step trace.

order_no	product_key	sales_amount
O-1	11	40.00
O-1	12	15.00
O-2	11	20.00

1. At line grain, order O-1 is two rows (products 11 and 12); order O-2 is one row.
2. Per-product rollup groups by product_key: product 11 = 40.00 + 20.00 = 60.00; product 12 = 15.00.
3. Per-order rollup groups by the degenerate order_no: O-1 = 55.00, O-2 = 20.00 — the coarse view is recovered by aggregation.
4. Had the table been built at order grain, step 2 would be impossible: the per-product split was discarded, and you cannot drill below the stored grain.

Output:

view	key	value
per-product	product 11	60.00
per-product	product 12	15.00
per-order	O-1	55.00
per-order	O-2	20.00

Why this works — concept by concept:

• Finest grain wins — storing one row per line item preserves the maximum detail; every coarser question is answerable by a GROUP BY, so fine grain is strictly more flexible than pre-aggregated grain.
• Degenerate dimension — order_no on the fact lets you regroup to order level with no join, giving order-total analysis for free without a redundant order dimension table.
• Additivity — because each line is an independent event, SUM(sales_amount) is correct at every rollup level, product or order or day.
• No drill-down below grain — the reason order-grain is rejected is asymmetry: you can always roll up, never down, so the coarse design permanently loses the product breakdown.
• Cost — fine grain costs more storage, O(lines) rows instead of O(orders), but query rollups are a single scan-and-aggregate, a cheap price for never having to reload to answer a new question.

Dimensional
Topic — fact-grain
Declaring-the-grain and fact-design problems

Practice →

Star schema Topic — fact-table Transaction-fact and star-schema problems

Practice →

---

3. Periodic snapshot facts — one row per entity per period

A periodic snapshot samples state on a heartbeat — one row per entity per period, whether or not anything moved

Where a transaction fact records flow (things that happened), a periodic snapshot records level (the state of things at a point in time). Its defining property: there is one row for each entity at every regular period — end of every day, or every month — recorded whether or not any transaction touched that entity in the period. Account balances, inventory on hand, insurance policy status, subscription counts: these are questions of "how much was there at close of business," and no amount of summing individual transactions answers them as cleanly as a snapshot.

The defining characteristics.

• Grain = one entity per period. "One row per account per day," "one row per product per store per month." The period is part of the grain and is fixed and regular — the snapshot's heartbeat.
• Dense, not sparse. A row exists for every entity every period even if nothing changed. An account with no transactions today still gets today's balance row. This predictable density is what makes trend queries trivial: every period is present.
• Measures are levels, and levels are semi-additive. A balance can be summed across accounts ("total deposits today") but not across time ("Jan balance + Feb balance" is meaningless). This semi-additivity is the single most tested property of periodic snapshots.
• Often derived, not raw. Snapshot measures are frequently computed by rolling forward the prior snapshot plus the period's transactions, so a periodic snapshot commonly sits downstream of a transaction fact.

Why snapshots exist at all.

• The question is about state, not events. "What was every account's balance at month-end?" cannot be read off a transaction log without replaying every transaction from the beginning of time.
• Performance. Pre-computing the period-end level once turns an expensive running-sum-since-inception query into a single-row lookup.

Semi-additivity — the rule you must state.

• Additive across every dimension except time. SUM(balance) GROUP BY date gives a correct total-across-accounts per day.
• Across time, you do not sum — you take last or average. End-of-month balance is the last daily balance; "average balance" is AVG over the period. Summing daily balances across a month is a classic wrong answer.

Worked example — a daily account-balance snapshot

Detailed explanation. The canonical periodic snapshot is a daily balance. Every account gets one row per day carrying its end-of-day balance, produced by taking yesterday's balance and applying today's transactions. Even a dormant account gets a row — its balance simply repeats — which is what keeps the table dense and every day queryable.

Question. Build a fact_account_balance_daily snapshot and show two accounts across two days, including a day where one account had no activity.

Input.

account	day	activity	end-of-day balance
A1	2026-03-01	+100	100
A2	2026-03-01	+250	250
A1	2026-03-02	(none)	100
A2	2026-03-02	-50	200

Code.

-- Grain: ONE ROW PER ACCOUNT PER DAY (periodic snapshot)
CREATE TABLE fact_account_balance_daily (
    date_key    INT NOT NULL,
    account_key INT NOT NULL,
    balance     NUMERIC(14,2) NOT NULL,   -- LEVEL: semi-additive
    PRIMARY KEY (date_key, account_key)
);

-- Roll yesterday's balance forward + today's net movement, for EVERY account.
INSERT INTO fact_account_balance_daily (date_key, account_key, balance)
SELECT :today_key, a.account_key,
       COALESCE(y.balance, 0) + COALESCE(t.net_amount, 0)
FROM        dim_account a
LEFT JOIN   fact_account_balance_daily y
       ON   y.account_key = a.account_key AND y.date_key = :yesterday_key
LEFT JOIN ( SELECT account_key, SUM(amount) net_amount
            FROM   fact_transactions
            WHERE  date_key = :today_key
            GROUP  BY account_key ) t
       ON   t.account_key = a.account_key;

Step-by-step explanation. The INSERT iterates every account in dim_account, so even accounts with no transactions today get a row — that is the density guarantee. Each new balance is yesterday's balance (y.balance) plus today's net movement (t.net_amount), with COALESCE handling first-day and no-activity cases. Account A1 on day 2 has no transactions, so net_amount is NULL → 0 and its balance simply carries 100 forward. The measure stored is a level, so it must be treated as semi-additive downstream.

Output.

date_key	account_key	balance
20260301	A1	100.00
20260301	A2	250.00
20260302	A1	100.00
20260302	A2	200.00

Rule of thumb. A periodic snapshot must emit a row for every entity every period — if dormant entities are missing, gap-filling trend queries silently break.

Dimensional modeling interview question on semi-additive balances

Question. An analyst runs SELECT SUM(balance) FROM fact_account_balance_daily WHERE month = 'March' to get "total March balance" and gets a number 30× too large. Explain the bug and write the correct queries for (a) total balance across accounts on a given day and (b) each account's month-end and average balance.

Solution Using semi-additive aggregation — last over time, sum across accounts

Code.

-- WRONG: summing a LEVEL across time multiplies it by the number of days.
-- SELECT SUM(balance) ...  -- ~30x too big for a 30-day month

-- (a) Correct total ACROSS ACCOUNTS on one day — summing across a
--     non-time dimension is valid, balances ARE additive there:
SELECT date_key, SUM(balance) AS total_balance_that_day
FROM   fact_account_balance_daily
WHERE  date_key = 20260331
GROUP  BY date_key;

-- (b) Correct OVER TIME per account — month-end = LAST value, plus AVG:
SELECT account_key,
       MAX(balance) FILTER (WHERE date_key = 20260331) AS month_end_balance,
       AVG(balance)                                    AS avg_daily_balance
FROM   fact_account_balance_daily
WHERE  date_key BETWEEN 20260301 AND 20260331
GROUP  BY account_key;

Step-by-step trace.

account_key	date_key	balance
A1	20260330	100.00
A1	20260331	120.00
A2	20260330	200.00
A2	20260331	200.00

1. The buggy SUM(balance) adds every daily row: A1 alone contributes 100 + 120 + … for all 30 days, inflating a single balance into a 30-day sum — the "30× too large" symptom.
2. Query (a) sums across accounts on one fixed day. That is a legal semi-additive aggregation because balances are additive across every dimension except time: A1 120 + A2 200 = 320 total on the 31st.
3. Query (b) collapses time correctly: month-end is the LAST (here 31st) value via a filtered MAX, and AVG(balance) gives the average daily balance — the two time-collapsing operations that are valid on a level.
4. The fix is conceptual, not syntactic: recognise balance as semi-additive and never let SUM cross the time dimension.

Output:

result	account	value
total across accounts (Mar 31)	—	320.00
month-end balance	A1	120.00
month-end balance	A2	200.00
avg daily balance	A1	~110.00

Why this works — concept by concept:

• Level vs flow — a balance is a level (state at an instant); a transaction amount is a flow (a quantity over an interval). Flows sum over time, levels do not — that distinction is the whole of semi-additivity.
• Semi-additive — a level is additive across all dimensions except time, so SUM ... GROUP BY date is fine but SUM over dates is not; the model must document which measures are semi-additive.
• Last over time — period-end state is the last snapshot in the period, expressed with a filtered MAX (or a window LAST_VALUE), never a sum.
• Average as the other time collapse — when you do need a single number over time, AVG (average daily balance) is the meaningful reduction, not SUM.
• Cost — the correct queries are the same single scan the wrong one was, O(rows in range); semi-additivity costs nothing at runtime, only discipline at query-writing time.

Snapshot
Topic — periodic-snapshot
Periodic-snapshot and balance-modelling problems

Practice →

Measures Topic — semi-additive Semi-additive aggregation problems

Practice →

---

4. Accumulating snapshot facts — one row per process instance

An accumulating snapshot tracks a workflow in a single evolving row — many date keys, updated as milestones complete

The third pattern is the odd one out, and interviewers love it precisely because it breaks the "facts are insert-only" reflex. Its defining property: there is one row per process instance — one order, one claim, one mortgage application — and that same row is repeatedly updated in place as the instance passes through the milestones of a defined, finite pipeline. It exists to answer "how long does each stage take, and where is each instance stuck right now?"

The defining characteristics.

• Grain = one process instance. One row per order, per claim, per candidate application — a workflow with a clear start and end and a known set of steps in between.
• Multiple date foreign keys — one per milestone. order_date_key, payment_date_key, ship_date_key, deliver_date_key. These are role-playing dimensions: every one points back to the same dim_date, playing a different role. Unreached milestones hold a NULL or a "not yet" surrogate key.
• Updated in place — the one mutable fact. When an order ships, you UPDATE its existing row to fill ship_date_key and recompute lag measures. This is the only mainstream fact pattern that routinely issues UPDATE.
• Lag / duration measures. The high-value columns are the gaps between milestones: order_to_ship_days, ship_to_deliver_days. They are what let the business see and attack bottlenecks.

How it is loaded.

• Insert on creation. When the process starts (order placed), insert one row with the start milestone filled and later milestones NULL.
• Update on each milestone. Each subsequent event locates the row by its business key (order id, itself often a degenerate dimension) and updates the matching date key plus the affected lag measures.
• Row count stays roughly the number of open + completed instances, not the number of events — far smaller than a transaction fact of the same process.

When to reach for an accumulating snapshot.

• The process has a well-defined, short-ish set of milestones with a start and an end.
• The business cares about elapsed time between steps and current pipeline status ("how many orders are between shipped and delivered?").
• You want one-row-per-instance convenience for lifecycle analysis rather than reconstructing it from scattered events.

Worked example — an order-fulfillment accumulating snapshot

Detailed explanation. The textbook accumulating snapshot is order fulfillment: ordered → shipped → delivered. The row is inserted when the order is placed with only order_date_key set, then updated twice as the shipment and delivery events arrive, each update filling one more date key and computing the lag since the previous milestone.

Question. Model an order-fulfillment accumulating snapshot and show order 1001 as it moves ordered → shipped → delivered, filling milestone dates and lag measures on each update.

Input.

event	date	milestone
placed	2026-03-01	order_date
shipped	2026-03-03	ship_date
delivered	2026-03-06	deliver_date

Code.

-- Grain: ONE ROW PER ORDER (accumulating snapshot)
CREATE TABLE fact_order_fulfillment (
    order_no            VARCHAR NOT NULL PRIMARY KEY,  -- degenerate dimension
    order_date_key      INT     NOT NULL,
    ship_date_key       INT,                           -- NULL until shipped
    deliver_date_key    INT,                           -- NULL until delivered
    order_to_ship_days  INT,
    ship_to_deliver_days INT
);

-- 1) INSERT when the order is placed (only the start milestone is known):
INSERT INTO fact_order_fulfillment (order_no, order_date_key)
VALUES ('1001', 20260301);

-- 2) UPDATE in place when it ships:
UPDATE fact_order_fulfillment
SET    ship_date_key      = 20260303,
       order_to_ship_days = 20260303_date - 20260301_date  -- 2 days
WHERE  order_no = '1001';

-- 3) UPDATE in place again when it is delivered:
UPDATE fact_order_fulfillment
SET    deliver_date_key      = 20260306,
       ship_to_deliver_days  = 20260306_date - 20260303_date  -- 3 days
WHERE  order_no = '1001';

Step-by-step explanation. The order starts life as one inserted row with ship_date_key and deliver_date_key left NULL — the milestones it has not reached. Each later event finds the row by order_no and updates exactly the columns that milestone unlocks: shipping fills ship_date_key and the order_to_ship_days lag; delivery fills deliver_date_key and ship_to_deliver_days. The same physical row evolves; no new rows are inserted. At any moment the row's NULLs tell you where the order is stuck.

Output.

stage	order_date_key	ship_date_key	deliver_date_key	order_to_ship_days	ship_to_deliver_days
placed	20260301	NULL	NULL	NULL	NULL
shipped	20260301	20260303	NULL	2	NULL
delivered	20260301	20260303	20260306	2	3

Rule of thumb. Use an accumulating snapshot only for a bounded pipeline with a fixed set of milestones — an open-ended process with unknown steps does not fit the fixed date-key columns.

Dimensional modeling interview question on process-lifecycle facts

Question. The business wants two things from order data: (1) average days from order to delivery for completed orders, and (2) how many orders are currently shipped-but-not-delivered. Which fact-table pattern serves both cheaply, and write the two queries.

Solution Using an accumulating snapshot with milestone date keys

Code.

-- Accumulating snapshot answers both from ONE row per order.

-- (1) Average order-to-delivery time for COMPLETED orders:
SELECT AVG(order_to_ship_days + ship_to_deliver_days) AS avg_days_to_deliver
FROM   fact_order_fulfillment
WHERE  deliver_date_key IS NOT NULL;        -- completed only

-- (2) Orders currently shipped but NOT yet delivered — pipeline status
--     read straight off the NULL pattern, no event replay:
SELECT COUNT(*) AS in_transit
FROM   fact_order_fulfillment
WHERE  ship_date_key    IS NOT NULL
  AND  deliver_date_key IS NULL;

Step-by-step trace.

order_no	ship_date_key	deliver_date_key	order_to_ship_days	ship_to_deliver_days
1001	20260303	20260306	2	3
1002	20260304	NULL	1	NULL
1003	NULL	NULL	NULL	NULL

1. Query (1) filters to deliver_date_key IS NOT NULL — only completed order 1001 — and averages total lag 2 + 3 = 5 days.
2. Query (2) reads the milestone NULL pattern: ship IS NOT NULL AND deliver IS NULL matches order 1002 only, so in_transit = 1.
3. Order 1003 (placed, not shipped) is excluded from both — its NULLs place it earlier in the pipeline.
4. Both answers come from scanning one small row-per-order table; no transaction-event replay and no window functions are needed because the milestones already live on the row.

Output:

question	result
avg days to deliver (completed)	5.0
orders in transit (shipped, not delivered)	1

Why this works — concept by concept:

• One row per process — collapsing an order's whole lifecycle into a single evolving row makes lifecycle questions single-table scans instead of multi-event reconstructions.
• Role-playing date keys — several foreign keys (order, ship, deliver) all reference one dim_date in different roles, so each milestone is a fully-attributed date without duplicate calendars.
• Update in place — the accumulating snapshot deliberately mutates the row as milestones complete, which is what keeps lag measures and current status always current.
• NULL as pipeline position — an unreached milestone's NULL is the status signal, so "where is each order" is a WHERE on NULLs, not an event-sourcing query.
• Cost — storage and scan are O(instances), far smaller than the O(events) transaction fact of the same process; the trade is that loads must UPDATE by business key rather than pure append.

Snapshot
Topic — accumulating-snapshot
Accumulating-snapshot and lifecycle-lag problems

Practice →

Snapshot facts Topic — snapshot-fact Snapshot-fact design problems

Practice →

---

5. Additive, semi-additive & non-additive measures

Additivity is the property that says which SUM is legal — get it wrong and every dashboard lies

Choosing the fact-table pattern is half the design; the other half is classifying each measure by additivity — the set of dimensions across which the measure may be summed. This is not academic: a percentage summed across rows, or a balance summed across time, produces a confidently wrong number that survives into a board deck. Kimball's taxonomy has exactly three buckets, and you should tag every measure with one.

The three additivity classes.

• Additive — sum across every dimension, including time. Counts and amounts of independent events: sales_amount, quantity_sold. These are the easiest and the reason transaction facts are so pleasant. If it is additive, any SUM ... GROUP BY <anything> is valid.
• Semi-additive — sum across all dimensions except time. Levels and balances: account_balance, inventory_on_hand, headcount. You may SUM across accounts or warehouses, but over time you take last (period-end) or average, never SUM. These live mostly in periodic snapshots.
• Non-additive — never sum, across any dimension. Ratios, percentages, and unit prices: margin_pct, conversion_rate, unit_price. Averaging or summing them across rows is meaningless; you must recompute from their additive components (SUM(margin) / SUM(revenue)).

How to handle non-additive measures correctly.

• Store the components, derive the ratio. Instead of storing margin_pct, store additive revenue and cost; compute the percentage at query time from their sums. This keeps every stored column aggregatable.
• Never pre-average a ratio. The average of per-row percentages is not the overall percentage — Simpson's-paradox territory. Recompute from summed parts.

Factless fact tables — the teaser.

• A fact table with foreign keys but no numeric measures. The existence of the row is the fact. Its only measure is an implicit COUNT(*).
• Two flavours. Event-tracking factless facts record that something happened (a student attended a class, a promotion was emailed to a customer) — you count occurrences. Coverage factless facts record what could have happened (which products were on promotion in which store on which day) so you can find the gaps — the sales that did not occur — by comparing coverage against the transaction fact.
• Why they matter. Some of the most important business questions ("which promoted products sold nothing?") are about absence, and only a factless coverage table lets you see what is missing.

Worked example — classifying and aggregating three measures

Detailed explanation. Take one fact row set carrying an additive amount, a semi-additive balance, and a non-additive margin percentage, and aggregate each the way its class demands. The point is to see three different SQL treatments applied to columns that look superficially identical.

Question. Given rows with sales_amount (additive), balance (semi-additive), and margin_pct (non-additive), write the correct aggregation for each over a month.

Input.

date	sales_amount	balance	revenue	cost
2026-03-30	100	500	100	60
2026-03-31	200	650	200	150

Code.

SELECT
    -- Additive: SUM over time is correct.
    SUM(sales_amount)                          AS total_sales,

    -- Semi-additive: take the LAST (period-end) balance, never SUM.
    MAX(balance) FILTER (WHERE date_key = 20260331) AS month_end_balance,

    -- Non-additive: recompute the ratio from ADDITIVE components,
    -- do NOT average the per-row margin_pct.
    SUM(revenue - cost)::numeric / NULLIF(SUM(revenue), 0) AS margin_pct
FROM fact_daily
WHERE date_key BETWEEN 20260301 AND 20260331;

Step-by-step explanation. sales_amount is additive, so SUM across the month (100 + 200 = 300) is exactly right. balance is semi-additive: summing 500 + 650 would be nonsense, so the period-end value is the last day's balance, 650, via a filtered MAX. margin_pct is non-additive: averaging the per-row percentages (40% and 25%) gives 32.5%, which is wrong; the true blended margin is total-margin over total-revenue = (40 + 50) / (100 + 200) = 30%, computed from the additive components.

Output.

measure	class	correct value
total_sales	additive	300
month_end_balance	semi-additive	650
margin_pct	non-additive	30%

Rule of thumb. Tag every measure additive / semi-additive / non-additive at design time; store the additive parts of any ratio so the correct number is always recomputable.

Dimensional modeling interview question on additivity and factless facts

Question. Marketing put 50 products on promotion across stores this week; 12 of them recorded zero sales. The transaction fact_sales has no rows for products that did not sell, so you cannot find the 12 by querying it. Design the model that surfaces "promoted-but-unsold," and write the query.

Solution Using a factless coverage table anti-joined to the sales fact

Code.

-- Factless COVERAGE fact: one row per product per store per day it was
-- ON PROMOTION. No measures — the row's existence is the fact.
CREATE TABLE fact_promotion_coverage (
    date_key    INT NOT NULL,
    store_key   INT NOT NULL,
    product_key INT NOT NULL,
    promo_key   INT NOT NULL
    -- no numeric measures: COUNT(*) is the only measure
);

-- Promoted-but-unsold = coverage rows with NO matching sales row.
SELECT c.product_key, c.store_key, c.date_key
FROM   fact_promotion_coverage c
LEFT   JOIN fact_sales s
       ON  s.product_key = c.product_key
       AND s.store_key   = c.store_key
       AND s.date_key    = c.date_key
WHERE  s.product_key IS NULL;          -- the sale that did NOT happen

Step-by-step trace.

coverage (product, store)	matching sales row?	result
(SKU-A, S1)	yes (sold)	excluded
(SKU-B, S1)	no	promoted-but-unsold
(SKU-C, S2)	no	promoted-but-unsold

1. The coverage table records every promoted product/store/day even though nothing sold — that is the only place the "could have sold" universe exists.
2. The LEFT JOIN to fact_sales attaches a sales row where one exists and NULLs where none does.
3. WHERE s.product_key IS NULL keeps exactly the coverage rows with no sale — the promoted-but-unsold set, the absence the transaction fact alone can never show.
4. COUNT(*) over that result (here 2) is the measure; the factless table's power is that its rows, not any numeric column, carry the meaning.

Output:

product_key	store_key	status
SKU-B	S1	promoted, zero sales
SKU-C	S2	promoted, zero sales

Why this works — concept by concept:

• Factless fact — a table of foreign keys with no measures lets you record events or coverage where the meaningful quantity is a COUNT(*), not a stored number.
• Coverage vs event — a coverage factless table captures what could happen (products on promotion) so absence is queryable; an event factless table would capture what did happen.
• Absence via anti-join — the promoted-but-unsold answer is a LEFT JOIN ... WHERE right IS NULL, the canonical way to find rows on one side with no match on the other.
• Non-additive discipline — had the question been about margin, you would recompute from additive parts; additivity classification is what keeps every downstream aggregate honest.
• Cost — the anti-join is O(coverage + sales) with a hash join on the shared keys, and the coverage table is small (only promoted rows), so surfacing absence is cheap.

Measures
Topic — semi-additive
Additive and semi-additive measure problems

Practice →

Factless Topic — factless-fact Factless-fact and coverage problems

Practice →

---

Cheat sheet — fact table pattern recipes

Declare-the-grain checklist (always first).

1. Business process?      -> what activity are we measuring
2. Grain?                 -> "one row means ____" (one sentence, no AND)
3. Dimensions?            -> the FKs that attach AT that grain
4. Facts?                 -> the numeric measures + their additivity class

Transaction fact (one row per event, additive, insert-only).

CREATE TABLE fact_sales (
    date_key      INT NOT NULL, store_key INT NOT NULL, product_key INT NOT NULL,
    order_no      VARCHAR NOT NULL,          -- degenerate dimension
    quantity_sold INT, sales_amount NUMERIC(12,2)   -- additive
);

Periodic snapshot (one row per entity per period, semi-additive).

CREATE TABLE fact_balance_daily (
    date_key INT, account_key INT,
    balance  NUMERIC(14,2),                  -- semi-additive: LAST/AVG over time
    PRIMARY KEY (date_key, account_key)
);
-- period-end: MAX(balance) FILTER (WHERE date_key = :period_end)

Accumulating snapshot (one row per process, UPDATE in place).

CREATE TABLE fact_fulfillment (
    order_no VARCHAR PRIMARY KEY,
    order_date_key INT, ship_date_key INT, deliver_date_key INT,  -- role-playing dates
    order_to_ship_days INT, ship_to_deliver_days INT              -- lag measures
);
UPDATE fact_fulfillment SET ship_date_key = :d WHERE order_no = :id;

Non-additive ratio — store the parts, derive the ratio.

SELECT SUM(margin) / NULLIF(SUM(revenue), 0) AS margin_pct   -- NOT AVG(margin_pct)
FROM fact_sales;

Factless coverage — find the absence.

SELECT c.* FROM fact_promotion_coverage c
LEFT JOIN fact_sales s USING (date_key, store_key, product_key)
WHERE s.product_key IS NULL;                 -- promoted but unsold

Pattern picker.

Question shape	Pattern	Additivity
Individual events / fine rollups	transaction fact	additive
State/level at regular periods	periodic snapshot	semi-additive
Elapsed time & status of a workflow	accumulating snapshot	lag measures
Which events/coverage happened (count)	factless fact	COUNT(*)

---

Frequently asked questions

What are the three fact table types in Kimball dimensional modeling?

The three fact table patterns are the transaction fact (one row per measurement event at the finest grain, fully additive and insert-only), the periodic snapshot fact (one row per entity per regular period such as a daily balance, dense and semi-additive), and the accumulating snapshot fact (one row per process instance with multiple milestone date keys that you update in place). Each answers a different question — individual events, state at a point in time, and the lifecycle of a workflow — so most real warehouses use all three side by side.

What is the grain of a fact table and why declare it first?

The grain is the business meaning of a single row, stated in one sentence — "one row per order line," "one row per account per day." You declare it before choosing columns because the grain determines which fact-table pattern you are building, which dimensions attach, and whether the measures are additive. A mixed grain (some rows per line, some per order total) makes every aggregate double-count, so pinning a single clean grain is the precondition for trustworthy math.

What is the difference between a transaction and a periodic snapshot fact?

A transaction fact records flow — one sparse, immutable row each time an event happens — and its measures are fully additive. A periodic snapshot records level — one dense row for every entity at every period whether or not anything changed — and its measures (balances, inventory) are semi-additive, meaning you sum across accounts but take last or average over time. Transaction facts are insert-only; periodic snapshots are usually derived by rolling the prior snapshot forward with the period's transactions.

What is a semi-additive measure?

A semi-additive measure can be summed across every dimension except time. Balances, inventory on hand, and headcount are the classic examples: SUM(balance) GROUP BY date correctly totals across accounts on one day, but summing daily balances across a month is meaningless and inflates the number. Over time you instead take the period-end (last) value or the average, which is why semi-additivity is the most-tested property of periodic snapshot facts.

When do you use an accumulating snapshot fact table?

Use an accumulating snapshot when you are tracking a workflow with a defined, finite set of milestones and you care about how long each stage takes and where each instance currently sits — order fulfillment, insurance claims, loan applications, hiring pipelines. It keeps one row per process instance with a date foreign key per milestone (role-playing on one date dimension) and lag measures between them, and it is the one fact pattern you routinely UPDATE as each milestone completes.

What is a factless fact table?

A factless fact table has foreign keys but no numeric measures — the existence of the row is the fact, and its only measure is an implicit COUNT(*). There are two kinds: event-tracking (a student attended a class, a customer was emailed a promotion), which you count; and coverage (which products were on promotion in which store on which day), which lets you find absence — for example promoted products that recorded zero sales — by anti-joining the coverage table to the transaction fact.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every fact-table idea above, from declaring the grain to the transaction / periodic snapshot / accumulating snapshot patterns and the additive / semi-additive / non-additive measure taxonomy, maps to a hands-on practice room where you model the schema and write the aggregation against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "what is the grain, and can I SUM this?" holds up under a senior interviewer's depth probes.

Practice fact-grain problems now →
Accumulating-snapshot drills →