Computing the El Niño index from ERA5 with SQL

Part 2 of a three-part series on computing the El Niño index with SQL over cloud-hosted array data: Part 1 — reproducing NOAA’s official index from ERSST · Part 2 (this post) · Part 3 — auditing the sampling shortcut.

In Part 1 we reproduced NOAA’s official Oceanic Niño Index (ONI) to 0.001 °C by pointing SQL at ERSST v5 — the dataset NOAA actually builds it from. That proves the method is right. This post asks a different question: is the ENSO signal itself real, or an artifact of one dataset? To answer it we recompute the same index from ERA5 — a completely independent reanalysis — and see whether the two agree.

They do: a mean absolute error of 0.16 °C and a Pearson correlation of 0.966 against the ERSST-based reference, across 75 years — with zero ENSO sign reversals.

Satellite view of equatorial Pacific sea-surface temperatures during an El Niño event.

Image credit: NOAA Climate.gov

The ONI is the headline number for tracking El Niño and La Niña. When it climbs above +0.5 °C, insurers reprice catastrophe risk, grain traders revise yield forecasts, and energy planners brace for shifted demand. Part 1 reproduced it from its native source; here we stress-test both the method and the engine against a dataset built on entirely different physics.

What we’re computing

The ONI is a 3-month running mean of sea-surface-temperature (SST) anomalies in the Niño-3.4 region of the equatorial Pacific — the box bounded by 5°S–5°N and 170°W–120°W. An “anomaly” means how much warmer or cooler the ocean is than its normal baseline for that month. Average the anomaly over three consecutive months and you get one ONI value:

El Niño when ONI ≥ +0.5 °C
La Niña when ONI ≤ −0.5 °C
Neutral in between

The subtle part is “normal.” NOAA uses a centred, rolling 30-year climatology that shifts forward every five years, so each season is judged against a baseline contemporary to its own decade. In a warming climate that’s the difference between measuring ENSO and accidentally measuring the warming trend.

The data: ERA5, queried where it lives

We query ARCO-ERA5, the cloud-optimized, Zarr-formatted copy of ECMWF’s ERA5 reanalysis on Google Cloud, spanning 1940 to the present at 0.25° resolution. We never download it — zarr-datafusion registers it as an external table and reads only the chunks the query touches:

CREATE EXTERNAL TABLE IF NOT EXISTS era5
  STORED AS ZARR
  LOCATION 'gs://gcp-public-data-arco-era5/ar/full_37-1h-0p25deg-chunk-1.zarr-v3';

Where Part 1’s ERSST was already monthly and already in °C, ERA5 is hourly and in Kelvin — so this recipe carries a little more machinery. That extra machinery is exactly what we audit in Part 3.

Building the query

Step 1 — Sample the Niño-3.4 box. Pull SST from inside the box and convert Kelvin to Celsius. We take a single noon-of-the-15th reading per month — not a true monthly mean, but a cheap, representative sample that keeps the remote read small. The WHERE clause filters only on coordinates, which is what lets the engine push the filter down to the chunks and skip the rest of the planet.

WITH samples AS (
  SELECT
    CAST(extract(year  FROM ts) AS INT) AS yr,
    CAST(extract(month FROM ts) AS INT) AS mo,
    sst_c
  FROM (
    SELECT
      arrow_cast(time, 'Timestamp(Microsecond, Some("UTC"))') AS ts,
      sea_surface_temperature - 273.15 AS sst_c
    FROM era5
    WHERE latitude  BETWEEN  -5.0 AND   5.0
      AND longitude BETWEEN 190.0 AND 240.0   -- 170°W–120°W in 0–360° form
  ) AS box
  WHERE extract(day  FROM ts) = 15
    AND extract(hour FROM ts) = 12
    AND extract(year FROM ts) BETWEEN 1940 AND 2026
)

The one approximation to flag up front: a “monthly” value here is built from a single hourly field — 12:00 UTC on the 15th — treated as representative of the whole month. ERA5 actually has hourly data; a faithful monthly mean would average all ~720 hourly steps. We don’t, deliberately: one timestep per month keeps the remote reads cheap. Does that shortcut distort the result? Part 3 measures exactly that — and the answer is reassuring.

Step 2 — Collapse the box to a monthly number. Average all grid cells per (year, month); the HAVING clause drops months where absent chunks read back as NaN.

monthly AS (
  SELECT yr, mo, AVG(sst_c) AS sst_c
  FROM samples
  GROUP BY yr, mo
  HAVING AVG(sst_c) BETWEEN 0 AND 50
)

Steps 3–5 — climatology, anomaly, moving average. A small lookup table maps each block of season-years to NOAA’s contemporary 30-year base period; we compute the per-month climatology for each, subtract to get anomalies, then a self-join stitches each month to its neighbours for the 3-month centred average. (The full chain of CTEs is in oni_all_seasons.sql.)

Step 6 — Label and classify. Map each centre month to its season code 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;

That final SELECT is the ONI table — 916 seasons of it.

Running it

On cloud infrastructure in the same region as the data, this returns in about 60 seconds. Over a home connection it’s a 25–35 minute run pulling several GB of remote reads — the query reaches back to 1940 so the deep-past climatology has data. Either way: no download, no warehouse, no pipeline. One SQL file, a complete decades-long climate index.

Does it hold up?

We compare all 916 overlapping seasons against the ONI derived from ERSST v5 (the reference we reproduced exactly in Part 1) — an entirely different dataset from ERA5. Agreement means the signal is real, not an artifact of one data source.

ONI computed from ERA5 (zarr-datafusion) overlaid on NOAA's official ONI, 1950–present — the two lines track tightly across every major El Niño and La Niña event.

Metric	Result
Mean absolute error	0.16 °C overall; 0.11 °C from 1979 on
Pearson correlation	0.966
Systematic bias	−0.04 °C (negligible)
ENSO phase agreement	weighted κ = 0.88, zero sign reversals

Zero sign reversals is the one to sit with: across 916 seasons, the query never once called a warm event cold or a cold event warm. Every disagreement is a single-threshold boundary slip near ±0.5 °C.

Being honest about the error

The residuals aren’t uniform, and that’s the interesting part:

ONI residuals (ERA5 minus the ERSST reference) split by era — error is largest in the 1950s–60s and shrinks steadily toward the present.

The error splits cleanly by era: 0.25 °C mean absolute error before 1979 versus 0.11 °C after. That’s exactly the shape you’d predict — ERA5’s pre-satellite era (before ~1979) is more weakly constrained by observations, so its tropical Pacific SSTs wander further from ERSST v5. A few knowingly-accepted sources of scatter:

Dataset mismatch — ERA5 vs. ERSST v5 carries an inherent ~0.1–0.2 °C difference, which explains most of the residual post-1979.
Sampling shortcut — the single 12:00 UTC reading on the 15th stands in for the whole month. This is the largest knob we chose to leave un-tuned; Part 3 quantifies exactly how much scatter it adds (spoiler: it’s confined to the high-frequency noise band and leaves the ENSO signal intact).
Truncated baseline — ARCO-ERA5 starts in 1940, so the earliest 30-year base period (nominally 1936–1965) is really 1940–1965. This is the one caveat Part 1’s ERSST recipe doesn’t have, since ERSST’s record reaches back to 1854.

None of these are bugs to hide — they’re the honest seams of computing a 75-year index from a single, fast, in-place query.

The takeaway

Two datasets, one methodology: Part 1 reproduced the authoritative number from ERSST exactly; here ERA5 — separate data, separate physics — confirms the signal is real, at 0.16 °C and r = 0.966. No ETL job, no warehouse bill, no stale copy — just SQL pointed at cloud-hosted arrays.

Next, Part 3 turns the microscope on the one shortcut we took here — is one sample per month actually honest? — and uses Part 1’s ERSST series as the yardstick to find out.

If your workflow looks like a copy-and-load ETL pipeline, we can help you cut the cost — reach us at [email protected].

References

zarr-datafusion — the SQL engine for Zarr-native array data
el-nino-oni cookbook — full runnable SQL, validation script, and plots
ARCO-ERA5 — the cloud-optimized ERA5 reanalysis queried in place
NOAA CPC — change to ONI base periods — the rolling 30-year climatology schedule
Reproducing NOAA’s official El Niño index (Part 1) · Auditing the sampling shortcut (Part 3)