learning-it by example

#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.

1. In a periodic snapshot fact table, what is the grain?
2. In a SQLite order fulfilment example, what does an accumulating snapshot fact table hold?
3. A made-up order table in SQLite has a daily periodic snapshot of the open order value. Why is it wrong to add that open value up across days?
4. In a SQLite transaction fact table of nine made-up order events, what was the total value placed?
5. In a SQLite accumulating snapshot of four made-up orders, what were the average days to ship and to deliver at the end of 7 March?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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

All Data modelling videos

Related videos