#258 Data modelling 6.4 min
Fact Table Types: Transaction, Snapshot, Accumulating
The same nine order events become three fact tables, and the shipped orders took 2 days on average.
Predict the daily snapshot
Copy the code folder first. Read source.py and the OPEN_AT_END_OF query in facts.py, then write down the open orders and open value for each of the six days. Run python run.py snapshot and compare. You should see 34 on the second and 32 on the seventh, and 262 summed over the days.
A hint
An order is open from the day it is placed until the day it is delivered.
Make order four worth more
In source.py, change the value of O4 in AMOUNTS from 12 to 30. Predict the total placed and the open value on the last day, then run python run.py transaction and python run.py snapshot. You should see a total placed of 84 and an open value of 50 on the last day, with 316 summed over the days. Change the value back to 12 afterwards, because the next tasks expect it.
A hint
Order four is placed on the fifth and is never delivered.
Deliver order three
In source.py, add the event ("2026-03-07", "O3", "delivered") after the last event. Predict the open orders on the last day, then run python run.py snapshot and python run.py accumulating. You should see 1 open order worth 12 on the seventh, and order three with lags 3 and 4. Remove the added event afterwards, because the next task expects the original events.
A hint
The lag to deliver is counted from the placed date, which for order three is the third.
Ship order four
In source.py, add the event ("2026-03-07", "O4", "shipped") after the last event. Predict what changes in the fact_order table and in the number of rows in fact_event, then run python run.py transaction and python run.py accumulating. You should see 10 event rows, order four with a lag of 2 days to ship, and an average days to ship that is still 2.0.
A hint
The lags for shipping are now 1, 2, 3 and 2.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Nine made-up events in SQLite; the snapshot is computed from the events, the averages skip open orders, and no dimension tables are shown.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
