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.

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:

KindIn a shopWhat you see
1. TransactionThe shop's till rollOne line printed per sale, the moment it happens. Lines are never rewritten. A quiet day prints nothing.
2. Periodic snapshotA photo of the shelves every eveningTaken every evening, busy day or not. Each photo shows how much is there, not what happened.
3. Accumulating snapshotA parcel's tracking cardOne card per parcel. Boxes get ticked as it moves: packed, shipped, delivered. The same card is updated.
4. FactlessThe attendance registerA tick says someone was there. No amounts, just 'it happened'. A blank shows who did not come.
5. AggregateThe total at the bottom of each pageWritten from the lines above it, so nobody re-adds the page every time they ask.
6. ConsolidatedThe target chart on the office wallThis 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.

kindday 1 (1 Nov)day 2 (2 Nov)day 3 (3 Nov)1. Transactionquiet day: no new rowgroceries -1,200groceries -1,200salary +50,000groceries -1,200salary +50,0002. Periodicquiet day: still a row1 Nov: 8,8001 Nov: 8,8002 Nov: 58,8001 Nov: 8,8002 Nov: 58,8003 Nov: 58,8003. Accumulatingsame row, filled inLN-77 applied 1 Nov, rest emptyLN-77 applied, documents 2 NovLN-77 ... approved 3 Nov4. Factlessrows with no numbersAsha visited, 1 NovAsha visited, 1 NovRavi visited, 2 NovAsha visited, 1 NovRavi visited, 2 Nov5. Aggregatesummary row replacedNov: 1 txn, -1,200Nov: 2 txns, +48,800Nov: 2 txns, +48,8006. Consolidatedtwo sources, one rowNov: plan 40, approved 0Nov: plan 40, approved 0Nov: plan 40, approved 1green = new row, yellow = an existing row changed, white = unchanged

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.

1. TransactionWhat happened?a row per event, added2. Periodic snapshotHow much was there at the end of each day?a row per thing per period, quiet days too3. Accumulating snapshotHow far along is each order?one row per process, updated as it moves4. FactlessDid it happen? Who was visited?rows with no numbers at all5. AggregateA fast answer to a common questiona summary built from another fact6. ConsolidatedPlan against actual?two sources brought to one grain

Pick the kind from the shape of the question.

The question's shapeTyre exampleKind of factHow its rows behave
Something happened, onceAn order line was placed; a tyre was scrappedTransactionNew rows are added; old rows rarely change
How much is there at a momentStock in each warehouse at the end of each dayPeriodic snapshotA row per thing per period, even when nothing moved
A process with steps that takes daysAn order: ordered, shipped, delivered, invoicedAccumulating snapshotOne row per process, filled in as steps happen
Something happened, but there is nothing to measureA sales rep visited a dealerFactlessThe row itself is the fact; you count rows
The same big question, asked all dayOTIF per region per day on the home dashboardAggregateA small summary of a big fact, kept in step with it
Two sources that must be comparedSales plan against actual ordersConsolidatedEach 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)

accountdatewhatamount
A11 Novgroceries-1,200
A12 Novsalary+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)

accountdateclosing balance
A11 Nov8,800
A12 Nov58,800
A13 Nov58,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)

applicationapplieddocuments inapprovedmoney paiddays to approve
LN-771 Nov2 Nov3 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)

customerbranchdate
AshaMG Road1 Nov
RaviMG Road2 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)

branchmonthtransactionsdebitscredits
MG RoadNov2-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)

branchmonthloans plannedloans approvedgap
MG RoadNov401-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.

Business1. Transaction2. Periodic snapshot3. Accumulating snapshot4. Factless5. Aggregate6. Consolidated
Retail shopA sale line: one row per item on a receiptStock per store per product per dayOnline order: placed, packed, shipped, delivered, returnedA product on promotion in a store on a daySales per store per weekSales against target per store per month
BankA card transactionAccount balance at the end of each dayLoan application: applied, documents, approved, paidA customer attended a webinarTransactions per branch per monthPlanned against actual loans per branch
HospitalA lab test result; a medicine dose givenBeds occupied per ward at midnightA patient stay: admitted, operated, discharged, billedA patient booked into a clinic slot (no-shows from coverage)Admissions per ward per monthPlanned against actual operations per theatre per week
AirlineA ticket sold: one row per flight segmentSeats sold per flight, each day before departureA booking: booked, checked in, boarded, bags deliveredA crew member rostered on a flightPassengers per route per monthForecast against actual passengers per route
TelecomA call or a data sessionActive subscribers per plan at month endNew connection: ordered, SIM sent, activated, first rechargeA customer was eligible for an offerData used per mast per hourCapacity plan against actual traffic per region
School or universityA fee paymentStudents enrolled per course on census dayAdmission: applied, interviewed, offered, joinedA student was present in a classAverage marks per class per termPlanned against actual intake per course
HRA payroll payment lineHeadcount per department at month endA hire: job opened, interviews, offer, joinedAn employee completed a trainingLeavers per department per quarterHiring plan against actual hires
Streaming appA play: one row per playPaying subscribers per plan per dayA new user: signed up, first play, subscribed, cancelledA user was shown a title (an impression)Minutes watched per show per dayForecast against actual subscribers per country
Tyre maker (this page)An order line with OTIF flagsStock per warehouse per dayAn order line: ordered, shipped, delivered, invoicedA sales rep visited a dealerOTIF per region per dayPlan 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.

Is it built from other gold facts?Is it a process with steps over days?Is it a level at a moment (balance, stock, headcount)?Something happened. Does it have numbers to add?nononoyesto answer one question fast:5. Aggregateto compare two sources:6. Consolidatedyes3. Accumulating snapshotyes2. Periodic snapshotyes1. Transactionno4. Factless (count the rows)

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:

KindAdd across things (stores, accounts)?Add across time?Note
TransactionYesYesAmounts and quantities add up any way.
Periodic snapshotYes (stock in all warehouses)No: take the last day, or the averageLevels and balances. The classic trap: summing a month of daily balances.
Accumulating snapshotCounts yes; durations noCount by a milestone's dateDurations (days to approve) are averaged or put in bands, never summed.
FactlessCount rowsCount rowsThere is nothing else to add.
AggregateSame as the fact it came fromSame as the fact it came fromStore counts (lines, on-time lines), never only a percent: percents cannot be added or averaged.
ConsolidatedYes, per sideYes, per side, at the shared grainOnly after both sides are at the same grain; otherwise the plan is repeated and multiplied.

Part B · 8

Questions people get stuck on

QuestionAnswer
'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'.