DuckCon #7

20 years of wind: Auditing UK energy policy without a cluster

Barry Smart · DuckCon #7

sections

slides

DuckDBEnergy PolicyMedallion ArchitectureParquetTDDData Singularity

01 / The Project

Auditing UK energy policy from a laptop

Barry Smart, Director of Data & AI at endjin, a small fully-remote UK technology consultancy and a heavy DuckDB user.
A personal project with his father, an engineering professor: both spent their careers in the energy industry.
The goal: take an evidence-based look at whether the UK's dash to net zero is compromising energy security, reliability, and affordability.
endjin has published a series of blog posts sharing their real-world experience of adopting DuckDB.

02 / The Question

Is UK energy policy on the right trajectory? Framed as a pyramid: Green (carbon neutral by 2045), Affordable (accessible to all), Reliable (the lights stay on), Secure (energy independence).
The public debate is full of opinion; the aim of the audit is to form an independent, data-driven view.
Recent headlines set the scene: 'Dunkelflaute' collapsing wind output, the Spain and Portugal blackout, and rising UK energy bills.
The underlying data is publicly available but heavily siloed and hard to work with.

03 / The Data

Elexon: UK electricity market data (gas, nuclear, wind, solar, storage) via a REST API JSON payload, ~50M rows per year.
Met Office: historical weather across 133 weather stations, delivered as FTP CSV files, ~1.2M rows per year.
Department for Energy Security & Net Zero: a renewable-locations database (wind, solar, biomass, storage) as an Excel spreadsheet, ~3000 rows.
Total footprint: around 5GB, small enough to handle entirely on a laptop.

04 / Then vs Now

In 2001 at Scottish Power the same domain was tackled with a monolithic Oracle warehouse plus Business Objects: roughly 50 man-months and £500M of dedicated infrastructure.
The domain knowledge is still valuable, but the old architecture is not: Moore's law and DuckDB now put the most powerful analytics engine on the desktop.
The 'data singularity' curve: single-node processing capacity now covers about 99% of useful datasets.
Progression from Scale Up (Excel), to Scale Out (Spark/Pandas), to in-process analytics with DuckDB.

05 / The Architecture

Simple Python modules ingest the data and apply strict data contracts, so what lands in partitioned Parquet is trusted.
A medallion layout inside DuckDB, each layer a schema: Bronze as zero-copy views over Parquet, Silver where the heavy aggregation runs into materialized tables, Gold as analytical views for insights.
Data is served out to Jupyter for content and analytics.
It all runs on the laptop today, with confidence it can be pushed to the cloud unchanged.

06 / The Engineering

A data-driven approach with requirements written in Gherkin syntax, giving clean, self-documenting code and an extensible architecture.
Example scenario: applying a time-weighted average MW to a complex physical-notification profile, checked against an expected result.
Functions take a DuckDB relation type and return a relation type, making small units easy to test and compose into more sophisticated transformations.
Materializing those relations still gives full DuckDB query optimization and predicate push-down.
A full test suite enables confident, incremental delivery of each new insight; an earlier PySpark attempt did not scale, but DuckDB handles it easily.

07 / The Insights

Detailed analysis of the Dunkelflaute (low-wind, low-sun) event of 12 September 2024: the grid operator aggressively fired up the fossil-fuel fleet.
Gas produced over 70% of the UK's electricity and energy prices spiked 10X during the event.
Wind was curtailed both before and after the Dunkelflaute, with median wind speed below 4 m/s.
Zooming out, a calendar heatmap of Dunkelflaute hours per day maps these events across the full multi-year dataset at macro scale.

08 / The Payoff

Then vs now, side by side: 50 man-months and £500K became roughly 50 man-hours.
A large team became a small team; capital-intensive servers and licenses became a laptop plus DuckDB.
The project was delivered in a very short time, with help from Claude Code along the way.
The bigger prize is not the numbers but the new ways of working the technology unlocks.

09 / The Bigger Picture

Removing complex infrastructure lets data teams work straight with the data, delivering insights and responding to user feedback.
A socio-technical model: flow of value from the data team through a data product to users, with feedback loops flowing back.
The long-standing socio-technical constraints are fundamentally changing.
Teams can align directly to the priorities and value streams of the businesses they serve.

A single laptop running DuckDB now handles work that in 2001 needed a £500M Oracle warehouse and 50 man-months, collapsing it to about 50 man-hours.
Strict data contracts at ingest plus a Parquet-backed medallion architecture (Bronze views, Silver tables, Gold views) keep a solo project trustworthy and cloud-portable.
Modeling transformations as functions from DuckDB relation to relation makes logic composable and unit-testable while retaining query optimization and predicate push-down.
Gherkin-based, test-driven development enabled confident incremental delivery of each new insight, where an earlier PySpark approach failed to scale.
The audit surfaced concrete findings: the 12 Sep 2024 Dunkelflaute drove gas above 70% of UK electricity and a 10X price spike, with wind curtailed around the event.
Calendar heatmaps let the same pipeline zoom from a single event to macro patterns of Dunkelflaute hours across a multi-year dataset.
The real value of in-process analytics is organizational: data teams become innovation engines aligned to business value streams instead of managing infrastructure.