Audit + rebuild vector hex assets built before 2026-07-12: duplicate (feature, cell) rows from multipart polyfill

[cboettig]

Follow-up to the audit #387 asked for and #390 never did catalog-wide ("Worth auditing whether other hex assets built by the same step have the same defect"). Surfaced again while building #506.

The defect

The vector H3 polyfill emitted one row per (feature, cell, geometry part) instead of per (feature, cell), so any MultiPolygon whose parts share a cell produced byte-identical duplicate rows. COUNT(*), SUM(...), and SUM(h3_cell_area(...)) over such a hex asset all over-count.

This is already fixed upstream — boettiger-lab/datasets#150, PR #158, fix(#150): de-duplicate (feature, cell) rows in vector hex output, merged 2026-07-12 (Pass 2 now writes SELECT DISTINCT *). There is no tool bug to file and nothing to change in cng-datasets. The remaining problem is purely stale artifacts: every vector hex built before 2026-07-12 still carries the duplication.

Verified, not inferred

Ran the tool at :latest on the cluster with the exact production parameters of the conserved-areas hex job (--chunk-size 1000 --intermediate-chunk-size 10 --resolution 10 --parent-resolutions 9,8,0), chunk 4 (1,000 features):

	rows	duplicate (feature, cell) rows
Tool :latest, same params, same input	26,752	0
Published data for those same 1,000 features	26,889	137

Minimal reproducer of the mechanism — _cng_fid = 4661, "City Hall Park", a 2-part MultiPolygon of 9 total vertices covering 0.7 acres, both parts inside one res-10 cell. Pass 1 emits 2 rows ("Processing 2 rows in 2 batches"); the post-fix Pass 2 collapses them to 1. The published asset has 2.

Confirmed affected

Collection	Hex built	rows	duplicate (feature, cell) rows
ca30x30-conserved-areas-terrestrial-2025	2026-06-18	13,340,094	70,806 (0.53%)
padus-4-1/fee	2026-02-15	609,968,495	160,293 (0.026%)
ca30x30-ecoregion	rebuilt 2026-08-05	25,596,931	0 — fixed by the #507 res-10 rebuild on the post-fix tool

Worst single case in conserved-areas: 221 copies of one unit on a single res-10 cell (Black Mountain Ranch Open Space, 1,492 parts → 86 copies on one cell). Duplication scales with part count, so parcel/BLM-style multipart inventories are the exposed class; single-part polygons are unaffected.

Why it matters beyond a row count

_cng_fid is documented as the universal per-feature dedup key, and the hex assets STAC states "one row per (feature, h10) pair". Neither holds on a pre-fix build, so the guidance in AGENTS.md ("dedup by _cng_fid") does **not** rescue a consumer here — COUNT(DISTINCT _cng_fid)is fine, butCOUNT(*)and any per-cellSUMare inflated, andaudit-feature-dup.py --key _cng_fid` reports the asset as clean because it audits upstream (axis-2) duplication, not build artifacts.

The #506 hex-weights build dedups (cell, unit) explicitly before aggregating, so the published per-cell GAP fractions are correct and this issue does not block them. But it silently corrupted the issue`s own premise: the reported "max total weight 222" was 221 duplicate rows, not 222 overlapping units (real max: 16).

Proposed scope

1. Enumerate. For every vector hex asset in the catalog, read the h0=*/data_0.parquet mtime (rclone metadata listing, not a scan). Anything ≥ 2026-07-12 is clean by construction; only earlier builds are suspect.
2. Measure each suspect: COUNT(*) - COUNT(DISTINCT (h<native>, _cng_fid)) via the duckdb-geo MCP.
3. Remediate each affected asset with a SELECT DISTINCT * rewrite to a staging prefix → validate row delta + unchanged COUNT(DISTINCT h<native>) → purge-verify-empty → flip. This is the fix(#387,#389): dedup ca30x30 ecoregion hex + exact h3_cell_area area recipe #390 ecoregion-dedup.yaml pattern; it is far cheaper than a full re-hex and is provably equivalent, because the duplicates are byte-identical. A full re-hex is only needed if an asset also predates another polyfill fix (e.g. the transmeridian split, datasets#145, 2026-07-10 — relevant to global layers).
4. Verify rows == COUNT(DISTINCT (h<native>, _cng_fid)) afterwards, and consider adding that as a verify-stac.py data-backed check so a stale build cannot pass the gate silently.

REQUIRED: audit everything published before the gate existed

Scope amendment (2026-08-05). This issue is not complete when the known-affected assets are
fixed — it requires a systematic audit of all data predating the CI gate, because the two
defect classes found so far were both invisible for the same structural reason, not by chance:

• verify-stac.py and its CI gate landed 2026-06-24 (Enforce STAC correctness via a verify-stac CI gate on the recipe PR (replace per-defect-class manual audits) #302/verify-stac.py: enforce the STAC-correctness check suite via CI (#302) #306); individual checks landed
  later still (the values-vs-DISTINCT check 2026-06-26, this issue's row-uniqueness check
  2026-08-05).
• The gate is PR-triggered on changed catalog/** YAMLs. That is deliberate — gate at
  creation, not a periodic sweep — but the corollary is that any collection published before a
  check existed is never retro-verified unless someone happens to touch its recipe. An
  S3-only STAC edit triggers nothing.

So the pre-gate cohort has never been evaluated by the current rule set at all. Two confirmed
consequences already: the duplicate rows in this issue, and #511 (WDPA NO_TAKE = 'All ' with a
trailing space, which silently empties WHERE NO_TAKE = 'All').

Required work: run the current verify-stac.py (full, data-backed) against every collection
published before the gate — not just the row-uniqueness check — and triage every HARD finding.
Expect false positives from checks meeting old data for the first time: two were already found
(NULL-native hex columns fabricating 77,991,738 WDPA duplicates; a legitimate None category
discarded as a missing-value token, #511). Fix the checks as well as the data — a check that
misfires on the pre-gate cohort will misfire on new datasets too.

Acceptance for this half: every collection has been run against the current verifier at least
once, with each HARD finding either remediated or explicitly dismissed with a reason.

Acceptance: every vector hex asset in the catalog satisfies COUNT(*) == COUNT(DISTINCT (h<native>, _cng_fid)), except where a genuine multi-feature-per-cell case is documented on the asset.

[cboettig]

Progress: exemplar fixed, gate added, catalog enumerated (PR #510)

1. ca30x30-conserved-areas-terrestrial-2025 — remediated

13,340,094 → 13,269,288 rows, 70,806 duplicates removed. DISTINCT h10 (13,202,628) and DISTINCT _cng_fid (45,811) unchanged; the published #506 hex-weights reproduce from the deduped hex with zero disagreeing rows (asserted in-job before the swap). Live asset now reports 0 duplicate (cell, feature) pairs.

The SELECT DISTINCT * rewrite was confirmed provably equivalent to a re-hex here before running it, not just assumed: COUNT(*) over SELECT DISTINCT * equals COUNT(DISTINCT (h10, _cng_fid)) exactly, so every duplicate is a full-row copy and there are no legitimate multi-row-per-(feature, cell) cases to preserve.

2. Recurrence gate added (scope item 4)

verify-stac.py check_hex_row_uniqueness — data-backed, HARD. Per-collection verdict is now one command.

One correction to the scope above. Step 2 as written (COUNT(*) - COUNT(DISTINCT (h<native>, _cng_fid))) is wrong for any build that caps large features at a coarser resolution, and I hit it: keying on h3:native_resolution reported 77,991,738 duplicate rows on WDPA against a true count of 0. WDPA leaves h9 NULL for its 1,297 largest features (h8 complete) and COUNT(DISTINCT) drops NULL keys. Anyone running the sweep manually must key on the finest non-NULL cell per row — COALESCE(h10, h9, h8, …) — which is collision-free because H3 ids encode their resolution. The shipped check does this.

3. Catalog enumerated (scope item 1)

108 of 164 vector hex assets predate 2026-07-12. Full list in the PR discussion; oldest are cpad-holdings / cced / cpad-units (2026-01-01) and the PAD-US family (2026-02-15).

Predating the fix is not proof of damage — only multipart features that share cells duplicate, so the population needs measuring, not blanket rewriting. Sampled:

Collection	rows	duplicate rows
padus-4-1/fee	609,968,495	160,293	0.026%
usgs-wbd hu12	15,191,575	28,106	0.185%
calfire-2025 firep	11,377,557	1,806	0.016%
ace-terrestrial-biodiversity-summary	519,971	57	0.011%
cpad-holdings	12,562,921	0	clean
wdpa	207,833,890	0	clean

2 of 6 sampled are already clean, and the affected ones are all ≲0.2% — materially smaller than the 0.53% on conserved-areas. Worth noting ace-terrestrial-biodiversity-summary is the feature grid behind the #506 analysis; at 57 rows it changes nothing there (and #506 used SELECT DISTINCT h0, h8 throughout), but it should still be cleaned.

Remaining

Measure the other ~102 and remediate the affected ones. Not started — that is a sizeable sweep (PAD-US alone is 610M rows) and worth a scoping decision on cadence before firing it off. Each is now a mechanical verify-stac.py --bucket X --dataset Y followed by the same dedup job pattern (catalog/ca30x30/k8s/conserved-areas-terrestrial-2025/conserved-areas-terrestrial-2025-hex-dedup.yaml is the template).

[cboettig]

Verified: the flagged duplicates are byte-identical, not information-bearing

Prompted by a fair challenge after the WDPA false positive — a row-count difference alone does not prove redundancy. If duplicate (cell, feature) rows differed in any column, they would carry information and SELECT DISTINCT * would neither collapse them nor be a safe remedy.

Test: compare COUNT(*) over SELECT DISTINCT * (full-row distinct) against COUNT(DISTINCT (finest-cell, _cng_fid)). Equal ⇒ every duplicate group collapses to exactly one distinct row ⇒ pure redundancy. Also checked for NULL _cng_fid, which would fabricate duplicates the same way NULL h9 did on WDPA.

Collection	rows	distinct full rows	distinct (cell, fid)	NULL keys	verdict
ace-terrestrial-biodiversity-summary	519,971	519,914	519,914	0	identical ⇒ 57 pure dups
calfire-2025 firep	11,377,557	11,375,751	11,375,751	0	identical ⇒ 1,806 pure dups
usgs-wbd hu12	15,191,575	15,163,469	15,163,469	0	identical ⇒ 28,106 pure dups

For padus-4-1/fee a full-row DISTINCT over 610M rows exceeds the query timeout, so the test was restricted to the duplicate groups themselves in its worst partition (h0=577164439745200127, 78,108 of the 160,293) — a stronger check, since it asks whether anything differs within the groups:

rows in duplicate groups        = 155,714
duplicate groups                =  77,606
DISTINCT * over those rows      =  77,606   <- exactly the group count
NULL h10 or _cng_fid            =       0

Every group collapses to exactly one row. Same result as the conserved-areas asset before its rewrite.

So SELECT DISTINCT * is provably lossless on this class, and the counts in the table above are real. What is not yet established for the unmeasured ~102 is whether they are affected at all — that still needs measuring per collection, and each one should get this same full-row check before any rewrite rather than trusting the row-count delta.

[cboettig]

CA-app data surface: audited and cleared

Prioritized ahead of the rest of the sweep. Measured every pre-gate vector hex asset in the California app's buckets (public-ca30x30, public-cdfw, public-cpad, public-fire/calfire-*, public-cgs, public-swap/california) — 25 assets — then fixed the affected ones in one batch job (catalog/audit/k8s/hex-dedup-batch-ca.yaml, PR #510).

14 affected, 8,575 duplicate rows removed. All now verified 0 live.

Asset	before	after	removed
williamson-act/all-years	18,695,136	18,692,440	2,696
calfire-2025/firep	11,377,557	11,375,751	1,806
calfire-2025/rxburn	640,951	639,897	1,054
williamson-act/2021	2,981,406	2,980,456	950
williamson-act/2022	3,845,125	3,844,642	483
williamson-act/2025	3,933,100	3,932,656	444
williamson-act/2024	4,055,692	4,055,254	438
williamson-act/2023	3,898,160	3,897,778	382
freshwater-species-richness	522,414	522,169	245
ace/terrestrial-biodiversity-summary	519,971	519,914	57
swap-2025/conservation-units	1,943,434	1,943,418	16
swap-2025/marine-bioregions	17,269	17,267	2
fmmp-2022	134,914	134,913	1
cpad-2025b-units	12,562,430	12,562,429	1

Already clean, no action: gde-vegetation, gde-wetlands, cced-2025b, cpad-2025b-holdings, fish-passage-pad, sierra-nevada-atlas, usgs-fires-2021/combined, calfire-2024/firep, calfire-2024/rxburn, swap-2025/provinces, swap-2025/noaa-esu-dps, swap-2025/bay-delta-conservation-unit. Plus conserved-areas-terrestrial-2025 (fixed earlier) and ecoregion (clean by construction from the #507 rebuild). cwhr13 is raster-derived — no feature dimension, exempt.

The batch job proves safety per target before writing, rather than assuming: COUNT(*) over SELECT DISTINCT * must equal COUNT(DISTINCT (finest non-NULL cell, _cng_fid)). If they differ, the duplicates are not byte-identical, SELECT DISTINCT * would not collapse them, and rewriting could destroy information — the target is skipped and reported instead. All 14 passed; none skipped. It also asserts cell count and feature count are unchanged before each swap. That template is reusable for the remaining ~94.

Downstream impact re-verified. ace/terrestrial-biodiversity-summary is the feature grid behind the #506 analysis, so its dedup could in principle have moved those numbers. It did not — both the res-8 join and the res-10 overlay still return 21.04 / 29.62 / 49.34, unchanged.

Remaining on this issue: the other ~94 pre-fix hex assets, and the broader pre-gate verify-stac.py audit now required in the issue body.