Comparison
Accumulating snapshotvsPeriodic snapshot
Accumulating snapshot
the row for an order is updated as it moves through picked, packed and shipped, so each stage's date is a column.
One row per instance of a multi-step process, updated in place as the process reaches each milestone. It makes duration questions trivial — time from order to dispatch is a subtraction rather than a self-join — at the cost of being the one fact table that gets updated, which is awkward on append-only storage. It is the right shape whenever the business talks about a pipeline with named stages.
Full entry →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 →