How we built it
Method, sources and limitations
What this platform can support, what it cannot, and every assumption behind the numbers on the other three pages.
What this is
A demonstration analytics platform built by Collinalitics Ltd to show how we take a raw sensor feed through to board-level economics. It uses a synthetic dataset because client data is confidential.
What this is not
This is not Transport Scotland data. Collinalitics Ltd has no affiliation with, commission from, or endorsement by Transport Scotland, its operating companies, or any other public body. The road identifiers, city names, geography and appraisal values are real and publicly sourced. Every traffic measurement is generated. No figure on this site describes an actual road, and nothing here should inform a real investment, operational or policy decision.
The pipeline
- Generation
- Python, seeded and reproducible
- Warehouse
- dbt + DuckDB — 33 models, bronze to marts
- Storage
- PostgreSQL — 8 dimensions, 3 facts, 5 aggregates
- Quality
- 120 automated tests, 12 rules with dispositions
- ML
- scikit-learn, gradient boosting, temporal hold-out
- Internal BI
- Power BI — 232 measures over one model
- This site
- Static HTML, no framework, no server
The data
100,000 hourly observations across 74 sensor sites on 9 roads — M8, M74, M77, M90, A9, A90, A82, A1, A96 — serving 7 cities, January 2023 to December 2025.
The generator is not a random-number exercise. It models:
- Demand by site, hour, day type and season, with morning and evening peaks and a weekday/weekend split that shifts commuter volume into leisure volume.
- Speed through a Bureau of Public Roads curve, JTI = 1 + 0.52·(v/c)2.9, so speed falls as volume approaches capacity rather than by assignment.
- Weather on per-city climatology, with winter visibility loss and rainfall feeding through to both congestion and collision probability.
- Disruption from collisions, lane closures, roadworks and planned events, each with its own effect on journey time.
- Sensor behaviour, including seven classes of fault seeded at 2–5% of records: dropouts, stuck readings, implausible speeds, missing values, duplicate timestamps and extreme spikes.
The faults are deliberate. A platform that only works on clean data is not a platform.
Data quality
Of 100,000 raw records, 96,652 (95.70%) pass all twelve rules and enter the analytical layer. The remainder are quarantined, imputed or flagged according to a documented disposition per rule — never silently dropped.
A bug the pipeline caught on itself
During build, the reconciliation test found 318 records carrying a null vehicle-class
count. Their totals were intact but their cost components were null, which excluded them
from component sums while leaving them in the row count — so the parts stopped adding up
to the whole. Two things had let it through: the quality rule checked only the total, not
the five class counts; and the reconciliation test compared with <>,
which is never true against a null. Both were fixed and every figure on this site is
post-fix. We mention it because a data platform that has never caught anything has not
been tested.
Economic values
| Item | Source | Value |
|---|---|---|
| Value of time | DfT TAG data book | By journey purpose |
| Fatal casualty | DfT RAS4001 | £2,411,659 |
| Serious casualty | DfT RAS4001 | £271,003 |
| Slight casualty | DfT RAS4001 | £20,892 |
| Carbon | DESNZ central non-traded, 2022 prices | £273 / tCO₂e (±50%) |
The carbon value carries a wide official sensitivity range, and carbon is the largest cost component here — so the headline is correspondingly sensitive. At the low bound the annual figure falls to roughly £1.05bn; at the high bound it rises to about £1.65bn against a £1,353.3m central estimate. We state this rather than presenting a single number as precise. The finding that carbon exceeds delay survives at the central and high values, but not at the low one.
Sampling and annualisation
The feed represents a sample of site-hours rather than a continuous census. Each site
carries an expansion factor of period_hours ÷ clean_observations, and an
annualisation factor dividing that by the period length in years.
Observed figures describe what the sensors recorded;
annualised figures are grossed to full-year equivalents. Mixing the two
produces nonsense, so they are never combined within a chart, and every monetary visual
states which it shows.
Known limitations
- Synthetic data. Relationships are as modelled, so the analysis validates the method, not any claim about Scotland's roads.
- Carbon sensitivity. The headline is dominated by a value with a ±50% official range.
- No origin–destination data. Site-level counts cannot support routing or diversion analysis.
- Speed is space-mean. Harmonic means are used where appropriate; the arithmetic mean of 88.7 km/h overstates the correct space-mean speed of 82.7 km/h, and the distinction is preserved through the model.
- The risk model forecasts rather than fits. Realised flow and speed are deliberately excluded from the features, so it predicts collision risk from conditions known in advance. This costs accuracy and buys operational usefulness. On synthetic data its 63.7% capture in the top 5% of hours is an upper bound; expect materially lower performance against live STATS19-linked data.
- Segments treated independently. These are corridor-monitoring totals, not a de-duplicated national inventory.
- Static build. The site reflects the last pipeline run, which is appropriate for a fixed historical dataset.
Want this for your own data?
This is the same approach we apply to client work: agree the definitions, model the data properly, quantify what it means in money, and show the quality layer rather than hiding it. We can't show you a client's figures — so we built this instead.