Data pipeline: thumbnail_url column is empty across all 6.7M samples

[rdhyee]

Problem

The wide parquet (`isamples_202601_wide.parquet`) exposes a `thumbnail_url` column, but it is NULL / empty for every single row.

```sql
SELECT n as source, COUNT() AS total,
COUNT() FILTER (WHERE thumbnail_url IS NOT NULL AND thumbnail_url <> '') AS with_thumb
FROM read_parquet('https://data.isamples.org/isamples_202601_wide.parquet')
WHERE otype = 'MaterialSampleRecord'
GROUP BY 1
```

source	total	with_thumb
OPENCONTEXT	1,064,831	0
SESAR	4,688,386	0
SMITHSONIAN	322,161	0
GEOME	605,554	0

Impact

• No image-rich UX possible in the browser explorers without per-source API calls (slow, rate-limited)
• Sample cards in the Interactive Explorer currently can't show a preview
• Discovery features like "find beautiful samples near X" (see Showcase samples: make the 4 front-page images locate themselves on the globe #130) would require hitting OpenContext / iDigBio / Smithsonian / SESAR individually for each candidate
• The column's presence advertises a capability that doesn't exist — confusing for PQG consumers building tools on top of the parquet

Upstream availability (spot-checks)

• OpenContext: rich media at `/subjects/.json` → `oc-gen:has-obs[*]` → linked media records. Poggio Civitate alone has 209,234 media items.
• Smithsonian NMNH: iDigBio indexes species with `hasMedia:true`. E.g. `Paracirrhites arcatus` has ≥5 media records per iDigBio search.
• SESAR: rarely has images (geological samples).
• GEOME: sparse photos via FIMS.

So the raw data exists — it's the PQG pipeline that isn't populating the column.

Proposal

Decide whether the pipeline should populate `thumbnail_url`:

Option 1: Populate it

Extend the narrow → wide conversion (or the source→PQG ingester) to extract a thumbnail URL per source:

• OpenContext: first `oc-gen:has-obs.*.link-media[0]` with `dc-terms:format` starting with `image/`
• Smithsonian: iDigBio's `dwc:associatedMedia` → resolve via iDigBio's media URL pattern
• SESAR / GEOME: skip (likely NULL)

Estimated effort: ~2 days, modest ongoing maintenance.

Option 2: Remove the column

If no one is going to populate it, drop from the schema so consumers don't have false expectations. Trivial.

Option 3: Leave as-is, document the gap

Update `how-to-use.qmd` or the PQG spec to note the column exists but is empty pending future work.

Related

• Discovered while investigating Showcase samples: make the 4 front-page images locate themselves on the globe #130 ("Showcase samples: make the 4 front-page images locate themselves on the globe") — the showcase hunt hit this wall hard.

[rdhyee]

Traced the flow — thumbnails are dropped in pqg/sql_converter.py

Evidence at each stage

File	thumbnail_url column?	populated
iSamples Export (Zenodo 2025-04-21 geo.parquet, 297 MB)	no column	—
Eric's OC iSamples PQG narrow (~/Data/iSample/pqg_refining/oc_isamples_pqg.parquet, 691 MB, Jun 2025)	yes	48,415 / 11.6 M
Eric's OC iSamples PQG wide (same dir, 276 MB, Dec 2025)	yes	50,212 / 2.5 M
Zenodo narrow, Dec 12 2025	yes	0 / 101 M
Zenodo wide, Jan 9 2026	yes	0 / 20.7 M
Production isamples_202601_wide.parquet on R2	yes	0 / 20.7 M

Thumbnails exist in Eric Kansa / OpenContext's PQG files. They are lost when the unified Zenodo narrow/wide parquets are built from the iSamples export.

Root cause: pqg/sql_converter.py

The converter that builds narrow+wide from the iSamples export hard-codes NULL for thumbnail at 13 sites — one per entity type:

pqg/sql_converter.py:204:            NULL::VARCHAR as thumbnail_url
pqg/sql_converter.py:258:            NULL::VARCHAR as thumbnail_url
pqg/sql_converter.py:336:            NULL::VARCHAR as thumbnail_url
… (13 occurrences total)

Each is inside a SELECT … FROM source block where source = read_parquet(iSamples_export). The iSamples export doesn't carry thumbnail_url, so the current code has no value to pull — it emits NULL.

The thumbnail column is in the PQG schema definitions (pqg/schemas/narrow.py:151, wide.py:126, sql_columns.py:56) as a required column, which is why it appears in output schema but is always NULL.

The overlap is large

Cross-referencing Eric's OC narrow (pids with thumbnails) against the unified Zenodo narrow:

Eric's OC narrow: distinct pids with thumbnail = 48,415
Overlap with zenodo narrow (MaterialSampleRecord pids): 47,716

~47.7 K thumbnails are recoverable today via a simple post-step LEFT JOIN on pid.

Sample thumbnails that already exist upstream

ark:/28722/k2xs64h0k          → .../historic-fort-snelling/thumbs/317_Pitcher_handle_side.jpg
ark:/28722/r2p24/pc_19720208  → .../ia-thumbnails/24-19720208bmce1972fig9jpg.jpg
ark:/28722/r2p24/pc_19730131  → .../ia-thumbnails/24-19730131bwjpg.jpg

Note rows 2 and 3 — they're the r2p24 legacy ark namespace, same as the showcase askoi (ark:/28722/r2p24/vdm_19600211). However, spot-checking the exact askoi pid: it is not in Eric's OC narrow either. The four homepage showcase items sit in an "outside our data" zone — curated directly from OpenContext / Smithsonian websites rather than from iSamples ingests.

Fix options

Option 1 — post-process: LEFT JOIN to recover ~47 K thumbnails (~1 day)

After sql_converter.py finishes, add a finalization step that:

1. Reads oc_isamples_pqg.parquet (Eric's OC narrow with thumbnails)
2. Builds a lookup: (pid → thumbnail_url) where thumbnail is non-null
3. LEFT JOINs this onto the unified narrow + wide outputs and overwrites thumbnail_url

Pro: ships thumbnails for existing OpenContext samples today without touching upstream formats.
Con: requires the OC PQG file available at build time, adds a dependency.

Option 2 — upstream: extend the iSamples export format (weeks)

Add thumbnail_url as a first-class column in the iSamples export format. Requires changes to the iSamples team's export pipeline and Zenodo archive workflow, then downstream code changes to populate it from source APIs (OpenContext, iDigBio for Smithsonian, etc.).

Pro: right answer long-term; thumbnails become canonical.
Con: multi-team coordination; slow.

Option 3 — during conversion: source APIs at ingest (days + ongoing cost)

Modify sql_converter.py to fetch thumbnails from source APIs while building narrow/wide. Adds network dependencies to the build, rate-limit concerns, non-deterministic builds.

Pro: avoids upstream changes.
Con: brittle; couples PQG build to external services.

Recommendation

Option 1 now, Option 2 queued. The join is simple, recovers 47.7 K real thumbnails immediately, and doesn't block on other teams. Option 2 is the right long-term home once the iSamples team has bandwidth.

Open questions

1. Do we have (or can we negotiate) the oc_isamples_pqg.parquet file as a stable build-time input? Currently at storage.googleapis.com/opencontext-parquet/oc_isamples_pqg.parquet.
2. Does Eric's OC PQG also include Smithsonian thumbnails, or only OpenContext? SELECT DISTINCT n FROM oc_isamples_pqg.parquet returned only None — the n (source_collection) field isn't populated in that file, so we can't tell source-by-source from the file alone. Worth checking with him.
3. The showcase askoi on the homepage (ark:/28722/r2p24/vdm_19600211) isn't in Eric's file either. That means the showcase curation drew from a wider OpenContext pool than what's indexed into iSamples. Either accept that (curate from what we have), or expand the ingest (new scope).

Related

Closes the question raised in #130 about why homepage thumbnails don't match parquet data — they come from different sources, and even the thumbnails we could recover via Eric's PQG file are being thrown away in our build.

[rdhyee]

Eric's OC PQG — source characterized

Bucket: opencontext-parquet (public GCS, anonymous read + listing allowed)
Base URL: https://storage.googleapis.com/opencontext-parquet/

Current state:

File	Size	Last-modified (GCS)	md5 (hex)	Generation
oc_isamples_pqg.parquet	727 MB	2025-11-07T18:41:08Z	8751a33c73cdbcd52ae5435cf4ce49ae	1762540868182013
oc_isamples_pqg_wide.parquet	289 MB	2025-11-16T19:18:46Z	a86bcd10902dcec6d8e6848c0c7152bc	1763320726816580

CORS: Access-Control-Allow-Origin: * — browsers can fetch directly. So we don't strictly need a mirror for CORS. The reasons to mirror are:

1. Stability — if Eric's URL breaks or the bucket gets reorganized, our build breaks silently.
2. Reproducibility — the PQG build should pin to a known version, not "whatever the URL returned today".
3. Change detection — if Eric re-uploads, we want to know (and decide whether to rebuild).

Proposed mirror design

Storage layout in R2 bucket isamples-ry

oc_pqg/
  oc_isamples_pqg_20251107.parquet          # versioned by GCS updated date
  oc_isamples_pqg_20251107.manifest.json    # provenance (see schema below)
  oc_isamples_pqg_wide_20251116.parquet
  oc_isamples_pqg_wide_20251116.manifest.json
  latest.json                                # points to the current versions

Served through the existing Cloudflare Worker (Worker already mirrors pattern rules to R2 via env.BUCKET):

• Immutable files: data.isamples.org/oc_pqg/oc_isamples_pqg_YYYYMMDD.parquet
  → Cache-Control: public, max-age=31536000, immutable (already covered by the Worker's isamples_\d{6}_.*\.parquet pattern... actually no — the current pattern is tight to isamples_202601_*. I'd expand the immutable regex to also match oc_isamples_pqg_\d{8}.*\.parquet).
• latest.json: short TTL (e.g. 5 min) so drift-check can update the pointer atomically.

Manifest schema (oc_isamples_pqg_YYYYMMDD.manifest.json)

{
  "source_url": "https://storage.googleapis.com/opencontext-parquet/oc_isamples_pqg.parquet",
  "source_generation": "1762540868182013",
  "source_etag": "8751a33c73cdbcd52ae5435cf4ce49ae",
  "source_md5_base64": "h1GjPHPNvNUq5UNc9M5Jrg==",
  "source_updated": "2025-11-07T18:41:08.202Z",
  "source_size": 727402756,
  "mirrored_at": "2026-04-17T17:00:00Z",
  "mirrored_size": 727402756,
  "mirrored_sha256": "(computed during upload)"
}

Drift detection

A small script (scripts/check_oc_pqg_drift.py):

1. HEAD storage.googleapis.com/opencontext-parquet/oc_isamples_pqg.parquet
2. Read data.isamples.org/oc_pqg/latest.json → look up current manifest
3. Compare source_etag and source_generation
4. If different: emit a clear report ("GCS has a new version dated YYYY-MM-DD with etag X, we have YYYY-MM-DD with etag Y")

Initially this runs manually. Can be wired to cron (GitHub Actions weekly, or a Cloudflare Worker cron trigger) as follow-up.

Implementation (~1 hr)

1. Mirror both files today to R2 under the naming above, compute sha256 during upload, write manifests + latest.json.
2. Expand the Worker's immutable-file regex to also match oc_isamples_pqg_\d{8}.*\.parquet. Redeploy.
3. Verify via curl -sI https://data.isamples.org/oc_pqg/oc_isamples_pqg_20251107.parquet that we see cache-control: public, max-age=31536000, immutable.
4. Commit drift-check script to this repo under scripts/. First run manually, schedule later.

Happy to proceed with this as described. Flag any naming or layout concerns before I start the copy (1 GB transfer, ~5 min).

[rdhyee]

Mirror executed ✅ — PR #132

Both files uploaded to R2 under oc_pqg/, manifests + latest.json written, Worker's immutable regex expanded.

Live URLs

	URL
narrow (727 MB)	https://data.isamples.org/oc_pqg/oc_isamples_pqg_20251107.parquet
wide (289 MB)	https://data.isamples.org/oc_pqg/oc_isamples_pqg_wide_20251116.parquet
narrow manifest	https://data.isamples.org/oc_pqg/oc_isamples_pqg_20251107.manifest.json
wide manifest	https://data.isamples.org/oc_pqg/oc_isamples_pqg_wide_20251116.manifest.json
latest pointer	https://data.isamples.org/oc_pqg/latest.json

Verification

Headers on the parquets:

cache-control: public, max-age=31536000, immutable
etag: "f1acceeb8f309401d500a45d6c612ac2-14"    (narrow)
etag: "8b6d96453ab6c5c28da348295bb54ee8-6"     (wide)

Metadata JSONs (short TTL so we can update quickly):

cache-control: public, max-age=300

Drift check (manual, exit 0 = in sync)

$ python scripts/check_oc_pqg_drift.py
[narrow] in sync
    mirrored: etag=8751a33c73cdbcd52ae5435cf4ce49ae updated=2025-11-07T18:41:08.202Z
    gcs:      etag=8751a33c73cdbcd52ae5435cf4ce49ae last-modified=Fri, 07 Nov 2025 18:41:08 GMT
[wide] in sync
    mirrored: etag=a86bcd10902dcec6d8e6848c0c7152bc updated=2025-11-16T19:18:46.850Z
    gcs:      etag=a86bcd10902dcec6d8e6848c0c7152bc last-modified=Sun, 16 Nov 2025 19:18:46 GMT

What's next on this issue

Now that the mirror is stable, the remaining work is Option 1 from the earlier comment: add a LEFT JOIN post-step to pqg/sql_converter.py so the unified Zenodo narrow + wide pull thumbnail_url from Eric's file, recovering ~47.7 K thumbnails in the next build. That's a PR against the pqg repo, not this one — I'll file a follow-up issue there referencing this one.

Follow-ups to file separately

1. PR in pqg repo: LEFT JOIN thumbnails from mirrored OC file during narrow+wide build. Input: https://data.isamples.org/oc_pqg/latest.json → resolve → join.
2. Cron for scripts/check_oc_pqg_drift.py — GitHub Actions weekly, comment on this issue if drift detected. Low priority; can eyeball manually for a while.
3. Harvest-ourselves exercise for SESAR/GEOME/Smithsonian thumbnails — the "data enrichment or PI data dump" path you mentioned. Separate issue.

[rdhyee]

Regeneration shipped ✅ — PR #133

What's live now

Stable URL	302s to	Contents
https://data.isamples.org/current/wide.parquet	isamples_202604_wide.parquet	20.7 M rows, 47,717 OpenContext thumbnails populated
https://data.isamples.org/isamples_202604_wide.parquet	(direct, immutable)	same 292 MB file, cache-control: public, max-age=31536000, immutable
https://data.isamples.org/isamples_202601_wide.parquet	(direct, unchanged)	still there, still immutable — the original no-thumbnail version
https://data.isamples.org/current/manifest.json	(direct, 5-min cache)	pointer from <flavor> → dated filename + provenance

Verified end-to-end

-- Through the stable alias
SELECT COUNT(*) FROM read_parquet('https://data.isamples.org/current/wide.parquet')
  WHERE thumbnail_url IS NOT NULL AND thumbnail_url <> '';
-- 47,717

-- Sample
SELECT pid, label, thumbnail_url FROM read_parquet('https://data.isamples.org/current/wide.parquet')
  WHERE thumbnail_url IS NOT NULL LIMIT 3;
-- ark:/28722/k27374x45 | Animal Bone Bone 258 | https://artiraq.org/...AY_XTNL017.jpg
-- ark:/28722/k2bv7vq74 | Animal Bone Bone 259 | ...
-- ark:/28722/k23b6f40f | Animal Bone Bone 257 | ...

How rotation works

To rotate (e.g. when next month's ingest lands):

1. Build new dated file → upload to R2 as isamples_YYYYMM_wide.parquet
2. Update current/manifest.json → new public_url
3. The /current/wide.parquet redirect picks up the new target within 5 min
4. Old dated files stay in R2 untouched, immutable, reachable by their dated URL

What's not done yet (tracked separately)

1. /current/narrow.parquet — same treatment for the narrow file. Easy to add once we decide whether the narrow rebuild is worth the ~820 MB bandwidth of each refresh cycle.
2. Updating existing tutorial pages to query /current/wide.parquet instead of the dated isamples_202601_wide.parquet. Opt-in when each tutorial is next touched. Not ripping through all tutorials now since each is immutable and still works — but new tutorials should use the stable alias.
3. Cron the drift check (scripts/check_oc_pqg_drift.py) — GitHub Actions weekly — so we get alerted when Eric re-uploads and can trigger a rebuild. Low priority.
4. Proper fix in pqg repo: make this enrichment step a standard post-stage in sql_converter.py rather than a one-off local script. The current script lives in this repo under scripts/ because that's where the operational tooling went — moving it to pqg is the right long-term home.

I think this resolves the core ask on this issue. Happy to close #131 once the three specific pids on the homepage are sorted (tracked separately in #130).