Home › Data engineering › Gold data modelling › Silver to gold › The six kinds of fact
Silver to gold · Learn it · 11 min read
The six kinds of fact table, from zero
Why one fact table is not enough, with everyday pictures first, then one bank over three days, nine businesses, and four questions that pick the kind. This is one page of Silver to gold.
- The six kinds of fact, from zero
- Why are there so many kinds of fact?
- One bank, all six kinds, with rows
- The same six kinds in nine businesses
- Which kind is it? Four questions
- What you may add up, per kind
- Questions people get stuck on
Part B · 0
The six kinds of fact, from zero
A fact table is a business's notebook of what happened or what was measured. Real businesses keep several kinds of notebook, because they need to remember different kinds of things. Picture a small shop:
| Kind | In a shop | What you see |
|---|---|---|
| 1. Transaction | The shop's till roll | One line printed per sale, the moment it happens. Lines are never rewritten. A quiet day prints nothing. |
| 2. Periodic snapshot | A photo of the shelves every evening | Taken every evening, busy day or not. Each photo shows how much is there, not what happened. |
| 3. Accumulating snapshot | A parcel's tracking card | One card per parcel. Boxes get ticked as it moves: packed, shipped, delivered. The same card is updated. |
| 4. Factless | The attendance register | A tick says someone was there. No amounts, just 'it happened'. A blank shows who did not come. |
| 5. Aggregate | The total at the bottom of each page | Written from the lines above it, so nobody re-adds the page every time they ask. |
| 6. Consolidated | The target chart on the office wall | This month's target and this month's actual, side by side, from two different notebooks. |
The feel comes from watching the tables change. Here are all six at one bank, over three days. Day 1, a customer buys groceries. Day 2, her salary arrives. Day 3 nothing happens on her account, but her loan gets approved. Look at what each table does on each day.
Transaction adds rows only when something happens. Periodic adds a row every day. Accumulating fills one row in. Factless adds rows with no numbers. Aggregate and consolidated replace a summary row.
That picture is the whole idea:
- Transaction: rows only get added, and only when something happens.
- Periodic snapshot: a row for every period, even a quiet one.
- Accumulating snapshot: one row per process, updated as it moves.
- Factless: rows with no numbers; you count them.
- Aggregate and consolidated: built from other facts, and their rows are replaced when their sources change.
Part B · 1
Why are there so many kinds of fact?
Because business questions come in a few different shapes, and each shape needs rows that behave differently over time. "What happened?" needs a row per event that never changes. "How much is in stock?" needs a row per day even on quiet days. "How far along is each order?" needs one row that keeps changing. Put the wrong shape in the wrong kind of table and the numbers come out wrong, or the queries crawl.
So the kind is not decoration. It decides how the reader finds the windows and how the writer writes. That is why the harness asks for it in the settings file, and why one harness can build all six: it knows six ways to read and two ways to write, and the business rule stays in the small build.
Pick the kind from the shape of the question.
| The question's shape | Tyre example | Kind of fact | How its rows behave |
|---|---|---|---|
| Something happened, once | An order line was placed; a tyre was scrapped | Transaction | New rows are added; old rows rarely change |
| How much is there at a moment | Stock in each warehouse at the end of each day | Periodic snapshot | A row per thing per period, even when nothing moved |
| A process with steps that takes days | An order: ordered, shipped, delivered, invoiced | Accumulating snapshot | One row per process, filled in as steps happen |
| Something happened, but there is nothing to measure | A sales rep visited a dealer | Factless | The row itself is the fact; you count rows |
| The same big question, asked all day | OTIF per region per day on the home dashboard | Aggregate | A small summary of a big fact, kept in step with it |
| Two sources that must be compared | Sales plan against actual orders | Consolidated | Each source brought to the same grain, then joined |
Part B · 4
One bank, all six kinds, with rows
The same customer, Asha, account A1, branch MG Road, in all six tables. These rows are made up to teach, not from a run. Read each note: it says what you may and may not add up.
1. Transaction: fact_card_txn (one row per card transaction)
| account | date | what | amount |
|---|---|---|---|
| A1 | 1 Nov | groceries | -1,200 |
| A1 | 2 Nov | salary | +50,000 |
3 Nov had no transactions, so it has no row. Add the amounts up any way you like: by day, month, account, merchant.
2. Periodic snapshot: fact_daily_balance (one row per account per day)
| account | date | closing balance |
|---|---|---|
| A1 | 1 Nov | 8,800 |
| A1 | 2 Nov | 58,800 |
| A1 | 3 Nov | 58,800 |
The account opened November with 10,000. 3 Nov was quiet, but it still has a row, so a balance chart has no gaps. Never add balances across days: 8,800 + 58,800 + 58,800 is not money anyone has.
3. Accumulating snapshot: fact_loan_application (one row per application)
| application | applied | documents in | approved | money paid | days to approve |
|---|---|---|---|---|---|
| LN-77 | 1 Nov | 2 Nov | 3 Nov | (not yet) | 2 |
One row, updated on each of the three days as the loan moved. 'Money paid' is still empty and will be filled in on the day it happens. 'How long do approvals take?' is the average of days_to_approve across applications.
4. Factless: fact_branch_visit (one row per customer per branch per day)
| customer | branch | date |
|---|---|---|
| Asha | MG Road | 1 Nov |
| Ravi | MG Road | 2 Nov |
No amounts. Count rows to get footfall. Who did NOT visit this month? Compare with the customer list (a dimension), or keep a coverage table of who was invited.
5. Aggregate: fact_branch_month (one row per branch per month)
| branch | month | transactions | debits | credits |
|---|---|---|---|---|
| MG Road | Nov | 2 | -1,200 | +50,000 |
Built from fact_card_txn, never from silver, so it always agrees with it. On 2 Nov the November row was replaced (1 transaction became 2).
6. Consolidated: fact_loans_plan_vs_actual (one row per branch per month)
| branch | month | loans planned | loans approved | gap |
|---|---|---|---|---|
| MG Road | Nov | 40 | 1 | -39 |
Plan comes from the finance budget, approved loans from fact_loan_application; both brought to branch and month first, then put side by side.
Part B · 5
The same six kinds in nine businesses
Every business has all six shapes of question. Read one row to see a whole business. Read one column to get the feel of one kind.
| Business | 1. Transaction | 2. Periodic snapshot | 3. Accumulating snapshot | 4. Factless | 5. Aggregate | 6. Consolidated |
|---|---|---|---|---|---|---|
| Retail shop | A sale line: one row per item on a receipt | Stock per store per product per day | Online order: placed, packed, shipped, delivered, returned | A product on promotion in a store on a day | Sales per store per week | Sales against target per store per month |
| Bank | A card transaction | Account balance at the end of each day | Loan application: applied, documents, approved, paid | A customer attended a webinar | Transactions per branch per month | Planned against actual loans per branch |
| Hospital | A lab test result; a medicine dose given | Beds occupied per ward at midnight | A patient stay: admitted, operated, discharged, billed | A patient booked into a clinic slot (no-shows from coverage) | Admissions per ward per month | Planned against actual operations per theatre per week |
| Airline | A ticket sold: one row per flight segment | Seats sold per flight, each day before departure | A booking: booked, checked in, boarded, bags delivered | A crew member rostered on a flight | Passengers per route per month | Forecast against actual passengers per route |
| Telecom | A call or a data session | Active subscribers per plan at month end | New connection: ordered, SIM sent, activated, first recharge | A customer was eligible for an offer | Data used per mast per hour | Capacity plan against actual traffic per region |
| School or university | A fee payment | Students enrolled per course on census day | Admission: applied, interviewed, offered, joined | A student was present in a class | Average marks per class per term | Planned against actual intake per course |
| HR | A payroll payment line | Headcount per department at month end | A hire: job opened, interviews, offer, joined | An employee completed a training | Leavers per department per quarter | Hiring plan against actual hires |
| Streaming app | A play: one row per play | Paying subscribers per plan per day | A new user: signed up, first play, subscribed, cancelled | A user was shown a title (an impression) | Minutes watched per show per day | Forecast against actual subscribers per country |
| Tyre maker (this page) | An order line with OTIF flags | Stock per warehouse per day | An order line: ordered, shipped, delivered, invoiced | A sales rep visited a dealer | OTIF per region per day | Plan against actual per dealer per month |
Patterns to notice down the columns:
- Transaction is always a single event with an amount: a sale, a payment, a call, a play.
- Periodic always has a level and a time point: "at midnight", "at month end", "each day before departure".
- Accumulating always reads like a journey with three to five named steps.
- Factless is always "X was with Y at time Z": attended, rostered, eligible, shown.
- Aggregate always says "per something per period".
- Consolidated always says "against": plan against actual, forecast against actual.
Part B · 6
Which kind is it? Four questions
Ask the questions in this order. The first "yes" is your kind.
Built from other facts? Process? Level? Event with numbers? The first yes wins.
Try it: "seats sold per flight, each day before departure". Not built from other facts. Not a journey. A level at a moment (seats sold so far, as of each day). So: periodic snapshot. "A user was shown a title": not built from facts, not a process, not a level; it happened, with no number to add. So: factless.
Part B · 7
What you may add up, per kind
Most wrong numbers in reports come from adding up something that must not be added. Each kind has its own rule:
| Kind | Add across things (stores, accounts)? | Add across time? | Note |
|---|---|---|---|
| Transaction | Yes | Yes | Amounts and quantities add up any way. |
| Periodic snapshot | Yes (stock in all warehouses) | No: take the last day, or the average | Levels and balances. The classic trap: summing a month of daily balances. |
| Accumulating snapshot | Counts yes; durations no | Count by a milestone's date | Durations (days to approve) are averaged or put in bands, never summed. |
| Factless | Count rows | Count rows | There is nothing else to add. |
| Aggregate | Same as the fact it came from | Same as the fact it came from | Store counts (lines, on-time lines), never only a percent: percents cannot be added or averaged. |
| Consolidated | Yes, per side | Yes, per side, at the shared grain | Only after both sides are at the same grain; otherwise the plan is repeated and multiplied. |
Part B · 8
Questions people get stuck on
| Question | Answer |
|---|---|
| 'A daily balance is just the sum of transactions. Why store it?' | You could compute it, but every chart would re-add years of transactions for every day. Some sources only send balances (a bank statement, a stock count). And the quiet days must exist as rows. So the snapshot is stored as its own fact. |
| 'Is an order a transaction or an accumulating snapshot?' | Both, for different questions. 'What did we sell?' reads the order lines as events (transaction). 'Where is each order and how long do steps take?' reads one row per order that keeps changing (accumulating). Many businesses have both, built from the same silver. |
| 'Is attendance a fact or a dimension?' | The student list is a dimension (who exists). The attendance register is a factless fact (who was where, when). Rule: if it happens at a time and links several things (student, class, day), it is a fact, even with no numbers. |
| 'Is an aggregate the same as a metric view or a dashboard total?' | An aggregate is a stored table, kept in step with its fact. A metric view or a dashboard computes the total each time someone asks. Build an aggregate only when that is too slow and the question is asked often. |
| 'Why not join plan and actual in the dashboard?' | Every dashboard would have to know the grain trap (a monthly plan joined to daily sales repeats the plan 30 times). A consolidated fact does it once, correctly, in gold. |
| 'Is OTIF a transaction fact? It changes when a late delivery arrives.' | Yes: one row per order line, an event. Its flags can change while late deliveries arrive inside the lookback, and each change is logged as a restatement. If you mainly track the journey of each line, model it as an accumulating snapshot instead. |
| 'How many kinds will my project need?' | Most marts are mostly transaction facts, a few periodic snapshots for levels, one or two accumulating snapshots for key processes, and aggregates only where speed demands. Factless and consolidated appear when someone asks 'who did not' or 'against plan'. |