Fact table types

From additivity, the types of fact table fall out.

Transaction fact

One row per event. The grain equals the event. Measures are additive. This is the default, and the one you should have a reason to leave.

Periodic snapshot

One row per entity per period (for example one row per account per day). Measures are semi-additive (balance, stock level).

It exists for two independent reasons:

Some stocks have no event stream at all: a measured tank level, a headcount from an HR system with no change log.
Others have an event stream, but deriving "balance on every day for three years" needs a running sum per entity over all history. That is cheap to compute once and expensive to compute for every query.

So the snapshot is a precompute. It trades storage that is linear in (entities × periods) for O(1) reads. Worked example: 2 million accounts × 1,095 days (three years) = 2.19 billion rows. If answering "balance on date D" by summing all events per account costs a 20-minute scan, and 50 people ask each day, that is 1,000 minutes of compute per day. A daily snapshot costs one extra day of rows (2 million) and about one minute to build. (These costs are illustrations.) The break-even is the same materialization arithmetic as for any precomputed result: number of reads times cost per read, against the cost to build and store.

Accumulating snapshot

One row per process instance, with a foreign key to a date for each milestone and lag measures between milestones. It is updated as the instance progresses.

order_id	ordered_date	paid_date	shipped_date	delivered_date	days_order_to_ship	days_ship_to_delivery
A-1	Mar 1	Mar 1	Mar 3	Mar 7	2	4
A-2	Mar 2	Mar 2	Mar 5	(empty)	3	(empty)

It exists because the question is about latency between events ("how long does an order wait between payment and shipping?"), which no single event row can answer. The alternative is a self-join of the transaction fact across milestone pairs.

Note the anomaly: this is the one fact type that mutates. That puts it directly at odds with append-only storage and with immutable table formats (formats that never rewrite data files in place). The honest framing is that an accumulating snapshot is a materialized view over an event stream. Its popularity is inversely proportional to how cheaply your engine does stateful or as-of computation. If your engine can easily compute "time between two events of the same order", you may not need one.

Factless fact

A factless fact table has rows that record that something occurred or was eligible, with no measure. Examples: students enrolled in courses, promotions offered to customers, insurance coverage relationships. The measure is simply COUNT(*).

The important use is the negative question: which eligible customers received no offer?

SELECT c.customer_key
FROM fact_promo_eligibility e                    -- who COULD have received the promo
JOIN dim_customer c ON c.customer_key = e.customer_key
LEFT JOIN fact_promo_sent s
       ON s.customer_key = e.customer_key AND s.promo_key = e.promo_key
WHERE e.promo_key = 17 AND s.customer_key IS NULL;

This needs the coverage table, because absence cannot be queried from an event table. No row in the sent table can say "this customer was eligible but was not sent anything".