How does an aggregate fact table differ from a periodic snapshot fact table?

## Why the two get confused An aggregate fact table at month grain and a periodic snapshot fact table both look like "one row per month per product per location, with some numbers on it". The shapes are similar; the roles are not, and mixing them up leads to summing something that should never be summed. ## The four fact table types, for orientation Dimensional modelling recognizes four kinds of fact table: - **Transaction** — one row per event as it happens (an order line, a payment, a click). The atomic layer of most warehouses. - **Periodic snapshot** — one row per entity per regular interval, capturing state at that moment (inventory at each month end, balance at each day close). - **Accumulating snapshot** — one row per pipeline instance, updated in place as it passes milestones (an order from placement through shipment to delivery). - **Factless** — rows recording that something happened or was eligible, with no numeric measure (attendance, promotion coverage). An aggregate fact table is not a fifth type. It is a coarser-grained copy of one of these — nearly always of a transaction fact table. ## The distinguishing test: derivable or measured Ask one question: **can this table be reproduced by re-summing the atomic fact table?** A month-by-product sales aggregate can. Every row is `SUM(revenue)` over the order lines in that bucket. It is redundant by design, kept only because reading it is cheaper. A month-end inventory snapshot generally cannot. To derive on-hand quantity from movement transactions you would need a correct opening balance from before the warehouse's earliest transaction, plus every adjustment, shrinkage and correction ever applied — often outside the transaction feed entirely. In practice the snapshot is loaded from the operating system's own balance, because that is the number the business reports. This is why deleting an aggregate is a performance regression while deleting a snapshot is data loss. ## Flows versus levels A second framing that interviewers like: transaction facts and their aggregates measure **flows** — quantities that accumulate over an interval and are fully additive, so summing days into months is meaningful. Periodic snapshots measure **levels** — quantities that exist at an instant. Levels are **semi-additive**: on-hand inventory adds correctly across products and warehouses, but summing January's daily balances yields a number roughly thirty times too large. A snapshot's summarization over time is an ending value or an average, never a sum. ## Density differs, and that matters An aggregate is **sparse**: it has a row only where activity occurred. A product that sold nothing in March simply has no March row, and that is correct — nothing happened. A periodic snapshot is typically **dense**: every product in every warehouse gets a row every period, including the ones with a balance of zero, because "we held none of this on 31 March" is itself a fact the business needs. That density decision is deliberate and makes snapshots grow predictably rather than with activity — and it means a zero row in a snapshot is meaningful, while a missing row in an aggregate is. ## Both can exist over the same process The two are not alternatives. A warehouse can hold a daily inventory snapshot *and* a monthly aggregate of that snapshot — where "aggregate" means the month-end row or the average daily balance, selected rather than summed. The aggregate of a snapshot is still derived; it just uses a summarization rule other than `SUM` over the time dimension. ## Naming keeps them apart Because the shapes are similar, naming does real work. `agg_` or `_summary` prefixes for derived rollups; `snapshot` in the name for state tables; and measure names that state the rule — `ending_balance`, `avg_daily_balance` rather than a bare `balance` on a month-grain row. A month-grain table with a column called `quantity` tells a reader nothing about whether summing it is legitimate. ## What interviewers listen for The derivability test, the flow-versus-level distinction, and semi-additivity. A candidate who says "a monthly balance table is just an aggregate of transactions" has usually never had to explain to finance why the year-to-date balance came out twelve times too high.