Skip to content

About

Daily ETL pipeline that ingests Spain's hourly electricity prices (PVPC) from the REE API into PostgreSQL, orchestrated with Apache Airflow 3 and Docker Compose.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

2 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 

Repository files navigation

Spain Electricity Prices Pipeline

A small, focused data pipeline that ingests Spain's hourly electricity prices (PVPC) from the public Red Eléctrica de España (REE) API into PostgreSQL every day, orchestrated with Apache Airflow 3 and running entirely on Docker Compose.

Overview

flowchart LR
    API[REE API] --> extract
    subgraph Airflow DAG: electricity_prices
        extract --> load
        create_table --> load
    end
    load --> DB[(PostgreSQL)]
Loading

The DAG runs once a day and has three tasks:

Task What it does
extract Calls the REE API for the run's date and returns the hourly PVPC prices. Retries up to 3 times if the API fails.
create_table Creates the electricity_prices table if it does not exist.
load Upserts the prices into PostgreSQL.

Tech stack

  • Apache Airflow 3 (TaskFlow API, CeleryExecutor)
  • PostgreSQL 16 as the destination database
  • Docker Compose for local orchestration
  • Python with requests and the Airflow Postgres provider

Data model

Table electricity_prices:

Column Type Description
datetime TIMESTAMPTZ (primary key) Hour the price applies to
price_eur_mwh NUMERIC(10, 2) PVPC price in €/MWh
loaded_at TIMESTAMPTZ When the row was inserted or last updated

Design decisions

  • Idempotent loads. Rows are written with INSERT ... ON CONFLICT (datetime) DO UPDATE, so re-running a day updates the existing rows instead of duplicating them.
  • Date-driven extraction. Each run requests the day given by its logical_date, not "today". A re-run of a past date always fetches that same date.
  • Explicit time zone handling. Airflow works in UTC, but electricity prices are published per Spanish calendar day. The run date is converted to Europe/Madrid before calling the API, otherwise a run at midnight in Spain would request the previous day.
  • Separate databases. Price data lives in its own PostgreSQL service (prices-db), isolated from Airflow's metadata database.
  • Connection as configuration. The prices_db connection is defined through the AIRFLOW_CONN_PRICES_DB environment variable in docker-compose.yaml, so the project runs after cloning with no manual setup in the UI.

Getting started

Requirements

  • Docker and Docker Compose
  • At least 4 GB of RAM available to Docker

Run it

git clone https://github.com/juancarlosfdzcode/spain_airflow_electricity_prices.git
cd spain_airflow_electricity_prices

# Local folders and environment file
mkdir -p logs plugins config
cp .env.example .env
sed -i "s/^AIRFLOW_UID=.*/AIRFLOW_UID=$(id -u)/" .env

# Initialise Airflow and start all services
docker compose up airflow-init
docker compose up -d

Then open http://localhost:8080 and log in with user airflow and password airflow. Unpause the electricity_prices DAG and trigger it.

Check the results

docker compose exec prices-db psql -U prices -d prices \
  -c "SELECT * FROM electricity_prices ORDER BY datetime LIMIT 24;"

The database is also exposed on localhost:5433 (user, password and database: prices) for tools such as DBeaver or psql.

Stop it

docker compose down        # stop the services
docker compose down -v     # stop and delete all data

Project structure

.
├── dags/
│   └── electricity_prices.py   # The DAG
├── docker-compose.yaml         # Airflow services + prices database
├── .env.example                # Environment variables template
└── README.md

Possible next steps

  • Data quality task between extract and load (expected number of records, no nulls)
  • Daily summary with the cheapest and most expensive hours
  • Backfill of historical prices

Notes

Credentials in docker-compose.yaml are for local development only. In a real deployment they would be stored in a secrets manager.

Data source: REData API by Red Eléctrica de España.

About

Daily ETL pipeline that ingests Spain's hourly electricity prices (PVPC) from the REE API into PostgreSQL, orchestrated with Apache Airflow 3 and Docker Compose.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages