Scaling for MHHS: how Octopus Vitality achieved a 50x price discount in margin information engineering

The vitality transition has an information downside

The UK’s vitality grid is in the midst of its most vital structural transformation in a long time. As renewables like wind and photo voltaic take a bigger share of electrical energy technology, intermittency turns into a first-class downside: vitality is reasonable when the solar shines and costly when it does not.

The prevailing settlement mannequin – constructed on month-to-month meter reads and averaged consumption profiles – can not value that sign precisely. And if you cannot value it precisely, you’ll be able to’t cross the sign to customers, and demand by no means shifts to match provide.

Market-wide Half-Hourly Settlement (MHHS) is the regulatory response. Each family in Nice Britain strikes from two meter reads per thirty days to 48 reads per day. That isn’t an incremental change. For a provider like Octopus Vitality serving over 8 million clients, it’s a 48x enhance within the information factors driving each margin calculation, each settlement obligation, and each business choice.

The info engineering implication is direct: with out re-architecture, the infrastructure price to run Octopus Vitality’s margin pipelines was projected to balloon by $1 million yearly.

Why throwing compute at this does not work

The intuition when information volumes enhance 48x is to provision extra infrastructure. For Octopus Vitality’s margin information workforce, that intuition was rapidly validated as untenable. The projected price per settlement date underneath the legacy structure was $23.63 – a 33x enhance from historic norms. Multiply that throughout settlement home windows, and the invoice compounds quick.

Nonetheless, the deeper downside was not compute price – it was structure mismatch. The legacy pipeline had been constructed round a single grain: month-to-month. Billing ran month-to-month. Settlement ran month-to-month. Your complete pipeline was monolithic by design.

MHHS launched a basic cut up. Business price information now arrives at half-hourly granularity – 48 information factors per buyer per day. Sensible tariff clients with EVs and warmth pumps want half-hourly income calculations. Customary tariff clients nonetheless settle month-to-month. Working all three by way of a single monolithic pipeline meant processing your entire dataset on each run, no matter what had truly modified.

As Saad Ali, Lead of the Margin Knowledge Group at Octopus Vitality, framed it: “You possibly can’t simply throw extra compute at an issue like this. You must rebuild and rethink your logic from the bottom up.”

The structure: three streams, one supply of reality

The workforce re-architected round three specialised streams, every optimised independently for its pure grain:

Settlement – Half-hourly granularity for regulatory settlement and price allocation. Business prices at 48 information factors per day; this stream matches that grain precisely.

Half-Hourly – Half-hourly processing for sensible tariff clients: EV drivers, warmth pump customers, and time-of-use merchandise the place the half-hourly value sign is your entire business proposition.

Month-to-month – Month-to-month processing for traditional tariff clients, unchanged in grain however now reconcilable in opposition to the half-hourly information.

A “Job of Jobs” orchestration sample manages dependencies and parallel execution throughout all three streams. Every stream is independently tunable – what works as a Spark optimisation for Settlement will not be essentially proper for NHH.

Underpinning all three is the downstream consumption layer: a unified, multi-grain supply of reality consolidating meter reads, sensible meter information, and business flows at multi-terabyte scale. This layer is the reconciliation bridge between month-to-month billing and half-hourly settlement – and it grew to become the location of the only highest-leverage optimisation within the challenge.

Incremental processing: 98.8% fewer rows

The naive strategy to the upstream consumption tables – reprocessing your entire multi-terabyte dataset on each run – would have meant unsustainable compute prices on the new quantity.

Delta Lake’s Change Knowledge Feed (CDF) made true incremental processing viable at this grain. As an alternative of full overwrites, the pipeline now reads solely data which have truly modified because the final run. The outcome: rows processed per run dropped from 25 billion to 300 million – a 98.8% discount.

Knowledge freshness improved from weekly to each day. For the business workforce, that shift means margin visibility on the grain the place pricing choices are literally made – each morning, not as soon as per week.

Be aware: the $1M in annualised financial savings figures cited beneath exclude the extra financial savings from this transfer to incremental processing on upstream tables. The total effectivity acquire is bigger.

Spark & Delta optimisation – and what to take away

With 48x extra information flowing by way of the system, the workforce utilized focused optimisations validated by measurement throughout 4 classes:

Lineage and I/O discount

• Simplified lineage by consolidating information early within the pipeline, lowering downstream joins and shuffle operations
• Knowledge pruning: chosen solely the columns strictly crucial for settlement and pruned rows on the earliest attainable stage, lowering I/O overhead earlier than costly transformations

Be a part of and partition tuning

• Broadcast joins for reference tables underneath 500MB, eliminating costly shuffle operations on complicated multi-key joins with date ranges
• Liquid clustering was enabled throughout a number of tables for columns often utilized in filters and joins. Liquid clustering dynamically co-locates associated data on the desired clustering keys with out requiring fastened partition boundaries. Liquid clustering avoids the small-file downside, larger reminiscence consumption, and I/O overhead that come from over-partitioning.

Trusted the optimiser

• In a number of instances, Spark’s Adaptive Question Execution (AQE) outperformed hand-tuned logic. The workforce eliminated customized optimisation code and let AQE do its job.

That final level bears emphasis: eradicating unjustified compute operations was as impactful as including new optimisations. If you’re working Z-ordering or ANALYZE with out measuring their impact, they could be costing you greater than they’re saving.

Serverless as a growth accelerator

Databricks Serverless made the three-month supply window viable. Zero cluster startup time meant the workforce might iterate quickly – write, run, measure, modify – with out ready for infrastructure to provision.

The Serverless UI enabled side-by-side run comparisons, making it sensible to isolate the impact of particular person optimisations.

Within the workforce’s personal phrases: “The testing and growth course of couldn’t have been completed with out serverless. Utilizing the serverless UI helped us to establish bottlenecks and make straightforward comparisons between completely different runs.”

Outcomes

The $0.48 per settlement date isn’t just a 50x discount from the MHHS projected price – it’s 2x cheaper than the legacy system had ever been, regardless of processing 48x extra information factors. Re-architecture delivered regulatory compliance and made the system materially extra environment friendly than the one it changed.

What this implies past vitality

MHHS is a UK vitality regulation. Nonetheless, the sample it represents – a regulatory or enterprise occasion that multiplies information quantity at a finer grain – will not be distinctive to vitality. Any time a system strikes from month-to-month to each day, each day to real-time, or combination to transactional, the identical dynamics apply.

4 transferable takeaways from the Octopus Vitality expertise:

1. Grain misalignment is the hidden price driver. When a pipeline processes every part on the most interesting grain no matter enterprise want, you pay for it in compute, freshness, and upkeep complexity. Determine the pure grains in your information and align processing to them.
2. Incremental processing transforms pipeline economics. The 98.8% row discount got here from CDF-based incremental logic, not Spark tuning. Begin there – and keep in mind the total financial savings are bigger than the headline determine.
3. Take away earlier than you add. Audit present optimisation decisions earlier than assuming you want extra compute. Z-ordering, ANALYZE, and customized shuffle logic utilized with out measurement could also be costing you greater than they save.
4. Belief the optimiser. AQE outperformed hand-coded logic in a number of instances. Earlier than writing customized optimisation, take a look at whether or not Spark already handles your case.

The larger image

Within the phrases of Saad: “By making our techniques sooner and extra environment friendly, we will provide smarter tariffs that assist our clients use vitality when it is most cost-effective and cleanest.”

The lowered price base does one thing particular: it removes the financial barrier to high-frequency information processing. That makes grid balancing viable as a product. That makes sensible tariffs commercially sustainable. That’s how information engineering at scale connects to the vitality transition – not as infrastructure overhead, however because the business basis for it.

MHHS compliance was the mandate. Making sustainable vitality the inexpensive possibility is the mission. The info engineering is what connects the 2.

Go additional

———

Saad Ali is Lead of the Margin Knowledge Group at Octopus Vitality. Ismail Makhlouf, David Poulet, and Daniel Taylor are Options Architects at Databricks.