Types of Fact Tables
Concepts covered: Fact Tables, Metric Additivity
Ridgeline runs 3 business processes against the same dimensions. Sales asks what happened. Inventory asks what the level was on a given day. Fulfilment asks how long each stage of an order took. Each question maps to a type of fact table, and a table built for 1 answers the others slowly or not at all.
Transaction facts
A transaction fact holds 1 row per discrete event, written once and never updated. fact_sales is 1. A return is a new row with a negative quantity rather than an update, so the table stays append-only and reloading any day's partition reproduces the same rows. The measures are fully additive: quantity and line_amount sum across customers, products, days and channels, and the total always means something.
| order_id | line_seq | product_sk | quantity | line_amount |
|---|---|---|---|---|
| 50218 | 1 | 41077 | 1 | 299.99 |
| 50218 | 2 | 41530 | 1 | 109.99 |
| 50218 | 3 | 43902 | 1 | 79.99 |
| 50219 | 1 | 61204 | 1 | 44.99 |
| 50231 | 1 | 41077 | -1 | -299.99 |
The last row returns the tent from order 50218 as its own event. What a transaction fact cannot answer is a question about state. fact_inventory_movements records every receipt, shipment and adjustment as a row with a signed quantity_delta, so units on hand for a product on 3 March is the sum of every movement since the warehouse opened, and a dashboard of 60 days of stock levels replays 3 years of movements 60 times.
Periodic snapshot facts
A periodic snapshot holds 1 row per entity per period, capturing the state at the close of the period. fact_inventory_daily has the grain of 1 row per product per warehouse per day: 38,000 products across 6 warehouses produce 228,000 rows every night, whether or not anything moved. The 3 March question becomes 1 lookup. Rows are never updated; the next night writes a new set.
- 83.2M
The measure units_on_hand is semi-additive: it sums across some dimensions and not others. Adding the 6 warehouses for a product on 1 day gives company-wide stock that day, a number a buyer can act on. Adding the same product across 30 days describes nothing, because each day's row already includes the units carried over from the day before.
| sku | warehouse | snapshot_date | units_on_hand |
|---|---|---|---|
| RL-4471 | Denver | 2026-03-01 | 131 |
| RL-4471 | Denver | 2026-03-02 | 125 |
| RL-4471 | Denver | 2026-03-03 | 128 |
| RL-4471 | Reno | 2026-03-03 | 84 |
Summing the 2 warehouses on 3 March gives 212 units, a real figure. Summing the 3 Denver rows gives 384, which counts a unit that sat on the shelf all 3 days 3 times. Across time the valid aggregates are the last day's value, the period average or the minimum, and the metric's written definition fixes which one applies.
Accumulating snapshot facts
An accumulating snapshot holds 1 row per instance of a process with a defined start and end. fact_order_fulfilment has 1 row per order, created at placement and updated as each milestone arrives. Each milestone is a date key, NULL until it happens, and the row carries the carrier_sk that shipped the order. Lag columns such as days_to_deliver, counted from placement, are computed in the load, so a lifecycle report never redoes date arithmetic across 325 million orders. This is the 1 fact type whose rows change after they are written, so its load is a merge rather than an append.
| order_id | placed_date_sk | shipped_date_sk | delivered_date_sk | days_to_deliver |
|---|---|---|---|---|
| 50218 | 20260902 | 20260903 | 20260906 | 4 |
| 50219 | 20260902 | 20260904 | NULL | NULL |
| 50231 | 20260904 | NULL | NULL | NULL |
Factless facts
A factless fact table has keys and no measures. fact_promotion_coverage holds 1 row per product per promotion per day on promotion, and the row itself is the fact. COUNT(*) is the metric. It answers questions about what did not happen: promoted products that sold nothing, found by left-joining coverage to fact_sales and keeping the rows with no match.
| Type | One row per | Rows updated | Measures | Answers |
|---|---|---|---|---|
| Transaction | 1 event, such as a line item sold | never | additive | what happened |
| Periodic snapshot | 1 entity per period | never | semi-additive | the level on a date |
| Accumulating snapshot | 1 process instance | at each milestone | durations between milestones | how long each stage took |
| Factless | 1 coverage or relationship | never | none, COUNT(*) | what did and did not happen |
The 4 types are complementary. Ridgeline's sales, inventory and fulfilment facts share the same dimensions, and a question goes to the table whose grain matches its shape.
The operations team wants the median number of days from order placement to delivery, by carrier, for orders placed in September 2026. Which table should the query read?
- fact_sales, filtered to September order dates.
- A transaction fact of carrier scan events, 1 row per scan.
- fact_order_fulfilment, grouping days_to_deliver by carrier_sk.
- fact_inventory_daily, joined to a carrier dimension.
About this lesson
This section is part of the Star Schemas lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.