#257 Data modelling 6.5 min
Declare the Grain: The Mistake That Breaks Reports
A daily sales report said 108 pounds when the shop sold 54, and one rule fixed it.
Predict the four runs
Copy the code folder first. Read source.py, mixed.py and fixed.py, then write down the report totals you expect from python run.py mixed, pushed and fixed. Run them and compare. You should see 108 against 54, then 54 and 20 against 54 and 15, then 54 and 15 matching.
A hint
The mixed table holds both the order lines and a total row per order.
Raise a shipping charge
In source.py, change the shipping of O4 from 5 to 10. Predict the shipping report totals of the pushed and fixed runs, then run python run.py pushed and python run.py fixed. You should see 25 against 20 for the pushed table and 20 against 20 for the fixed tables.
A hint
O4 has one line, so the pushed table copies its shipping only once.
Add a line to an order
In source.py, add the line ("O2", "Pen", 1, 2) straight after the O2 Pad line. Predict the numbers, then run python run.py mixed. You should see 12 rows for 8 order lines, and a report total of 112 against a source total of 56.
A hint
The mixed table doubles every pound, so the report total is twice the source total.
Loosen the guard
In fixed.py, delete the words NOT NULL after product TEXT in the fact_order_line table. Predict what happens to the order total row with no product, then run python run.py guard. You should see the repeat line still refused, but the row with no product accepted.
A hint
The primary key on its own does not stop an empty product in an ordinary SQLite table.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Four made-up orders in SQLite; it shows two grain mistakes and one fix, not allocation or dimension tables.
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
