The problem
Millions of raw meter readings are difficult to analyse consistently. Consumption, household characteristics, weather and holidays need to be cleaned and aligned before they can support useful comparisons or models. This was an academic cloud-computing project at York St John University.
Data and approach
The source is the Low Carbon London smart-meter dataset, with household information, daily weather and bank holidays. The pipeline reads 21 half-hourly block files in parallel. Daily consumption is derived from those readings, keeping the aggregated output connected to the original input.
How the solution works
ADLS Gen2 holds the source files. Bronze Delta tables preserve raw records and source lineage. Silver tables clean types, remove duplicates and derive daily readings. Gold tables form a star schema around daily consumption, with household, weather and holiday dimensions. Spark SQL queries and dashboard marts support reporting.
A practical engineering decision
Spark MLlib was not available in the selected Serverless environment. I used Spark for distributed data preparation and scikit-learn on the aggregated data for modelling. That preserved the large-scale processing pipeline while moving smaller modelling tables to a compatible tool. Unity Catalog and managed identity handle governed storage access without embedding account keys.
What worked
The outputs include SQL analytics, a dashboard, three K-Means household segments and a gradient-boosted demand forecast. Normalising the 48-point daily load profiles lets segmentation focus on the shape of demand rather than only the amount consumed. The model predicts average daily consumption per home across the household sample, in kWh/home/day. It trains on the earliest 75% of days and tests on the latest 25%, using weather and calendar features.
Findings and their meaning
My analysis found stronger winter demand, an evening peak around 17:00–20:00 and a negative relationship between temperature and consumption. Time-of-use households averaged 11.90 kWh/day versus 13.15 for standard-tariff households. These are observed associations, not proof that changing a tariff causes lower consumption.
Constraints and effect
The main engineering constraint was the Serverless modelling restriction; I split distributed preparation from single-node modelling. The result is an analysis-ready pipeline and forecasting demonstration. The forecast was evaluated on a time-based holdout of historical London data. The notebooks need to be rerun to reproduce the outputs; this project does not measure utility cost savings.
Analysis and modelling outputs
| Measure | Result |
|---|---|
| Winter / summer daily demand | 15.08 / 10.08 kWh per home |
| Demand–temperature correlation | −0.83 |
| Forecast RMSE | 1.25 kWh/home/day |
| Forecast MAE | 0.66 kWh/home/day |
| Forecast R² | 0.68 |
| Households in three segments | 289 / 398 / 363 |
The forecast metrics apply to daily mean consumption per home, not individual half-hourly readings. The next evaluation step is to compare persistence and seasonal-naive forecasts on these same test days and calculate RMSE as a percentage of mean test demand. Until then, these scores describe model error without establishing an advantage over a simple forecasting rule. Correlations and group differences do not establish causation.
Reproduce the analysis
The notebooks run in Databricks with the source data in cloud storage. There is no public hosted demo. View the forecast notebook for the target definition, time split and evaluation code.