ETL Phase 0 · Ground truth & credentials (cmgd_nextflow 2.2.1)

[seandavi]

Prove the environment before writing engine code.

• Confirm the exact <publish_base> for 2.2.1 from the deployed client config (not a guess).
• Pull one known-completed 2.2.1 sample and diff its rclone lsf -R against the expected spec inventory (manifest.json, MARK_COMPLETE, full_data/ + rarefied_data/, metaphlan/bracken/gtdb/resistome step files).
• Prove creds on onclappc02: rclone lsd r2: + rclone lsd gs1:; psql SELECT 1; DuckDB httpfs reads a probe parquet from R2 via a CREATE SECRET.
• Verify the load-bearing assumption: DuckLake data paths are relative / data_path-overridable, so one parquet set resolves under both s3:// and https:// (throwaway two-endpoint lake).

✅ Done when: a captured listing of a real 2.2.1 sample matches the spec inventory; a one-liner prints the count of completed 2.2.1 samples from Postgres; DuckDB lists an R2 bucket and reads a probe parquet back; the two-endpoint attach test passes (same rows via s3:// and https://).

Part of #57. Design & acceptance criteria: https://github.com/seandavi/nextflow_telemetry/blob/main/docs/output-catalog-etl-plan.md#phase-0--ground-truth--credentials

[seandavi]

Phase 0 findings (recon from onclappc02)

Environment ✅ — on onclappc02; rclone remotes gs1: (source) + r2: (lake target) work; DuckDB 1.4.4 + ducklake extension load; telemetry DB reachable via the nf_telemetry_api container (Postgres on 5432). Source files are GCS gs://cmgd-data (confirmed); R2 is only for the lake.

Publish path (confirmed) — publish_base_dir = gs://cmgd-data/results/cMDv4; derived path …/cmgd_nextflow/2.2.1/<sample>/. A real sample (04835269…) matches the spec inventory: manifest.json + MARK_COMPLETE at root, full_data/ + rarefied_data/ branches, steps metaphlan_markers, metaphlan_lists, kraken (bracken), resistome, strainphlan_markers.

2.2.1 differs from the 2.0.7 forensics:

• No gtdb step in 2.2.1 (no skip_gtdb, no gtdb dir). The spec must not expect it.
• manifest.json keys live under provenance (pipeline_version, pipeline_name, git_commit, input_ids), plus top-level read_accounting (raw/decontaminated/surviving fractions), parameters (metaphlan_index=mpa_vJan25_CHOCOPhlAnSGB_202503, skip_humann=true), software_versions.

⚠️ BLOCKER — publish layout is inconsistent across DB-"completed" 2.2.1 samples. DB reports 1713 completed 2.2.1 samples (active version), but only 591 dirs exist at the versioned path. Spot-check of the 8 most-recently-completed: all absent from the versioned path. They exist instead at the flat results/cMDv4/<sample>/ path with an old/partial layout — no manifest.json, no MARK_COMPLETE, no branches, only a subset of steps (e.g. metaphlan_markers/marker_abundance.tsv.gz). Two samples completed one day apart (07-05 vs 07-06) land in the two different layouts. The MARK_COMPLETE HEAD-gate correctly ingests only the ~591 full-format samples and skips the rest — but that means ~1122 "completed" samples have no ingestable output. Needs a decision: are the flat/partial samples to be re-published in full format, excluded, or is a second cluster/config publishing differently? (Likely traceable to the deployed daemon config on the cluster login nodes, which isn't visible from onclappc02.)

Load-bearing shared-file assumption — CONFIRMED, with a pinned mechanism. DuckLake stores data-file paths relative to data_path (ducklake_data_file.path_is_relative), so one physical parquet set can serve both s3:// (internal) and https:// (external). However, DuckLake rejects re-attaching a catalog with a mismatched DATA_PATH ("does not match existing data path in the catalog"). So the freeze cannot just attach with a different path — it must rewrite the data_path metadata in the frozen catalog copy (UPDATE …ducklake_metadata SET value='<https-base>' WHERE key='data_path'). This is cheap and resolves plan open-Q #1.

Net: Phase 1 (parsers) can proceed against the 591 full-format versioned samples. Phases 2–6 (ingest/trigger/freeze) are gated on resolving the layout-inconsistency blocker + the R2 lake bucket/prefix + public-HTTPS-access decision.

[seandavi]

Likely root cause of the layout split (per @seandavi): Alpine vs Anvil daemons using different publishDir configs — one publishing the full versioned layout (…/cmgd_nextflow/2.2.1/<sample>/ with manifest + MARK_COMPLETE + branches), the other the flat/partial results/cMDv4/<sample>/ layout. That's a pipeline/daemon-config bug (fix lives in the cluster daemon configs — see the cmgd-config / alpine-daemon domain), not an ETL bug. The ETL's MARK_COMPLETE gate already does the safe thing (ingest only full-format samples); ingest coverage will jump once the misconfigured cluster is realigned to the versioned publishDir. Tracking this as the prerequisite for Phases 2–6.

[seandavi]

RESOLVED — root cause found & fixed

Root cause (confirmed from telemetry + pipeline config): the two layouts map exactly to the two clusters, split by profile:

• Alpine ran -profile alpine,gcs → full versioned publish to GCS (591 samples). ✅
• Anvil ran -profile anvil — missing ,gcs (1122 samples). ❌

The gcs profile (ADR-0011, deliberately split so it composes) is what sets params.publish_base_dir = gs://cmgd-data/results/cMDv${cmgd_version} + publish_mode='copy'. Without it, publish_base_dir defaulted to ${launchDir}/results — a local Anvil scratch path. So Anvil's 1122 samples never reached GCS.

Fix applied: client-anvil.yaml on the Anvil daemon (/anvil/projects/x-cis240955/cmgd/nf_worker/): profile: "anvil" → profile: "anvil,gcs" (backup saved), daemon restarted in tmux nfdaemon, healthy. The submit template already exports GOOGLE_APPLICATION_CREDENTIALS and the SA key is valid (verified it lists gs://cmgd-data), so future Anvil runs publish to the versioned path like Alpine. Final end-to-end confirmation is on the next Anvil dispatch (queue was empty at fix time).

Follow-up (separate decision): the 1122 already-'completed' Anvil samples have outputs that were on scratch and are now purged — not recoverable. Getting them into the catalog means re-running them (they're cached in store_dir, so no DB re-download, but per-sample compute reruns). Requires resetting those jobs pending. Not done here.

Unblocks Phase 3–6 ingest coverage going forward.