Reproducing NOAA's official El Niño index — exactly

Part 1 of a three-part series on computing the El Niño index with SQL over cloud-hosted array data: Part 1 (this post) · Part 2 — the independent ERA5 cross-check · Part 3 — auditing the sampling shortcut.

In the ERA5 cross-check (Part 2) we compute the Oceanic Niño Index (ONI) from ERA5 and validate it against NOAA’s official table — landing within 0.16 °C on a completely independent dataset. That’s the cross-check. This post is the other half: can we reproduce NOAA’s official number — not approximate it, reproduce it — with one SQL query?

Yes. Against the dataset NOAA actually builds the ONI from, the query matches the published table to a mean absolute error of 0.001 °C — agreement to rounding precision. And it does it while reading the source NetCDF in place, with no conversion step at all.

The dataset NOAA actually uses

NOAA CPC computes the official ONI from ERSST v5 — the Extended Reconstructed Sea Surface Temperature, v5 — not from ERA5. So if the goal is to reproduce the authoritative value rather than independently confirm it, ERSST is the dataset to point SQL at. It’s monthly, it starts in 1854, and it’s distributed as NetCDF by NOAA PSL.

That last fact used to be the problem. NetCDF isn’t cloud-native the way Zarr is — the usual move is to convert the whole archive to Zarr first, then query it. We skip that.

Reading NetCDF in place — no conversion

zarr-datafusion reads the ERSST NetCDF through a VirtualiZarr reference: a small manifest (built once by a virtualize_ersst.py helper) that maps Zarr-style chunks onto byte-ranges inside the original NetCDF file. The engine then reads only the chunks a query touches, straight out of the NetCDF — no copy, no reformat.

CREATE EXTERNAL TABLE IF NOT EXISTS ersst
  STORED AS ZARR
  LOCATION 'data/ersst_v5.parq';   -- a VirtualiZarr manifest over NOAA's ERSST NetCDF

That STORED AS ZARR over a .parq manifest is the whole trick: the table is the NetCDF, addressed as if it were Zarr. Everything downstream is ordinary SQL.

Why the query gets simpler than ERA5

Because ERSST is the native ONI source, four of the approximations we had to make for ERA5 simply vanish:

Already monthly — no day-15 / noon-12 sampling shortcut. Each row is a monthly value.
Already in °C — no sea_surface_temperature - 273.15 Kelvin conversion.
Record starts 1854 — so the earliest rolling base period (1936–1965) is fully covered. ERA5’s one unavoidable caveat — its archive starting in 1940 — is gone.
Dims are (time, lat, lon) — no vertical level, and the Niño-3.4 box is entirely ocean, so no NaN cells to filter.

The methodology is otherwise identical to the ERA5 recipe: a 3-month running mean of monthly Niño-3.4 SST anomalies, measured against NOAA’s rolling, centred 30-year base periods that advance every five years.

The query

Sample the Niño-3.4 box (5°S–5°N, 170°W–120°W, written in 0–360° longitude) and read sst directly — no unit math:

WITH samples AS (
  SELECT
    CAST(extract(year  FROM ts) AS INT) AS yr,
    CAST(extract(month FROM ts) AS INT) AS mo,
    sst                                 AS sst_c
  FROM (
    SELECT
      arrow_cast(time, 'Timestamp(Microsecond, Some("UTC"))') AS ts,
      sst
    FROM ersst
    WHERE lat BETWEEN  -5.0 AND   5.0
      AND lon BETWEEN 190.0 AND 240.0
  ) AS box
  WHERE extract(year FROM ts) BETWEEN 1936 AND 2026
),

Collapse the box to one number per month, encode NOAA’s rolling base-period schedule as a small lookup table, subtract each month’s contemporary climatology to get the anomaly, then take the 3-month centred average and apply the ±0.5 °C thresholds:

SELECT
  o.yr                AS year,
  sl.season,
  ROUND(o.oni_raw, 2) AS oni_c,
  CASE WHEN o.oni_raw >=  0.5 THEN 'El Niño'
       WHEN o.oni_raw <= -0.5 THEN 'La Niña'
       ELSE 'Neutral' END AS enso_phase
FROM oni o
JOIN season_label sl USING (mo)
ORDER BY o.yr, o.mo;

The full file — every CTE, the base-period table, the climatology join — is oni_ersst.sql.

The result: 0.001 °C

Compared against NOAA CPC’s published ONI table across all 916 overlapping seasons from 1950 on:

Metric	Result
Mean absolute error	0.001 °C
Agreement	reproduces NOAA’s values to rounding precision

This is the point worth sitting with. NOAA’s ONI is one of the most consequential numbers in climate, published from an established pipeline. Pointed at the same source data, one SQL query — reading the NetCDF in place — returns the same table. Not “close.” The same.

Where this leaves ERA5

If ERSST reproduces the official number exactly, what’s ERA5 for? Independence. ERSST tells you the method is right; ERA5 — an entirely separate reanalysis — tells you the signal is real and not an artifact of one dataset. That’s the companion post: the same ONI from ERA5, landing at 0.16 °C MAE and Pearson r = 0.966 against this ERSST-derived reference, with an honest error breakdown by era.

Two datasets, two recipes, one methodology: the authoritative reproduction here, the independent confirmation there.

Run it yourself

The el-nino-oni cookbook has both recipes, the VirtualiZarr helper, the comparison script, and the plots.

If you’re sitting on NetCDF archives you currently copy-and-convert before you can query them, that’s exactly the workflow VirtualiZarr removes — reach us at [email protected].

References

zarr-datafusion — SQL engine for Zarr-native (and, via VirtualiZarr, NetCDF) array data
oni_ersst.sql — the full ERSST recipe
NOAA CPC — official ONI table — the reference we reproduce
Part 2 — the ERA5 independent cross-check · Part 3 — auditing the sampling shortcut

Reproducing NOAA's official El Niño index — exactly — with SQL