Where is it raining in Spain, and is that rain reaching the reservoirs?
A live, open dataset joining Spain's official rain gauges, river gauges and reservoirs, kept up to date inside LakehouseBox. It is published as a public Apache Iceberg catalog and read straight from the browser.
-
The page: https://lakehousebox.com/a/II58fpqq/embalses-el-agua-en-espana. A LakehouseBox artifact (
artifact/page/index.html): LakehouseBox attaches the public catalog and the page's SQL runs in DuckDB 2.0 in the visitor's browser. The map outlines come from the catalog too, so the page holds no data and has no backend. It has deep links to every basin, region, province and reservoir, a live SQL box and CSV download. The old address, https://lakehousebox.github.io/embalses/, redirects there. -
Data: the public catalog
embalses. Attach it from any DuckDB, no account needed:INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs; ATTACH 'jatorre/embalses' AS embalses (TYPE iceberg, ENDPOINT 'https://public-catalog.lakehousebox.com', AUTHORIZATION_TYPE 'none'); SELECT * FROM embalses."now".reservoir_status LIMIT 10;
-
For AI agents:
docs/AGENTS.mdexplains every table, how they join, the units and the caveats. LakehouseBox serves it at the catalog root.
| Namespace | What | Tables |
|---|---|---|
ref |
places | basins, catchments, rivers, reservoirs, reservoir_catchments, reservoir_links, stations, municipalities, supply_units, municipality_supply, supply_sources, grid_cells, catchment_cells, map_outlines (GeoJSON text for drawing) |
obs |
measurements, append-only | precipitation, weather, reservoir_readings, river_flow, reservoir_weekly (since 1988), daily_weather (since Oct 2023) |
now |
rebuilt hourly | rain_grid (H3), upstream_rain, reservoir_status, basin_summary, station_status |
Every column is documented in the catalog itself (docs/table_docs.json).
official sources ──► scheduled SQL in LakehouseBox ──► Iceberg tables (obs.*)
│
scheduled SQL, sql/now/ (hourly) ◄──────┘──► now.* ──► the page (a LakehouseBox artifact)
- Ingestion is SQL. Each source is one query in
sql/ingest/that LakehouseBox runs on a schedule. It fetches the publisher's own feed, reshapes it and appends it to an Iceberg table. - Derived tables are SQL too. One query per table in
sql/now/, run hourly by LakehouseBox itself:- rain is interpolated onto an H3 grid (inverse distance, gauges within 40 km);
- it's averaged over every catchment upstream of each dam;
- and compared with live storage and the same week in past years.
- The page is one file.
artifact/page/index.html, plain HTML and JavaScript with no build step, plus its font inartifact/assets/. It reads the catalog through LakehouseBox's artifact runtime (lhbox.query), so it runs inside LakehouseBox rather than from a local server.
There is no server, worker or cron job of ours anywhere: LakehouseBox runs the SQL and serves the page.
Nothing was set up by hand in a console. The whole deployment is in this repository, and CI keeps LakehouseBox equal to main.
| What | Where | Applied by |
|---|---|---|
| Tables, partitioning, column comments | sql/migrations/: plain DuckDB DDL, numbered, append-only |
lhbox migrate |
| Catalog, connection, POST prefixes, the 14 scheduled sinks | lakehouse.yaml, or the same in main.tf |
lhbox apply, or tofu apply |
| The SQL each sink runs | sql/ingest/, sql/now/ |
with its sink |
| Documentation served by the catalog | docs/AGENTS.md, docs/table_docs.json |
with the catalog |
| The page | artifact/ |
lhbox artifact publish (CI, on every change) |
| Redirect from the old address | site/ |
GitHub Pages |
Tables are migrations rather than Terraform resources on purpose: a schema is changed by a reviewed, ordered script, and no apply can ever drop a table or its rows. It is the usual split between Flyway or dbt for schemas and Terraform for infrastructure.
CI (.github/workflows/lakehouse.yml):
- a pull request shows what would change:
lhbox migrate --dry-run,lhbox planandtofu plan; - a merge to
mainrunslhbox migrateandlhbox apply; - then OpenTofu adopts the live catalog and plans
main.tfagainst it. That plan must be empty, so the two declarations cannot drift apart.
The only secret is one LakehouseBox API key. The AEMET key is an integration held by LakehouseBox and never appears in the repository, in CI or in Terraform state.
Loaded once, not on every merge: the reference geography in sql/reference/ (basins, catchments, rivers, stations, map outlines) and the history backfills in tools/ (MITECO weekly bulletin since 1988, AEMET daily values since 2023). They are scripts you run when the source changes.
Official sources only, credited as their publishers ask:
- MITECO: weekly reservoir bulletin (Boletín Hidrológico), dam inventory (Inventario de Presas y Embalses), SAIH station register, river basin districts.
- AEMET OpenData: hourly and daily weather observations.
- SAIH real-time networks of the river-basin authorities: Ebro, Guadalquivir, Guadiana, Segura, Miño-Sil and Cantábrico (more to come).
- HydroSHEDS (Lehner & Grill 2013): HydroBASINS and HydroRIVERS, CC BY 4.0.
Real-time values are provisional: the publishers haven't validated them.
lhbox catalog create embalses
lhbox migrate --catalog embalses # creates the 20 tables from sql/migrations/, with partitioning and descriptions
lhbox plan -f lakehouse.yaml # shows what apply would create or change
lhbox apply -f lakehouse.yaml # connections, POST prefixes, the 14 scheduled sinks, table descriptionsmain.tf declares the same deployment for the LakehouseBox provider, which installs with both tofu and terraform:
export LHBOX_API_KEY=... # `lhbox login` makes one
tofu init
tofu apply -target=lakehousebox_catalog.embalses # the catalog first
lhbox migrate --catalog embalses # then its tables
tofu apply # then everything that fills and documents themTo adopt a catalog that already exists, tools/tofu_adopt.sh imports its 40 resources and shows the plan. It is what CI runs.
The code is under the Apache License 2.0. The artifact ships the Source Sans 3 font (Adobe, SIL Open Font License 1.1). The data belongs to its publishers and keeps their terms: see Sources.
