verify-stac.py: check the STAC-declared schema against the actual parquet schema

[cboettig]

The last untracked item from the #506/#509/#512 run. Subsumes the detector sketched under "Also worth doing" in #520 — implement it here, not there, so we do not end up with two overlapping schema checks.

The gap

verify-stac.py validates the declared schema internally (placement, _cng_fid presence, h3 resolutions, categorical values against ingested DISTINCT) and runs data-backed checks on specific columns. It never asks the basic question: do the columns the STAC declares actually exist in the parquet, and vice versa?

So a STAC can confidently document a column the data does not have, and the gate passes green.

Three live motivations

1. rfmo/rfb rebuild: CCAMLR missing from hex entirely; _cng_fid absent from flat + 22 partitions and colliding where present #520 — rfmo/rfb/hex declares _cng_fid; 22 of its 121 partitions do not have it. 303M rows (19%). A plain SELECT _cng_fid FROM read_parquet(<glob>) errors outright; with union_by_name=true those rows read as NULL and COUNT(DISTINCT _cng_fid) silently undercounts by a fifth. This also proves the check must be per partition file, not just against the glob's unioned schema — a whole-glob DESCRIBE shows the column present and hides the hole entirely.

2. A near-miss during hex-weights: res-10 is sparse but undocumented, nland duplicates the ecoregion land grid, and the documented aggregation is latitude-biased #522. That work removed nland from hex-weights-res9/-res8 (relocating it to ca30x30-ecoregion). They updated the STAC correctly — but had they not, the published schema would have documented a column that no longer existed, plus an asset description whose worked example (SUM((w1+w2)*nland)/SUM(nland)) referenced it. Nothing in the gate would have caught that. Two agents editing the same live S3 STAC days apart is now normal here, so "someone remembered" is not a control.

3. Columns silently added by a rebuild are equally invisible — undocumented columns are exactly what the table:columns contract exists to prevent.

Proposed check

check_declared_schema_matches_data(doc, mcp) — data-backed, for every parquet asset:

-- per-file column sets, not the unioned glob schema
SELECT file_name, list(name) AS cols
FROM parquet_schema('s3://.../h0=*/data_0.parquet') GROUP BY file_name;

Then compare against table:columns:

• declared but missing from ANY file → HARD. Name the column and the count of files missing it (_cng_fid: absent from 22/121 files). This is the rfmo/rfb rebuild: CCAMLR missing from hex entirely; _cng_fid absent from flat + 22 partitions and colliding where present #520 case and the hex-weights: res-10 is sparse but undocumented, nland duplicates the ecoregion land grid, and the documented aggregation is latitude-biased #522 near-miss.
• present in data, undocumented in STAC → HARD for a normal attribute; the table:columns contract is that assets are self-describing.
• heterogeneous across files (present in some, absent in others) → HARD, reported distinctly from "absent everywhere", because the remedies differ: a uniform absence is a STAC fix, a partial absence is a data rebuild.
• Skip: partition keys supplied by the path rather than the file, and geometry columns where the writer names them differently.

Cost is a footer read per file, no row scan — cheap even on padus-4-1/fee (610M rows, 21 files).

Watch out for

parquet_schema returns one row per column per file including nested/struct entries; filter to top-level fields or a geometry struct will read as several phantom columns. Verify against a known-good collection and against rfmo/rfb (which must report 22/121) before trusting it.

Done when

• The check ships in verify-stac.py, wired into the data-backed pass.
• Unit tests in tests/test_verify_stac.py (added in test: regression tests + CI for verify-stac.py #531) cover: clean, declared-but-absent, undocumented-extra, and the heterogeneous case. Mutation-test it — a schema check that passes against rfmo/rfb is worthless, and the WDPA false positive is what motivated that suite.
• rfmo/rfb/hex reports the 22/121 hole, closing the detection half of rfmo/rfb rebuild: CCAMLR missing from hex entirely; _cng_fid absent from flat + 22 partitions and colliding where present #520.
• The severity choice is deliberate: if the pre-gate backlog (Audit + rebuild vector hex assets built before 2026-07-12: duplicate (feature, cell) rows from multipart polyfill #509) turns out large, ship ADVISORY and promote to HARD once clear, the way Clear the column-description divergence backlog, then promote column-description-divergent to HARD #532 handles column-description-divergent.

[cboettig]

Boundary note, so the two checks don't get conflated: this check cannot catch a missing feature, and #520 contains one.

rfmo/rfb/hex is missing CCAMLR entirely — 105 features in the flat, 104 in the hex, zero rows for RFB='CCAMLR'. Every partition still has exactly the right columns, so a declared-vs-actual schema comparison reports it clean. The complementary coverage check is now #535 (COUNT(DISTINCT _cng_fid) on the hex vs COUNT(*) on the flat).

Dependency worth noting in this issue's ordering: #535 depends on this one. Its key is _cng_fid, which on rfmo/rfb is absent from 22/121 partitions and collides where present — so a coverage check run against a schema-broken asset would compare against a meaningless key. This check should gate it, which makes rfmo/rfb a useful shared fixture: it must report 22/121 here and 104 of 105 there.