ctd_measurement has duplicate (ctd_cast_uuid, depth_m, measurement_type) keys

[bbest]

Problem

ctd_measurement (released parquet, v2026.04.08) has duplicate (ctd_cast_uuid, depth_m, measurement_type) keys — its declared natural key. assign_deterministic_uuids_md5() only warns on non-unique keys, so ctd_measurement_uuid is itself non-unique and the duplication shipped silently.

Evidence

Sampled the first 4 cruise partitions of data/parquet/calcofi_ctd-cast/ctd_measurement/:

cruise_key	rows	duplicate-key rows
1998-02-31JD	1,250,773	0
1998-04-31JD	3,040,202	1,520,101 (every row doubled)
1998-07-32NM	1,582,430	0
2003-02-31JD	6,006,342	3,002,285 (every row doubled)

Across those 4 cruises: 4,522,386 duplicate-key groups. 96.6% have identical measurement_value (true exact duplicates); 3.4% have conflicting values (max spread 43.76 in measurement units) — likely the same cast present in both a final and a preliminary source file, both unzipped and loaded.

Likely cause

In ingest_calcofi_ctd-cast.qmd, the read/bind/filter step or the final-vs-preliminary selection is not deduplicating: some cruises' casts are loaded twice. The "selects final if available, otherwise preliminary" logic may not be excluding the preliminary file when a final exists, or a file is bound twice.

Impact

• ctd_measurement_uuid is non-unique → breaks any downstream join/PK assumption.
• Doubled rows inflate the table (~38% of sampled rows are duplicates).
• Conflicting-value dups mean some (cast, depth, type) positions have two different recorded values with no recorded provenance to disambiguate.

Workaround in place

The new ctd_thin table (#51) de-duplicates defensively — QUALIFY ROW_NUMBER() OVER (PARTITION BY ctd_cast_uuid, depth_m, measurement_type ORDER BY measurement_qual NULLS LAST, measurement_value) = 1 — so it has a unique key regardless. But ctd_measurement itself should be fixed at the source.

Suggested fix

• Identify why those cruises double-load (final/preliminary dedup, or a double bind)
• Add a uniqueness assertion (not just a warning) on ctd_measurement's key after the pivot, failing the ingest if violated
• For genuinely conflicting final/preliminary values, decide a precedence rule (prefer final) and record it

[bbest]

Root cause confirmed

Diagnosed against the wrangling DB + download archive. It is truly duplicate records in the source archive — not a missing key column.

What duplicates: some cruises ship their casts twice in the download archive — a primary copy plus a reorganized copy in a sub-folder. Example, cruise 0302 (2003-02-31JD):

20-0302JD_CTDFinalQC/db_csvs/20-0302JD_CTDBTL_001-100D.csv               <- primary
20-0302JD_CTDFinalQC/db_csvs/20-0302JD_CTDBTL_001-100U.csv
20-0302JD_CTDFinalQC/db_csvs/south-north/20-0302JD_CTDBTL_001-073ID.csv   <- reorganized copy
20-0302JD_CTDFinalQC/db_csvs/south-north/20-0302JD_CTDBTL_074-100ID.csv
... (and the U equivalents)

Both paths contain Final and db_csv, so the data_stage regex tags both as final, both get priority = 1, and the per-cruise priority filter (filter(priority == best_priority)) keeps both — every cast loads twice.

Why values differ: the ctd_cast duplicate-uuid rows differ only in lat_dec/lon_dec — the reorganized copy was re-processed with slightly different coordinate precision (sub-meter). lat_dec/lon_dec are coordinates, not an identity dimension — keying on them would be wrong. ~96.6% of duplicate groups have identical measurement values; ~3.4% differ slightly (two processing passes).

Scale: ctd_measurement 233,008,709 rows -> 210,815,579 distinct (ctd_cast_uuid, depth_m, measurement_type) keys — 9.5% (22.2M rows) are pure duplicates. ctd_cast_uuid is fully determined by (cruise_key, cast_key, cast_dir, datetime_utc), so it is exactly the de-dup target.

Fix applied

New dedup_ctd_raw chunk in ingest_calcofi_ctd-cast.qmd, right after ctd_raw is built (before the tidy tables / ctd_wide are split out):

CREATE OR REPLACE TABLE ctd_raw AS
SELECT * FROM ctd_raw
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY cruise_key, cast_key, cast_dir, datetime_utc, depth_m
  ORDER BY length("_source_file"), "_source_file", lat_dec, lon_dec) = 1

Keeps the primary copy (reorganized duplicates sit in deeper source paths -> longer _source_file), coordinates tie-break for determinism. A row-level safety net — robust regardless of which folders cause the duplication, so it covers all affected cruises (2017-04-3322, 2008-01-31JD, 2003-02-31JD, … — widespread). Fixes ctd_cast, ctd_measurement, ctd_summary, ctd_wide, and ctd_thin in one step.

Status: code change in the working tree, parses clean. Pending verification via a full notebook run (the assign_deterministic_uuids_md5 warning should disappear and ctd_thin's uniqueness should hold from the source, not just the defensive QUALIFY).