# Daily city weather forecasts

> A daily snapshot of the Open-Meteo weather forecast for a fixed panel of eight major world cities: temperature, precipitation, wind, UV and sunrise/sunset for every day of the forecast horizon, recorded as of the day it was published. Because each run keeps what was forecast on that day, the series can be used to study how forecasts change as the target day approaches.

- Dataset id: `open-meteo-daily`
- Update cadence: daily
- Upstream source: https://open-meteo.com/en/docs
- Tags: weather, forecast, climate, cities, open-meteo, daily
- Current version: ingest `01M0NH25E3QSH5TCFXMRTAR9X0`, snapshot 2026-08-22, generated 2026-08-22T20:02:21.327Z

## Licence and attribution

- Licence: Creative Commons Attribution 4.0 International (`CC-BY-4.0`)
- Licence text: https://open-meteo.com/en/licence
- Required attribution: Weather data by Open-Meteo.com
- Notes: Open-Meteo publishes its API data under CC-BY-4.0 and asks that a link to https://open-meteo.com/ appear next to any display of the data; its source code is AGPL-3.0, which does not apply here. The free tier is non-commercial and rate limited to under 10,000 calls per day, 5,000 per hour and 600 per minute — this connector uses one call per run. Open-Meteo aggregates national weather services (DWD, ECMWF, NOAA, Met Norway, MeteoSwiss, KNMI, DMI, BOM, CMA, JMA and others); most are CC-BY or CC-BY-SA and some, notably NOAA and the JMA, publish under their own terms, so a reuser must honour those upstream attributions as well. What we publish is a derived work: the daily aggregations listed in api.ts, for eight fixed coordinates, reshaped into rows. These are model forecasts, not observations, and must not be presented as measurements.

Reproduce the attribution line verbatim anywhere you republish this data.

## Manifest

- This dataset: https://data-directory.fsn1.your-objectstorage.com/public/open-meteo-daily/manifest.json
- All datasets: https://data-directory.fsn1.your-objectstorage.com/public/index.json
- Human page: https://data.bunnytech.app/datasets/open-meteo-daily

The manifest is the contract: file URLs, row counts, column types, min/max statistics and
example queries all live there. Re-read it to discover a new version.

## Fastest path — Parquet exports

Immutable, physically sorted snapshot exports. No catalog fetch, best row-group pruning,
safe to cache forever. Register one view per table:

```sql
INSTALL httpfs; LOAD httpfs;

CREATE OR REPLACE VIEW daily_weather AS
  SELECT * FROM read_parquet('https://data-directory.fsn1.your-objectstorage.com/public/open-meteo-daily/v/01M0NH25E3QSH5TCFXMRTAR9X0/daily_weather.parquet');

CREATE OR REPLACE VIEW cities AS
  SELECT * FROM read_parquet('https://data-directory.fsn1.your-objectstorage.com/public/open-meteo-daily/v/01M0NH25E3QSH5TCFXMRTAR9X0/cities.parquet');
```

Example queries:

-- Today’s forecast for every city: Joins the fact table to the dimension. The snapshot_date range is what prunes: it is the leading sort column.
```sql
SELECT c.name, w.forecast_date, w.temperature_min, w.temperature_max, w.precipitation_sum
FROM daily_weather w
JOIN cities c USING (city_id)
WHERE w.snapshot_date = (SELECT max(snapshot_date) FROM daily_weather)
  AND w.lead_days = 0
ORDER BY w.temperature_max DESC;
```

-- How the forecast for one day drifted as it got closer: Every run keeps its own view of the future, so a single forecast_date has one row per lead time.
```sql
SELECT w.snapshot_date, w.lead_days, w.temperature_max
FROM daily_weather w
JOIN cities c USING (city_id)
WHERE c.name_lc = 'tokyo'
  AND w.forecast_date = DATE '2026-08-28'
ORDER BY w.lead_days DESC;
```

## Live lake — DuckLake catalog with time travel

Attach the published catalog read-only over plain https. This works in native DuckDB AND in
duckdb-wasm in the browser — the reader catalog resolves its data files through public https
URLs, so no credentials and no backend are involved.

```sql
INSTALL ducklake; LOAD ducklake;
ATTACH 'ducklake:https://data-directory.fsn1.your-objectstorage.com/catalog/open-meteo-daily/catalog.ducklake' AS lake (READ_ONLY); SELECT * FROM lake.main.daily_weather LIMIT 5;
USE lake;
```

- Latest catalog: https://data-directory.fsn1.your-objectstorage.com/catalog/open-meteo-daily/catalog.ducklake
- This exact version: https://data-directory.fsn1.your-objectstorage.com/catalog/open-meteo-daily/v/01M0NH25E3QSH5TCFXMRTAR9X0/catalog.ducklake (sha256 dd3036e2469184bf4018be74283b76cd9e2b46e022c3d55ad906aaa8316b8a35)
- Written by DuckDB v1.5.5
- Time travel: this ingest is snapshot 4 — e.g. `SELECT * FROM lake.daily_weather AT (VERSION => 4)`

## Wholesale — S3 access

For bulk downloads, read the bucket prefix directly over S3 with httpfs:

```sql
INSTALL httpfs; LOAD httpfs;
CREATE SECRET hetzner (
  TYPE s3,
  ENDPOINT 'fsn1.your-objectstorage.com',
  URL_STYLE 'path',
  KEY_ID '<public-read-not-required>', SECRET ''
);
SELECT * FROM read_parquet('s3://data-directory/public/open-meteo-daily/v/01M0NH25E3QSH5TCFXMRTAR9X0/*.parquet');
```

## Schema

### daily_weather
One row per city per forecast day, as of the run day. A full daily snapshot of the forecast horizon: re-running a day rewrites exactly that day for exactly those cities, so the history of what was forecast when is preserved and comparable.
- 56 rows · 5.4 KB · write mode `snapshot_history`
- Physically sorted by: `snapshot_date`, `city_id`, `forecast_date`
- Primary key: `snapshot_date`, `city_id`, `forecast_date`
- Files: 1
  - https://data-directory.fsn1.your-objectstorage.com/public/open-meteo-daily/v/01M0NH25E3QSH5TCFXMRTAR9X0/daily_weather.parquet (56 rows)

Columns:
- `snapshot_date` DATE NOT NULL — The run day (UTC) this forecast was captured on. (range: "2026-08-22" … "2026-08-22") (~1 distinct)
- `city_id` INTEGER NOT NULL — Our stable id for the city; joins to cities.city_id. (range: 1 … 8) (~9 distinct)
- `forecast_date` DATE NOT NULL — The day (UTC) this row describes. (range: "2026-08-22" … "2026-08-28") (~7 distinct)
- `lead_days` INTEGER NOT NULL [days] — forecast_date minus snapshot_date, in whole days. 0 is today, 1 is tomorrow. Lets you compare a forecast against the same day observed later.
- `weather_code` INTEGER — WMO 4677 weather code for the day (0 clear … 95+ thunderstorm).
- `temperature_max` DOUBLE [°C] — Highest 2 m air temperature of the day.
- `temperature_min` DOUBLE [°C] — Lowest 2 m air temperature of the day.
- `temperature_mean` DOUBLE [°C] — Mean 2 m air temperature of the day.
- `precipitation_sum` DOUBLE [mm] — Total precipitation (rain + showers + snow water equivalent).
- `rain_sum` DOUBLE [mm] — Rain only, excluding snow.
- `snowfall_sum` DOUBLE [cm] — Snowfall, as fresh snow depth rather than water equivalent.
- `precipitation_hours` DOUBLE [h] — Hours of the day with measurable precipitation.
- `wind_speed_max` DOUBLE [km/h] — Highest 10 m wind speed of the day.
- `wind_gusts_max` DOUBLE [km/h] — Highest 10 m wind gust of the day.
- `wind_direction_dominant` INTEGER [°] — Dominant 10 m wind direction, meteorological convention (0 = from the north, 90 = from the east). Rounded to whole degrees.
- `uv_index_max` DOUBLE — Highest UV index of the day.
- `sunrise` TIMESTAMPTZ — Sunrise instant. The request pins timezone=UTC, so the naive local time the API returns is UTC and is stored as such.
- `sunset` TIMESTAMPTZ — Sunset instant, on the same UTC basis as sunrise.
- `ingested_at` TIMESTAMP NOT NULL — When this row was produced by the ingest run (UTC).

### cities
The fixed panel of cities the forecast is collected for: our own stable id, the requested city-centre coordinates, and the model grid point Open-Meteo actually answered from. Reference data with no history — every run replaces it wholesale.
- 8 rows · 2.1 KB · write mode `replace`
- Physically sorted by: `city_id`
- Primary key: `city_id`
- Text search columns: `name`
- Files: 1
  - https://data-directory.fsn1.your-objectstorage.com/public/open-meteo-daily/v/01M0NH25E3QSH5TCFXMRTAR9X0/cities.parquet (8 rows)

Columns:
- `city_id` INTEGER NOT NULL — Stable id of the city. Ours, not Open-Meteo’s; never reused. (range: 1 … 8) (~9 distinct)
- `name` VARCHAR NOT NULL — City name as displayed.
- `name_lc` VARCHAR NOT NULL — name, lowercased and accent-folded. The prefix-search column — “sao paulo” must find “São Paulo”.
- `country` VARCHAR NOT NULL — Country name as displayed.
- `country_code` VARCHAR NOT NULL — ISO 3166-1 alpha-2 country code.
- `latitude` DOUBLE NOT NULL [°] — Requested city-centre latitude (WGS84).
- `longitude` DOUBLE NOT NULL [°] — Requested city-centre longitude (WGS84).
- `grid_latitude` DOUBLE [°] — Latitude of the model grid cell Open-Meteo actually answered from. NULL when the last run could not read it.
- `grid_longitude` DOUBLE [°] — Longitude of the model grid cell that answered.
- `elevation` DOUBLE [m] — Elevation of the grid cell above sea level.
- `timezone` VARCHAR — Timezone the API reported for the answer. Always UTC/GMT here.


## Performance notes

- There are no indexes in a lakehouse. The physical sort order plus Parquet row-group min/max
  statistics ARE the index. Predicates on the leading sort columns prune whole row groups;
  everything else is a full column scan.
- `daily_weather` prunes on `snapshot_date` → `city_id` → `forecast_date`.
- `cities` prunes on `city_id`.
- `ILIKE '%needle%'` cannot prune: it decompresses that whole column. Prefer prefix predicates
  on the lowercased search companion columns where they exist.
- Every file URL under `/v/<ingestId>/` is immutable. Cache it as aggressively as you like.
- Files are never rewritten in place — a new ingest writes a new version path. Re-read the
  manifest to pick up updates rather than polling the file URLs.
- Pull only the columns you need. Parquet is columnar and the reader range-GETs what it reads.

## Do not

- Do not scrape https://data.bunnytech.app HTML. Every number on those pages comes from the manifest above.
- Do not hit the upstream source (https://open-meteo.com/en/docs) in bulk. This mirror exists so you do not have to.
- Do not read DuckLake internal data files with a plain Parquet reader. They come with
  positional delete files, and ignoring those silently returns deleted rows. Use the ducklake
  extension, or use the published exports above.
- Do not republish without the attribution line, and do not imply endorsement.