Periodic Snapshots for Inventory, Account Balances, Daily State, and Semi-Additive Metrics

Learning outcomes

AtlasMart’s inventory team asks “how many units were on hand at the end of September 20?” and “how did end-of-day stock change from September 19 to 20?” Those are state-at-period questions. A periodic snapshot intentionally records the state for each expected product/date combination so the warehouse does not confuse event counts with balances.

1. Period grain, not transaction grain

AtlasMart declares the inventory snapshot grain as one product at the end of one calendar day. The snapshot is expected to contain all four governed products on both dates in the lab, even if a product had no movement during that day.

Within one snapshot date, on-hand units can be summed across products. Across dates, the same physical inventory can appear repeatedly, so 146 + 137 = 283 is not “inventory on hand.” It is merely the arithmetic sum of two balance observations.

2. Build and query the dense daily fixture

CREATE TABLE fact_inventory_daily (  snapshot_date TEXT NOT NULL,  product_id TEXT NOT NULL,  on_hand_units INTEGER NOT NULL CHECK(on_hand_units >= 0),  snapshot_batch_id TEXT NOT NULL,  PRIMARY KEY(snapshot_date,product_id));INSERT INTO fact_inventory_daily VALUES('2026-09-19','P100',42,'INV-20260919'),('2026-09-19','P200',63,'INV-20260919'),('2026-09-19','P300',13,'INV-20260919'),('2026-09-19','P400',28,'INV-20260919'),('2026-09-20','P100',40,'INV-20260920'),('2026-09-20','P200',60,'INV-20260920'),('2026-09-20','P300',12,'INV-20260920'),('2026-09-20','P400',25,'INV-20260920');SELECT snapshot_date,SUM(on_hand_units) AS on_handFROM fact_inventory_dailyGROUP BY snapshot_dateORDER BY snapshot_date;

Expected result: 146 on September 19 and 137 on September 20. The two rows answer two separate as-of questions.

3. Deliberately wrong approach: sum balances through time

SELECT SUM(on_hand_units) AS wrong_inventoryFROM fact_inventory_daily;-- Returns 283.-- That is not an end-of-day balance, average balance, or units moved.

The repair depends on the metric. For current/end-of-period inventory, choose the appropriate snapshot date. For average daily inventory, average the daily totals or product-level daily balances according to the intended grain; do not call a raw sum across days “inventory.”

-- End-of-period balanceSELECT SUM(on_hand_units) AS on_hand_20260920FROM fact_inventory_dailyWHERE snapshot_date='2026-09-20';-- 137-- Two-day average of daily total inventoryWITH daily AS (  SELECT snapshot_date,SUM(on_hand_units) AS daily_on_hand  FROM fact_inventory_daily  GROUP BY snapshot_date)SELECT AVG(daily_on_hand) AS avg_daily_on_hand FROM daily;-- 141.5

4. Snapshot completeness is a separate quality dimension

A daily total can look plausible even when one product row is missing. The lab therefore tests expected member coverage: 4 rows per date and the exact governed product set. Missing coverage should fail the snapshot before publication, not silently shrink the total.

SELECT snapshot_date,COUNT(*) AS product_rowsFROM fact_inventory_dailyGROUP BY snapshot_dateHAVING COUNT(*) <> 4;-- Expected: no rows.-- Negative test: delete P400 on 2026-09-20.-- Completeness becomes 3 rows and the total falls from 137 to 112.

Completeness proves all expected rows are present. It does not prove that each source balance is accurate; source-to-warehouse reconciliation is still required.

5. Reruns and corrections

Because (snapshot_date,product_id) is the declared key, a rerun must not create a second row for the same daily measurement. If the source corrects P300 from 12 to 11, AtlasMart must record an authorized snapshot restatement or correction lineage and then republish a reconciled total of 136. A “delete all history and rebuild” shortcut is unsafe unless the blast radius, source replayability, downstream dependencies, and rollback are explicitly controlled.

6. Production judgment

Choose a periodic snapshot when regular state measurements are themselves the analytical object: daily inventory, month-end balances, daily active subscriptions, or service-capacity state. The period must be part of the grain, snapshot completeness must be testable, and every semi-additive balance must carry a documented time aggregation rule.

Knowledge check

Summary and next step

Periodic snapshots make regular state visible and queryable, but their balances demand explicit time semantics. Lesson 3 moves to a different mutable fact shape: one row that accumulates predictable lifecycle milestones.

Authoritative references

• Kimball Group — Transaction Fact Tables — Primary reference for atomic measurement-event fact tables and transaction grain.
• Kimball Group — Periodic Snapshot Fact Tables — Primary reference for standard-period snapshot grain.
• Kimball Group — Accumulating Snapshot Fact Tables — Primary reference for pipeline/workflow facts whose rows are revisited as milestones occur.
• Kimball Group — Lag/Duration Facts — Reference for milestone lag and duration measures in accumulating snapshots.
• Kimball Group — Fact Tables — Overview of transaction, periodic snapshot, and accumulating snapshot grains.
• SQLite — CREATE TABLE — Constraint semantics used by the deterministic local fixture.
• SQLite — UPSERT — Official SQLite upsert syntax reference; the lab uses explicit event-log idempotency rather than assuming engine-independent merge semantics.
• SQLite — Date And Time Functions — Reference for local deterministic timestamp and duration calculations.