Fact Table Types: Transaction, Periodic and Accumulating Snapshots

A fact table records the measurements of one business process at one declared grain, plus foreign keys to the dimensions that describe each measurement. There are three core kinds, and they differ in what a row represents in time: a transaction fact records an event when it happens, a periodic snapshot records the state of something at regular intervals, and an accumulating snapshot follows one process instance from start to finish. A fourth, the factless fact table, records that something happened (or could have) without any numeric measure.

The three core fact table types

Transaction fact tables

The most common and most atomic kind. Each row is one event: a sale, a click, a payment, a login. Because it is atomic, it answers the widest range of questions and every other table can be derived from it. Its weakness is questions about state: "how much stock did we have on 3 March?" requires summing every movement since the beginning of time.

Periodic snapshot fact tables

A periodic snapshot takes a picture at fixed intervals: inventory per product per warehouse each day, balance per account each month, open tickets per queue each hour. It answers "what was the level at time T" with a single filter. It is dense by design: a row is written for every entity in every period, even when nothing changed, so zero is a real value rather than a missing row.

Accumulating snapshot fact tables

An accumulating snapshot has one row per instance of a pipeline-like process with well-defined milestones: an order (placed, paid, shipped, delivered), a loan application (submitted, approved, funded), a trip (requested, accepted, picked up, dropped off). The row is updated in place each time a milestone is reached, filling in that milestone's date key and the lag since the previous one. It makes questions like "median time from order to delivery" trivial.

create table fact_order_fulfilment (
  order_number          varchar(20) primary key,  -- degenerate dimension; one row per order
  customer_key          int not null,
  placed_date_key       int not null,
  paid_date_key         int not null,             -- points at an "Unknown / not yet" row until it happens
  shipped_date_key      int not null,
  delivered_date_key    int not null,
  order_amount          decimal(12,2),
  hours_placed_to_paid       decimal(8,2),          -- lag facts, filled as milestones arrive
  hours_paid_to_shipped      decimal(8,2),
  hours_shipped_to_delivered decimal(8,2),
  is_delivered          smallint                  -- 0/1 counter, so "delivered orders" is a sum
);

Use a special "Not yet happened" date row for milestones that haven't occurred, rather than nulls, so joins to the date dimension never drop rows.

Factless fact tables

Some processes have no numeric measure: a student attended a class, a customer was eligible for a promotion, a driver was online in a zone. A factless fact table stores only the dimension keys (and often a constant count column of 1). There are two uses:

• Events without measures: attendance, logins, product page views. Counting rows answers "how many".
• Coverage: which products were on promotion in which stores on which days, whether they sold or not. Combined with the sales fact, it answers "what was on promotion but didn't sell?", which the sales table alone can never show because it has no rows for things that didn't happen.

Additive, semi-additive and non-additive facts

How a measure may be summed decides how BI tools and analysts aggregate it, and getting it wrong produces confident nonsense.

Summing a daily inventory snapshot across a month multiplies stock by thirty. Averaging a margin percentage across stores gives every store equal weight regardless of size. The fix for ratios is always the same: store revenue and cost, sum both, then divide.

-- Wrong: averages each line's margin % equally
select avg(margin_pct) from fact_sales;

-- Right: sum the additive parts, then divide
select (sum(net_amount) - sum(cost_amount)) / nullif(sum(net_amount), 0) as margin_pct
from fact_sales;

Nulls in fact tables

• Measures may be null when a value is genuinely unknown. Aggregate functions ignore nulls, which is usually the desired behaviour; a zero would distort averages.
• Foreign keys should never be null. A null key makes the row vanish from inner joins. Point it at a special dimension row instead: "Unknown", "Not applicable", or "Not yet happened".

Aggregated and consolidated fact tables

Aggregate fact tables pre-summarise an atomic fact to a coarser grain, such as daily sales by product category and region, purely for speed. They join to shrunken dimensions (a category-level product dimension, a month-level date dimension) and must always reconcile with the atomic table. Modern columnar warehouses need fewer of them, but they still pay off for dashboards hit thousands of times a day.

A consolidated fact table combines measures from several processes that share a grain, such as forecast and actual sales per product per month in one row. It saves a drill-across query, but only works when the grains really are identical.

Facts that need care

• Header and line facts: an order has header amounts (shipping, order discount) and line amounts. Either keep a separate order-level fact, or allocate header amounts down to lines with a documented rule, such as proportional to line value.
• Allocated facts: costs known only at a higher level (a delivery truck's fuel) spread across lower-level rows so profit can be computed per line. The rule must be agreed by the business, since it changes everyone's margins.
• Lag and duration facts: elapsed time between two events (minutes from order to dispatch) stored as a number, so averages and percentiles are easy.
• Multiple currencies: store the transaction currency amount and a converted amount in one reporting currency, plus a currency dimension; never sum amounts in different currencies.
• Multiple units of measure: store the quantity in one standard unit and conversion factors in the product dimension, or store each unit as its own fact column.
• Year-to-date facts: avoid storing them; they break when someone sums across months. Compute them in queries or views.
• Late-arriving facts: a fact whose event happened days ago. Look up the dimension version that was valid at the event time, not today's, and load it into the correct date partition.
• Fact table surrogate keys: a unique row id, useful for idempotent reloads, auditing, and updating specific rows. Users never query by it.

Anti-pattern: the centipede fact table. Normalising a hierarchy into the fact table (separate keys for day, month, quarter and year, or for product, brand and category) makes it huge and fragile. Keep one key per dimension and put the hierarchy inside the dimension.

Choosing a type in an interview

Real models usually combine them. An e-commerce warehouse has an order-line transaction fact, a daily inventory snapshot and an order fulfilment accumulating snapshot, all sharing the same product, customer and date dimensions. See the e-commerce example.