Periodic Snapshot vs Additive
Periodic snapshot fact and additive fact are different concepts, although they may appear similar at first glance.
A periodic snapshot fact table is designed to capture a point-in-time snapshot of some measure at regular intervals, typically periodically, such as daily, weekly, monthly, or quarterly.
An additive fact represents a measure that can be aggregated across all dimensions in a fact table. For example, sales revenue is an additive fact because it can be summed up across all dimensions such as time, product, region, etc.
| Additive Fact | Periodic Snapshot Fact | |
|---|---|---|
| Definition | Represents a measure that can be aggregated across all dimensions in a fact table | Captures a point-in-time snapshot of some measure at regular intervals |
| Granularity | Can be at any level of granularity | Usually at a higher level of granularity such as daily, weekly, monthly, or quarterly |
| Aggregation | Can be summed up across all dimensions in the fact table | Captures the total value of the measure at a specific point in time |
| Example | Sales revenue, profit, number of clicks on a banner ad | Monthly sales revenue, quarterly website visits, daily customer support tickets |