#259 Data modelling 7 min
Conformed Dimensions and the Bus Matrix in SQL
Joining two fact tables made 25 pounds of refunds look like 50, and drilling across fixed it.
Predict the matrix and the drill
Copy the code folder first. Read the DDL in bus.py and source.py, then write down the bus matrix and the region drill across you expect. Run python run.py matrix and python run.py drill and compare. You should see North 60, 20 and 2, and South 65, 5 and 1.
A hint
Tickets have no product, so they are missing from the category report.
Add a support ticket
In source.py, add the ticket ("2026-04-10", "C2") to TICKETS. Predict which row of the region report changes, then run python run.py drill. You should see South with 2 tickets and every other number unchanged.
A hint
Customer C2 is Ben, who is in the South.
Fix the labels
In source.py, change the returns team's labels in RETURNS_TEAM_REGIONS from N and S to North and South. Predict how many rows the report has, then run python run.py unconformed. You should see two rows, North and South, with sales, returns and tickets lined up.
A hint
Conforming means the same labels with the same meaning.
Add a return and join the facts again
In source.py, add a third return to RETURNS: ("2026-04-10", "C3", "P2", "Late", 15). Predict the true refund total, then run python run.py drill and python run.py wrong. You should see North returns 35 and Kitchen returns 35 in the drill, and in the wrong join a refund total of 65 against a true refund total of 40.
A hint
The true refunds are now 20, 5 and 15. The join repeats each return once for every sale by that customer.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Three made-up processes in SQLite; it shows drilling across and two failures, not shrunken rollup dimensions or speed.
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
