One e-commerce order can be recorded in a warehouse in at least three useful ways. You can store each event as it happens: ordered, paid, shipped, delivered. You can photograph the state of all open orders at the end of every day. Or you can keep a single row per order that gets filled in as it moves through its life. These correspond to the three classic fact table types described in Ralph Kimball’s dimensional modelling, and each is good at answering questions the others answer badly.

Transaction fact tables: one row per event

This is the most common type. Each row records a single business event at a point in time: a sale line, a payment, a bank withdrawal, a web click, a call to a contact centre. Rows are inserted and, apart from corrections, never updated.

Transaction facts are as detailed as the source allows, so they answer almost any “how much” or “how many” question about the events: revenue by product by day, payments by mode, clicks by campaign. Their measures are usually fully additive.

Their weakness shows up with questions about state. “What was the outstanding balance on every loan account on 30 September?” can, in theory, be answered by summing every disbursement and repayment since each account opened. In practice that means scanning years of history for a single number, and it gets slower every month. That is what snapshots are for.

Periodic snapshot fact tables: one row per thing per period

A periodic snapshot records the state of something at regular intervals. The grain is “one row per entity per period”, for example one row per loan account per month-end, one row per SKU per warehouse per day, or one row per employee per month.

A lending company’s monthly snapshot might carry outstanding principal, days past due, interest accrued that month and the account’s bucket (current, 1–30 DPD, 31–60 DPD and so on). Portfolio questions become trivial: total outstanding by branch at quarter-end, number of accounts that moved from current into 31–60 DPD, month-on-month trend in delinquency.

Two properties matter. First, a row exists for every entity every period, even if nothing happened, so a dormant account still appears with its balance. That density is the point: you can compare periods without worrying about missing rows. Second, many snapshot measures, such as balances and stock levels, are semi-additive. They sum across accounts or warehouses but not across time; for time you take the closing value or an average.

The cost is volume. A million accounts produce twelve million rows a year in a monthly snapshot, and 365 million in a daily one. Teams usually pick the coarsest period that answers real questions.

Accumulating snapshot fact tables: one row per process instance

Some business processes have a clear start, a defined sequence of milestones and an end. An order goes from placed to paid to shipped to delivered. A loan application goes from submitted to documents verified to sanctioned to disbursed. A recruitment requisition goes from opened to shortlisted to offered to joined.

An accumulating snapshot has one row per instance of such a process, with a date column for each milestone. Unlike other fact tables, rows are updated as the process advances. When the order ships, the shipped date on its existing row is filled in.

Consider an order placed on 2 March, paid on 3 March, shipped on 5 March and delivered on 9 March. Its row stores all four dates plus derived lag measures: 1 day to payment, 2 days from payment to shipping, 4 days in transit, and 7 days end to end. Now questions about pipeline speed and bottlenecks become simple aggregations. Average days from payment to shipping by warehouse, the share of orders delivered within five days, how many orders are paid but not yet shipped right now: each is a single query over one table.

The date columns each relate to the date dimension in a different role (order date, ship date, delivery date). Unreached milestones are typically set to a special “not yet happened” date key rather than left null, so joins still work.

Accumulating snapshots work best when the milestones are well defined and fairly stable. If the process loops (an order is shipped, returned, reshipped) or has many optional paths, the fixed-column design strains, and a transaction table of status changes is often a better foundation.

Factless fact tables: events with no numbers

Sometimes the event itself is the fact, and there is nothing to measure. A student attends a class. An employee completes a mandatory compliance training. A customer is targeted by a promotion. A table that holds only dimension keys (student, class, date) is called a factless fact table. You analyse it by counting rows: attendance by course, training completion by department.

A second use is recording coverage: what could have happened. A table of which products were on promotion in which stores on which days lets you answer the question a sales table alone cannot, namely which promoted products did not sell. Sales facts only contain things that happened; the coverage table supplies the denominator.

Picking the right type

Question shapeBest fit
How much happened in a period?Transaction
What was the level or status at a point in time?Periodic snapshot
How long do steps in a defined process take, and what is stuck?Accumulating snapshot
Did an event occur, or what was eligible but did not happen?Factless

These are not exclusive. A mature lending warehouse would plausibly have all of them: a transaction table of repayments, a monthly account snapshot for portfolio reporting, an accumulating snapshot for the application-to-disbursal pipeline, and a factless table of collection calls. Snapshots are usually built from the transaction data, so the transaction table remains the source of truth and the others are derived views optimised for particular questions.

The mistake to avoid is forcing one shape to do every job. Computing quarter-end balances from raw transactions in a dashboard, or trying to measure pipeline speed by self-joining an events table on every refresh, both work on small data and become painful as volume grows. Recognising the question shape early tells you which table to build.

Written by the RP Strativa Team. Dates and scenarios in the examples are illustrative.

Leave a Reply

Trending