# 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.