Transactional fact tables are excellent for recording specific events like a website click, a product sale, or a bank deposit. However, they are inefficient at answering questions about the state of a system. If a business analyst asks, "What was the total inventory value for Product X across all warehouses on March 31st?", answering this using only transactional data requires summing every stock-in and stock-out event from the beginning of time up to March 31st. As historical data grows, this calculation becomes computationally expensive and slow.
The Periodic Snapshot Fact Table resolves this performance bottleneck. Instead of recording every discrete event, this table type samples the status of an entity at regular, predictable intervals. It works like a high-speed camera taking a photo of your business processes at the end of every day, week, or month.
The defining characteristic of a periodic snapshot is its grain. While a transactional grain is defined by "one row per event", the periodic snapshot grain is defined as "one row per period per entity".
Consider a daily inventory snapshot. The grain is defined by the combination of the date and the product (and likely the warehouse). Even if a product had no sales or restocking events on that day, a row is still generated to record that the inventory level remained unchanged.
This structural difference creates a fundamental distinction in data density:
Because snapshots are dense, they allow for immediate reporting on status without complex re-calculations. You simply query the specific date required.
Diagram illustrating the transformation from sparse transactional events to a dense periodic snapshot. Note that the snapshot table generates rows even for days with no activity to preserve the state.
The most important technical consideration when designing periodic snapshots is the behavior of the measures (numerical facts) within them. In transactional tables, measures like quantity_sold or sales_amount are usually fully additive. You can sum them across all dimensions.
In periodic snapshots, measures are often semi-additive. This means they can be summed across some dimensions, but not all. The most common dimension where summation fails is Time.
Take a bank account balance as an example.
When aggregating semi-additive measures in SQL, you must use functions other than SUM() for the time dimension. Common aggregations include AVG(), MIN(), MAX(), or LAST_VALUE().
For example, to analyze inventory performance, you might calculate the average daily inventory for the month:
When implementing these tables in a data warehouse, you will typically rely on a scheduled ETL (Extract, Transform, Load) job that runs after the close of the business period (e.g., midnight).
To select the right pattern, compare the primary question the business user needs to answer:
| Feature | Transactional Fact | Periodic Snapshot Fact |
|---|---|---|
| Primary Question | "What happened?" | "How much do we have?" |
| Data Density | Sparse (rows only on events) | Dense (rows for every period) |
| Time Dimension | Specific timestamp of event | End-of-period date |
| Measure Type | Mostly Additive (e.g., Quantity Sold) | Semi-Additive (e.g., Balance, Level) |
| Volume | Proportional to activity | Proportional to (Time × Dimensions) |
By layering a Periodic Snapshot Fact table on top of your foundational data models, you provide analysts with a high-performance structure for trending and status reporting, eliminating the need for expensive on-the-fly calculations.
Was this section helpful?
© 2026 ApX Machine LearningEngineered with