Example · Public data · Scheduled SQL · Geospatial

Spain's rain, rivers and reservoirs, live

Every official source on Spain's water in one public Iceberg catalog, collected and derived by scheduled SQL inside LakehouseBox, declared in one file, shown by a public LakehouseBox artifact.

Open the artifact

Verified 11 November 2026

OFFICIAL SOURCES AEMET OpenData hourly rain, weather SAIH portals six basins, live levels MITECO bulletin weekly, since 1988 HydroSHEDS catchments, rivers → scheduled SQL, into the catalog every 15 min to once a day LAKEHOUSEBOX embalses catalog 20 Iceberg tables, public obs.* what the sources publish ref.* basins, reservoirs, H3 grid hourly, inside now.* rain grid, reservoir status 14 sinks, declared in lakehouse.yaml tables from sql/migrations/ READ BY The artifact a public page, live no backend of its own a live SQL box Any engine DuckDB, an agent
Official sources only, one catalog, no server of the project's own.

How much water Spain holds, and how much just fell.

Spain publishes its water in many places. AEMET, the state weather agency, publishes the rain of the last hour at every station. Each river basin authority runs a SAIH, a real-time network whose viewer shows its reservoirs' levels. MITECO publishes a weekly bulletin of every large reservoir, with history back to 1988. None of them keeps what it publishes in a form you can query: the hourly rain is gone after a day, a viewer only shows now.

embalses keeps all of it in one public Iceberg catalog and answers the questions they answer together: how full is each basin compared with the same week of the last ten years, how much rain fell upstream of each reservoir in the last 24 hours, which reservoirs are rising now. Everything runs inside LakehouseBox: the collection, the derived tables, the schedule, and the page people open: a public LakehouseBox artifact that reads the catalog live.

The artifact's national view: a map of Spain's basins coloured by how full their reservoirs are, the national level at 55.3 % with its change this week, and the year's curve against 2025 and the ten-year average.
The artifact's national view: a map of Spain's basins coloured by how full their reservoirs are, the national level at 55.3 % with its change this week, and the year's curve against 2025 and the ten-year average.
  1. Official sourcesAEMET, the SAIH of each basin, MITECO
  2. Scheduled SQL14 sinks, every 15 min to once a day
  3. embalses catalog20 public Iceberg tables
  4. The artifacta public page, live
weekly bulletin rows since 1988
725 k
reservoirs, with their catchments
375
scheduled SQL sinks, no server of the project's own
14
rain-grid rows rebuilt every hour
54 k

Counted in the embalses catalog on 11 November 2026 (lhbox query and lhbox sink list).

Before you start

  • A LakehouseBox accountScheduled SQL is on the free plan, two sinks, and this project runs 14 on a raised limit.
  • An AEMET OpenData keyFree, from AEMET, kept as the organisation's web key and never in a query.
  • The repositorygithub.com/LakehouseBox/embalses: lakehouse.yaml, main.tf, sql/, docs/.

Know the sources

Official sources only, each read the way its publisher offers it.

source what how it is read
AEMET OpenData hourly rain and weather at every station its API: a keyed call answers a short-lived URL, read at once
SAIH portals (six basins) live reservoir levels, volumes, inflow, outflow each basin's public viewer, as its own page asks for it
MITECO the weekly bulletin of every large reservoir, since 1988 its published files
HydroSHEDS (CC BY 4.0) catchments and rivers loaded once into ref.*

The SAIH viewers are public pages: each sink asks them what a visitor's browser would, every 15 minutes or every hour, one request at a time.

Tables come from migrations

Twenty documented tables, from SQL files.

The tables are declared in sql/migrations/ as plain DuckDB DDL, numbered and applied once each by lhbox migrate. A comment line above a table says what DuckDB's DDL cannot: its Iceberg format version and its partition.

sql/migrations/002_observations.sql, abridged
-- lhbox: format-version=3 partition=day(observed_at)
CREATE TABLE IF NOT EXISTS embalses.obs.reservoir_readings (
  station_id VARCHAR NOT NULL,
  observed_at TIMESTAMPTZ,
  stored_volume_hm3 DOUBLE,
  fill_pct DOUBLE,
  inflow_m3s DOUBLE,
  outflow_m3s DOUBLE,
  source VARCHAR
);
COMMENT ON COLUMN embalses.obs.reservoir_readings.stored_volume_hm3 IS 'Water stored, hm³.';
lhbox migrate --catalog embalses --dry-run
lhbox migrate --catalog embalses

The catalog records each file with its checksum: a second run applies nothing, and a fork of the repository gets the same tables from the same files. Three layers: ref.* holds the places (basins, catchments, rivers, 375 reservoirs, 2,787 stations, an H3 grid of 13,652 cells, with geometry columns, and ref.map_outlines: the same outlines as GeoJSON text, each reservoir's catchment included, for a map that needs no spatial extension), obs.* what the sources publish, now.* what is derived from both.

Collect with scheduled SQL

Fourteen sinks, each one SELECT, run by LakehouseBox on its own schedule.

Each source is read by a scheduled SQL sink: one DuckDB SELECT that LakehouseBox runs every 15 minutes, hourly or daily, its rows committed to a table. AEMET's API answers a URL that expires minutes later, so its query reads it in two steps with http_get. The key is the organisation's web key, sent by a connection; the query never holds it.

sql/ingest/aemet_precipitation.sql, abridged
WITH s AS MATERIALIZED (
  SELECT (http_get('https://opendata.aemet.es/opendata/api/observacion/convencional/todas')).body::JSON ->> 'datos' AS u
),
d AS MATERIALIZED (SELECT (http_get(u)).body AS txt FROM s),
o AS (SELECT unnest(from_json(txt, '[{"idema":"VARCHAR","fint":"VARCHAR","prec":"DOUBLE"}]'), recursive := true) FROM d)
SELECT 'aemet:' || idema AS station_id,
       strptime(fint, '%Y-%m-%dT%H:%M:%S%z') AS period_end_at,
       60 AS period_minutes,
       prec AS precipitation_mm
FROM o
WHERE prec IS NOT NULL
lhbox sink connection create aemet --catalog embalses --integration aemet
lhbox sink post-prefix add https://saihweb.chsegura.es/apps/iVisor/ --catalog embalses

The SAIH viewers that answer a form are read with http_post, only under the URL prefixes the catalog declared. Six basins feed one table, obs.reservoir_readings, one sink each, so a viewer that changes stops only its own sink.

Derive the now tables, inside

Hourly, from the catalog's own tables, each run swapping its table whole.

The now.* tables are what the page shows: the rain of the last 1, 6, 12, 24 and 72 hours on every cell of the H3 grid (inverse-distance weighted from the stations within 40 km), the rain over the land that drains into each reservoir, each reservoir's status against the same week of the last ten years, and each basin's totals. Each is a scheduled query that reads the catalog's own tables, in mode replace: every hourly run replaces its table in one commit, so a reader sees the last hour or the one before, never a mix. They run a few minutes apart, each after the one it reads.

The rain layer: the last 24 hours on the H3 grid over the peninsula, darkest in the south-west, with the two wettest gauges named.
The rain layer: the last 24 hours on the H3 grid over the peninsula, darkest in the south-west, with the two wettest gauges named.
One reservoir, El Atazar: its catchment shaded on the map, its level of 63.6 % and the year's curve against 2025 and the ten-year average.
One reservoir, El Atazar: its catchment shaded on the map, its level of 63.6 % and the year's curve against 2025 and the ten-year average.
lhbox sink create --catalog embalses --name now_basin_summary --table now.basin_summary \
  --sql @sql/now/basin_summary.sql --every 1h --offset 37m --mode replace --min-rows 10

Declare it all: lakehouse.yaml

One file, one command.

lakehouse.yaml declares the catalog, its migrations, AGENTS.md and table descriptions, its POST prefixes, its connection and its 14 sinks with their SQL in files. lhbox plan says what lhbox apply would change, with each query's diff; apply makes it, migrations first. Nothing is deleted unless --prune says so, and never a table. The repository also holds the same setup as main.tf, for the OpenTofu and Terraform provider: one tool or the other drives the catalog, not both.

version: 1
catalogs:
  embalses:
    public: true
    migrations: sql/migrations/
    agents_md: docs/AGENTS.md
    connections:
      aemet: { integration: aemet }
    sinks:
      aemet_precipitation: { table: obs.precipitation, sql: sql/ingest/aemet_precipitation.sql, every: 30m, offset: 23m }
      now_rain_grid: { table: now.rain_grid, sql: sql/now/rain_grid.sql, every: 1h, offset: 28m, mode: replace, min_rows: 1000 }
lhbox plan
lhbox apply

Publish the page as an artifact

One HTML file, published by CI, opened by anyone.

An artifact is a page published inside LakehouseBox. Its script names the catalogs it reads and queries them with lhbox.query(): LakehouseBox attaches them, and the page's SQL runs in DuckDB in the visitor's browser. The page holds no data and reaches no other host. Here it is artifact/page/index.html, shared publicly, so it opens without an account.

The map draws ref.map_outlines and the now.* tables, with no spatial extension. Every basin, region and reservoir has its own link (lhbox.setState), the CSV button saves through lhbox.download, and "Copiar enlace" copies lhbox.link. The open-data section runs SQL in the page and shows the ATTACH for your own DuckDB:

The artifact's open-data section: the DuckDB ATTACH snippet on the left, and on the right a SQL box listing the ten fullest reservoirs, with the result table below it.
The artifact's open-data section: the DuckDB ATTACH snippet on the left, and on the right a SQL box listing the ten fullest reservoirs, with the result table below it.

The repository's CI publishes every change to the page as its next version. artifact/artifact.json names the artifact, so each publish lands at the same address:

lhbox artifact publish artifact --note "what changed"

Pointed at a private catalog, the same page would show each person who opens it what they may read, and nothing else.

Read it from anywhere

A public catalog: no account, no key.

Any DuckDB attaches the catalog the page reads:

How full is each basin, now
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 basin_id, round(100 * sum(stored_volume_hm3) / sum(capacity_hm3), 1) AS fill_pct
FROM embalses."now".basin_summary
GROUP BY 1 ORDER BY 2 DESC;

The catalog's AGENTS.md tells an agent what each table means and how they join, so "which reservoirs rose most in the last 24 hours, and how much rain fell upstream of them" is a question to ask, not a schema to learn.

What to know
  • The live readings are what each viewer shows, as its basin authority publishes it: provisional, and corrected later by the weekly bulletin. The weekly bulletin is the reference series.
  • AEMET answers a rolling window of about 12 hours, so readings repeat between runs; the derived tables count each station and hour once.
  • The project's code, its declared setup and its documentation are public at github.com/LakehouseBox/embalses.