Comparison
Periodic snapshotvsSemi-additive measure
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 →Semi-additive measure
you summed the daily account balances across the month and got a number thirty times too big.
A measure that can be added across some dimensions but not others — a balance sums across accounts but not across days, a ratio sums across nothing. It is the reason a snapshot table needs documentation about which aggregations are legal, and it is one of the most common sources of a confidently wrong dashboard. Marking measures as additive, semi-additive or non-additive in the model is the cheap prevention.
Full entry →