Comparison
Periodic snapshotvsTransaction fact table
Periodic snapshot
you write one row per account per day whether anything happened or not, so 'what was the balance on the fourteenth' is a lookup rather than a replay.
A fact table recording the state of things at regular intervals, regardless of activity. It exists because some questions are about levels rather than events, and reconstructing a balance by summing every transaction since the beginning of time is both slow and fragile. The cost is that the table's size is driven by the number of entities times the number of days, not by how much actually happened.
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 →