Snapshot Facts And Periodic Snapshots

Why this matters

Periodic snapshots turn volatile, transactional data into stable, easy-to-query facts. As an Analytics Engineer, you will routinely create daily, weekly, or monthly snapshot fact tables to report: daily inventory levels, month-end ARR/MRR, weekly pipeline by stage, headcount by department, balances by account, or SLA statuses.

• They simplify BI: one row per period per grain, no complex re-aggregation.
• They support consistent trend lines and time comparisons.
• They de-risk late-arriving data by rebuilding historical periods deterministically.

Concept explained simply

Think of a periodic snapshot like a camera taking a picture of your metrics at a fixed interval (daily, weekly, monthly). Each period has exactly one record per grain (e.g., product-day, account-month).

How it fits in dimensional modeling:

• Transaction fact: one row per event (e.g., each order).
• Periodic snapshot fact: one row per period capturing state (e.g., inventory level at end of day).
• Accumulating snapshot fact: one row per lifecycle tracking milestones (e.g., order to delivery).

Mental model

Camera, not a CCTV stream. You decide the frame cadence (periodicity) and the subject (grain). Then you compute measures as-of the snapshot moment and store them. Queries are then as simple as filtering by period and grouping.

Design the grain

• Grain: The most important decision. Examples: product-day, account-month, region-week-stage.
• Periodicity: Choose based on reporting cadence and data latency: daily for operations, weekly for pipeline, month-end for finance.
• As-of moment: end_of_day, start_of_week (Monday), last_calendar_day_of_month, fiscal_month_end.
• Dimensions: Date, product, customer, region, stage. Use Type 2 SCDs when you need the historical attribute as-of the snapshot date.
• Measures: Design additive or semi-additive. Many snapshot measures are semi-additive (additive across dimensions but not across time). Example: inventory_level is not additive across days.

Worked examples

Example 1 — Daily inventory snapshot (product-day)

Goal: One row per product per calendar day with end_of_day on-hand quantity.

-- Date spine (calendar) for the snapshot range
WITH date_spine AS (
  SELECT d::date AS snapshot_date
  FROM generate_series('2024-01-01'::date, '2024-01-31'::date, interval '1 day') AS s(d)
),
-- Inventory movements: +inbound, -outbound
movements AS (
  SELECT product_id,
         occurred_at::date AS d,
         SUM(qty_change) AS delta
  FROM inventory_tx
  GROUP BY 1,2
),
-- Cumulative end-of-day balance by product
balances AS (
  SELECT product_id,
         d,
         SUM(delta) OVER (PARTITION BY product_id ORDER BY d
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS end_qty
  FROM movements
),
-- Ensure each product has a value for every date via forward-fill
products AS (
  SELECT DISTINCT product_id FROM inventory_tx
),
snapshot AS (
  SELECT s.snapshot_date,
         p.product_id,
         -- Latest known balance up to the snapshot date; defaults to 0 if no prior
         COALESCE(
           MAX(b.end_qty) FILTER (WHERE b.d <= s.snapshot_date),
           0
         ) AS inventory_level
  FROM date_spine s
  CROSS JOIN products p
  LEFT JOIN balances b
    ON b.product_id = p.product_id
   AND b.d <= s.snapshot_date
  GROUP BY 1,2
)
SELECT * FROM snapshot;

Notes: Forward-filling creates a complete picture per day. Inventory level is semi-additive across products but not across days (do not sum daily levels over a month).

Example 2 — Monthly subscription snapshot (account-month)

Goal: Month-end active_accounts, total_MRR.

-- Month-end spine
WITH months AS (
  SELECT (date_trunc('month', d) + interval '1 month - 1 day')::date AS month_end
  FROM generate_series('2024-01-01'::date, '2024-12-31'::date, interval '1 day') AS s(d)
  GROUP BY 1
),
-- Subscription intervals constructed from events
subs AS (
  -- Example structure: account_id, start_date, end_date (nullable), mrr
  SELECT account_id, start_date, end_date, mrr FROM subscription_intervals
),
status_asof AS (
  SELECT m.month_end,
         s.account_id,
         s.mrr
  FROM months m
  JOIN subs s
    ON s.start_date <= m.month_end
   AND (s.end_date IS NULL OR s.end_date > m.month_end)
),
agg AS (
  SELECT month_end,
         COUNT(DISTINCT account_id) AS active_accounts,
         SUM(mrr) AS total_mrr
  FROM status_asof
  GROUP BY 1
)
SELECT * FROM agg ORDER BY month_end;

Notes: The as-of join uses the interval [start_date, end_date). Summaries are one row per month.

Example 3 — Weekly pipeline snapshot (stage-week)

Goal: Monday-start-of-week snapshot of pipeline_amount by stage.

-- Week spine (Mondays)
WITH weeks AS (
  SELECT date_trunc('week', d)::date AS week_start
  FROM generate_series('2024-04-01'::date, '2024-06-30'::date, interval '1 day') AS s(d)
  GROUP BY 1
),
-- Deals are SCD2: each row valid_from, valid_to, stage, amount
scd_deals AS (
  SELECT deal_id, stage, amount, valid_from::date AS vf, COALESCE(valid_to::date, '2999-12-31') AS vt
  FROM deals_scd2
),
asof AS (
  SELECT w.week_start,
         d.deal_id,
         d.stage,
         d.amount
  FROM weeks w
  JOIN scd_deals d
    ON d.vf <= w.week_start
   AND d.vt > w.week_start
),
by_stage AS (
  SELECT week_start,
         stage,
         SUM(amount) AS pipeline_amount
  FROM asof
  GROUP BY 1,2
)
SELECT * FROM by_stage ORDER BY week_start, stage;

Notes: Join to SCD2 on validity range for correct historical stage at week_start.

Implementation steps (repeatable)

-- Example: daily spine between two dates
SELECT d::date AS date_day
FROM generate_series('2024-01-01'::date, '2024-12-31'::date, interval '1 day') AS s(d);

-- Forward-fill pattern (BigQuery/Databricks/others with IGNORE NULLS)
SELECT date_day, product_id,
       LAST_VALUE(end_qty IGNORE NULLS) OVER (
         PARTITION BY product_id ORDER BY date_day
       ) AS end_qty_ff
FROM daily_balances_gapped;

Exercises

Complete these practical tasks. You can check solutions at the end of each exercise. Your progress is saved if you are logged in; otherwise you can still complete everything for free.

Exercise 1 — Daily inventory snapshot

Given table inventory_tx(product_id, occurred_at, qty_change), create a fact_inventory_snapshot with columns: snapshot_date, product_id, inventory_level. The snapshot_date should be daily; inventory_level is end-of-day balance with forward-fill and default 0 before the first movement.

Exercise 2 — Month-end MRR snapshot

You have subscription_intervals(account_id, start_date, end_date, mrr). Build a fact_mrr_monthly snapshot with month_end, active_accounts, total_mrr. Use an as-of join with [start_date, end_date) semantics.

Common mistakes and self-check

• Mistake: Summing balances across time. Fix: Use last day of period or average balance; document semi-additivity.
• Mistake: Undefined grain. Fix: Write it as a sentence: "One row per [entity] per [period]."
• Mistake: Missing days/weeks. Fix: Always join to a date spine and forward-fill when appropriate.
• Mistake: Wrong historical attributes. Fix: Join SCD2 on validity range that includes the snapshot moment.
• Mistake: Late-arriving data not reprocessed. Fix: Make builds idempotent and backfill affected periods.
• Mistake: Time zone drift. Fix: Convert to a canonical timezone before truncating to day/week/month.

Practical projects

• Retail: Daily product price snapshot with min/max/list price and promo flags.
• People Analytics: Weekly headcount snapshot by department, location, and employment type.
• Marketing: Monthly spend snapshot by channel with cost, clicks, impressions, and CPM/CPC.

Who this is for

• Analytics Engineers who build stable, reusable BI datasets.
• Data Analysts who need consistent trend reporting.
• Data Modelers defining star schemas for time-based reporting.

Prerequisites

• Solid SQL window functions and date functions.
• Understanding of star schemas and fact types (transaction, periodic snapshot, accumulating).
• Basic knowledge of SCD Type 2 for historical dimensions.

Learning path

1. Review fact types and grain definition.
2. Build a date/month spine and practice as-of joins.
3. Create a simple daily snapshot (inventory or balances).
4. Add SCD2-aware attributes to the snapshot.
5. Harden for production: backfills, partitioning, and documentation.

Next steps

• Automate backfills for late data by period.
• Add data quality tests: completeness by period and monotonic period coverage.
• Expose the snapshot in BI with certified definitions and clear metric guidance.

Mini challenge

Design a weekly product availability snapshot for an e-commerce site. Define the grain, periodicity, as-of moment, required dimensions, and at least three measures. Write a short paragraph describing how you will forward-fill missing data and handle products that go out of catalog.

Quick Test

Available to everyone for free. Only logged-in users will have their progress saved.