Implementing Periodic Snapshot Fact Tables

In this lesson, you will learn how to build Periodic Snapshot Fact Tables to track the state of your business at fixed, predictable intervals.

Why Do We Need Snapshots?

Up until now, you have mostly worked with Transaction Fact Tables, which record individual events (like a single sale). However, business leaders often ask questions like, "What was our total inventory value at the end of every month?" or "What was the balance of every customer account at the start of each day?"

A Transaction Fact Table is terrible for this because you would have to calculate a running sum across millions of historical transactions to find the answer. A Periodic Snapshot Fact Table solves this by capturing a "photo" of the business status at regular intervals (daily, weekly, or monthly), regardless of whether any transactions occurred.

How It Works

Instead of recording an action, a snapshot table records the state of an entity at a specific point in time.

Imagine a bank account. Instead of storing every withdrawal, we store the account balance once a day. If you check the table for 2023-10-31, you see the final balance for that day.

-- Structure of a Periodic Snapshot Fact Table
CREATE TABLE fact_account_monthly_snapshot (
    snapshot_date_key INT,      -- Date of the snapshot
    account_key INT,            -- Surrogate key to the Account dimension
    branch_key INT,             -- Surrogate key to the Branch dimension
    month_ending_balance DECIMAL(18, 2), -- The "state" of the account
    total_deposits_amount DECIMAL(18, 2),-- Aggregated metric
    total_withdrawals_amount DECIMAL(18, 2)
);

Key Implementation Rules

1. Defined Interval: You must choose a grain, such as "monthly" or "daily," and stick to it rigidly.
2. Dense Data: Even if an account has zero activity during the month, you still insert a row into the snapshot table to represent the balance at that time.
3. Additive Facts: Because the state is pre-aggregated, these tables are excellent for trend reporting. You can easily compare the month_ending_balance from January to February to visualize growth.

By moving from tracking events to tracking snapshots, you transform your data warehouse from a "ledger of actions" into a "library of trends."

Key Takeaway: Periodic Snapshot Fact Tables record the state of business entities at regular intervals, enabling efficient trend analysis without the need to re-process historical transactions.