50x Cost Reduction: Data Engineering for Market-Wide Hourly Settlement at Scale

Summary

Octopus Energy's gross margin engineering team achieved a 50x cost reduction after the UK's Market-wide Half-Hourly Settlement regulation increased data volumes 48 times, turning a million-dollar annual infrastructure bill into a cost-effective production pipeline.
The team decoupled the pipeline into consumption, settlement, and billing layers and applied Spark optimizations—broadcast joins, partition pruning, and adaptive query execution—to reduce rows processed from 25 billion to 300 million, a 98.8% reduction.
Incremental processing using Change Data Feed and DBT Microbatch patterns further reduced compute by limiting recalculation to only changed data, and the optimized pipeline now enables real-time pricing signals for energy customers.

50x Cost Reduction: Data Engineering for Market-Wide Hourly Settlement at Scale

When the UK moved to Market-wide Half-Hourly Settlement (MHHS), energy suppliers faced a data engineering crisis: 48 times more data, dual consumption sources, and a million-dollar annual infrastructure bill just to recalculate margins. Octopus Energy rebuilt their gross margin pipeline from the ground up, applying targeted optimizations at each layer instead of simply scaling compute. By decoupling the pipeline into consumption, settlement, and billing layers, they achieved 98.8% reduction in rows processed (25 billion to 300 million) and a 50x cost reduction per settlement date.
Learn how Spark optimization techniques (broadcast joins, partition pruning, adaptive query execution) and incremental processing (Change Data Feed, DBT Microbatch) transform a regulatory hurdle into a scalable, cost-effective engine. this video covers real numbers from production deployments and practical patterns for data engineers facing data volume explosions in heavily regulated industries.
🤝

Chapters

FAQs

What is Market-wide Half-Hourly Settlement (MHHS) and why did it create a data engineering challenge?

MHHS is a UK energy market regulation requiring electricity suppliers to calculate settlement based on half-hourly meter readings rather than monthly estimates, increasing data granularity by 48 times. For Octopus Energy, this regulation turned a manageable margin calculation pipeline into a data engineering crisis with an estimated million-dollar annual infrastructure bill.

How did Octopus Energy reduce processed rows from 25 billion to 300 million?

Octopus Energy decoupled the pipeline into three distinct layers—consumption, settlement, and billing—and applied targeted Spark optimizations at each layer rather than simply scaling compute. Key techniques included broadcast joins to reduce shuffling, partition pruning to limit data scanned, and adaptive query execution, collectively achieving a 98.8% reduction in the processing footprint.

What is Change Data Feed and how does it help with incremental processing?

Change Data Feed is a Databricks Delta Lake feature that tracks which rows have changed between pipeline runs, enabling downstream pipelines to process only modified records rather than reprocessing the full dataset. Octopus Energy combined Change Data Feed with DBT Microbatch patterns to build incremental pipelines that dramatically reduce compute costs while keeping margin calculations current.

What is the business impact of Octopus Energy's pipeline optimization beyond cost reduction?

Beyond the 50x cost reduction, the optimized pipeline enables real-time pricing signals that can be surfaced to customers, supporting Octopus Energy's mission to make energy cleaner and cheaper. The decoupled architecture also makes the system more maintainable and adaptable as UK energy market regulations continue to evolve.

Full transcript

[00:10] All right, welcome everyone. Today we're going to be talking about two things. Um, energy and data. Now, I know some of people here are already into flex uh flexibility in the energy markets. So, I'll try to go into a bit more deep dive than I had originally
[00:26] planned. And then the second part of the session will be a deep dive into data engineering. So I'm Sad Ali. Um I work for Octopus Energy and I'm the head of gross margin engineering. My co-speaker is Ben who is the data engineer in the team. And what we're going to be talking
[00:43] about is how is data engineering helping our mission which is to make energy cleaner and cheaper for our customers.
[00:59] So what we'll cover is firstly what we do in the gross margin uh engineering team and more broadly how does the economics of energy supply works for our consumers? What does that mean from a data perspective? So what problems do they create and finally how do we solve those
[01:17] problems? So firstly the margin calculation itself. So if you're a supplier you essentially earn the gap between what it costs you to supply energy and versus what you get
[01:34] out of revenue from the customers. Right? Very simple. Now if you start looking at the revenue side, I think as most of you will know, you are most likely on a on a tariff
[01:49] which you give a meter reading at the start of the month and you give a meter reading at the end of the month. All right? So you consume a set amount of energy in a month and very generally you have a flat tariff which is I will pay X amount per those energy used and that's
[02:08] kind of your revenue profile how it looks like right you just have one flat rate that you fix for 12 months maybe more and that's what dictates how much revenue um that's going to come out or the price that you pay. However,
[02:26] if you look under the hood on what's happening on the cost side on energy, what is happening is there's about three components to that. One is the commodity price. So, whenever you hear, oh, people are trading energy, you know, we're
[02:41] trading gas, we're trading electricity, what they mean is this commodity is being traded. But on top of that, almost about 40% is the non-commodity cost. So what does it take to then actually distribute that energy to a household
[03:00] and finally there's all the losses in between that goes into play right so simple I think the stack now looks like okay you have a cost you know you have a a revenue a price that you pay so where's the problem what's the what's the what's the issue here right well
[03:18] the issue here is that um we have a lot more renewables um in the grid. Now you have solar, you have vent and our commodity costs moves by each half hour. So this is a real um uh price uh in the
[03:35] UK market and as you'll see middle of the day there's a lot of sun, a lot of solar generation and the commodity price has dipped all the way to negative pricing. And if you're in the energy industry you'll hear this a lot. You know the prices are going negative. um
[03:51] in Europe um in the last um month we had negative 500 pounds or euros per megawatt hour. So very very negative right? So in that case essentially as a consumer if you are a household what it
[04:07] means is that you are being paid or you should be paid to consume that uh electricity from the grid right however as you know that doesn't happen right and the big reason for that is
[04:24] because in the UK and I think in large parts of the world because you're submitting those 2 meter readings that I mentioned at the start the the consumption profile is the consumption between those is
[04:42] profiled. So grid does a calculation and says on average an average household will would have used uh energy in this way. Right? So if you're a supplier, you you don't
[04:57] really have a mechanism to say X individual actually used a lot more when the prices were cheaper or X individual used more when the prices were higher and so on.
[05:14] So what happens is wholesale is diving below zero. A lot of the distribution costs that I talked about are also time related but you still pay the flat tariff right
[05:30] and there's no real signal that we can send on a larger scale to our consumers to say okay uh the grid is very clean right now it's very cheap please you more use more energy this is what the talk is about and in
[05:48] the UK in particular what they have is uh a program called marketwide halfhourly. And what that means is that for the entirety of UK, which is about more than
[06:03] 30 million households, you instead of having those profiling, you will now have the actual half hourly data from the smart meters flowing into the cost calculation in in order to enable the flexibility.
[06:26] So what does it what does it do? So what what is the data problem that it creates? All right. So well the first problem is we talked about the the revenue side uh previously and we talked about the cost side. Now previously from a data engineering perspective those were just
[06:43] one data sets right you take the reading from the customer you you go to your app you say okay this is my what my meter is saying or automatically and then that goes back to industry to say hey I need to be costed on that right the first
[06:58] problem is now you have a billing system which is on a different um meter reads to what your in in in in the industry what we call settlement the cost side is on on a different uh consumption
[07:14] and so and the for the first time we see the actual halfhourly consumption of our customers. So what does it look like in in in kind of in practice? You had one monthly read coming in um
[07:31] telling us how much energy was used but not when importantly. We we used to have this energy and then a profile was applied to it to say okay well this is the total energy used and we're going to assume this is how it's used. But now
[07:48] the challenge is that we're getting the 48 um um actual halfhourly reads for each um customer in the UK.
[08:07] The second problem uh that we have is is kind of like this issue of now we have two views of consumption. Billing for for vast majority of our customers but settlement or the cost side is now half hourly.
[08:23] So before just to show you what you'd have is you'd have the table on on the top um top left over there um which would be for each period you'll get how much energy was used and then you would
[08:40] get um a profile applied to it to figure out how for each half an hour what that energy was. The the good thing about that from a data engineering perspective was that the profile was fixed for different regions
[08:58] um and essentially the same profile could be applied across the data set. So we didn't have a lot of data to play with in that in that sense. However, it limited what you could do with passing the signals, right? Which is the real challenge when you're trying to drive
[09:13] cleaner energy and cheaper energy. So now we have two problems. A the data is is exploding plus you have two sources of truth, right?
[09:28] Well, what does that mean in in numbers in in pipeline numbers? So we had a pipeline. We had a gross margin pipeline and we said well okay let's simulate let's pass the half hourly data through that pipeline and see how how much compute do we need to
[09:45] get this calculation going. And what we found that you would need more than a million dollars um uh to get that pipeline run to finish um across a year and that's just the data bricks cost not the AWS cost uh that you need to also
[10:02] pay behind the scenes uh at at the pipeline level what was happening is of course now the pipeline was a daily pipeline you have to now scale that 48 times to make sure that you're capturing
[10:18] those halfhourly uh data for each customers. But this 48 is perhaps a little bit misleading because if you look at the kind of underlying consumption table from which we used to
[10:35] pull the data you would have one read per customer per month and then you're taking that one read well actually now that's 30 days in a month times 48 in a day and you are into 1440x
[10:52] in one of the sources. Right. Octopus in the UK has 80 million customers and if you take that across a year, you are into hundreds of billions of rows that you have to process. Um, another nuance here for those of you
[11:10] who are familiar with energy industry or even any other trading industry is a lot of the times in when you look at this these kind of scale you're only dealing with data on a daily level. Okay, I don't care about the data that is one year back. In energy, settlement
[11:27] runs on a cycle and even in trading settlement runs on a cycle. That means that the consumption for 14 months ago can change. That means you have to recomputee your costs um everything going back to previous years. So this is
[11:44] um actually a far um it becomes a much much uh bigger problem than um uh than just computing for one year and then and so um so we have got two problems. One is
[12:04] the um data explosion problem. How do we make sure that we don't um have our compute costs exposed by 1 million USD? And the other problem is well how do we deal with now consumption data how our
[12:20] consumers are using it but there are multiple sources of truth for it. So to talk about the solution I'll invite the data engineer who worked on on this project Ben Wood to come and talk about it.
[12:39] Thank you. Thanks. Hey everyone, can you hear me? Nice. Um, yeah, thanks Sard. Uh, so my name is Ben. I work in the gross margin engineering team in Octopus Energy. Um, and this is a pretty big problem, right? Um, what's the solution? Well, as
[12:54] Sard alluded to, the first thing that we tried was, well, we've got a pipeline that works pretty well. Um, and we've got basically this data scale problem. Like the volume's going up a lot. How about we just chuck compute at it? Like let's just not think about the cost for
[13:10] a second and just like that's someone else's problem. Let's just see if it works, right? Um, and we very quickly found two things. The first thing was that even though the old pipeline worked, there was a lot of tech debt, a
[13:26] lot of tech debt that compounded over quite a few years. um and it just broke down really with the increase in data volume. It just couldn't handle the additional compute that was required and then also secondly the costs and I don't think our platform
[13:41] team would be very happy if uh we just passed that to them. So it was really a good opportunity for us to take things back to the start. Um how do we start from the ground up? what are
[13:57] the best data engineering principles to apply um and how can we do that in a way which is scalable and it looks a little bit something like this. So what you can see here is essentially the core components of what
[14:13] a gross margin pipeline in our sector kind of looks like. Um we decided to really break the problem down so and apply targeted optimizations throughout the pipeline. There isn't a one-sizefits-all solution here. It's actually, you know, spend the time to do
[14:30] the research, see what works, see what doesn't work. So, if we go from left to right, we first start with our config spine. And this is our source of truth. This is basically a table which says, right, for each half hour period, tell
[14:47] me what meters are on supply. um how many registers it has? Uh what type of tariff is it on? So is it a smart tariff? Is it a fixed tariff? Um what are the billing attributes that we need to then join on the billing rates? What
[15:02] are the cost attributes that we then can join on the cost rates too? And that's our source of truth, right? That's our single source of truth that we continue to refer back to. Um this luckily for us isn't something that changed a lot throughout between the old world and the
[15:18] new world. The biggest problem here is again that 48 times increase going from daily to half hourly. So kind of good news is that like our source of truth still remains intact. Um the sec the well the first big real problem for us
[15:35] was this consumption layer as Sard alluded to. So before we you know we have our sources of consumption but we never needed to process the halfhourly data right um for the new world we have
[15:50] industry data that's coming in telling us for each half hour period how much did all your 8 million meters um use. So when we talk about consumption we're talking about the volume of uh electricity that was used. Um, so we have one source of half hourly data, but
[16:06] it's not just that. We have uh another one. We have we have data coming in from the smart meters themselves. Um, so that's also at the halfhourly grain. So we've actually got two. Not only that, we also have uh daily reads coming from the the meters themselves. And not only
[16:24] that, we have the meter readings that come in from the the customers themselves. So in this layer we're talking about multiple different sources each a very granular grain. Um and so the optimizations and the the kind of way to think about this layer is
[16:41] a very different problem to maybe what comes next. So the consumption layer really was how do we process that much data in a way that's scalable that's reliable and essentially allows our gross margin pipeline to run on a
[16:56] regular basis. Um stability is like the real key thing here and also speed of our pipeline that then flows into essentially these three parallel metric layers and that's like the crux of our gross margin pipeline. Um essentially each meter will
[17:14] be build and uh we'll pay costs on that as well. So each meter will go through the bottom one. So we talk about settlement that's um industry tells us this is how much uh consumption was used for every single meter. Um and then we
[17:31] apply the costs there. That's what we pay. Each meter will then go through one of the the top two. So depending on what type of tariff you're on. So if you're on a smart tariff where the half hourly unit rate changes um at particular times of the day. So if it's at nighttime when
[17:48] the energy is cheap um you might have a lower rate and when it's more expensive uh like when the the grid is dirtier um it'll be more expensive. So like in the evenings in the mornings. So if you're a smart uh customer you'll go through the top one. If you're on a fixed tariff
[18:05] you'll go through this middle one here. Um, and a quick shout out to V who worked on the consumption layer and Lily and our team who worked on the two billing layers. Um, I worked on the settlement costs. And so what we've done here is we've really just sliced and
[18:21] diced um our pipeline. Um, we've before in in like our monolithic pipeline, it was all very entangled, not very um separated out. The best way that we found to approach this was what is the
[18:37] best way to slice and dice our portfolio in such a way that we can apply these targeted optimizations to each of the different layers. And broadly speaking, those optimizations come in two different groups. One is spark optimization and
[18:55] the other is incremental processing. So if we think about the consumption layer, the consumption layer which has multiple different sources um and varying different grains. So half hourly daily just an absolute mammoth of a
[19:10] layer. Um we before in the old world we didn't have to deal with that amount of data, right? So we were actually just refreshing that table all the time from scratch. And
[19:26] that was great in terms of accuracy. gave us 100% accuracy. But going forward that this is not a sustainable solution. We had to think of a different way to approach this an incremental processing where you only process the new data as it comes in. Leave everything historical
[19:43] just stale. Um yeah, so consumption implementing incremental processing was really the key to um unlocking that scalability um and spark optimization. So when we
[20:01] think about spark optimization, we're really thinking about distributed computing. How do we structure the underlying data within the pipelines uh in such a way that when you're you when you're doing heavy joins big tables on big tables joining them together how do
[20:17] we do it in such a way which is fast which is cost effective um and again scalable right this is a pipeline that runs regularly it needs to be reliable otherwise we're going to get a lot of questions and a lot of angry
[20:33] stakeholders So going into spark optimization a bit when we think about that distributed computing the real uh aim of the game here is how to reduce shuffling. So just as a kind of explainer
[20:50] um shuffling takes place when imagine you've got two big tables and you're joining them together. Um the way Spark this the Spark engine processes that is it basically divides the job into lots of different worker nodes. Um and the
[21:07] shuffling that takes place is actually the different worker nodes shuffling data between them. Um, and if your underlying tables are unstructured, it means that uh your joins are going to take a long time to uh to do. Um, and
[21:24] the worker nodes are going to have to do a lot of shuffling between them and that's just going to compound and cause the job to just never complete really. Um, so how do we reduce shuffling? That's the big thing. And we found these
[21:40] three core concepts to be the most effective. So in our old pipeline we one way to kind of reduce the uh the computational heaviness of like these big joins was actually we've got so we've got our
[21:56] config spine that was on the daily level. We then aggregated that to a group's level. I think that was on uh like location. We then join on the cost attributes um on that group's level and then we redistribute back to the level that we care about and that worked
[22:13] really well. Um it was a really neat way to just reduce the strain on that one join which was the biggest blocker for us in the past. Um but actually what we found is when we applied that with into the new world with the massive increase
[22:28] in data volume that we're experiencing it just killed it like the the massive bottleneck that that um introduced where you essentially the redistribution step like after you aggregate and then join was the thing that really really didn't
[22:45] help. And so actually enabling adaptive query execution in data bricks really um helped in terms of reducing the shuffling. Um Spark was able to coalesce a lot of the partitions together under the hood um so that there weren't as
[23:01] many small files um and also having this enabled meant that the Spark query planner could adapt as as it went along. And that's really important for the second one which is implementing broadcast joins. So a lot of uh the
[23:18] joins um in our pipeline they're actually like a lot of small tables that are joining onto lots of big ones, right? Um and if so broadcasting is a way for Spark to essentially make the same copy of the same small table
[23:34] available to each of the individual worker nodes. So instead of having to slice and dice the table and then share the data between the nodes and then increase the shuffling, there is no shuffling that has to take place because every single worker nodes has the exact same copy of that small table. It's able
[23:52] to really improve the join speed and the join performance and help reduce cost in the process. Um there is a caveat here. I think the uh the default size of broadcasting is about 500 megabytes. I think you can actually experiment with
[24:08] that and like make it bigger, make it smaller. I think we ended up like experimenting up to 2 gigabytes, but it's definitely worth um doing that if you've got a lot of small tables, especially if they're interized. Uh this can really help. And the last one
[24:24] surprisingly is partitioning. So quite an old school uh solution, but for our gross margin pipeline, the queries um are static. It's a you know it's not really changing week to week. Um we a lot of the joins take place on
[24:41] location and time based fields usually the same ones. So we have a really good understanding of the query um the query requirements like how that evolves over time. Um and so what we actually found is that
[24:58] if we implemented partitioning on these really huge tables on the common join keys, things like settlement date, things like um location-based fields, u we actually reduce performance, increased performance by four times. Um and that just speaks to again having
[25:16] correctly structured data underlying like being the foundation of your pipeline. Um and this really enables partition pruning. So when you've got two massive tables joining onto each other, um Spark immediately knows where the the relevant files are under the
[25:32] hood how to increase that performance. I would say as a caveat to this that um we did experiment with liquid clustering and in fact I would recommend liquid clustering. Uh the reason why we didn't use it in our use case is again because
[25:47] our queries are pretty static over time. we have a really good grasp of how the data is structured. Um, and also we wouldn't actually necessarily feel the benefits of liquid clustering. That's really great on um live tables that are
[26:03] appended to live and breathe over time. Our pipeline for for context is a simple it's lots of create and replaces and it's like writing to different schemas every single week. So um our outputs definitely liquid clustered but for this
[26:19] pipeline we found that partitioning was the best for speed and performance but I would definitely recommend experimenting with liquid clustering. And this is just a snippet of the kind of uh before and after. So um we were
[26:37] able to use serverless uh throughout our testing process. We found this to be by far the most um useful compute for us because it it made the UI well the UI for serverless is really really useful. Um it can you can really see where the
[26:52] the bottlenecks are. Um and so on the left we have the old world where we were doing this aggregation step right so we're going from bottom up we're going from our config spine we're then aggregating we're joining on our
[27:08] relevant cost rates um and then we're redistributing and what you can see here is uh the the bottleneck was really in this disagregation step because you have to scan the base table the base config table twice and again in the old world
[27:25] that wasn't necessarily a problem because we're not dealing with a massive data volume like we are now. When you have this massive increase in scale, this becomes your bottleneck. Um and what we found is so on the right uh we have everything at the half hourly level
[27:42] we have our spine on the half hourly we have our consumption on the half hourly we join them together costs on the half hourly as well enabling AQE enabling partitioning and we were able to reduce the so for one settlement date as a this is a a real test um the uh total tasks
[28:00] time were reduced from nine uh nine well nine hours to 45 minutes and uh the amount of data scanned was reduced from 350 GB to just 32. So this is just a really I think helpful illustration for
[28:17] trusting AQE, trusting Spark to really take your data and run with it if it's structured in the right way. A little bit on incremental processing. Um so this again is for the consumption layer. Um what we utilized was um change
[28:35] data feed. So change data feed essentially tells us um every time a table is is changed, is updated, how many rows were inserted, updated or deleted. And what that meant is we were able to just immediately process only
[28:51] the data that had changed. Um a small restriction here was uh we're kind of at the mercy of um how the source comes in. So one of our consumption sources fantastic CDF enabled. Um all of the others CDF not enabled. So we had to
[29:09] develop a custom way to process this incrementally because again we just couldn't afford to just process everything from scratch every single time. And DBT Microbatch was that solution. um we get the benefits of CDF.
[29:27] So we have um what the the same sort of like um solution as CDF I suppose. So this is time bounded. So we're basically looking at specific times time windows um what d what new data is coming in in
[29:43] those windows and only processing those changes. um we have a really good understanding of um how this works now and also the the kind of accuracy differences between these two methods. But um it was a really really uh useful
[30:00] implementation for us and again microbatch it definitely carries uh most of that CDF benefit um without forcing the source side change that we would prefer but this is a really great workaround.
[30:19] And finally, I guess taking a step back, aside from all of the optimizations and what it means for um our pipeline, um I think all of that aside, fundamentally this has also changed the way as a data engineer that we kind of work with this
[30:36] pipeline now going forward. So before we would spend a lot of compute in this monolithic pipeline every time it runs um and at the end of it if there's an issue one of our downstream stakeholders will flag it say hey this number doesn't
[30:52] look right and then as a data engineer it's the worst case because we actually just don't know where to start. We have to work from back to front find out oh maybe it was a cost rate issue maybe it was a consumption issue. um we just didn't really know cuz everything was so
[31:07] intertwined. In this new world where everything is modularized, we've got a consumption layer, we've got the three different metric layers for billing, two billing ones and one cost one. Um we're able to set tests, catch those faults before
[31:26] they then come down into the the output. And it really makes debugging uh so much easier, so much easier as a data engineer. Um and it just it is just uh a good case in point of reconciling early
[31:42] um and not late and how this new architecture enables that. So these no spoiler alert I guess but um this is how this is how it panned out.
[31:58] So we faced this problem at the beginning uh where we were, you know, massive increase in data volume. What are we going to do? Compute is not going to save us. We're going to have to build everything again. Um taking the time to do that research and development, taking the time to really utilize serverless,
[32:15] find out where the bottlenecks are, continue to iterate and improve. We had a 50 we avoided those 50 times projected costs per settlement date, which was great. Um, but not only that, we actually ended up with a pipeline which is twice as efficient as our legacy one.
[32:32] So it really goes to show that even though we're dealing with a massive increase in volume, you can end up with a pipeline which is that much more efficient and that's a really solid foundation for us going forward because it then enables us to add more features on top of it. Um, gives us that little
[32:50] bit of extra leg room to work with and so overall it's been a massive massive success. And to wrap this all up, I'm going to invite Sard back to the stage.
[33:10] Perfect. So finally, what does this all mean? The data engineering work, the the modularization work, the rearchitecture work, what this in the end enables us is when the grid is cheap and when it's clean. Again, a real example here, carbon dioxide in the grid overlaid with
[33:28] the prices on the grid. Now, what this gives us is during those middle of the day hours. Now, because you're each consumer's actual consumption is coming through the pipeline, you can actually price that hours at a much lower rate
[33:46] and actually enable consumers to use cheaper electricity. but not only cheaper but also cleaner electricity during those time and so this is what a real day looks like and I think I'll end the conversation this this um
[34:03] presentation here and thank you so much for coming over here and listening to our talk and if there's any questions around the GBLXD market spark tuning or incremental uh we're here to answer them thank you so

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.