Designing Periodic Snapshot Fact Tables

Build snapshot tables to capture state-based metrics at regular intervals, enabling precise trend analysis over specific reporting periods.

In this lesson, you will learn how to build Periodic Snapshot Fact Tables to capture the state of your business at fixed, regular intervals for effective trend analysis.

What is a Periodic Snapshot?

While transaction fact tables record individual events (like a single sale), a Periodic Snapshot Fact Table records the "state of things" at a specific point in time—such as the end of a day, month, or quarter. Think of this like taking a photograph of your business dashboard at midnight every day; you aren't capturing the individual clicks or changes, but rather the total summary of the status at that moment.

Why use them?

These tables are essential for answering "How did our balances change over time?" questions. They provide a dense, predictable grain that makes calculating performance trends—like month-over-month account growth—trivial because the data is already aggregated and aligned by period.

Design Pattern

A periodic snapshot typically includes:

Time Dimension Key: Identifying the specific period (e.g., date_key).
Dimension Keys: References to the entities being measured (e.g., account_key, product_key).
Performance Metrics: The state-based values (e.g., account_balance, current_inventory_count).

For example, if you want to track daily account balances, your table structure might look like this:

CREATE TABLE fact_account_daily_snapshot (
    date_key INT,
    account_key INT,
    branch_key INT,
    ending_balance_amt DECIMAL(18, 2),
    overdraft_fee_accrued_amt DECIMAL(18, 2)
);

Implementing the Snapshot

Unlike transaction tables that populate whenever a row is inserted in the source system, a periodic snapshot is populated via an ETL process that triggers on a schedule (e.g., 12:00 AM daily). Your ETL job queries the current state of your dimensions and operational tables and inserts a single row per entity into the snapshot table. If an account has no activity, you still record the balance, effectively "stamping" the state for that period to ensure your trend reports have no gaps in the timeline.

Key Takeaway: Periodic Snapshot Fact Tables transform a series of individual events into a reliable, time-series summary, allowing you to easily report on status-based trends across fixed reporting intervals.