Periodic Snapshot Fact Tables

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 Structure and Grain

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:

• Transactional Tables are Sparse: Rows exist only when activity occurs.
• Periodic Snapshots are Dense: Rows exist for every time period, regardless of activity.

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.

Semi-Additive Measures

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.

$Total\_Sales = \sum_{all\_dimensions} sales\_amount$

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.

• Across Customers (Additive): You can sum the balances of all customers on January 1st to get the total bank liability.
• Across Time (Non-Additive): You cannot sum the balance of January 1st and January 2nd. If you have $100 on Monday and $100 on Tuesday, you do not have $200.

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:

$Average\_Inventory = \frac{\sum_{d=1}^{n} Daily\_Count_d}{n}$

Implementation Strategy

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).

1. Identify the Grain: Determine the necessary frequency. Daily snapshots are common for fast-moving retail, while monthly snapshots may suffice for financial ledger balances.
2. Handle Late Arriving Data: Periodic snapshots assume the state is final at the time of extraction. If a transaction for January 31st arrives on February 2nd, you must decide whether to update the January 31st snapshot row. In strict accounting systems, the snapshot might remain static, with the correction applied to the current period. In analytical systems, you often update the historical snapshot to reflect reality.
3. Storage Optimization: Because these tables are dense, they grow rapidly. A snapshot of 1 million products taken daily generates 365 million rows per year. Partitioning this table by the date dimension is critical for query performance.

Fact Table Comparison

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.