Comparison
Factless fact tablevsTransaction fact table
Factless fact table
the table records that a student attended a class, with no numbers on it at all, and the only measure is counting rows.
A fact table with keys and no numeric measures, recording that something happened or that a relationship was in force. It answers 'how many' and, more usefully, 'what did not happen' — the promotions with no sales, the students who did not attend — by comparing coverage against events. It confuses people because it looks empty, and it is one of the few clean ways to make an absence queryable.
Full entry →Transaction fact table
one row per thing that happened, inserted and never touched again, and the table only ever grows.
A fact table at the grain of an individual business event, written once at the moment it occurs. It is the most flexible shape — anything can be aggregated up from it — and the most expensive to query at scale, which is why aggregates get built on top. Being append-only is its operational advantage: it partitions by time cleanly and it never needs updating in place.
Full entry →