Who this is for
Data Architects, Analytics Engineers, and Data Engineers who design warehouse models for reporting and KPI tracking. Useful if you own pipelines that monitor inventory, subscriptions, funnels, or process SLAs.
Prerequisites
- Know the basics of star schemas: fact vs dimension tables, surrogate keys, grain.
- Comfortable with SQL joins and date handling.
- Familiar with transaction fact tables (helpful, not mandatory).
Why this matters
Real business questions often need state-over-time and funnel analytics, not just single transactions. Examples:
- How many tickets were open yesterday by priority? What is the 7-day SLA breach rate?
- What was on-hand inventory each day, and how many days of cover do we have?
- Where do orders stall in the fulfillment pipeline, and how long does each step take?
Periodic Snapshot and Accumulating Snapshot fact tables are the standard dimensional patterns that make these questions fast, consistent, and auditable.
Concept explained simply
Periodic Snapshot Fact
A periodic snapshot captures the state at regular intervals (daily, weekly, monthly).
- Grain: one row per entity per period (e.g., product-warehouse per day).
- Measures: often semi-additive (sum across entities, not across time) such as inventory balance, active subscribers, open tickets.
- Use when you need time-series of states and rates (stock levels, daily MRR, backlog counts).
Accumulating Snapshot Fact
An accumulating snapshot tracks one business process from start to finish in a single row with milestone dates.
- Grain: one row per process instance (e.g., order, ticket, application).
- Columns: key milestones (submitted_date_key, approved_date_key, shipped_date_key, etc.), current_status, cycle time metrics.
- Use for funnels and SLA measurements where you need durations and step conversions.
Mental model
- Periodic Snapshot: a calendar grid. Every cell is a measurement of state at that time for that entity.
- Accumulating Snapshot: a traveler’s passport. One row gets stamped with milestone dates as the instance progresses.
Choosing the right fact type
- Need daily/weekly history of a changing state (balances, counts, rates)? Use a periodic snapshot.
- Need to analyze a pipeline with clear steps and durations, one row per instance? Use an accumulating snapshot.
- Need both? It’s common to have a transaction fact, a periodic snapshot, and an accumulating snapshot for the same domain.
Grain and keys
Periodic Snapshot Grain
- Example: one row per (date_key, product_key, warehouse_key).
- Surrogate keys for all conformed dimensions; date_key is the snapshot date.
- Measures can include: on_hand_qty, backorder_qty, open_orders_count, backlog_age_days.
Accumulating Snapshot Grain
- Example: one row per order_id_key (degenerate natural ID also okay as degenerate dimension alongside surrogate keys).
- Milestone date keys: order_date_key, payment_authorized_date_key, packed_date_key, shipped_date_key, delivered_date_key, canceled_date_key (nullable).
- Status: current_status, is_closed_flag.
- Derived metrics: days_to_ship, days_to_deliver, total_cycle_days.
Key behaviors and gotchas
- Semi-additive measures: You can sum across entities in a single day, but not across multiple days without care. For multi-day totals, prefer averages, last-non-null, or re-aggregate from transactions.
- Late-arriving facts: Periodic snapshot for day D should reflect the best-known state as of the close of D. Corrections may require restating snapshots for D or adding a restatement mechanism.
- Accumulating updates: Rows are updated as milestones occur. Track audit columns (inserted_at, last_updated_at) and consider change data capture to downstream systems.
- SCD interplay: Use surrogate keys for dimensions (Type 2). A snapshot row binds to the dimension version valid on the snapshot date (periodic) or at the time of each milestone (accumulating).
- Null milestones: For an open process, future milestones remain NULL. That’s expected and important for measuring in-progress stages.
Worked examples
1) Daily Inventory Snapshot
Grain: one row per (snapshot_date_key, product_key, warehouse_key).
Columns:
- Keys: snapshot_date_key, product_key, warehouse_key
- Measures: on_hand_qty, reserved_qty, backorder_qty, safety_stock_qty
- Derived: days_of_cover = on_hand_qty / avg_daily_demand_14d
Use cases: trend stockouts, compute service level, feed planning dashboards.
2) Monthly Subscription Snapshot
Grain: one row per (month_start_date_key, account_key)
Measures:
- mrr_amount, active_flag, seats_count, churn_risk_score
Notes: MRR sums across accounts in a month, but do not sum across months to get total revenue (use revenue facts or month-end last value logic).
3) Order Fulfillment Accumulating Snapshot
Grain: one row per order_key (order_id as degenerate dimension also included)
Milestones:
- order_date_key, payment_authorized_date_key, packed_date_key, shipped_date_key, delivered_date_key, canceled_date_key
Status and metrics:
- current_status, is_closed_flag
- days_to_pack, days_to_ship, days_to_deliver, total_cycle_days
Use cases: bottleneck analysis, SLA tracking, cohort by order month.
4) Hiring Pipeline Accumulating Snapshot
Grain: one row per candidate_application_key.
Milestones: applied_date_key, screened_date_key, interviewed_date_key, offer_date_key, accepted_date_key, start_date_key, rejected_date_key.
Metrics: days_from_applied_to_offer, conversion_rate per stage (via counts).
Design checklist
- Define the grain precisely in one sentence.
- List all required dimensions and confirm surrogate keys.
- Identify semi-additive measures and how they roll up.
- For accumulating: enumerate milestones and expected null behavior.
- Plan for late-arriving data and restatements.
- Add audit columns (inserted_at, last_updated_at, source_system).
Exercises
Note: The quick test works for everyone. Only logged-in users will have their progress saved.
Exercise 1: Choose the right fact type
Scenario: Support operations want daily trends of open tickets by priority and SLA breach count, plus a way to calculate the average time to resolution per ticket.
- Pick the appropriate fact type(s).
- Define the grain.
- List key columns.
Hint
- You may need both a periodic snapshot and an accumulating snapshot.
- Think about which table produces daily counts vs durations.
Exercise 2: Model an e-commerce order accumulating snapshot
Design the schema focusing on milestones and cycle-time metrics.
- Define the row grain.
- List milestone date keys (at least 5).
- Add status and derived metrics for durations.
Hint
- Include a degenerate order_id for user-friendly joins.
- Durations can be date_diff between milestones; some will be NULL.
Common mistakes and self-check
- Mixing grains: e.g., recording multiple product warehouses in one row. Self-check: One sentence grain rule still holds?
- Summing balances across time: e.g., adding daily inventory across a month. Self-check: Is the measure semi-additive? Use last value or average instead.
- Missing milestone definitions: Ambiguous timestamps cause inconsistent durations. Self-check: Is each milestone tied to a clear business event?
- Overwriting history: Updating periodic snapshots instead of restating or annotating. Self-check: Can you explain changes via audit columns?
- Forgetting nulls for future steps: Filling with fake dates breaks SLA math. Self-check: Are open items carrying NULLs for unmet milestones?
Practical projects
- Build a daily inventory snapshot table and a dashboard showing stockouts and days of cover trend.
- Create an order fulfillment accumulating snapshot and a report of median days to ship by warehouse.
- Implement a monthly subscription snapshot and calculate net MRR movement using cohorts.
Learning path
- Confirm transaction fact design for your domain (orders, tickets, subscriptions).
- Add periodic snapshot for state-over-time metrics.
- Add accumulating snapshot for pipeline milestones and durations.
- Introduce conformed dimensions and SCD handling for stable joins.
- Optimize with partitioning by date and surrogate keys.
Next steps
- Finish the exercises below and take the Quick Test.
- Apply the patterns to one real domain in your stack this week.
- Document grain, measures, and restatement policy for your snapshot tables.
Mini challenge
You manage a loan application process with steps: submitted, KYC completed, risk approved, funded, closed. Sketch an accumulating snapshot: define the grain, list milestones, add two duration metrics, and one status column. Then propose one periodic snapshot that complements it.