Rigorous, reproducible data pipeline + AI-free runnable tests for derived parquet

[rdhyee]

Epic / tracking issue. Goal: a reproducible data pipeline for the explorer's derived parquet, plus tests a human can run and trust without any AI in the loop. Scope hardened by an adversarial Codex audit (findings below). AI sign-off is explicitly not the gate — the executable test suite + human review are.

Why now

Building/verifying #271 (drop the SKOS root "Material" leak) surfaced defects that document review and AI review did NOT catch — only execution did. The Stage-4 "frontend derived" pipeline (DATA_PROVENANCE.md) is ad-hoc, unpinned, and untested.

Evidence (executable, AI-free)

scripts/validate_frontend_derived.py run against live production today → exit code 1:

facets: no root 'Material' value   FAIL  346,768 rows still = root (want 0)
summaries: no root material row     FAIL  1
cross_filter: no root material      FAIL  23
ark:/28722/k2p55x96j preserved      PASS
facets: non-empty                   PASS  5,980,282 rows

Defects to fix/track

Reproducibility & provenance

• Deployed 202601 facets are not reproducible: rebuild from any available wide gives 528,983 root-material rows vs deployed 346,768; exact prod invocation unrecorded.
• No machine-checkable build manifest (input checksum, argv, git SHA, DuckDB + extension versions, output schemas/row-counts/checksums).
• Version skew: derived files 202601 vs canonical wide 202604; stale default --tag.

Builder correctness (scripts/build_frontend_derived.py)

• Geometry input-contract: requires WKB BLOB (ST_GeomFromWKB); 202601/Zenodo wides are GEOMETRY → BinderException. Make geometry-agnostic.
• Perf regression (build: stop SKOS root 'Material' leaking into the material facet (#265) #271): MAP cross-join mixed with correlated subqueries → planner blowup (base build >16 min, killed). [Codex Github account setup #2] context/object_type still use per-row correlated lookups. Resolve all 3 concept columns by one consistent, decorrelated method.
• [Codex Update index.md #1] --only/--skip not isolated — always builds samp/samp_geo + H3/spatial before honoring them.
• [Codex Setup development environments #3] Unknown --only/--skip names silently succeed (typo → emits nothing, exit 0).
• [Codex Quartoinit #5] Concept resolution silently drops broken refs (missing IdentifiedConcept row-ids → NULL, no integrity threshold).
• [Codex document how to use parquet from duckdb #13] Not byte-reproducible: no ORDER BY on COPY; MODE(source) nondeterministic on ties; float AVG parallel-variance.
• [Codex updates to github.io page #9] PID uniqueness assumed by browser, not enforced by build.
• [Codex update extension vocab html #11] facet_cross_filter emits self-dimension rows the UI ignores (counts should exclude the active dimension).

Schema / contract drift

• [Codex replace github.io pages #8] place_name unstable: VARCHAR[] → cast to VARCHAR in facets but stays array in samples_map_lite; docs say VARCHAR.
• [Codex update vocabulary html #10] facet_cross_filter baseline rows have all filter_* NULL, contradicting SERIALIZATIONS.md ("exactly one non-null").
• [Codex integrating Raymond's experiments so far with quarto #14] H3 summary types: COUNT(*)/UBIGINT vs docs' INT/BIGINT.
• [Codex Switching to quarto for rendering #6] Concept-selection contract contradiction: script = "first-remaining" vs SERIALIZATIONS.md = "leaf concept".

Validator is too weak (scripts/validate_frontend_derived.py)

• [Codex Added background to oc data #17] Can pass very wrong data (no-root + 1 sentinel + >1M rows + >50% populated passes even if materials collapsed/counts wrong).
• [Codex install vocab_tools from isamplesorg/vocab_tools #12] Never recomputes facet_summaries/facet_cross_filter from sample_facets_v2 → drift uncaught.
• [Codex Make nicer Cesium map point click events with more meaningful data #18] Sentinel PID check not uniqueness-safe (fetchone() can mask a wrong duplicate).
• [Codex 'Error running filter pandoc-plantuml' following Development instructions.  #7] Only checks material root; context/object_type root leakage untested.
• [Codex make clearer that iSamples central (and its API) is down right now #16] Narrow scope — ignores samples_map_lite, H3 summaries, vocab_labels, manifest, current aliases.
• [Codex Use fixed width font for displaying records #4] --validate-against is print-only, not a gate (skips missing files, compares only column names, never exits non-zero).

Docs

• [Codex sidebar (mostly) changes per last Friday review session #19] DATA_PROVENANCE.md stale (says Stage 4 ad-hoc / "no build script" while shipping it).
• [Codex Issue 13 parquet duckdb #15/Evaluate Cesium Ion token dependency in tutorials #20] Pin deps (DuckDB + h3/spatial versions, currently community-installed at runtime); reconcile SERIALIZATIONS.md.

Acceptance — tests assert the derived-file algebra, not spot checks

A wrong rebuild must FAIL. Required (all human-runnable, make test / pytest, non-zero exit on failure):

• facet_summaries == GROUP BY sample_facets_v2; facet_cross_filter == conditional GROUP BY sample_facets_v2; sample_facets_v2.pid == samples_map_lite.pid
• PID uniqueness on every pid-keyed file
• Exact schema tests (types, column order, nullability, value ranges)
• H3: int/hex equivalence, resolution correctness, summary counts sum to geo sample count, deterministic tie policy
• Geometry fixtures: WKB BLOB, GEOMETRY, null, invalid, non-point, out-of-range
• Concept-resolution fixtures (material/context/object_type; missing row-id; root policy) — root-first / root-only / real-first / NULL array / missing-id
• place_name fixtures (null/empty/single/multi/quotes/serialization)
• CLI failure tests (bad --only fails; missing output fails; --validate-against exits non-zero on mismatch)
• Perf gate: fixture unit tests in CI + marked full/sampled smoke with a wall-clock budget
• Publish/cutover: no mixed 202601/202604 artifacts unless documented; current/manifest.json coherent; remote HEAD/checksum checks

Workstreams (Codex-recommended split — file as sub-issues if/when picked up)

1. Provenance manifest + canonical input fetch/verify
2. Builder hardening (geometry, concept resolution, determinism, CLI failures)
3. Data-contract pytest suite over fixtures
4. Full-output validator over real parquet (algebraic consistency)
5. Dependency / container pinning (DuckDB, spatial, H3)
6. Docs reconciliation (DATA_PROVENANCE.md, SERIALIZATIONS.md)
7. CI + optional publish/cutover automation

Related

#271 (material fix — carries the perf regression above) · #272 (OC sidecar) · #268/#264 (provenance docs) · #265/#260 (reports that surfaced this) · #131 #135 #138

---

Scope hardened by an adversarial Codex audit (20 defects + algebra-not-spot-checks). 🤖 rbotyee; RY directing. Process: Codex attacks, executable tests gate, human approves.

[rdhyee]

Implemented in #274 (autonomous build, 3 adversarial Codex rounds, executable-proven).

Workstream status:

• 1 — Provenance manifest + canonical input (manifest with input/output sha256, argv, git SHA, DuckDB+ext versions)
• 2 — Builder hardening (geometry-agnostic; decorrelated concept resolution → 5.4 s vs >16-min blowup; deterministic; hard-fail dup keys; strict CLI)
• 3 — Data-contract pytest suite (12 fixture tests; both geometry encodings; corruption-catch; dup-fail; manifest; wide_h3)
• 4 — Full-output validator (algebraic + --wide semantic re-derivation; proven to catch corruption internal checks miss)
• 5 — Dep pinning (duckdb==1.4.4; extension hashes recorded in manifest)
• 6 — Docs reconciliation (DATA_PROVENANCE.md + SERIALIZATIONS.md)
• 7 — CI fixture gate (.github/workflows/pipeline-tests.yml)
• R2 publish + current/manifest.json cutover — deliberately left for a human gate (the one irreversible step). Rebuilt 202606 files + manifest are generated and validated locally; ready to publish.

Evidence in #274. The acceptance "tests assert the algebra, not spot checks" is met: a wrecked-but-self-consistent rebuild fails the --wide gate (test-proven).

[rdhyee]

Cross-ref: per the Slack exchange with @ekansa (2026-06-09/10), #272 is unblocked and has grown from "sidecar overlay" to fuller OC reintegration — rebuilding the OC portion of the published data from Eric's freshly refreshed OC PQG exports, through this pipeline (deterministic build + semantic validation + manifest). Gap quantified in #272's comment: 47.7% of overlapping OC samples have differing material categories; 74,922 OC samples are missing from the deployed data entirely.

---

— 🤖 rbotyee (RY's bot).