Snapshot Versus Transaction Facts

Who this is for

BI Analysts and data practitioners who design or query fact tables and need to choose between transaction facts and snapshot facts for reliable KPIs, trends, and diagnostics.

Prerequisites

• Basic SQL (SELECT, GROUP BY, JOIN)
• Understanding of dimensions (date, product, customer) and fact tables
• Comfort with defining business grain (the "one row = ?" rule)

Why this matters

Picking the wrong fact type leads to broken KPIs and confusing dashboards. In real BI work you will:

• Design a sales model that supports both daily revenue trends and order-level drilldowns
• Produce inventory at end-of-day without double counting shipments
• Report on subscription MRR and churn consistently month over month
• Build reliable funnels where stages move forward without inflating counts

Knowing when to use a transaction vs a snapshot fact avoids rework and trust issues with stakeholders.

Concept explained simply

Fact tables record numeric measures at a defined grain.

• Transaction fact: one row per business event (e.g., each order, payment, click). Ideal for drilldown, auditing, and event-rate metrics.
• Periodic snapshot fact: one row per entity per time period (e.g., inventory per product per day). Ideal for trending balances and states that exist at a point in time.
• Accumulating snapshot (related concept): one row per process instance (e.g., an opportunity) updated as it moves through milestones. Ideal for funnels and cycle-time metrics.

Choosing the right fact type

1. Define the question: Do we need events ("what happened and when?") or states/balances ("what was it on a given day?")?
2. Set the grain: Transaction = one row per event. Periodic snapshot = one row per entity per period (e.g., product-day).
3. Pick measures: Transaction = additive amounts (qty, revenue). Snapshot = semi-additive balances (add across products, not across time).
4. Validate with examples: Can you explain exactly one row using a sentence? If not, the grain is unclear.

Worked examples

1) E-commerce orders (Transaction fact)

Grain: one row per order line. Measures: quantity, line_revenue. Dimensions: date_time, customer, product, channel.

-- Example query: total revenue by day from transaction fact
SELECT order_date, SUM(line_revenue) AS revenue
FROM f_order_line
GROUP BY order_date;

Use when you need returns processing, discount analysis, and item-level drilldowns.

2) Daily inventory (Periodic snapshot)

Grain: one row per product per day at end-of-day. Measures: on_hand_qty (semi-additive), backorder_qty. Dimensions: date, product, warehouse.

-- Build snapshot from events (receipts, shipments)
WITH movements AS (
  SELECT product_id, DATE(event_ts) AS d,
         SUM(CASE WHEN type='RECEIPT' THEN qty ELSE -qty END) AS net_qty
  FROM f_inventory_movements
  GROUP BY product_id, DATE(event_ts)
), daily AS (
  SELECT product_id, d,
         SUM(net_qty) OVER (PARTITION BY product_id ORDER BY d ROWS UNBOUNDED PRECEDING) AS eod_on_hand
  FROM movements
)
SELECT d AS snapshot_date, product_id, eod_on_hand AS on_hand_qty
FROM daily;

Use when you need point-in-time balances and stockouts over time.

3) Subscription funnel (Accumulating snapshot, for contrast)

Grain: one row per subscription lifecycle. Measures: cycle_time_days, flags for each stage. Dimensions: customer, acquisition_source, dates for stages.

-- Read cycle time from accumulating snapshot
SELECT acquisition_source,
       AVG(DATEDIFF(day, created_at, activated_at)) AS avg_activation_days
FROM f_subscription_accum
WHERE activated_at IS NOT NULL
GROUP BY acquisition_source;

Here, accumulating snapshot prevents double counting across stages while retaining a single row per process.

Design checklist

• State your grain in one sentence.
• List required dimensions to reach that grain (date, product, customer, etc.).
• Decide measures and whether they are additive, semi-additive, or non-additive.
• Confirm how late-arriving data will be handled (reprocessing, late snapshots, or corrections).
• Validate with 3 real queries stakeholders need.

Exercises

Note: The quick test is available to everyone; only logged-in users will have their progress saved.

Exercise 1: Pick the right fact type for SaaS revenue

You have subscription invoices (each invoice has amount, customer_id, invoice_date, status). Stakeholders want reliable Monthly Recurring Revenue (MRR) and churn trend by month.

• Decide: transaction vs periodic snapshot vs both.
• Define the grain and measures.
• Sketch SQL for a monthly snapshot of MRR built from invoices.

Exercise 2: Build a daily orders snapshot from events

You have f_order_line with order_ts, order_id, customer_id, line_revenue. Create a daily snapshot with revenue_day and distinct_customers_day.

• Define the snapshot grain.
• Write SQL to populate the daily table from transactions.
• Explain how you handle late-arriving orders.

Common mistakes and self-check

• Mixing grains: Joining a transaction fact to a daily snapshot without careful aggregation causes duplicates. Self-check: after joins, does a simple COUNT(*) inflate?
• Summing semi-additive measures across time: Adding inventory across days inflates. Self-check: use MAX over time or report balances at a single point.
• Unstated grain: If you cannot explain one row in a sentence, the model will confuse users. Self-check: write the row sentence and verify with stakeholders.
• Missing date dimension in snapshots: Without a proper date dimension, time-series filters break. Self-check: ensure every snapshot row ties to a date key.
• No late data plan: Transaction late arrivals can alter snapshots. Self-check: document reprocess policy and verify reproducibility of yesterday’s snapshot.

Practical projects

• Retail: Build both a transaction fact for sales and a daily revenue snapshot. Compare answers to "What was revenue on 2025-03-01?"
• Inventory: Create a daily on-hand snapshot from movement events. Add stockout flag and average stockout duration by product.
• SaaS: Produce a monthly MRR snapshot and a separate transaction fact of invoices. Build a dashboard with churn, expansion, and net MRR.

Learning path

• Start with transaction facts for event correctness and drilldowns.
• Add periodic snapshots to support balances and trends.
• Introduce accumulating snapshots for funnels/cycle time.
• Harden the model: late data strategy, surrogate keys, partitions, and documentation.

Next steps

• Complete the exercises below and run the quick test.
• Refactor one existing dashboard to use the correct fact type.
• Document grain and measures for each fact in your team wiki.

Mini challenge

You have bank ledger transactions (debits/credits) and must show customer balance on any day and also show top merchants by monthly spend. What two fact structures do you create, and what is the grain of each?