Weather · Public data

Daily weather

Temperature and rainfall by capital and day.

GHCN-Daily (NOAA National Centers for Environmental Information) · Public domain · commons_pubdata.weather.daily

Read Markdown ↗ Copy page

• 244,987rows
• One station per capital where GHCN has one within 60 km reporting since 2015coverage
• Weeklyrefresh
• 10 Oct 2026last refreshed
• 1.3 MBtable size

Chart Sample rows

Query it with DuckDB.

With an account Without an account

BashCopy

lhbox duckdb --catalog commons/pubdata

SQL · Last week in MadridCopy

SELECT d.day, d.tmax_c, d.tmin_c, d.precipitation_mm
FROM commons_pubdata.weather.daily AS d JOIN commons_pubdata.weather.stations AS s USING (station_id)
WHERE s.capital_city = 'Madrid' AND d.day >= current_date - 7
ORDER BY d.day;

Read it where you work.

Public, read-only data. With an account, lhbox duckdb attaches the catalog as commons_pubdata; an agent with the LakehouseBox connector opens it with open_public_catalog("commons/pubdata"). Without one, DuckDB reads the public files directly.

Example queries

• Last week in Madrid

  SELECT d.day, d.tmax_c, d.tmin_c, d.precipitation_mm
  FROM commons_pubdata.weather.daily AS d JOIN commons_pubdata.weather.stations AS s USING (station_id)
  WHERE s.capital_city = 'Madrid' AND d.day >= current_date - 7
  ORDER BY d.day;

• Your daily sales against the capital's rain, by country

  SELECT s.day, s.country_code, s.revenue, w.precipitation_mm, w.tmax_c
  FROM my.sales_daily AS s
  JOIN commons_pubdata.weather.stations AS st ON st.country_code = s.country_code
  JOIN commons_pubdata.weather.daily AS w ON w.station_id = st.station_id AND w.day = s.day;

Know your columns.

Column	Type	Meaning
station_id	string	GHCN station id (joins weather.stations)
day	date	the local calendar day of the observation
tmax_c	double	maximum temperature, °C
tmin_c	double	minimum temperature, °C
tavg_c	double	average temperature, °C (not every station reports it)
precipitation_mm	double	precipitation, mm

View all 8 columns

snowfall_mm	double	snowfall, mm
snow_depth_mm	double	snow depth, mm

Join keys: station_id, day.

Companion tables

• commons_pubdata.weather.stations: one row per station: name, coordinates, elevation, the capital and country it stands for, the distance

Before you query.

1. Join through weather.stations to get from a country or capital to a station_id; one station per country.
2. Nulls are normal (a station may not report TAVG, or a day may be late).
3. The most recent 3-7 days are often incomplete.
4. Units are metric; temperatures in °C, precipitation in mm.

Source & licence.

Source

GHCN-Daily (NOAA National Centers for Environmental Information)

Licence

Public domain (US government work); attribution requested

Attribution

Menne, M.J., I. Durre, R.S. Vose, B.E. Gleason, and T.G. Houston, 2012: An overview of the Global Historical Climatology Network-Daily Database. NOAA NCEI.

Coverage

one station per capital where GHCN has one within 60 km reporting since 2015 (62 capitals at the first load); 2015-01-01 to a few days ago

Refresh

weekly, the whole table replaced; source version ghcn-daily 2026-10-10

Table

commons/pubdata · weather.daily · Iceberg v2 · browse the catalog

Recipe

pubdata/recipes/weather_daily.py, registry entry pubdata/registry/weather_daily.yaml

Source: GHCN-Daily (NOAA National Centers for Environmental Information) · Public domain · Snapshot shown: 10 Oct 2026HTML for people. Markdown for agents.

# Daily weather

*Temperature and rainfall by capital and day.*

Daily observations from NOAA's Global Historical Climatology Network for a weather station at each of the world's capitals (the nearest station within 60 km that reports since 2015 and still reports this year): maximum, minimum and average temperature, precipitation, snowfall and snow depth, from 2015 on. One row per station and day. The companion table `weather.stations` says which station stands for which capital, and how far.

## At a glance

| | |
|---|---|
| Table | `pubdata.weather.daily` |
| Category | Weather · Daily, Time series, Weather |
| Rows | 244,987 |
| Size | 1.3 MB in 1 data file(s) |
| Iceberg format | v2 |
| Last refreshed | 2026-10-10T12:53:00Z |
| Source version | ghcn-daily 2026-10-10 |
| Refresh cadence | weekly (replace) |
| Coverage | one station per capital where GHCN has one within 60 km reporting since 2015 (62 capitals at the first load); 2015-01-01 to a few days ago |

## Origin

- **Source:** [GHCN-Daily (NOAA National Centers for Environmental Information)](https://www.ncei.noaa.gov/products/land-based-station/global-historical-climatology-network-daily)
- **Licence:** Public domain (US government work); attribution requested
- **Attribution:** Menne, M.J., I. Durre, R.S. Vose, B.E. Gleason, and T.G. Houston, 2012: An overview of the Global Historical Climatology Network-Daily Database. NOAA NCEI.
- **Note:** Stations report with a lag of days; recent days can be missing or partly filled.
- **Recipe:** `pubdata/recipes/weather_daily.py` in the LakehouseBox repository; registry entry `pubdata/registry/weather_daily.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `station_id` | string | GHCN station id (joins weather.stations) |
| `day` | date | the local calendar day of the observation |
| `tmax_c` | double | maximum temperature, °C |
| `tmin_c` | double | minimum temperature, °C |
| `tavg_c` | double | average temperature, °C (not every station reports it) |
| `precipitation_mm` | double | precipitation, mm |
| `snowfall_mm` | double | snowfall, mm |
| `snow_depth_mm` | double | snow depth, mm |

**Join keys:** `station_id`, `day`.

## Companion tables

- `pubdata.weather.stations`: one row per station: name, coordinates, elevation, the capital and country it stands for, the distance (62 rows)

## For agents

Join through `weather.stations` to get from a country or capital to a `station_id`; one station per country.
Nulls are normal (a station may not report TAVG, or a day may be late). The most recent 3-7 days are often
incomplete. Units are metric; temperatures in °C, precipitation in mm.

## Example SQL (DuckDB)

```sql
-- last week in Madrid
SELECT d.day, d.tmax_c, d.tmin_c, d.precipitation_mm
FROM pubdata.weather.daily AS d JOIN pubdata.weather.stations AS s USING (station_id)
WHERE s.capital_city = 'Madrid' AND d.day >= current_date - 7
ORDER BY d.day;
-- your daily sales against the capital's rain, by country
SELECT s.day, s.country_code, s.revenue, w.precipitation_mm, w.tmax_c
FROM my.sales_daily AS s
JOIN pubdata.weather.stations AS st ON st.country_code = s.country_code
JOIN pubdata.weather.daily AS w ON w.station_id = st.station_id AND w.day = s.day;
```

## How to read it

Without an account, over HTTPS (the bucket is public):

```sql
INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
-- anonymous: the bucket is public, requests go unsigned, through the public-data cache
CREATE OR REPLACE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
CREATE OR REPLACE VIEW daily AS SELECT * FROM iceberg_scan('s3://commons--pubdata/weather/daily');
SELECT * FROM daily LIMIT 10;
```

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.weather.daily LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/weather/daily/`. This card: `https://lakehousebox.com/pubdata/cards/weather_daily.md`; the page: https://lakehousebox.com/public-data/weather_daily. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.