Differentiating Transactional and Snapshot Fact Tables

Evaluate the distinct analytical use cases for atomic event logging versus aggregated state monitoring in dimensional data structures.

In this lesson, you will learn how to choose between recording individual business events versus capturing the periodic state of your data.

When building a dimensional model, you must decide how to represent change. We do this by choosing between two primary fact table types: Transactional Fact Tables and Snapshot Fact Tables.

The Transactional Fact Table: "The Event Logger"

A transactional fact table records the "atomic" events of your business—the things that happen at a specific point in time. Each row represents a single occurrence.

Best for: Answering questions like "What did this customer buy in this specific order?" or "At what time did this sensor register a temperature change?"
Structure: It contains granular facts, such as quantity_sold, discount_amount, or transaction_id.

-- Example: Sales Transaction Fact
-- Each row is a unique receipt line item.
CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    customer_key INT,
    transaction_id INT, -- Degenerate dimension
    quantity INT,
    extended_price DECIMAL(10,2)
);

The Snapshot Fact Table: "The State Monitor"

A snapshot fact table records the state of something at a regular interval (like daily or monthly). Instead of logging every event, it captures a "picture" of the business status at the end of a cycle.

Best for: Answering questions like "How many items were in inventory at the end of the day?" or "What was the total account balance at month-end?"
Structure: These tables often store "semi-additive" or non-additive facts, like a balance, because you cannot simply sum a snapshot of an account balance across different days to get a meaningful total.

-- Example: Daily Inventory Snapshot
-- Each row shows the quantity on hand for a product at the end of the day.
CREATE TABLE fact_inventory_snapshot (
    date_key INT,
    product_key INT,
    warehouse_key INT,
    quantity_on_hand INT -- The state at the end of the day
);

How to Decide?

The choice depends entirely on the analytical use case. If you need to trace the history of every movement, use a transactional table. If you need to report on the current status or trend over time without processing millions of individual events, use a snapshot table.

Key Takeaway: Use transactional fact tables for granular event auditing and snapshot fact tables for periodic state tracking and cumulative analysis.