Vibe Coding #5 - I90 ESIOS Files ETL to ClickHouse | How we deploy our electricity market database

What we’ll build

A complete ETL that loads I90 ESIOS operational data into ClickHouse and exposes it via an MCP server, so any AI agent can query the Spanish electricity market by programming unit.

The problem

• I90 ESIOS files are the richest public dataset of the Spanish electricity market, but they’re trapped in multi-sheet Excels
• No queryable database — every analysis requires manual download and parsing
• Queries like “how many MWh of constraints did this unit have in January?” take hours of manual work
• AI agents can’t access this data because there’s no programmatic interface

What if we could ask in natural language about generation programs and constraints in the electricity market?

What you’ll see

1. Schema design: ClickHouse schema optimized for analytical queries by programming unit
2. ETL transformation: Processing the 9 key sheets from I90 ESIOS files (PBF, constraints, deviations, tertiary…)
3. Incremental loading: Deduplication and efficient loading into ClickHouse
4. MCP server: Exposing the database so Claude or ChatGPT can query it
5. Live demo: Asking in natural language about constraints, deviations, and redispatch

Architecture

Stack

• Python 3.11+ · ClickHouse · Polars · MCP · I90 · REE

Who is this for

• Energy sector developers working with Spanish electricity market operational data
• Data engineers learning to build ETLs with ClickHouse and expose them via MCP
• Market analysts who want to query I90 ESIOS data without touching Excel

Note

I90 ESIOS files are published with a 90-day delay from the data date — the most recent available data corresponds to December 2025.