Fact Table Types & Additivity

## Where these two shapes fit A fact table holds the numeric measurements of a business process, with foreign keys to the dimensions that give those measurements context. Dimensional practice names four fact-table shapes: **transaction**, **periodic snapshot**, **accumulating snapshot** and **factless**. Transaction and periodic snapshot are the pair you are asked to separate most often, because picking the wrong one gives you a warehouse that either cannot answer the question at all or answers it far too slowly. ## Transaction fact tables A transaction fact table stores **one row per measurement event**: one row per order line, per payment, per shipment scan, per web request. The row is written once when the event lands and is not revisited. Properties that follow from that: - **Sparse.** If a customer buys nothing on Tuesday, there is no Tuesday row for that customer. Storage tracks activity, not calendar coverage. - **Insert-only.** Loads append; there is no update path to reason about, which makes the table cheap to build incrementally and easy to reason about for audit. - **Mostly additive measures.** Amount, quantity, tax, discount — you can sum them across product, customer, store and time and get a meaningful number. - **Finest available detail.** Because it sits at the atomic event, it can be rolled up any way an analyst asks; nothing has been pre-decided. ```sql CREATE TABLE fct_order_line ( order_date_key INTEGER NOT NULL, product_key INTEGER NOT NULL, customer_key INTEGER NOT NULL, order_number VARCHAR(20) NOT NULL, quantity INTEGER NOT NULL, extended_amount DECIMAL(18,2) NOT NULL ); ``` ## Periodic snapshot fact tables A periodic snapshot stores **one row per entity per period**, written by a scheduled process: every account gets a row every day, every store-product gets a row every week. The row is a photograph of state at the end of the period. Properties that follow: - **Dense and predictable.** Row count is roughly entities × periods, regardless of activity. A million accounts over three years of daily snapshots is about a billion rows whether or not anyone transacted. - **Answers state questions directly.** "What was the balance on 30 June?" is a single row lookup instead of summing every transaction since the account opened. - **Semi-additive measures.** A balance or an inventory level can be summed across accounts, products or regions, but summing it across days is meaningless — you would add the same money 30 times in a month. Aggregation over time uses the period-end value, or an average. - **Survives missing source events.** Some processes never expose transactions at all (a scale reading, a headcount, an interest accrual). A snapshot is the only way to capture them. ```sql CREATE TABLE fct_account_balance_daily ( snapshot_date_key INTEGER NOT NULL, account_key INTEGER NOT NULL, ending_balance DECIMAL(18,2) NOT NULL, interest_accrued DECIMAL(18,2) NOT NULL ); ``` Note the second measure: `interest_accrued` is a *flow* over the period and is fully additive across days, while `ending_balance` is a *level* and is not. A snapshot table routinely mixes both, and knowing which is which is the whole skill. ## Why teams build both They are complements, not alternatives. The transaction table is the auditable atomic record and supports any drill-down an analyst invents. The snapshot is the fast, complete state series that dashboards and trend charts read. A common shape is: load the transaction facts first, then derive the daily snapshot from them plus the prior snapshot row. That derivation only works when *every* movement is captured as a transaction; when it is not — adjustments, revaluations, a source that only publishes state — the snapshot must be sourced independently. ## Practical distinctions to state in an interview | | Transaction | Periodic snapshot | |---|---|---| | Row means | an event occurred | a period ended | | Density | sparse | dense | | Load pattern | append | append per period | | Typical measures | additive amounts | semi-additive levels plus additive flows | | Natural question | "how much did we sell?" | "how much did we hold?" | ## Common mistakes The first is treating the snapshot as an aggregate of the transaction table and therefore redundant. It is not: it captures state that may never have been expressed as an event. The second is summing snapshot levels over time; the number will scale with the number of periods and looks plausible enough to ship. The third is skipping the transaction table because "the dashboard only needs daily totals" — you lose the ability to answer any question the dashboard did not anticipate, and rebuilding history later is usually impossible.