The problem
OpenAQ publishes readings from 55,580 stations. Each row looks simple: a station, a time, a pollutant, a number. They are not. Sensors drift, stations fall silent, and two stations in the same city report different units.
A warehouse that ignores that serves a clean map of wrong numbers.
The architecture
Daily run Reference data, once or on demand
The daily run fetches the station-days that are missing, rewrites the last six months of the lake, rebuilds the models and exports the map. The reference data changes rarely: the station sample, the country borders, the parameter list. Every step still runs by hand.
The model
One row per station, per pollutant, per hour. The map reads the monthly country table, about 140 rows, never the millions beneath it.
The monthly value is a mean, not a sum, because two days at 20 µg/m³ are not 40 µg/m³ of anything. The count of days above the WHO guideline does add up, so that count is the monthly total. A partial month says that it is partial.
The stack
| Layer | Tool |
|---|---|
| Storage and compute | DuckDB |
| Transform | dbt with dbt-duckdb |
| Schema and reads | SQLModel |
| Map | MapLibre GL, no tile provider |
| Borders | Natural Earth, 110m |
The map needs no API key and makes no third-party request.
What broke
The archive is gzipped CSV, not parquet. It partitions by station before date, so one day across every station would cost one list request per station. The loader works from a seed set and builds each station-day URL directly.
The first sample was 70 percent Japanese. Station ids run in join order, so the lowest 4,000 gave 1,219 Japanese stations out of 1,736. Discovery now samples evenly across all 55,580.
Timestamps arrive in local time, twice over. DuckDB applied the session timezone on read. Then the file names turned out to carry the station’s local day, so reading only the files inside the window lost 3,289 rows of 43,928.
The feed carries weather. Temperature, humidity and wind sit beside the pollutants, in units that cannot become a mass per volume. They are excluded, not converted.
Stations rename themselves. Ten stations had two identities at identical coordinates, such as al. Jana Pawła II against Al. Jana Pawła II. The newest reading wins.
A test that warns about everything is not a test. The silent-station check compared each station to wall clock time, so it flagged all of them. Measured against the newest reading in the warehouse, it names the ones that truly stopped.
Negative concentrations are real. PM2.5 reaches -1.6 µg/m³. An average that keeps those readings is quietly wrong.
Assumptions
The conversion from ppm to µg/m³ assumes 25 degrees C and one atmosphere. The feed measures temperature at many of these stations, so the constant can be replaced with the reading. That is the next change, and it is only possible because the weather rows arrived as a test failure.