# GHCN-Daily: global daily weather observations

> The global daily weather observation archive NOAA publishes as the Global Historical Climatology Network – Daily, kept in the source’s own long form — one row per station, day and element: maximum and minimum temperature, precipitation, snow, wind, pressure, humidity, sunshine, soil temperature and weather types. About 3.2 billion measurements from 132,501 stations worldwide, some reaching back to 1750, plus the 782,538-row inventory saying which years and elements each station covers. REFERENCED LIVE, NOT COPIED: five of the six tables — observations, stations, inventory, countries, states — are published as views over NOAA’s own public AWS Open Data bucket, so no row of them is stored in this directory’s lake and every query reads NOAA directly. That means the data is always exactly what NOAA publishes right now, which matters more here than for most sources: NOAA reprocesses its ENTIRE period of record every day, so a stored copy would freeze yesterday’s revision of an archive that runs from 1763 to today. It also means there is no history of what a value used to say before NOAA revised it, no row counts or column statistics of our own, and a query needs network access to s3://noaa-ghcn-pds (public, no credentials). The sixth table, elements, IS stored here as ordinary parquet — all 213 documented measurement types rather than a curated handful — because it is transcribed from the source’s prose readme and has no upstream file to read live. Every observation row carries both the integer NOAA published and the value converted to its real unit, alongside the quality, measurement and source flags that say how far to trust it.

- Dataset id: `ghcn-daily`
- Update cadence: daily
- Upstream source: https://registry.opendata.aws/noaa-ghcn/
- Tags: weather, climate, noaa, ghcn, observations, temperature, precipitation
- Current version: ingest `01M5GS2HENV35C87PAWCVAV4ZY`, snapshot 2026-10-22, generated 2026-10-22T03:04:38.782Z

## Licence and attribution

- Licence: Creative Commons Zero v1.0 Universal (US Government work, public domain) (`CC0-1.0`)
- Licence text: https://www.ncei.noaa.gov/access/crn/pdf/NOAA-Data-Policy.pdf
- Required attribution: Data from the NOAA National Centers for Environmental Information, Global Historical Climatology Network – Daily (GHCN-Daily), Version 3. Menne, M.J., I. Durre, B. Korzeniewski, S. McNeill, K. Thomas, X. Yin, S. Anthony, R. Ray, R.S. Vose, B.E. Gleason and T.G. Houston, 2012. doi:10.7289/V5D21VHZ.
- Notes: GHCN-Daily is a work of the United States federal government, produced by NOAA’s National Centers for Environmental Information, and US federal works carry no copyright under 17 U.S.C. §105. NOAA’s data policy places its data in the public domain and asks for acknowledgement rather than requiring it; the AWS Open Data registry entry for s3://noaa-ghcn-pds states the same. Redistribution is therefore permitted, and CC0-1.0 is the closest SPDX identifier to "no rights reserved, attribution requested". IMPORTANT CAVEATS FOR A REUSER. (1) GHCN-Daily is an AGGREGATE: it merges data from many national and regional meteorological services — Environment Canada, the Australian Bureau of Meteorology, the European Climate Assessment and Dataset, the China Meteorological Administration, CONAGUA, the Fiji Met Service, several African services whose data was previously withheld pending permission, and about twenty more, each identified by the source_flag column. Those providers’ own terms may differ from NOAA’s for their contribution, and a reuser redistributing a subset should check the flag. NOAA publishes the merged product openly, and that is what is republished here. (2) Values that FAILED a quality check are published, not removed: a non-null quality_flag means the value failed the check that letter names. Presenting flagged values as measurements without saying so misrepresents the source. (3) Values are model-free observations, but TAVG from source flag "S" is derived from hourly synoptic reports and can differ significantly from a true daily mean, particularly for precipitation — the readme says so explicitly. (4) What is republished is a derived work: the source’s own columns, reshaped, with a scaled value and the element catalogue added. (5) For five of the six tables nothing is redistributed at all — they are published as views over NOAA’s own bucket, so what is offered is the reshaping, not the bytes, and a reader’s query fetches the data from NOAA under NOAA’s terms directly. Only `elements`, transcribed from the source readme, is actually stored and served here.

Reproduce the attribution line verbatim anywhere you republish this data.

## Manifest

- Start here for how this directory works: https://data.bunnytech.app/llms.txt
- This dataset: https://data-directory.fsn1.your-objectstorage.com/public/ghcn-daily/manifest.json
- All datasets: https://data-directory.fsn1.your-objectstorage.com/public/index.json
- Human page: https://data.bunnytech.app/datasets/ghcn-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 elements AS
  SELECT * FROM read_parquet('https://data-directory.fsn1.your-objectstorage.com/public/ghcn-daily/v/01M5GS2HENV35C87PAWCVAV4ZY/elements.parquet');

-- observations: queried live at the source, never copied here — no parquet export.

-- stations: queried live at the source, never copied here — no parquet export.

-- countries: queried live at the source, never copied here — no parquet export.

-- states: queried live at the source, never copied here — no parquet export.

-- inventory: queried live at the source, never copied here — no parquet export.
```

Example queries:

-- A station’s temperature series, in real units: observations is a view over NOAA’s bucket, so what prunes here is NOAA’s own layout, not ours: the archive is Hive-partitioned YEAR=/ELEMENT=, and obs_year plus element are exactly those two partition keys, so this reads a handful of the 14,032 files rather than the archive. Keep both predicates — dropping the year turns it into a full scan of every year’s TMAX. quality_flag IS NULL keeps only values that passed every check.
```sql
ATTACH IF NOT EXISTS 'ducklake:https://data-directory.fsn1.your-objectstorage.com/catalog/catalog.ducklake' AS "datadirectory" (READ_ONLY);
CREATE OR REPLACE SECRET "noaa_ghcn_pds" (TYPE s3, SCOPE 's3://noaa-ghcn-pds', PROVIDER 'config', REGION 'us-east-1');
ATTACH IF NOT EXISTS ':memory:' AS "ghcn_daily";
CREATE SCHEMA IF NOT EXISTS "ghcn_daily"."main";
CREATE OR REPLACE VIEW "ghcn_daily"."main"."observations" AS SELECT
  "ID"                             AS station_id,
  strptime("DATE", '%Y%m%d')::DATE AS obs_date,
  "ELEMENT"                        AS element,
  CAST("YEAR" AS INTEGER)          AS obs_year,
  "DATA_VALUE"                     AS data_value,
  CASE
    WHEN "ELEMENT" IN ('SNOW', 'SNWD', 'ACMC', 'ACMH', 'ACSC', 'ACSH', 'AWDR', 'DAEV', 'DAPR', 'DASF', 'DATN', 'DATX', 'DAWM', 'DWPR', 'FMTM', 'FRGB', 'FRGT', 'FRTH', 'GAHT', 'MDSF', 'MDWM', 'PGTM', 'PSUN', 'RHAV', 'RHMN', 'RHMX', 'TSUN', 'WDF1', 'WDF2', 'WDF5', 'WDFG', 'WDFI', 'WDFM', 'WDMV', 'WT01', 'WT02', 'WT03', 'WT04', 'WT05', 'WT06', 'WT07', 'WT08', 'WT09', 'WT10', 'WT11', 'WT12', 'WT13', 'WT14', 'WT15', 'WT16', 'WT17', 'WT18', 'WT19', 'WT21', 'WT22', 'WV01', 'WV03', 'WV07', 'WV18', 'WV20') THEN "DATA_VALUE" / 1.0
    WHEN "ELEMENT" IN ('PRCP', 'TMAX', 'TMIN', 'ADPT', 'ASLP', 'ASTP', 'AWBT', 'AWND', 'EVAP', 'MDEV', 'MDPR', 'MDTN', 'MDTX', 'MNPN', 'MXPN', 'SN01', 'SN02', 'SN03', 'SN04', 'SN05', 'SN06', 'SN07', 'SN11', 'SN12', 'SN13', 'SN14', 'SN15', 'SN16', 'SN17', 'SN21', 'SN22', 'SN23', 'SN24', 'SN25', 'SN26', 'SN27', 'SN31', 'SN32', 'SN33', 'SN34', 'SN35', 'SN36', 'SN37', 'SN41', 'SN42', 'SN43', 'SN44', 'SN45', 'SN46', 'SN47', 'SN51', 'SN52', 'SN53', 'SN54', 'SN55', 'SN56', 'SN57', 'SN61', 'SN62', 'SN63', 'SN64', 'SN65', 'SN66', 'SN67', 'SN71', 'SN72', 'SN73', 'SN74', 'SN75', 'SN76', 'SN77', 'SN81', 'SN82', 'SN83', 'SN84', 'SN85', 'SN86', 'SN87', 'SX01', 'SX02', 'SX03', 'SX04', 'SX05', 'SX06', 'SX07', 'SX11', 'SX12', 'SX13', 'SX14', 'SX15', 'SX16', 'SX17', 'SX21', 'SX22', 'SX23', 'SX24', 'SX25', 'SX26', 'SX27', 'SX31', 'SX32', 'SX33', 'SX34', 'SX35', 'SX36', 'SX37', 'SX41', 'SX42', 'SX43', 'SX44', 'SX45', 'SX46', 'SX47', 'SX51', 'SX52', 'SX53', 'SX54', 'SX55', 'SX56', 'SX57', 'SX61', 'SX62', 'SX63', 'SX64', 'SX65', 'SX66', 'SX67', 'SX71', 'SX72', 'SX73', 'SX74', 'SX75', 'SX76', 'SX77', 'SX81', 'SX82', 'SX83', 'SX84', 'SX85', 'SX86', 'SX87', 'TAVG', 'TAXN', 'THIC', 'TOBS', 'WESD', 'WESF', 'WSF1', 'WSF2', 'WSF5', 'WSFG', 'WSFI', 'WSFM') THEN "DATA_VALUE" / 10.0
    ELSE NULL
  END                 AS scaled_value,
  nullif(trim("M_FLAG"), '')       AS measurement_flag,
  nullif(trim("Q_FLAG"), '')       AS quality_flag,
  nullif(trim("S_FLAG"), '')       AS source_flag,
  nullif(trim("OBS_TIME"), '')     AS obs_time,
  DATE '2026-10-22'                AS snapshot_date,
  TIMESTAMPTZ '2026-10-22T03:04:32.981Z'                       AS ingested_at
FROM read_parquet('s3://noaa-ghcn-pds/parquet/by_year/YEAR=2026/*/*.parquet', hive_partitioning = true, union_by_name = true);

SELECT o.obs_date, o.scaled_value AS tmax_c
FROM ghcn_daily.main.observations o
WHERE o.obs_year BETWEEN 2020 AND 2026
  AND o.element = 'TMAX'
  AND o.station_id = 'ROE00100899'
  AND o.quality_flag IS NULL
ORDER BY o.obs_date;
```

-- Which stations have a century of maximum temperature: Answered from the 782,538-row inventory rather than from the fact table — the reason inventory is published as its own table. Both are views, so this reads two small fixed-width files at the bucket root instead of three billion observation rows.
```sql
ATTACH IF NOT EXISTS 'ducklake:https://data-directory.fsn1.your-objectstorage.com/catalog/catalog.ducklake' AS "datadirectory" (READ_ONLY);
CREATE OR REPLACE SECRET "noaa_ghcn_pds" (TYPE s3, SCOPE 's3://noaa-ghcn-pds', PROVIDER 'config', REGION 'us-east-1');
ATTACH IF NOT EXISTS ':memory:' AS "ghcn_daily";
CREATE SCHEMA IF NOT EXISTS "ghcn_daily"."main";
CREATE OR REPLACE VIEW "ghcn_daily"."main"."inventory" AS SELECT
  nullif(trim(substr(line, 1, 11)), '')                        AS station_id,
  nullif(trim(substr(line, 32, 4)), '')                       AS element,
  TRY_CAST(nullif(trim(substr(line, 37, 4)), '') AS INTEGER)  AS first_year,
  TRY_CAST(nullif(trim(substr(line, 42, 4)), '') AS INTEGER)  AS last_year,
  TRY_CAST(nullif(trim(substr(line, 13, 8)), '') AS DOUBLE)   AS latitude,
  TRY_CAST(nullif(trim(substr(line, 22, 9)), '') AS DOUBLE)   AS longitude,
  DATE '2026-10-22'                      AS snapshot_date,
  TIMESTAMPTZ '2026-10-22T03:04:32.981Z'                             AS ingested_at
FROM read_csv('s3://noaa-ghcn-pds/ghcnd-inventory.txt', header = false, normalize_names = false, sample_size = -1, delim = '', quote = '', escape = '', columns = {'line': 'VARCHAR'})
WHERE trim(line) <> '';
CREATE OR REPLACE VIEW "ghcn_daily"."main"."stations" AS SELECT
  nullif(trim(substr(line, 1, 11)), '')                                   AS station_id,
  substr(line, 1, 2)                                AS country_code,
  substr(line, 3, 1)                                AS network_code,
  TRY_CAST(nullif(trim(substr(line, 13, 8)), '') AS DOUBLE)              AS latitude,
  TRY_CAST(nullif(trim(substr(line, 22, 9)), '') AS DOUBLE)              AS longitude,
  nullif(TRY_CAST(nullif(trim(substr(line, 32, 6)), '') AS DOUBLE), -999.9) AS elevation,
  nullif(trim(substr(line, 39, 2)), '')                                  AS state_code,
  nullif(trim(substr(line, 42, 30)), '')                                  AS name,
  lower(strip_accents(nullif(trim(substr(line, 42, 30)), '')))            AS name_lc,
  nullif(trim(substr(line, 73, 3)), '')                                  AS gsn_flag,
  nullif(trim(substr(line, 77, 3)), '')                                  AS hcn_crn_flag,
  nullif(trim(substr(line, 81, 5)), '')                                  AS wmo_id,
  DATE '2026-10-22'                                 AS first_seen_date,
  DATE '2026-10-22'                                 AS last_seen_date,
  TIMESTAMPTZ '2026-10-22T03:04:32.981Z'                                        AS ingested_at
FROM read_csv('s3://noaa-ghcn-pds/ghcnd-stations.txt', header = false, normalize_names = false, sample_size = -1, delim = '', quote = '', escape = '', columns = {'line': 'VARCHAR'})
WHERE trim(line) <> '';

SELECT s.name, s.country_code, i.first_year, i.last_year
FROM ghcn_daily.main.inventory i
JOIN ghcn_daily.main.stations s USING (station_id)
WHERE i.element = 'TMAX' AND i.first_year <= 1900 AND i.last_year >= 2020
ORDER BY i.first_year;
```

## 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/ghcn-daily/catalog.ducklake' AS ghcn_daily (READ_ONLY); SELECT * FROM ghcn_daily.main.elements LIMIT 5;
USE ghcn_daily;
```

- Latest catalog: https://data-directory.fsn1.your-objectstorage.com/catalog/ghcn-daily/catalog.ducklake
- This exact version: https://data-directory.fsn1.your-objectstorage.com/catalog/ghcn-daily/v/01M5GS2HENV35C87PAWCVAV4ZY/catalog.ducklake (sha256 7c93a081bb28697f9d2d65390738443fe6c9b28218d20f5cd2676a7e40517712)
- Written by DuckDB v1.5.5
- Time travel: this ingest is snapshot 21 — e.g. `SELECT * FROM ghcn_daily.elements AT (VERSION => 21)`

## 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/ghcn-daily/v/01M5GS2HENV35C87PAWCVAV4ZY/**/*.parquet');
```

## Schema

### elements
What each of the 213 GHCN-Daily element codes measures, in which unit, and by what factor its stored integer must be divided to get there. Transcribed from section III of the source’s own readme.txt. This is the table that makes observations readable: without it ADPT, SN35 and WT11 are three opaque codes, and a value column whose scale silently changes between rows. It covers the whole documented code space, which is a superset of the 144 codes the archive currently uses, so a code NOAA starts publishing tomorrow already has a name here. This is the ONE table of this dataset that is genuinely STORED in the lake as parquet: the other five are views over NOAA’s bucket, but the readme is prose rather than a machine-readable file, so there is nothing upstream to read live. It therefore has a real row count, real column statistics and DuckLake history, and joining to it from observations costs nothing over the network.
- 213 rows · 14 KB · write mode `replace`
- Physically sorted by: `element_code`
- Primary key: `element_code`
- Files: 1
  - https://data-directory.fsn1.your-objectstorage.com/public/ghcn-daily/v/01M5GS2HENV35C87PAWCVAV4ZY/elements.parquet (213 rows)

Columns:
- `element_code` VARCHAR NOT NULL — The four-character code as it appears in observations.element. (range: "ACMC" … "WV20") (~178 distinct)
- `name` VARCHAR NOT NULL — What the code measures, in English.
- `name_ro` VARCHAR NOT NULL — What the code measures, in Romanian.
- `element_group` VARCHAR NOT NULL — Family the code belongs to, in English, so 213 rows can be browsed rather than scanned: temperature, precipitation, snow, wind, pressure, humidity, sunshine, cloud, soil, ground, evaporation, multiday, weather-type, other.
- `element_group_ro` VARCHAR NOT NULL — The same family name, in Romanian.
- `unit` VARCHAR NOT NULL — The unit observations.scaled_value is expressed in for this code: °C, mm, m/s, hPa, %, degrees, cm, km, minutes, days, HHMM, or indicator.
- `scale_factor` INTEGER NOT NULL — What observations.data_value is divided by to reach `unit`. 10 for everything the readme calls "tenths of…" or "* 10"; 1 where the stored integer already is the measurement. Getting this wrong produces a plausible wrong number rather than an error, which is why it is published as data rather than left to the reader.
- `stored_unit` VARCHAR NOT NULL — The unit observations.data_value itself is in, in the readme’s own words.
- `stored_unit_ro` VARCHAR NOT NULL — The same stored unit, in Romanian.
- `is_indicator` BOOLEAN NOT NULL — True when the value is not a measurement on a scale at all — a clock time written HHMM (FMTM, PGTM) or a yes/no weather-type flag (WT**, WV**). Averaging data_value or scaled_value for these codes is meaningless; counting rows is what they are for.
- `definition` TEXT NOT NULL — The source readme’s definition of the code, in English.
- `definition_ro` TEXT NOT NULL — The source readme’s definition of the code, in Romanian.
- `snapshot_date` DATE NOT NULL — The run day (UTC) this lookup was written.
- `ingested_at` TIMESTAMP NOT NULL — When the ingest run that produced this row started (UTC).

### observations
One row per station, calendar day and element — the whole of GHCN-Daily in the source’s own long form. FEDERATED: nothing is stored here, this is a view over NOAA’s own Hive-partitioned Parquet on s3://noaa-ghcn-pds, so every query reads NOAA live and always sees the archive as NOAA reprocessed it most recently — including revisions to rows two centuries old, and with no record of what those rows said before. `element` says what was measured (join to `elements` for its name and unit), `data_value` is the integer NOAA published and `scaled_value` is that integer converted to the unit `elements.unit` names. Values that FAILED a quality check are present, not removed: `quality_flag` is how you exclude them, and `quality_flag IS NULL` is the filter almost every analysis wants.
- 0 rows · 0 B · write mode `snapshot_history`
- Physically sorted by: `obs_year`, `element`, `station_id`, `obs_date`
- Primary key: `station_id`, `obs_date`, `element`
- Files: 0

Columns:
- `obs_year` INTEGER NOT NULL — Calendar year of obs_date, redundant with it on purpose. It is NOAA’s own top-level partition key (the YEAR= directories under parquet/by_year/), which is what makes it the one predicate that genuinely reduces the work a query does against this federated view: filtering on it lets DuckDB skip whole directories of the archive. It used to also be the unit in which a copy of this table was reloaded; nothing is reloaded any more, because nothing is stored.
- `element` VARCHAR NOT NULL — Four-character GHCN-Daily element code, e.g. TMAX, PRCP, SN35. Joins to elements.element_code, which carries the name, unit and scale factor — and which is the one table of this dataset stored in the lake rather than read from NOAA. It is also NOAA’s second partition key (ELEMENT= inside each YEAR=), so filtering on it alongside obs_year is what keeps a read on this federated view down to a few files.
- `station_id` VARCHAR NOT NULL — Eleven-character GHCN-Daily station id; joins to stations.station_id. Its first two characters are the FIPS country code and the third is the network code.
- `obs_date` DATE NOT NULL — The calendar day the observation is for, exactly as NOAA’s own DATE field states it — a bare "yyyymmdd" civil date with no time-of-day or zone character in the format at all. Not a UTC day: GHCN-Daily merges many national meteorological services (see source_flag), and NOAA’s readme does not say which local convention each one uses to assign "the day" for its own readings. Compare obs_time, which carries the station’s own local time when present — also not UTC.
- `data_value` BIGINT — The integer exactly as NOAA published it, in the unit named by elements.stored_unit — usually tenths of the real unit. Kept raw so nothing is lost to a scaling decision; BIGINT rather than INTEGER because it mirrors the source column’s own type and can never overflow.
- `scaled_value` DOUBLE [the unit named by elements.unit for this row’s element] — data_value converted to a real measurement by dividing by elements.scale_factor — 287 for TMAX becomes 28.7 °C. NULL when the element code is not one this connector’s catalogue knows, because guessing a scale would produce a plausible wrong number rather than an error. Meaningless for the clock-time (FMTM, PGTM) and weather-type (WT**, WV**) codes, where elements.is_indicator is true.
- `measurement_flag` VARCHAR — M-FLAG: how the value was measured or derived. T is a trace of precipitation, B and D are totals formed from shorter periods, K is converted from knots, and so on. NULL means no measurement information applies. See the source readme, section III.
- `quality_flag` VARCHAR — Q-FLAG: NULL means the value passed every quality assurance check. Any letter means it FAILED one — X a bounds check, O a climatological outlier check, S a spatial consistency check, and so on. Flagged values are published rather than removed, so filter them out deliberately.
- `source_flag` VARCHAR — S-FLAG: which dataset this value came from. GHCN-Daily merges many national and regional collections and picks the highest-priority source per station-day, so this letter is what identifies whose data a row actually is. NULL means the value is missing. See the source readme, section III, for all thirty values.
- `obs_time` VARCHAR — Time of observation as HHMM, from NOAA’s HOMR station history database. Local time at the station, not UTC, and absent for most rows.
- `snapshot_date` DATE NOT NULL — The UTC calendar day on which the published view definition was built — NOT the day the rows behind it were read. This table is federated, so this is one constant for every row and it dates OUR view, not NOAA’s data: the rows arrive from NOAA at the instant you query them, which may be months after this date. It used to say how fresh our copy of this year was; there is no copy any more, so the honest reading of it is "the directory last re-pointed this view on this day".
- `ingested_at` TIMESTAMP NOT NULL — When the run that PUBLISHED THIS VIEW started (UTC). One constant for every row, for the same reason as snapshot_date: nothing was written, so no row has its own write time.

### stations
One row per GHCN-Daily weather station — 132,501 of them worldwide — with its coordinates, elevation, name and network memberships, from ghcnd-stations.txt. The dimension `observations.station_id` joins to. FEDERATED: nothing is stored here, this is a view that reads and slices NOAA’s fixed-width ghcnd-stations.txt at query time, so it is always the station list NOAA publishes now — a renamed station shows its new name without a re-ingest, and a station NOAA removes from the file simply disappears from this table.
- 0 rows · 0 B · write mode `upsert`
- Physically sorted by: `country_code`, `station_id`
- Primary key: `station_id`
- Text search columns: `name`
- Files: 0

Columns:
- `country_code` VARCHAR NOT NULL — FIPS country code, the first two characters of station_id; joins to countries.country_code. Computed by the view from the id rather than left as a substr each reader has to write, so "every station in Romania" is a plain equality on a named column. It buys no pruning — this table is federated, and the underlying ghcnd-stations.txt is one unsorted 132,501-line text file that is read whole either way — but it is what makes the filter obvious and the join to countries direct.
- `station_id` VARCHAR NOT NULL — The eleven-character GHCN-Daily station identifier.
- `network_code` VARCHAR — Third character of station_id: which numbering system the remaining eight characters come from. 0 unspecified, 1 CoCoRaHS, C US Cooperative, E ECA&D, M WMO, N a national met service, P pre-Coop, R RAWS, S SNOTEL, W WBAN.
- `latitude` DOUBLE [degrees] — Station latitude in decimal degrees.
- `longitude` DOUBLE [degrees] — Station longitude in decimal degrees.
- `elevation` DOUBLE [m] — Station elevation above sea level. The source writes -999.9 for "not known"; that sentinel is stored as NULL here, which is the case for 4,619 of the 132,501 stations.
- `state_code` VARCHAR — US postal code or Canadian province code, for stations in those two countries only; joins to states.state_code. NULL everywhere else.
- `name` VARCHAR — Station name as the source writes it, usually in capitals.
- `name_lc` VARCHAR — name lower-cased and accent-folded, for case- and accent-insensitive search. Computed by the view with strip_accents(lower(…)) at query time — this table is federated, so the folded value is not stored anywhere and searching it prunes nothing; the whole text file is read either way. It is published because it is the CORRECT way to match a name (a reader writing lower(name) alone misses the accented forms), not because it is the fast way, which is what it was while this table was copied and sorted.
- `gsn_flag` VARCHAR — GSN when the station belongs to the GCOS Surface Network, NULL otherwise. The flag is assigned by matching wmo_id against the official GSN list, so a station with no WMO number never carries it.
- `hcn_crn_flag` VARCHAR — HCN for a US Historical Climatology Network station, CRN for a US Climate Reference Network or Regional Climate Network station, NULL for everything else.
- `wmo_id` VARCHAR — World Meteorological Organization station number, where one has been matched to this station. Kept as text because it is an identifier with leading zeros, not a quantity.
- `first_seen_date` DATE NOT NULL — The UTC calendar day the published view definition was built. This table is federated, so it is NOT a seen-window: a view has no memory of previous runs, so first_seen_date and last_seen_date carry the same constant on every row. While the table was copied into the lake this really did record the first run on which a station appeared in ghcnd-stations.txt and was never moved forward; it is kept, with this description, rather than dropped, so a query written against the copied table still resolves. To ask when a station actually has data, use inventory.first_year.
- `last_seen_date` DATE NOT NULL — The same constant as first_seen_date (a UTC calendar day), for the same reason: this table is federated and a view cannot remember a previous run. A station NOAA drops from ghcnd-stations.txt is simply absent from this view — it is no longer retained under a stale last-seen value the way the copied, upserted version retained it, so an observations row for a delisted station can now find no dimension row at all. Use a LEFT JOIN if that matters to your query.
- `ingested_at` TIMESTAMP NOT NULL — When the run that published this view definition started (UTC). One constant for every row: nothing was written, so nothing has its own write time.

### countries
The 219 FIPS country codes used in the first two characters of a station id, with their names, from ghcnd-countries.txt. FIPS 10-4, not ISO 3166 — the codes differ (the United Kingdom is UK here, not GB). FEDERATED: a view over NOAA’s own ghcnd-countries.txt, so nothing is stored and the list is always the one NOAA publishes now.
- 0 rows · 0 B · write mode `replace`
- Physically sorted by: `country_code`
- Primary key: `country_code`
- Files: 0

Columns:
- `country_code` VARCHAR NOT NULL — FIPS 10-4 country code; joins to stations.country_code.
- `name` VARCHAR — Country name as the source writes it, in English.
- `snapshot_date` DATE NOT NULL — The UTC calendar day the published view definition was built, not the day this list was read: the table is federated, so it is one constant on every row and the rows come from NOAA at query time.
- `ingested_at` TIMESTAMP NOT NULL — When the run that published this view definition started (UTC). One constant for every row: nothing was written, so nothing has its own write time.

### states
The 74 US state, territory and Canadian province codes used in the state column of ghcnd-stations.txt, with their names, from ghcnd-states.txt. FEDERATED: a view over NOAA’s own ghcnd-states.txt, so nothing is stored and the list is always the one NOAA publishes now.
- 0 rows · 0 B · write mode `replace`
- Physically sorted by: `state_code`
- Primary key: `state_code`
- Files: 0

Columns:
- `state_code` VARCHAR NOT NULL — Postal code of the US state or territory, or of the Canadian province; joins to stations.state_code.
- `name` VARCHAR — Name of the state, territory or province, in English.
- `snapshot_date` DATE NOT NULL — The UTC calendar day the published view definition was built, not the day this list was read: the table is federated, so it is one constant on every row and the rows come from NOAA at query time.
- `ingested_at` TIMESTAMP NOT NULL — When the run that published this view definition started (UTC). One constant for every row: nothing was written, so nothing has its own write time.

### inventory
One row per station and element, giving the first and last year for which that station has unflagged data for that element — 782,538 rows, from ghcnd-inventory.txt. It answers "which stations have TMAX back to 1900" without touching the three billion rows of observations, which is exactly what it exists for — and that is worth more now than it was, because observations is a view over S3 and the three billion rows would be read across the network. FEDERATED too: this is a view over NOAA’s own ghcnd-inventory.txt, so nothing is stored and the answer is always NOAA’s current index.
- 0 rows · 0 B · write mode `replace`
- Physically sorted by: `element`, `first_year`, `station_id`
- Primary key: `station_id`, `element`
- Files: 0

Columns:
- `element` VARCHAR NOT NULL — Four-character element code; joins to elements.element_code.
- `first_year` INTEGER — First year for which this station has unflagged data for this element. Unflagged means the quality flag was empty, so it is a slightly stronger claim than "the first year with any row in observations".
- `station_id` VARCHAR NOT NULL — Station identifier; joins to stations.station_id.
- `last_year` INTEGER — Last year for which this station has unflagged data for this element. A value well short of the current year usually means the station closed.
- `latitude` DOUBLE [degrees] — Station latitude, repeated here by the source. Kept so this table answers a geographic "what is measured near here" question on its own, without a join.
- `longitude` DOUBLE [degrees] — Station longitude, repeated here by the source.
- `snapshot_date` DATE NOT NULL — The UTC calendar day the published view definition was built, not the day this inventory was read: this table is federated, so it is one constant on every row and the rows themselves come from NOAA at query time.
- `ingested_at` TIMESTAMP NOT NULL — When the run that published this view definition started (UTC). One constant for every row: nothing was written, so nothing has its own write time.


## 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.
- `elements` prunes on `element_code`.
- `observations` prunes on `obs_year` → `element` → `station_id` → `obs_date`.
- `stations` prunes on `country_code` → `station_id`.
- `countries` prunes on `country_code`.
- `states` prunes on `state_code`.
- `inventory` prunes on `element` → `first_year` → `station_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://registry.opendata.aws/noaa-ghcn/) 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.