Chapter 04 · Fact Table Design: Additive, Semi-Additive, Non-Additive Measures, and Factless Facts
Semi-Additive Balances/Snapshots: Why Time Often Requires Different Aggregation
Model AtlasMart inventory as a periodic snapshot and prove why on-hand balances are additive across products but not blindly additive across snapshot dates.
Learning outcomes
Inventory answers “how much do we have at a point in time?” rather than “how much activity happened over a period.” That difference changes aggregation semantics even though the column is a perfectly ordinary integer.
Define periodic-snapshot grain and distinguish stock from flow measurements.
Explain why inventory balances are additive across products at one time but semi-additive across time.
Implement two deterministic AtlasMart inventory snapshots while preserving the existing 137-unit control for September 20.
Write end-of-period and average-balance queries without summing duplicate time states.
Detect and repair the classic error of adding balances across snapshot dates.
The Chapter 03 sales fact remains one row per paid order line: 7 rows, 4 paid orders, 9 units, and 625 paid GMV. Chapter 04 does not change that grain. It adds explicitly documented measurement fixtures only where this chapter needs them: synthetic cost-at-sale evidence, a two-day inventory periodic snapshot, channel-session denominators, and promotion-eligibility coverage. Every new structure is reconciled back to the established source/control values before it is used.
The mandatory labs use Python's standard-library
sqlite3 module and synthetic local data. They are
semantic correctness labs, not performance benchmarks. Record
the actual Python and SQLite versions from your machine; query
speed, optimizer behavior, decimal representation, and
physical storage behavior are engine-specific.
1. Flow facts and stock facts answer different questions
Sales quantity is a flow: nine units sold across separate order-line events can be accumulated over a date range. Inventory on hand is a stock: each snapshot restates the current balance. Summing two daily balances counts some physical units more than once simply because they existed on two dates.
| Question | Valid aggregation | Why |
|---|---|---|
| Units on hand on 2026-09-20 | SUM across products within the chosen snapshot | Each product balance is a disjoint component of that point-in-time total |
| End-of-period inventory for a range | Choose the last applicable snapshot, then SUM products | Time is selected, not accumulated |
| Average daily inventory | Average comparable daily totals or entity balances under an explicit rule | Each snapshot gets a defined weight |
| Inventory across 2026-09-19 and 2026-09-20 | Do not SUM balances across dates | The same stock can persist across both dates |
2. Build a periodic snapshot at one product per day
The grain is one row per product per inventory snapshot date. Chapter 01 established the September 20 product balances 40, 60, 12, and 25, totaling 137. Chapter 04 adds a synthetic September 19 comparison snapshot totaling 146 solely to demonstrate time behavior.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS fact_sales;DROP TABLE IF EXISTS dim_store;DROP TABLE IF EXISTS dim_product;DROP TABLE IF EXISTS dim_customer;DROP TABLE IF EXISTS dim_date;CREATE TABLE dim_date ( date_key INTEGER PRIMARY KEY, full_date TEXT NOT NULL UNIQUE, calendar_year INTEGER NOT NULL, calendar_month INTEGER NOT NULL, day_of_month INTEGER NOT NULL, day_name TEXT NOT NULL);CREATE TABLE dim_customer ( customer_key INTEGER PRIMARY KEY, customer_id TEXT NOT NULL UNIQUE, customer_name TEXT NOT NULL, segment TEXT NOT NULL, region TEXT NOT NULL);CREATE TABLE dim_product ( product_key INTEGER PRIMARY KEY, product_id TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, category TEXT NOT NULL);CREATE TABLE dim_store ( store_key INTEGER PRIMARY KEY, store_id TEXT NOT NULL UNIQUE, store_name TEXT NOT NULL, store_type TEXT NOT NULL);CREATE TABLE fact_sales ( sales_key INTEGER PRIMARY KEY, date_key INTEGER NOT NULL REFERENCES dim_date(date_key), customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key), product_key INTEGER NOT NULL REFERENCES dim_product(product_key), store_key INTEGER NOT NULL REFERENCES dim_store(store_key), order_id TEXT NOT NULL, line_no INTEGER NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price NUMERIC NOT NULL CHECK (unit_price >= 0), extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0), source_batch_id TEXT NOT NULL, UNIQUE(order_id, line_no));INSERT INTO dim_date VALUES(20260918,'2026-09-18',2026,9,18,'Friday'),(20260919,'2026-09-19',2026,9,19,'Saturday'),(20260920,'2026-09-20',2026,9,20,'Sunday');INSERT INTO dim_customer VALUES(0,'__UNKNOWN__','Unknown customer','Unknown','Unknown'),(101,'C001','Ada Retail','SMB','North'),(102,'C002','Ben Home','Consumer','West'),(103,'C003','Cyra Labs','Enterprise','East'),(104,'C004','Dara Studio','Consumer','South');INSERT INTO dim_product VALUES(0,'__UNKNOWN__','Unknown product','Unknown'),(201,'P100','Keyboard','Accessories'),(202,'P200','Mouse','Accessories'),(203,'P300','Monitor','Displays'),(204,'P400','Dock','Accessories');INSERT INTO dim_store VALUES(0,'__UNKNOWN__','Unknown selling location','Unknown'),(301,'S-WEB','Web Store','Digital'),(302,'S-MOBILE','Mobile Store','Digital'),(303,'S-SALES','Assisted Sales','Assisted');INSERT INTO fact_sales VALUES(1,20260918,101,201,301,'O1001',1,2,50,100,'BATCH-CH03-001'),(2,20260918,101,202,301,'O1001',2,1,25,25,'BATCH-CH03-001'),(3,20260918,102,203,302,'O1002',1,1,200,200,'BATCH-CH03-001'),(4,20260919,101,204,301,'O1003',1,1,100,100,'BATCH-CH03-001'),(5,20260919,101,202,301,'O1003',2,2,25,50,'BATCH-CH03-001'),(6,20260920,104,201,302,'O1005',1,1,50,50,'BATCH-CH03-001'),(7,20260920,104,204,302,'O1005',2,1,100,100,'BATCH-CH03-001');DROP TABLE IF EXISTS fact_inventory_snapshot;CREATE TABLE fact_inventory_snapshot ( snapshot_date_key INTEGER NOT NULL REFERENCES dim_date(date_key), product_key INTEGER NOT NULL REFERENCES dim_product(product_key), on_hand_units INTEGER NOT NULL CHECK (on_hand_units >= 0), source_batch_id TEXT NOT NULL, PRIMARY KEY (snapshot_date_key, product_key));INSERT INTO fact_inventory_snapshot VALUES(20260919,201,42,'INV-20260919'),(20260919,202,63,'INV-20260919'),(20260919,203,13,'INV-20260919'),(20260919,204,28,'INV-20260919'),(20260920,201,40,'INV-20260920'),(20260920,202,60,'INV-20260920'),(20260920,203,12,'INV-20260920'),(20260920,204,25,'INV-20260920');SELECT d.full_date, SUM(i.on_hand_units) AS on_hand_unitsFROM fact_inventory_snapshot iJOIN dim_date d ON d.date_key=i.snapshot_date_keyGROUP BY d.full_dateORDER BY d.full_date;-- 2026-09-19 | 146-- 2026-09-20 | 1373. Controlled failure — sum balances across time
SELECT SUM(on_hand_units) AS wrong_two_day_inventoryFROM fact_inventory_snapshot;-- 283: arithmetic is correct, business meaning is wrong.There were not 283 units “in inventory” over the two-day window in the same sense that 625 dollars of paid sales occurred. The 283 result is the sum of repeated states. The repair depends on the question.
-- End-of-period balance for the selected window.WITH last_day AS (SELECT MAX(snapshot_date_key) AS date_key FROM fact_inventory_snapshot)SELECT SUM(i.on_hand_units) AS ending_unitsFROM fact_inventory_snapshot i JOIN last_day d ON d.date_key=i.snapshot_date_key;-- 137-- Average of the complete daily totals in this fixture.WITH daily AS ( SELECT snapshot_date_key, SUM(on_hand_units) AS units FROM fact_inventory_snapshot GROUP BY snapshot_date_key)SELECT AVG(units) AS average_daily_units FROM daily;-- 141.5In production, missing snapshots, intraday snapshots, time-zone boundaries, and different weighting requirements can change the average-balance rule. The rule belongs in the metric definition.
4. Semi-additive means “name the forbidden dimension”
A useful aggregation contract does not stop at the label
semi-additive. It states exactly where addition is
safe. For on_hand_units, addition is safe across
product (and, if modeled, warehouse/location) within the same
snapshot. Across the date dimension the default aggregation is
not SUM; consumers need a last-value, first-value,
average-balance, minimum, maximum, or another explicitly
governed temporal rule.
| Metric | Across product | Across date | Required time rule |
|---|---|---|---|
| on_hand_units | SUM | Not SUM | Latest snapshot for ending balance; explicit averaging policy for averages |
| paid_units | SUM | SUM | Normal accumulation of transaction events |
| paid_gmv | SUM | SUM | Normal accumulation of transaction events |
5. Hands-on lab — prove the time rule
import sqlite3conn=sqlite3.connect(":memory:")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_sales;\nDROP TABLE IF EXISTS dim_store;\nDROP TABLE IF EXISTS dim_product;\nDROP TABLE IF EXISTS dim_customer;\nDROP TABLE IF EXISTS dim_date;\nCREATE TABLE dim_date (\n date_key INTEGER PRIMARY KEY,\n full_date TEXT NOT NULL UNIQUE,\n calendar_year INTEGER NOT NULL,\n calendar_month INTEGER NOT NULL,\n day_of_month INTEGER NOT NULL,\n day_name TEXT NOT NULL\n);\nCREATE TABLE dim_customer (\n customer_key INTEGER PRIMARY KEY,\n customer_id TEXT NOT NULL UNIQUE,\n customer_name TEXT NOT NULL,\n segment TEXT NOT NULL,\n region TEXT NOT NULL\n);\nCREATE TABLE dim_product (\n product_key INTEGER PRIMARY KEY,\n product_id TEXT NOT NULL UNIQUE,\n product_name TEXT NOT NULL,\n category TEXT NOT NULL\n);\nCREATE TABLE dim_store (\n store_key INTEGER PRIMARY KEY,\n store_id TEXT NOT NULL UNIQUE,\n store_name TEXT NOT NULL,\n store_type TEXT NOT NULL\n);\nCREATE TABLE fact_sales (\n sales_key INTEGER PRIMARY KEY,\n date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n quantity INTEGER NOT NULL CHECK (quantity > 0),\n unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),\n extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0),\n source_batch_id TEXT NOT NULL,\n UNIQUE(order_id, line_no)\n);\nINSERT INTO dim_date VALUES\n(20260918,'2026-09-18',2026,9,18,'Friday'),\n(20260919,'2026-09-19',2026,9,19,'Saturday'),\n(20260920,'2026-09-20',2026,9,20,'Sunday');\nINSERT INTO dim_customer VALUES\n(0,'__UNKNOWN__','Unknown customer','Unknown','Unknown'),\n(101,'C001','Ada Retail','SMB','North'),\n(102,'C002','Ben Home','Consumer','West'),\n(103,'C003','Cyra Labs','Enterprise','East'),\n(104,'C004','Dara Studio','Consumer','South');\nINSERT INTO dim_product VALUES\n(0,'__UNKNOWN__','Unknown product','Unknown'),\n(201,'P100','Keyboard','Accessories'),\n(202,'P200','Mouse','Accessories'),\n(203,'P300','Monitor','Displays'),\n(204,'P400','Dock','Accessories');\nINSERT INTO dim_store VALUES\n(0,'__UNKNOWN__','Unknown selling location','Unknown'),\n(301,'S-WEB','Web Store','Digital'),\n(302,'S-MOBILE','Mobile Store','Digital'),\n(303,'S-SALES','Assisted Sales','Assisted');\nINSERT INTO fact_sales VALUES\n(1,20260918,101,201,301,'O1001',1,2,50,100,'BATCH-CH03-001'),\n(2,20260918,101,202,301,'O1001',2,1,25,25,'BATCH-CH03-001'),\n(3,20260918,102,203,302,'O1002',1,1,200,200,'BATCH-CH03-001'),\n(4,20260919,101,204,301,'O1003',1,1,100,100,'BATCH-CH03-001'),\n(5,20260919,101,202,301,'O1003',2,2,25,50,'BATCH-CH03-001'),\n(6,20260920,104,201,302,'O1005',1,1,50,50,'BATCH-CH03-001'),\n(7,20260920,104,204,302,'O1005',2,1,100,100,'BATCH-CH03-001');")conn.executescript("DROP TABLE IF EXISTS fact_inventory_snapshot;\nCREATE TABLE fact_inventory_snapshot (\n snapshot_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n on_hand_units INTEGER NOT NULL CHECK (on_hand_units >= 0),\n source_batch_id TEXT NOT NULL,\n PRIMARY KEY (snapshot_date_key, product_key)\n);\nINSERT INTO fact_inventory_snapshot VALUES\n(20260919,201,42,'INV-20260919'),\n(20260919,202,63,'INV-20260919'),\n(20260919,203,13,'INV-20260919'),\n(20260919,204,28,'INV-20260919'),\n(20260920,201,40,'INV-20260920'),\n(20260920,202,60,'INV-20260920'),\n(20260920,203,12,'INV-20260920'),\n(20260920,204,25,'INV-20260920');")daily=conn.execute("""SELECT snapshot_date_key,SUM(on_hand_units)FROM fact_inventory_snapshot GROUP BY snapshot_date_key ORDER BY snapshot_date_key""").fetchall()print("daily totals =", daily)assert daily == [(20260919,146),(20260920,137)]wrong=conn.execute("SELECT SUM(on_hand_units) FROM fact_inventory_snapshot").fetchone()[0]assert wrong == 283latest=conn.execute("""SELECT SUM(on_hand_units) FROM fact_inventory_snapshotWHERE snapshot_date_key=(SELECT MAX(snapshot_date_key) FROM fact_inventory_snapshot)""").fetchone()[0]assert latest == 137avg=sum(v for _,v in daily)/len(daily)assert avg == 141.5print("wrong_sum=",wrong,"ending=",latest,"avg_daily=",avg)print("semi-additive assertions: PASS")Verification: the test must intentionally prove that the wrong sum is 283 while the governed ending balance remains 137. That difference is the lesson.
6. Production judgment and bridge
Do not encode “SUM” as the universal default for every numeric fact in a BI layer. Snapshot measures need time-aware semantics, complete-period assumptions, and explicit handling for sparse observations. A dashboard that silently sums balances over months can be perfectly fast and perfectly wrong.
The next lesson generalizes the same principle to ratios, percentages, averages, and distinct counts, where aggregation must be reconstructed from atomic components or entity sets.
Knowledge check
Check your understanding
- Why is on_hand_units semi-additive rather than fully additive?
- What is the grain of fact_inventory_snapshot?
- What does 283 represent in the two-day fixture?
- How do you compute the ending balance?
- Can AVG(on_hand_units) over all product rows automatically be called average inventory?
Review the answers
1. It can be summed across products within one snapshot, but summing repeated balances across dates double counts state.
2. One row per product per inventory snapshot date.
3. The arithmetic sum of both days’ product balances; it is deliberately not a valid point-in-time inventory metric.
4. Select the appropriate last snapshot for the period, then sum balances across the required non-time dimensions.
5. Not without a definition. You must specify whether the intended metric is an average of daily totals, per-product balances, time-weighted states, or another policy.
Summary and next step
AtlasMart’s September 20 inventory control remains 137 units. The added September 19 snapshot shows why repeated states require a temporal aggregation rule rather than a blind SUM.
Next: Ratios, Percentages, Averages, Distinct Counts, and Other Non-Additive Metrics.
Authoritative references
- Kimball Group — Periodic Snapshot Fact Tables — Periodic-snapshot grain and repeated period rows.
- Kimball Group — Additive, Semi-Additive, and Non-Additive Facts — Definitions and the guidance to retain additive components for derived non-additive facts.
- Kimball Group — Fact Table Structure — Fact-table grain and measurement-event framing.
- SQLite — Aggregate Functions — Exact behavior of SUM, AVG, COUNT, and DISTINCT in the local execution harness.
- Python sqlite3 documentation — Standard-library API used by the deterministic labs.