---
title: "Ingest CDFW Dungeness Crab Megalopae"
calcofi:
# underscores: target_name must be a valid R symbol, unlike the filename
target_name: ingest_cdfw_dungeness_crab
workflow_type: ingest
dependency:
- ingest_swfsc_ichthyo
output: data/parquet/cdfw_dungeness-crab/manifest.json
provider: cdfw
dataset: dungeness-crab
# IN THE RELEASE as of 2026-08-14. Q01 — permission, licence, citation — was
# answered by CDFW via E. Satterthwaite (2026-08-13): the data may be published
# under CC BY 4.0. `in_release` is opt-out, so the key is simply gone rather
# than set to `true`. The same change moved measurement_type_new.csv into the
# shared registry, deleted the asserted coverage_* keys so coverage is measured
# like every other dataset, and flipped publish_to_gcs.
workflow_url: https://calcofi.io/workflows/ingest_cdfw_dungeness-crab.html
questions_file: metadata/cdfw/dungeness-crab/questions.csv
dataset_meta:
dataset_name: CDFW Dungeness Crab Megalopae
# display trio, read by the release `dataset` table and the
# consumer apps (calcofi4db >= 3.15.0) — see NEWS for why these
# left the apps' own hardcoded maps
dataset_name_short: Dungeness Crab Megalopae
category: Zooplankton
color: "#f76707"
description: >
Dungeness crab (Metacarcinus magister) megalopae and larval decapods
picked from archived CalCOFI oblique-tow plankton samples, examined for the
California Department of Fish and Wildlife. Two efforts: a 2008-2014 time
series of 310 sorted samples (A. Klemmedson, 2015) yielding 24 M. magister
megalopae from 14 samples, and a 1949-2009 master sorting log of 2,011
archived samples (E. Jones, as of 2012-02-15) recording which had been
examined. Spring cruises, CalBOBL oblique tows, lines < 80 / stations < 90.
Counts are occurrence in the examined aliquot, not standardized density.
Sorting was performed at the SIO Pelagic Invertebrate Collection, where the
removed megalopae are archived in vials.
# Per CDFW via E. Satterthwaite, 2026-08-13. Laura Rogers-Bennett is the
# primary data provider; the sorters are credited because the examination
# effort IS the dataset. CDFW is the current citable custodian even though
# the work originated under CFG in 2012 — the agency was renamed, and the
# current name is the one a reader can act on.
citation_main: >
Rogers-Bennett, L.; Jones, E.; Klemmedson, A. (2026). CDFW Dungeness Crab
Megalopae from archived CalCOFI plankton samples (1949-2014). California
Department of Fish and Wildlife, published through CalCOFI / Scripps
Institution of Oceanography.
link_calcofi_org: ""
link_data_source: ""
link_others: []
# coverage_temporal / coverage_spatial deliberately absent: measured at
# release time by calcofi4db::observed_coverage() now that this dataset
# reaches the core. Do not re-assert them — see "Coverage is measured, never
# asserted" in CLAUDE.md. The measured extent runs to 164.1°W, well outside
# the survey area; that is the known positive-longitude / off-grid-station
# pair (Q07, Q08), still open with the provider, not a new bug.
license: CC BY 4.0
pi_names: Laura Rogers-Bennett
# publishes the consolidated core AND NOTHING ELSE. The source shape is wrangled
# in the notebook and projected into sample / obs / obs_attribute /
# sample_measurement + the taxa refs via the generic append_* helpers. No source
# table is written to parquet: the workbooks are archived, so a per-dataset copy
# is redundant and would give consumers two representations to choose between.
# dungeness_* survive as compat VIEWs inside the notebook only.
tables_owned:
- {table: sample, shared: true, note: "core event dimension (310 examined subsamples + 2,011 sorting-log tows)"}
- {table: obs, shared: true, note: "core occurrence headline: counts x 4 resolved taxa, plus 216 zero-valued absences from the sorting log"}
- {table: obs_attribute, shared: true, note: "per-individual M. magister carapace lengths"}
- {table: sample_measurement, shared: true, note: "event-level effort (prop_sorted, settled volume)"}
- {table: taxon, shared: true, note: "shared taxa reference"}
- {table: dataset_taxon, shared: true, note: "raw count label -> taxon_key crosswalk"}
erd:
color: "#f5d5b8"
editor_options:
chunk_output_type: console
---
## Overview
**Source**: three hand-maintained Excel workbooks plus correspondence, under
`data-public/cdfw/dungeness-crab/`. Provider `cdfw` (California Department of Fish
and Wildlife), which commissioned the work; the sorting itself was done at the
SIO Pelagic Invertebrate Collection.
| Workbook | Becomes | Rows |
|---|---|---|
| `Dungeness Time Series - Angela Klemmedson.xlsx` (`Compiled Cruises`) | `dungeness_sample` + `dungeness_measurement` + `dungeness_carapace` | 310 samples |
| `Cancer Magister sorting update as of 02-15-12_EJones.xlsx` | `dungeness_sorting_log` | 2,011 samples |
| `ScrippsArchivedMegalopae_sent_5_10_2012.xlsx` | `dungeness_specimen` | 5 specimens |
The two PDFs and the `.docx` are the provenance for what the numbers mean and are
archived alongside the data, not parsed.
**Why this is interesting**: 24 *M. magister* megalopae in 310 samples over seven
years — a near-absence record. The 2012 correspondence establishes that historic
CalCOFI samples labelled *Cancer magister* are probably *C. productus*, so the
`dungeness_specimen` verification table is load-bearing rather than trivia.
**In the release** as of 2026-08-14. It was held out from first ingest until
CDFW confirmed permission, a licence and a citation (Q01, answered 2026-08-13 —
CC BY 4.0). Nothing about the outputs changed when it entered: this notebook
always wrote its complete parquet, including its slice of the consolidated
core, and `release_database.qmd` simply stopped skipping it.
```{mermaid}
graph LR
X1[Compiled Cruises] --> S[dungeness_sample<br/>station + effort]
X1 --> M[dungeness_measurement<br/>counts, long]
X1 --> C[dungeness_carapace<br/>per individual]
X2[sorting log] --> L[dungeness_sorting_log<br/>sorted / unsorted]
X3[archived megalopae] --> V[dungeness_specimen<br/>ID verification]
M -.dungeness_sample_id.-> S
C -.dungeness_sample_id.-> S
M -.measurement_type.-> T[measurement_type]
S -.site_sample_key.-> I[(swfsc_ichthyo<br/>sample / ship / grid)]
```
## Setup
```{r}
#| label: setup
#| message: false
devtools::load_all(here::here("../calcofi4db"))
devtools::load_all(here::here("../calcofi4r"))
librarian::shelf(
CalCOFI/calcofi4db, CalCOFI/calcofi4r,
DBI, dplyr, DT, fs, glue, here, janitor, jsonlite, knitr,
lubridate, purrr, readr, sf, stringr, tibble, tidyr, quiet = T)
options(readr.show_col_types = F)
options(DT.options = list(scrollX = TRUE))
source(here("libs/ingest.R"))
source(here("libs/parse_dungeness_crab.R"))
cc <- read_calcofi_meta(here("ingest_cdfw_dungeness-crab.qmd"))
provider <- cc$provider
dataset <- cc$dataset
tables_owned <- cc$tables_owned
dir_label <- glue("{provider}_{dataset}")
dir_parquet <- here(glue("data/parquet/{dir_label}"))
dir_stage <- cc_stage_path("parquet", dir_label, create = TRUE)
db_path <- here(glue("data/wrangling/{dir_label}.duckdb"))
dir_src <- path_expand(glue("{dir_data}/cdfw/dungeness-crab"))
if (overwrite) {
if (file_exists(db_path)) file_delete(db_path)
if (file_exists(paste0(db_path, ".wal"))) file_delete(paste0(db_path, ".wal"))
}
dir_create(dirname(db_path))
con <- get_duckdb_con(db_path)
load_duckdb_extension(con, "spatial")
# the tables this ingest owns
dcr_tbls <- c("dungeness_sample", "dungeness_measurement", "dungeness_carapace",
"dungeness_sorting_log", "dungeness_specimen")
# Both buckets are world-readable and uploading is a one-way door, so this was
# opt-in while permission to publish was itself the open blocker (Q01). CDFW
# answered on 2026-08-13 — CC BY 4.0, see the citation in the front-matter — so
# this ingest now archives and syncs like every other one. The flag stays as a
# named switch rather than being deleted: it is the thing to flip if a future
# dataset arrives under the same unsettled-permission conditions.
publish_to_gcs <- TRUE
```
## Read Source Workbooks
The workbook parsing lives in `libs/parse_dungeness_crab.R` so it is reviewable
and re-runnable. Three quirks it absorbs, each of which silently corrupts the
data if missed:
- the time series workbook is saved with Excel's **1904 date epoch**
(`date1904="1"`), so every date read as text is four years early — the cruise
labels are what caught it (`1404SH` reading as 2010);
- the sorting log lost the leading month from most `SampleDate` cells
(`/5/1988`), recoverable from its separate `Month` column;
- the sorting log pads numbers with U+00A0, which defeats `as.numeric()`.
```{r}
#| label: read-source
stopifnot("source dir not found" = dir_exists(dir_src))
# archive the sources (including the PDF/DOCX provenance) — see publish_to_gcs
if (publish_to_gcs) {
sync_to_gcs(local_dir = dir_src, gcs_prefix = glue("archive/{provider}/{dataset}"),
bucket = "calcofi-files-public", exclude = c(".DS_Store", "*.tmp"))
} else {
cat("publish_to_gcs is FALSE — sources NOT archived to gs://calcofi-files-public",
"(pending Q01: licence + permission to publish)\n")
}
f_ts <- file.path(dir_src, "Dungeness Time Series - Angela Klemmedson.xlsx")
f_log <- file.path(dir_src, "Cancer Magister sorting update as of 02-15-12_EJones.xlsx")
f_spec <- file.path(dir_src, "ScrippsArchivedMegalopae_sent_5_10_2012.xlsx")
stopifnot(file_exists(f_ts), file_exists(f_log), file_exists(f_spec))
d_ts <- read_dungeness_timeseries(f_ts)
d_log <- read_dungeness_sorting_log(f_log)
d_spec <- read_dungeness_specimens(f_spec)
d_note <- read_dungeness_notes(f_ts)
cat(glue("time series {nrow(d_ts)} samples ({format(min(d_ts$date))} to ",
"{format(max(d_ts$date))}); sorting log {nrow(d_log)} samples; ",
"specimens {nrow(d_spec)}"), "\n")
# the workbook's own "Plots" sheet summarises 310 samples / 24 M. magister; hold
# the parse to that, so a re-export that changes the sheet fails loudly here
stopifnot(
"expected 310 samples in 'Compiled Cruises'" = nrow(d_ts) == 310,
"expected 24 M. magister megalopae in total" =
sum(d_ts$n_mega_magister, na.rm = TRUE) == 24,
"1904 epoch not applied — dates predate the 2008-2014 series" =
min(d_ts$date) >= as.Date("2008-01-01"))
```
## Methods, in the Sorter's Own Words
The workbook's `Notes` sheet is **empty to every spreadsheet reader** — its
content is a floating text box, which lives in `xl/drawings/` rather than in any
cell, so `read_excel()` returns 0×0. It is the most valuable single artifact here:
Angela Klemmedson's dated statement of gear, magnification, measurement
landmarks, station bounds, results, and her reasoning about aliquots. Recovering
it answered three questions this ingest had open, so it is reproduced verbatim
below rather than summarized.
Three things it settles:
1. **Counts are occurrence, not density, by the author's explicit choice** —
*"I am ignoring 'Aliq.' and 'Volume' because I am not measuring the density of
megalopae in a volume of water, simply the occurrence of megalopae in
samples… therefore I will not adjust the numbers of megalopae."* So the raw
counts this ingest stores are what the author intended, and the aliquot column
is context, not a correction factor.
2. **"14 samples (15 total removal vials)"** — independent confirmation of the
15-rows / 14-occupations discrepancy found below, and its explanation: the
repeated station occupation is a second *removal vial*, not a duplicate row.
3. **The non-Dungeness counts are lower-confidence** — *"Data beyond Dungeness
crab megalopae should be used more as a guideline… there is potential for
error in some of the identifications."* Carried into the column descriptions
so it reaches consumers.
```{r}
#| label: methods-note
#| echo: false
#| results: asis
cat("::: {.callout-note appearance='simple' title='Notes sheet, ",
"\"Dungeness Time Series - Angela Klemmedson.xlsx\" (recovered from ",
"xl/drawings/drawing1.xml)'}\n\n", sep = "")
cat(paste0("> ", gsub("\n", "\n> ", d_note)), "\n\n")
cat(":::\n")
```
## Resolve Cruise + Position from the CalCOFI Reference
The time series records a cruise label and a CalCOFI line/station/order-of-
occupation, but **no coordinates**. Both are recoverable from the CalCOFI
reference tables:
- `cruise_key`: the label is `YYMM` + the two-letter CalCOFI ship abbreviation,
which is `ship.ship_key` — so `1404SH` → `2014-04-3322` (Bell M. Shimada).
- position: join the resulting `(cruise_key, site_key, order_occ)` to the station
occupations in the `swfsc_ichthyo` core `sample` table. That triple identifies
a physical station occupation, so the match also links each sorted sample back
to the tows and nets it came from via `site_sample_key`.
```{r}
#| label: resolve-keys
load_prior_tables(
con, parquet_dir = cc_stage_path("parquet", "swfsc_ichthyo"),
tables = c("ship", "cruise", "grid", "sample"),
geom_tables = c("grid", "sample"), as_view = TRUE)
d_ship <- dbGetQuery(con, "SELECT ship_key, ship_nodc, ship_name FROM ship")
valid_ck <- dbGetQuery(con, "SELECT DISTINCT cruise_key FROM cruise")$cruise_key
d_cruise_lbl <- parse_cruise_label(unique(d_ts$cruise_orig)) |>
left_join(d_ship, by = "ship_key") |>
mutate(cruise_key = if_else(
is.na(year) | is.na(ship_nodc), NA_character_,
sprintf("%04d-%02d-%s", year, month, ship_nodc))) |>
mutate(cruise_key = if_else(cruise_key %in% valid_ck, cruise_key, NA_character_))
stopifnot("every cruise label must resolve to a known cruise_key" =
!any(is.na(d_cruise_lbl$cruise_key)))
d_cruise_lbl |>
select(cruise_orig, ship_key, ship_name, cruise_key) |>
datatable(caption = "Cruise label → cruise_key", options = list(dom = "t"),
rownames = FALSE)
```
```{r}
#| label: match-sites
# CalCOFI station occupations: one row per (cruise, station, order of occupation)
d_site <- dbGetQuery(con, "
SELECT cruise_key, site_key, order_occ,
sample_key AS site_sample_key, latitude, longitude, datetime
FROM sample WHERE sample_type = 'site'")
d_ts_keyed <- d_ts |>
left_join(select(d_cruise_lbl, cruise_orig, ship_key, cruise_key),
by = "cruise_orig") |>
left_join(d_site, by = c("cruise_key", "site_key", "order_occ")) |>
mutate(position_source = if_else(
is.na(site_sample_key), "unmatched", "swfsc_ichthyo:site"))
n_matched <- sum(d_ts_keyed$position_source != "unmatched")
cat(glue("station match: {n_matched}/{nrow(d_ts_keyed)} samples ",
"({round(100 * n_matched / nrow(d_ts_keyed), 1)}%)"), "\n")
# an independent check on the match AND on the unconfirmed time zone: the source
# date should agree with the matched occupation's own timestamp
d_chk <- d_ts_keyed |>
filter(position_source != "unmatched") |>
mutate(d_days = as.integer(date - as.Date(datetime)))
cat(glue("date agreement with matched occupation: ",
"{sum(d_chk$d_days == 0, na.rm = TRUE)}/{nrow(d_chk)} same day, ",
"{sum(abs(d_chk$d_days) <= 1, na.rm = TRUE)} within 1 day"), "\n")
d_ts_keyed |>
filter(position_source == "unmatched") |>
select(cruise_orig, cruise_key, line, station, order_occ, date) |>
datatable(caption = "Samples with no matching CalCOFI station occupation (Q09)",
options = list(dom = "t"), rownames = FALSE)
```
## Build Sample + Measurement Tables
Counts go long, one measurement per row, with the sorter's free-text
identification note kept beside the count it describes. Per-individual carapace
lengths are their own table: the source cell holds one length per megalopa found
(`"6.0mm, 6.5mm, 7.0mm, 7.0mm"`), which is sub-occurrence detail, not a scalar.
Counts are recorded **as found in the examined aliquot** and are deliberately
*not* standardized — which matches the author's stated choice (see the Notes
sheet above): the aliquot is context for interpreting an absence, not a
correction factor, and no filtered-water volume exists in the source to make a
density anyway.
```{r}
#| label: build-sample
d_sample <- d_ts_keyed |>
mutate(dungeness_sample_id = row_number(), .before = 1) |>
transmute(
dungeness_sample_id, cruise_orig, cruise_key, ship_key,
line, station, site_key, order_occ = as.integer(order_occ),
date,
datetime_start_utc = as_datetime(date) + dseconds(coalesce(time_sec, 0L)),
latitude, longitude, position_source, site_sample_key,
aliquot, volume_ml,
sorted_by = "Angela Klemmedson",
comments)
dbWriteTable(con, "dungeness_sample", as.data.frame(d_sample), overwrite = TRUE)
cat(glue("dungeness_sample: {nrow(d_sample)} rows"), "\n")
```
```{r}
#| label: build-measurement
# (count column, free-text description column) -> canonical measurement_type
meas_map <- tribble(
~measurement_type, ~n_col, ~desc_col,
"megalopae_magister", "n_mega_magister", NA_character_,
"megalopae_other", "n_mega_other", "mega_other_desc",
"zoea_cancer", "n_zoea_cancer", "zoea_cancer_desc",
"zoea_other", "n_zoea_other", "zoea_other_desc")
d_meas <- pmap_dfr(meas_map, function(measurement_type, n_col, desc_col) {
tibble(
dungeness_sample_id = d_sample$dungeness_sample_id,
measurement_type = measurement_type,
measurement_value = d_ts_keyed[[n_col]],
taxa_description = if (is.na(desc_col)) NA_character_
else d_ts_keyed[[desc_col]])
}) |>
# a blank source cell is "not recorded", which is not the same as a counted
# zero — keep the row and flag it rather than dropping or zero-filling it
mutate(measurement_qual = if_else(
is.na(measurement_value), "blank in source", NA_character_)) |>
arrange(dungeness_sample_id, measurement_type) |>
mutate(dungeness_measurement_id = row_number(), .before = 1)
dbWriteTable(con, "dungeness_measurement", as.data.frame(d_meas), overwrite = TRUE)
# nothing may be lost or invented in the pivot
stopifnot(
"measurement rows must be samples x types" =
nrow(d_meas) == nrow(d_sample) * nrow(meas_map),
"pivoted counts must sum to the source totals" =
sum(d_meas$measurement_value, na.rm = TRUE) ==
sum(d_ts_keyed$n_mega_magister, d_ts_keyed$n_mega_other,
d_ts_keyed$n_zoea_cancer, d_ts_keyed$n_zoea_other, na.rm = TRUE))
cat(glue("dungeness_measurement: {nrow(d_meas)} rows ",
"({sum(!is.na(d_meas$measurement_qual))} blank in source)"), "\n")
```
```{r}
#| label: build-carapace
d_carapace <- parse_carapace_lengths(d_ts_keyed$carapace_len_txt) |>
mutate(dungeness_sample_id = d_sample$dungeness_sample_id[row_index]) |>
select(dungeness_sample_id, individual_num, carapace_length_mm) |>
arrange(dungeness_sample_id, individual_num) |>
mutate(dungeness_carapace_id = row_number(), .before = 1)
dbWriteTable(con, "dungeness_carapace", as.data.frame(d_carapace), overwrite = TRUE)
# one measured individual per megalopa counted — this is the check that the
# carapace cell was split correctly rather than plausibly
n_mag <- sum(d_ts_keyed$n_mega_magister, na.rm = TRUE)
stopifnot("one carapace length per M. magister megalopa counted" =
nrow(d_carapace) == n_mag)
cat(glue("dungeness_carapace: {nrow(d_carapace)} individuals, ",
"{min(d_carapace$carapace_length_mm)}-",
"{max(d_carapace$carapace_length_mm)} mm"), "\n")
```
## Build Sorting Log + Specimen Verification
The sorting log is **effort, not counts**: which archived samples had been looked
at as of 2012-02-15 and which had not. It carries its own coordinates, so no
station matching is needed — but it reaches far north and offshore of the CalCOFI
grid, so most rows get no `grid_key`.
It records no ship, so `cruise_key` is assigned only where the parsed year-month
matches exactly one cruise in the reference.
```{r}
#| label: build-sorting-log
d_ym_lookup <- dbGetQuery(con, "
SELECT strftime(date_ym, '%Y-%m') AS cruise_ym, cruise_key
FROM cruise") |>
count(cruise_ym, cruise_key) |>
add_count(cruise_ym, name = "n_cruises") |>
filter(n_cruises == 1) |>
select(cruise_ym, cruise_key)
d_sorting <- d_log |>
mutate(
# expedition labels are inconsistent ("8803", "CALCOFI 0404", "BODEGA BAY");
# take a trailing YYMM where one exists, otherwise no cruise at all
yymm = str_match(expedition_orig, "(\\d{2})(\\d{2})[A-Z]?$")[, 1],
yy = as.integer(str_sub(yymm, 1, 2)),
mm = as.integer(str_sub(yymm, 3, 4)),
# this log spans 1949-2009: resolve the 2-digit year against the row's own
# date rather than a fixed pivot
cruise_ym = if_else(
is.na(yymm) | is.na(date), NA_character_,
sprintf("%04d-%02d", 100L * (year(date) %/% 100L) + yy, mm)),
# one row records longitude +138.483 where every other is negative; flag it
# rather than silently negating (Q08)
longitude = if_else(!is.na(longitude) & longitude > 0, NA_real_, longitude)) |>
left_join(d_ym_lookup, by = "cruise_ym") |>
transmute(
sorting_status, expedition_orig, cruise_ym, cruise_key,
line, station, site_key, latitude, longitude,
date,
datetime_start_utc = as_datetime(date) + dseconds(coalesce(time_start_sec, 0L)),
datetime_end_utc = as_datetime(date) + dseconds(coalesce(time_end_sec, 0L)),
max_depth_m, net, mesh_mm, tow_type, preservative,
sorted_by = "Emily Jones") |>
arrange(date, site_key) |>
mutate(dungeness_sorting_log_id = row_number(), .before = 1)
dbWriteTable(con, "dungeness_sorting_log", as.data.frame(d_sorting), overwrite = TRUE)
cat(glue("dungeness_sorting_log: {nrow(d_sorting)} rows ",
"({sum(d_sorting$sorting_status == 'sorted')} sorted, ",
"{sum(d_sorting$sorting_status == 'unsorted')} unsorted); ",
"cruise_key resolved for {sum(!is.na(d_sorting$cruise_key))}; ",
"dates {format(min(d_sorting$date))} to {format(max(d_sorting$date))}"), "\n")
```
```{r}
#| label: build-specimen
d_specimen <- d_spec |>
transmute(
specimen_num, cruise_orig, line, station, site_key, date,
datetime_start_utc = as_datetime(date) + dseconds(coalesce(time_sec, 0L)),
is_magister, carapace_length_mm, notes)
dbWriteTable(con, "dungeness_specimen", as.data.frame(d_specimen), overwrite = TRUE)
cat(glue("dungeness_specimen: {nrow(d_specimen)} specimens, ",
"{sum(d_specimen$is_magister)} confirmed M. magister"), "\n")
```
## Enforce Column Types
Before any geometry exists: `enforce_column_types()` rewrites columns, and a
table carrying a `GEOMETRY` column is the one thing DuckDB ≥ 1.5.1 will not let
you rewrite (see the known-bug note in `CLAUDE.md`).
```{r}
#| label: enforce-types
d_flds_rd <- read_csv(here(glue("metadata/{provider}/{dataset}/flds_redefine.csv")))
enforce_column_types(con, d_flds_rd = d_flds_rd, tables = dcr_tbls)
```
## Spatial
`geom` and `grid_key` come last, so every other column value is already settled
in R and nothing has to `UPDATE` a table that has geometry on it.
```{r}
#| label: spatial
for (tbl in c("dungeness_sample", "dungeness_sorting_log")) {
add_point_geom(con, tbl, lon_col = "longitude", lat_col = "latitude")
assign_grid_key(con, tbl)
}
d_spatial_cov <- dbGetQuery(con, "
SELECT 'dungeness_sample' AS tbl, COUNT(*) AS n,
COUNT(latitude) AS n_positioned,
COUNT(grid_key) AS n_in_grid FROM dungeness_sample
UNION ALL
SELECT 'dungeness_sorting_log', COUNT(*), COUNT(latitude), COUNT(grid_key)
FROM dungeness_sorting_log")
# the borrowed swfsc_ichthyo reference VIEWs have done their job (cruise/ship
# resolution, station match, grid assignment). Drop them so validation and the
# metadata sidecar see only this dataset's tables — otherwise the checks below
# report NULLs in ichthyo's 61k-row `sample`, which is not this ingest's business.
for (v in c("sample", "ship", "cruise", "grid"))
dbExecute(con, glue("DROP VIEW IF EXISTS {v}"))
d_spatial_cov |>
datatable(caption = "Position + grid coverage", options = list(dom = "t"),
rownames = FALSE)
```
## Measurement Vocabulary + Dataset Metadata
The source's four count columns are **not** measurement types. Baking the taxon
into the type name (`megalopae_magister`) is exactly the anti-pattern the core
model exists to remove: in `obs` the quantity is `abundance` and the organism is
`taxon_key`. So the four raw labels became entries in
`metadata/measurement_taxon.csv`, which maps each to
`(measurement_type, taxon_key, life_stage)` — see "Emit Core Tables" below.
That leaves two genuinely new canonical quantities, `carapace_length` and
`settled_volume_ml`. They were staged in
`metadata/cdfw/dungeness-crab/measurement_type_new.csv` while this dataset was
held out — the shared registry ships wholesale into the release, so appending
them early would have published two types with no observations behind them. They
moved into `metadata/measurement_type.csv` on 2026-08-14, in the change that put
this dataset in the release, and the staging file is gone. The aliquot fraction
maps onto the existing `prop_sorted`, so it needs nothing new.
```{r}
#| label: meas-types
# `carapace_length` and `settled_volume_ml` were staged in
# measurement_type_new.csv while this dataset was held out of the release, since
# the shared registry ships wholesale and would have published two types with no
# observations. They moved into metadata/measurement_type.csv (via
# register_measurement_types(), which is append-only and always writes na = "")
# in the change that put this dataset in the release, so there is nothing left to
# union — the shared registry is now the whole story.
d_meas_type <- read_measurement_type(here("metadata/measurement_type.csv"))
stopifnot(
"the two formerly-staged types must now be in the shared registry" =
all(c("carapace_length", "settled_volume_ml") %in% d_meas_type$measurement_type))
dbWriteTable(con, "measurement_type", as.data.frame(d_meas_type), overwrite = TRUE)
this_provider <- provider; this_dataset <- dataset # `provider`/`dataset` are also column names
d_dataset <- ingest_yaml_to_dataset_df(read_ingest_yaml(here())) |>
filter(provider == this_provider, dataset == this_dataset)
stopifnot("this dataset must appear in the ingest YAML registry" = nrow(d_dataset) == 1)
dbWriteTable(con, "dataset", as.data.frame(d_dataset), overwrite = TRUE)
d_meas_type |>
filter(`_source_datasets` == glue("{provider}_{dataset}")) |>
select(measurement_type, description, units, grain) |>
datatable(caption = "Canonical measurement types contributed by this dataset",
options = list(dom = "t"), rownames = FALSE)
```
## Emit Core Tables
Project this dataset into the shared consolidated core model (`RUNBOOK.md` §3b,
`design_env-bio-consolidation.md`). These core tables **are** this ingest's
published output — `release_database.qmd` concatenates per-dataset shards rather
than re-deriving them — and the per-dataset tables survive as compat VIEWs.
This uses the generic `append_*` helpers with plain SQL, so **no `calcofi4db`
change is needed to add a dataset** — a dataset's projection belongs in the
notebook that owns it. The package used to carry a `switch(dataset_key, …)` arm
per core table; those were deleted in calcofi4db 3.0.0, because the release kept a
second copy of every arm and the two drifted into four silent data errors.
Two modelling decisions worth review:
- **`sample_type = 'subsample'` is new vocabulary** (existing values: `bottle`,
`cast`, `net`, `region_pool`, `site`, `tow`, `transect`, `underway`). The grain
genuinely is a lab subsample of an archived net catch — finer than the tow,
since three station occupations were examined twice as separate removal vials —
so none of the existing values fits. Flagged as Q05.
- **`parent_sample_key` points at the matched `swfsc_ichthyo` site occupation**, a
deliberate cross-dataset edge in the `sample` adjacency list. This is the payoff
of the station match: it makes crab larvae joinable to CTD and bottle
environment through the shared physical station occupation instead of stranding
them on their own island.
The 2,011-row sorting log becomes `sample` rows (`sample_type = 'tow'`), and the
sorted/unsorted distinction becomes the presence or absence of an `obs` row:
- **216 sorted** → one `obs` row for *M. magister* with `measurement_value = 0`.
"Examined, none found" is a real observation, and a zero is how you record it.
- **1,795 unsorted** → `sample` row, no `obs`. We have not looked, which is not
the same as looking and finding nothing.
That distinction is the entire scientific content of the log, and encoding it this
way means it needs no `sorting_status` column of its own — which is what lets this
ingest publish the core and nothing else (see "Write Outputs"). Note the zeros are
safe with respect to the open Q02: that question doubts the *positive* Reilly-era
"Cancer magister" labels, not Jones's report of finding none.
```{r}
#| label: emit-core
ds_key <- glue("{provider}_{dataset}")
# the taxon crosswalk resolves each raw count label to (measurement_type,
# taxon_key, life_stage). Restricted to this dataset so the helpers below build
# only our slice of the shared taxa references.
# worms_id/itis_id MUST be integer: read as double, CAST(... AS VARCHAR) yields
# "440388.0" and every taxon_key silently fails to resolve against `taxon`
mt_taxon <- read_csv(here("metadata/measurement_taxon.csv"),
col_types = cols(worms_id = "i", itis_id = "i",
bin_value = "d", .default = "c")) |>
filter(dataset_key == ds_key)
tx_over <- read_csv(here("metadata/taxon_override.csv"))
stopifnot("crosswalk must cover all four raw count labels" =
setequal(mt_taxon$raw_measurement_type[mt_taxon$target == "obs"],
c("megalopae_magister", "megalopae_other",
"zoea_cancer", "zoea_other")))
# taxa references — generic helpers, driven by the crosswalk rather than by a
# per-dataset vocabulary table
# cross-reference: resolve each taxon against BOTH authorities (cached in
# metadata/taxon_xref.csv, so a re-run costs no API calls). This fills the
# `worms_id` COLUMN on itis:-keyed taxa without touching their key — a consumer
# joining on worms_id used to match ZERO rows for every seabird and marine
# mammal — backfills `itis_id` the other way, replaces an id its authority has
# deprecated so the key is always an accepted id, and fetches the real
# `taxonomic_status` with the date it was checked. Must precede the lineage
# fetch, which should ask about the accepted id, not the deprecated one.
ensure_taxon_xref(con, mt_taxon, tx_over,
cache_csv = here("metadata/taxon_xref.csv"))
# lineage: fetch each taxon's WoRMS/ITIS classification (cached in
# metadata/taxon_lineage.csv, so a re-run costs no API calls) and stage it as the
# `taxon` hierarchy build_taxon_reference() reads. Without it a crosswalk- or
# vocabulary-resolved taxon reaches the release with a key and a name and NOTHING
# else — no rank, no parent_taxon_key, no classification — so hierarchy rollups
# ("all Decapoda") silently match nothing and no error is raised anywhere.
ensure_taxon_lineage(con, mt_taxon, tx_over,
cache_csv = here("metadata/taxon_lineage.csv"))
n_taxon <- build_taxon_reference(con, mt_taxon, tx_over)
n_ds_taxon <- build_dataset_taxon(con, mt_taxon, tx_over)
n_tx_group <- build_taxon_group(con, mt_taxon, tx_over)
# stage the crosswalk WITH its derived taxon_key, rather than dbWriteTable()-ing
# the raw CSV (which has no taxon_key column) and hand-rolling
# 'worms:' || worms_id in each arm — that inline string mis-keys any
# ITIS-resolved taxon and silently produces a taxon_key that joins to nothing
ensure_measurement_taxon(con, mt_taxon, dataset_key = ds_key)
sub_key <- glue("'{ds_key}:subsample:' || CAST(dungeness_sample_id AS VARCHAR)")
# 1. sample — examined subsamples, parented to the matched station occupation
append_sample(con, glue("
SELECT {sub_key}, 'subsample', site_sample_key,
COALESCE(site_sample_key, {sub_key}),
'{ds_key}', grid_key, site_key, cruise_key, order_occ,
latitude, longitude, CAST(datetime_start_utc AS TIMESTAMP),
NULL::DOUBLE, NULL::DOUBLE, NULL::VARCHAR
FROM dungeness_sample"))
# 1b. sample — sorting-log tows (effort/absence; no obs attach to these)
append_sample(con, glue("
SELECT '{ds_key}:tow:' || CAST(dungeness_sorting_log_id AS VARCHAR),
'tow', NULL::VARCHAR,
'{ds_key}:tow:' || CAST(dungeness_sorting_log_id AS VARCHAR),
'{ds_key}', grid_key, site_key, cruise_key, NULL::INTEGER,
latitude, longitude, CAST(datetime_start_utc AS TIMESTAMP),
0::DOUBLE, max_depth_m, tow_type
FROM dungeness_sorting_log"))
# 2. obs — occurrence headline. Because the taxon now lives in taxon_key, the
# four source columns collapse to ONE measurement_type across four
# taxon_key/life_stage combinations.
append_obs(con, glue("
SELECT 'bio', '{ds_key}',
'{ds_key}:subsample:' || CAST(s.dungeness_sample_id AS VARCHAR),
s.grid_key, s.cruise_key, s.latitude, s.longitude,
CAST(s.datetime_start_utc AS TIMESTAMP),
NULL::DOUBLE, NULL::DOUBLE,
mx.taxon_key,
mx.life_stage, mx.measurement_type, m.measurement_value,
m.measurement_qual, NULL::DOUBLE
FROM dungeness_measurement m
JOIN dungeness_sample s USING (dungeness_sample_id)
JOIN _measurement_taxon mx
ON mx.raw_measurement_type = m.measurement_type AND mx.target = 'obs'"))
# 2b. obs — the sorting log's ABSENCES. A sorted sample with no M. magister is a
# zero-valued occurrence, not a missing row; an unsorted sample gets nothing,
# because "not examined" and "examined, none found" are different facts.
append_obs(con, glue("
SELECT 'bio', '{ds_key}',
'{ds_key}:tow:' || CAST(l.dungeness_sorting_log_id AS VARCHAR),
l.grid_key, l.cruise_key, l.latitude, l.longitude,
CAST(l.datetime_start_utc AS TIMESTAMP),
0::DOUBLE, l.max_depth_m,
mx.taxon_key,
mx.life_stage, mx.measurement_type, 0::DOUBLE,
'examined, none found (E. Jones sorting log, as of 2012-02-15)',
NULL::DOUBLE
FROM dungeness_sorting_log l
CROSS JOIN (SELECT * FROM _measurement_taxon
WHERE raw_measurement_type = 'megalopae_magister') mx
WHERE l.sorting_status = 'sorted'"))
# 3. obs_attribute — per-individual carapace lengths within the M. magister
# occurrence: one row per measured megalopa, bin_value IS the length
append_obs_attribute(con, glue("
SELECT '{ds_key}',
'{ds_key}:subsample:' || CAST(c.dungeness_sample_id AS VARCHAR),
mx.taxon_key, mx.life_stage,
mx.measurement_type, c.carapace_length_mm,
NULL::VARCHAR, 1::BIGINT, NULL::VARCHAR
FROM dungeness_carapace c
CROSS JOIN (SELECT * FROM _measurement_taxon
WHERE target = 'attribute' AND measurement_type = 'carapace_length') mx"))
# 4. sample_measurement — event-level effort/context for the examined subsamples
append_sample_measurement(con, glue("
SELECT '{ds_key}:subsample:' || CAST(dungeness_sample_id AS VARCHAR),
'{ds_key}', mt, mv, NULL::VARCHAR
FROM (
SELECT dungeness_sample_id, 'prop_sorted' AS mt, aliquot AS mv FROM dungeness_sample
UNION ALL
SELECT dungeness_sample_id, 'settled_volume_ml', volume_ml FROM dungeness_sample)
WHERE mv IS NOT NULL"))
core_n <- list(
sample = dbGetQuery(con, "SELECT COUNT(*) FROM sample")[[1]],
obs = dbGetQuery(con, "SELECT COUNT(*) FROM obs")[[1]],
obs_attribute = dbGetQuery(con, "SELECT COUNT(*) FROM obs_attribute")[[1]],
sample_measurement = dbGetQuery(con, "SELECT COUNT(*) FROM sample_measurement")[[1]],
taxon = n_taxon, dataset_taxon = n_ds_taxon, taxon_group = n_tx_group)
cat(glue("core projection — ",
"{paste(names(core_n), unlist(core_n), sep = '=', collapse = ' ')}"), "\n")
```
The projection must be lossless and must not invent anything, so assert it rather
than eyeball it:
```{r}
#| label: core-parity
n_sub <- dbGetQuery(con, "SELECT COUNT(*) FROM sample WHERE sample_type='subsample'")[[1]]
n_tow <- dbGetQuery(con, "SELECT COUNT(*) FROM sample WHERE sample_type='tow'")[[1]]
stopifnot(
"one core sample per examined subsample" = n_sub == nrow(d_sample),
"one core sample per sorting-log tow" = n_tow == nrow(d_sorting),
"sample_key must be unique within the shard" =
dbGetQuery(con, "SELECT COUNT(*) FROM (SELECT sample_key FROM sample
GROUP BY 1 HAVING COUNT(*) > 1)")[[1]] == 0,
# the time series' counts, plus one zero-valued absence per sorted log sample
"obs must carry every source count row plus the sorting-log absences" =
core_n$obs == nrow(d_meas) + sum(d_sorting$sorting_status == "sorted"),
"obs totals must equal the source totals (absences add zero)" =
dbGetQuery(con, "SELECT SUM(measurement_value) FROM obs")[[1]] ==
sum(d_meas$measurement_value, na.rm = TRUE),
"every sorted log sample must have exactly one absence obs" =
dbGetQuery(con, "
SELECT COUNT(*) FROM obs o JOIN sample s USING (sample_key)
WHERE s.sample_type = 'tow'")[[1]] ==
sum(d_sorting$sorting_status == "sorted"),
"unsorted log samples must carry no obs" =
dbGetQuery(con, glue("
SELECT COUNT(*) FROM obs o
WHERE o.sample_key IN (
SELECT '{ds_key}:tow:' || CAST(dungeness_sorting_log_id AS VARCHAR)
FROM dungeness_sorting_log WHERE sorting_status = 'unsorted')"))[[1]] == 0,
"sorting-log absences must all be zero" =
dbGetQuery(con, "
SELECT COUNT(*) FROM obs o JOIN sample s USING (sample_key)
WHERE s.sample_type = 'tow' AND o.measurement_value <> 0")[[1]] == 0,
"every obs must resolve a taxon_key" =
dbGetQuery(con, "SELECT COUNT(*) FROM obs WHERE taxon_key IS NULL")[[1]] == 0,
# NOT-NULL is not enough: a numeric-typed worms_id renders as "440388.0", which
# is non-NULL and joins to nothing. Assert the FK actually resolves.
"every obs.taxon_key must resolve in taxon" =
dbGetQuery(con, "SELECT COUNT(*) FROM obs o LEFT JOIN taxon t USING (taxon_key)
WHERE t.taxon_key IS NULL")[[1]] == 0,
"every obs_attribute.taxon_key must resolve in taxon" =
dbGetQuery(con, "SELECT COUNT(*) FROM obs_attribute a
LEFT JOIN taxon t USING (taxon_key)
WHERE t.taxon_key IS NULL")[[1]] == 0,
"every obs.measurement_type must resolve in measurement_type" =
dbGetQuery(con, "SELECT COUNT(*) FROM obs o
LEFT JOIN measurement_type t USING (measurement_type)
WHERE t.measurement_type IS NULL")[[1]] == 0,
"every obs.sample_key must resolve in sample" =
dbGetQuery(con, "SELECT COUNT(*) FROM obs o LEFT JOIN sample s USING (sample_key)
WHERE s.sample_key IS NULL")[[1]] == 0,
"one obs_attribute row per measured individual" =
core_n$obs_attribute == nrow(d_carapace),
# the M. magister headline and its carapace attribution must agree per sample
"carapace individuals must match the magister count per sample" =
dbGetQuery(con, "
WITH a AS (SELECT sample_key, SUM(count) s FROM obs_attribute
WHERE measurement_type='carapace_length' GROUP BY 1),
o AS (SELECT sample_key, SUM(measurement_value) v FROM obs
WHERE taxon_key='worms:440388' AND measurement_type='abundance'
GROUP BY 1)
SELECT COUNT(*) FROM a JOIN o USING (sample_key) WHERE a.s <> o.v")[[1]] == 0,
# the headline count must come from the time series alone, not the absences
"the 24 megalopae must all come from the examined subsamples" =
dbGetQuery(con, "
SELECT SUM(o.measurement_value) FROM obs o JOIN sample s USING (sample_key)
WHERE s.sample_type = 'subsample' AND o.taxon_key = 'worms:440388'")[[1]] == 24)
cat("core parity: all assertions passed\n")
dbGetQuery(con, "
SELECT COALESCE(t.scientific_name, o.taxon_key) AS taxon, o.life_stage,
o.measurement_type, COUNT(*) AS n_obs, SUM(o.measurement_value) AS total
FROM obs o LEFT JOIN taxon t USING (taxon_key)
GROUP BY 1,2,3 ORDER BY total DESC") |>
datatable(caption = "obs by resolved taxon — the four source columns, collapsed",
options = list(dom = "t"), rownames = FALSE)
```
The per-dataset tables the core fully models become VIEWs over it, so the previews
below and any ad-hoc query keep working against the names this notebook built. The
originals are kept as `*_src` for the source ERD.
These VIEWs are a **convenience inside this notebook only** — they are not
published. Nothing per-dataset is: see "Write Outputs".
```{r}
#| label: compat-views
for (t in c("dungeness_measurement", "dungeness_carapace")) {
invisible(dbExecute(con, glue("DROP TABLE IF EXISTS {t}_src")))
invisible(dbExecute(con, glue("ALTER TABLE {t} RENAME TO {t}_src")))
}
# scoped to the examined subsamples: the 216 sorting-log absence rows are also
# `obs` for this dataset, but they were never part of dungeness_measurement and
# their sample_key resolves to a sorting-log id, not a dungeness_sample_id
invisible(dbExecute(con, glue("
CREATE OR REPLACE VIEW dungeness_measurement AS
SELECT obs_id AS dungeness_measurement_id,
CAST(split_part(sample_key, ':', 3) AS INTEGER) AS dungeness_sample_id,
measurement_type, measurement_value, measurement_qual
FROM obs
WHERE dataset_key = '{ds_key}' AND split_part(sample_key, ':', 2) = 'subsample'")))
invisible(dbExecute(con, glue("
CREATE OR REPLACE VIEW dungeness_carapace AS
SELECT obs_attribute_id AS dungeness_carapace_id,
CAST(split_part(sample_key, ':', 3) AS INTEGER) AS dungeness_sample_id,
ROW_NUMBER() OVER (PARTITION BY sample_key ORDER BY obs_attribute_id)
AS individual_num,
bin_value AS carapace_length_mm
FROM obs_attribute
WHERE dataset_key = '{ds_key}' AND measurement_type = 'carapace_length'")))
n_view <- dbGetQuery(con, "SELECT COUNT(*) FROM dungeness_measurement")[[1]]
n_cara <- dbGetQuery(con, "SELECT COUNT(*) FROM dungeness_carapace")[[1]]
stopifnot(
"compat view must expose every source measurement row" = n_view == nrow(d_meas),
"compat view must expose every obs_attribute row" = n_cara == core_n$obs_attribute)
cat(glue("compat views over core — dungeness_measurement {n_view} rows ",
"(the {core_n$obs - n_view} sorting-log absences are obs but not part of ",
"this view), dungeness_carapace {n_cara} rows"), "\n")
```
## Schema
Two diagrams, because they answer different questions: what the workbooks look
like, and what this ingest actually publishes.
### What gets published — the core
This is the schema a consumer sees. It is the only thing written to parquet.
```{r}
#| label: schema-core
core_tbls <- core_output_tables(con, extra = "measurement_type")
cc_erd(con, tables = core_tbls, rels = core_relationships(core_tbls),
colors = list(
lightblue = c("sample", "obs"),
lightgreen = c("obs_attribute", "sample_measurement"),
lightyellow = c("taxon", "dataset_taxon", "measurement_type")))
```
### How the source maps onto it
The workbooks are three unrelated spreadsheets; the core is five related tables.
Neither diagram alone shows how one becomes the other, so state it explicitly —
this is the part a reader actually needs in order to trust the numbers.
```{r}
#| label: source-to-core
#| echo: false
tribble(
~source, ~`source field`, ~`core table`, ~`core field`, ~transform,
"Compiled Cruises", "Cruise", "sample", "cruise_key",
"YYMM + 2-letter ship abbrev resolved through ship.ship_key",
"Compiled Cruises", "Line / Station", "sample", "site_key",
"formatted 'LLL.L SSS.S'; position from the matched ichthyo occupation",
"Compiled Cruises", "Order_Occ", "sample", "order_occ",
"matched to swfsc_ichthyo site occupation -> parent_sample_key",
"Compiled Cruises", "Date + Time", "sample", "datetime",
"1904-epoch date + day-fraction time (tz unconfirmed, Q04)",
"Compiled Cruises", "Dungeness Crab Megalopae", "obs",
"measurement_value", "measurement_type='abundance', taxon_key=worms:440388, life_stage='megalopa'",
"Compiled Cruises", "Other Megalopae", "obs", "measurement_value",
"as above, taxon_key=worms:1130 (Decapoda), life_stage='megalopa'",
"Compiled Cruises", "Cancer Zoea", "obs", "measurement_value",
"as above, taxon_key=worms:106749 (Cancridae), life_stage='zoea'",
"Compiled Cruises", "Other Zoea", "obs", "measurement_value",
"as above, taxon_key=worms:1130 (Decapoda), life_stage='zoea'",
"Compiled Cruises", "Carapace length", "obs_attribute", "bin_value",
"comma-separated cell split to one row per individual, count=1",
"Compiled Cruises", "Aliq.", "sample_measurement", "measurement_value",
"measurement_type='prop_sorted'",
"Compiled Cruises", "Vol (ml)", "sample_measurement", "measurement_value",
"measurement_type='settled_volume_ml'",
"sorting log", "SampleDate + Month", "sample", "datetime",
"day/year text recombined with the separate Month column",
"sorting log", "Lat / Lon", "sample", "latitude / longitude",
"as given; one positive longitude nulled as a suspected sign error (Q08)",
"sorting log", "Tow / MaxDepth", "sample", "tow_type / depth_max_m", "as given",
"sorting log", "sorted vs unsorted", "obs", "(row presence)",
"sorted -> one zero-valued M. magister obs; unsorted -> no obs at all",
"—", "the four raw count labels", "taxon / dataset_taxon", "taxon_key",
"metadata/measurement_taxon.csv -> WoRMS ids",
"NOT PUBLISHED", "free text (Description, Sample Comments, specimen notes)",
"—", "—", "no core home; stays in the archived source workbooks",
"NOT PUBLISHED", "gear detail (Net, Mesh, Preservative), Expedition label",
"—", "—", "beyond sample.tow_type the core does not model gear; archived source",
"NOT PUBLISHED", "5-specimen ID verification (is_magister)", "—", "—",
"a taxonomic determination, not an abundance observation; see Overview + Q02"
) |>
datatable(caption = "Source -> core mapping, including what is deliberately not published",
options = list(dom = "t", pageLength = 25), rownames = FALSE)
```
### The source shape
For reference only — it documents the wrangling above, and none of it is
published.
```{r}
#| label: schema
dcr_rels <- list(
primary_keys = list(
dungeness_sample = "dungeness_sample_id",
dungeness_sorting_log = "dungeness_sorting_log_id",
dungeness_specimen = "specimen_num",
measurement_type = "measurement_type"),
foreign_keys = list(
list(table = "dungeness_measurement_src", column = "dungeness_sample_id",
ref_table = "dungeness_sample", ref_column = "dungeness_sample_id"),
list(table = "dungeness_carapace_src", column = "dungeness_sample_id",
ref_table = "dungeness_sample", ref_column = "dungeness_sample_id")))
cc_erd(con,
tables = c("dungeness_sample", "dungeness_measurement_src",
"dungeness_carapace_src", "dungeness_sorting_log",
"dungeness_specimen", "dataset"),
rels = dcr_rels,
colors = list(
lightblue = c("dungeness_sample", "dungeness_measurement_src",
"dungeness_carapace_src"),
lightgreen = c("dungeness_sorting_log", "dungeness_specimen"),
white = "dataset"))
```
## Validate
```{r}
#| label: validate
results <- validate_for_release(con, checks = "all", strict = FALSE)
# `validate_for_release()`'s null check treats EVERY column ending _id/_key/_uuid
# as required, which is a heuristic, not this dataset's contract: `parent_taxon_key`
# is legitimately NULL for a root taxon, `cruise_key` for a log entry that names no
# ship, `grid_key` for a station outside the CalCOFI grid. So its verdict is
# "FAILED" for reasons that are mostly not defects.
#
# Reporting that as "FAILED, but all expected" is worthless — it is exactly how a
# real defect hides in a wall of accepted noise. Instead: every nullable case is
# declared below WITH ITS COUNT AND REASON, and anything that is not on this list,
# or whose count has moved, is a hard failure. That turns a vague verdict into a
# contract that breaks when the data changes.
nullable <- tribble(
~table, ~column, ~n, ~reason,
"dungeness_sample", "site_sample_key", 4L, "no matching CalCOFI station occupation (Q09)",
"dungeness_sample", "grid_key", 4L, "same 4 samples: no position, so no grid cell",
"dungeness_sorting_log", "cruise_key", 1639L,"log records no ship; cruise resolves only where YYYY-MM is unambiguous",
"dungeness_sorting_log", "site_key", 77L, "no line/station in the source row",
"dungeness_sorting_log", "grid_key", 77L, "same 77 rows: north of or offshore from the CalCOFI grid",
"sample", "parent_sample_key", 2015L,"2011 sorting-log tows are roots + the 4 unmatched subsamples",
"sample", "site_key", 77L, "inherited from the sorting log",
"sample", "cruise_key", 1639L,"inherited from the sorting log",
"sample", "grid_key", 81L, "4 unmatched subsamples + 77 out-of-grid log tows",
"obs", "grid_key", 30L, "16 from the 4 unmatched subsamples x 4 types + 14 out-of-grid absences",
"obs", "cruise_key", 87L, "absence rows whose log entry has no resolvable cruise",
"obs", "hex_id", 16L, "H3 needs a position; the 4 unmatched subsamples have none",
# taxon: 17 rows, not 3 — ensure_taxon_lineage() adds this dataset's 3 taxa
# PLUS their 14 WoRMS lineage ancestors, which have to be rows of their own or
# parent_taxon_key chains dead-end and "all Decapoda" matches nothing.
# `rank`/classification are no longer on this list: the lineage fetch fills them.
"taxon", "ncbi_id", 17L, "never populated by ANY dataset — the column exists, nothing fills it",
"taxon", "inat_id", 17L, "never populated by ANY dataset",
# 17 -> 15 -> 6. Nothing asked WoRMS for a cross-authority id at all; then
# ensure_taxon_xref() asked for this dataset's own taxa; then (calcofi4db
# 3.8.0) ensure_taxon_lineage() started topping the cross-reference up for the
# lineage ANCESTORS it discovers, which the xref step runs too early to see.
# The 6 that remain are taxa WoRMS links no TSN for — mostly the ranks ITIS
# does not share (Section, Subsection, Superclass).
"taxon", "itis_id", 6L, "WoRMS links no ITIS TSN for these — ranks ITIS does not carry (Section/Subsection) or unmatched nodes",
"taxon", "gbif_id", 17L, "nothing asks GBIF — unlike itis_id, there is no GBIF crosswalk in the xref",
"taxon", "parent_taxon_key", 1L, "the root of the lineage (Biota) has no parent — 0 dangling parents below it")
nulls <- results$checks |>
filter(check == "nulls", status != "PASS") |>
mutate(table = table,
column = str_match(message, "column '([^']+)'")[, 2],
n = as.integer(str_match(message, "has (\\d+) NULL")[, 2])) |>
select(table, column, n)
declared <- nullable |> select(table, column, n_expected = n)
reconciled <- full_join(nulls, declared, by = c("table", "column"))
unexplained <- reconciled |> filter(is.na(n_expected))
moved <- reconciled |> filter(!is.na(n_expected), !is.na(n), n != n_expected)
resolved <- reconciled |> filter(!is.na(n_expected), is.na(n))
if (nrow(unexplained))
print(unexplained)
if (nrow(moved))
print(moved)
stopifnot(
"a NULL appeared in a column with no declared reason — investigate, do not add it to the list reflexively" =
nrow(unexplained) == 0,
"a declared NULL count has changed — the data moved, so re-derive the reason" =
nrow(moved) == 0)
if (nrow(resolved))
cat(glue("{nrow(resolved)} declared nullable case(s) no longer occur — ",
"prune them from `nullable`: ",
"{paste(resolved$table, resolved$column, sep = '.', collapse = ', ')}"), "\n")
# The lineage gap this ingest used to assert (all 3 taxa with rank and
# parent_taxon_key NULL, so "all Decapoda" matched nothing) is FIXED:
# ensure_taxon_lineage() fetches the WoRMS classification above. Assert the fix
# rather than the gap — including that Metacarcinus magister is reachable from
# Decapoda, which is the rollup that was broken.
n_taxon_no_lineage <- dbGetQuery(con, "
SELECT COUNT(*) FROM taxon WHERE rank IS NULL AND parent_taxon_key IS NULL")[[1]]
n_decapoda <- dbGetQuery(con, "
WITH RECURSIVE d AS (
SELECT taxon_key FROM taxon WHERE scientific_name = 'Decapoda'
UNION ALL
SELECT t.taxon_key FROM taxon t JOIN d ON t.parent_taxon_key = d.taxon_key)
SELECT COUNT(*) FROM d")[[1]]
stopifnot(
"every taxon must carry rank + parent_taxon_key (the lineage fetch)" =
n_taxon_no_lineage == 0,
"M. magister must be reachable from Decapoda by walking parent_taxon_key" =
n_decapoda > 1)
cat(glue("validate_for_release: {ifelse(results$passed, 'PASSED', 'FAILED')} ",
"— its null heuristic treats every *_id/_key column as required, so that ",
"verdict is not this dataset's contract. {nrow(nulls)} null case(s) ",
"reconciled against {nrow(nullable)} declared, 0 unexplained."), "\n")
cat(glue("taxon lineage: {n_taxon_no_lineage} taxa without rank/parent_taxon_key; ",
"{n_decapoda} taxa reachable from Decapoda by walking parent_taxon_key ",
"(the rollup that used to return nothing)."), "\n")
# referential integrity of the two FKs this ingest owns
n_orphan_meas <- dbGetQuery(con, "
SELECT COUNT(*) FROM dungeness_measurement m
LEFT JOIN dungeness_sample s USING (dungeness_sample_id)
WHERE s.dungeness_sample_id IS NULL")[[1]]
n_orphan_cara <- dbGetQuery(con, "
SELECT COUNT(*) FROM dungeness_carapace c
LEFT JOIN dungeness_sample s USING (dungeness_sample_id)
WHERE s.dungeness_sample_id IS NULL")[[1]]
n_orphan_type <- dbGetQuery(con, "
SELECT COUNT(*) FROM dungeness_measurement m
LEFT JOIN measurement_type t USING (measurement_type)
WHERE t.measurement_type IS NULL")[[1]]
# primary keys must be unique
n_dup_pk <- dbGetQuery(con, "
SELECT COUNT(*) FROM (SELECT dungeness_sample_id FROM dungeness_sample
GROUP BY 1 HAVING COUNT(*) > 1)")[[1]]
stopifnot(
"dungeness_measurement.dungeness_sample_id must resolve" = n_orphan_meas == 0,
"dungeness_carapace.dungeness_sample_id must resolve" = n_orphan_cara == 0,
"dungeness_measurement.measurement_type must resolve" = n_orphan_type == 0,
"dungeness_sample_id must be unique" = n_dup_pk == 0)
cat("FK + PK integrity: OK\n")
# the headline result of the whole dataset, asserted so a bad re-parse is loud.
# 15 positive ROWS but 14 positive station OCCUPATIONS. The Notes sheet states it
# exactly — "M. magister megalopae found in 14 samples (15 total removal vials)"
# — so the repeat at 1204OS/070.0 055.0/occ 45 (1 megalopa in each row) is a
# second removal vial from one sample, not a duplicated row. The workbook's
# "M. magister values" sheet aggregates the pair, which is why it lists 14.
# asked of the CORE now, via taxon_key — the source's 'megalopae_magister' column
# name no longer exists as a measurement_type, which is the point of the model
# reported per EFFORT, because the two are not the same survey: the 2015 time
# series counted, the 2012 log only recorded that it looked. Lumping them silently
# changes the occurrence rate (15/310 becomes 15/526), so name the scope.
mag <- dbGetQuery(con, "
SELECT s.sample_type,
COUNT(*) AS n_examined,
COUNT(*) FILTER (WHERE o.measurement_value > 0) AS n_positive_rows,
COUNT(DISTINCT CASE WHEN o.measurement_value > 0
THEN s.cruise_key || s.site_key || s.order_occ END)
AS n_positive_stations,
SUM(o.measurement_value) AS n_megalopae
FROM obs o JOIN sample s USING (sample_key)
WHERE o.taxon_key = 'worms:440388' AND o.life_stage = 'megalopa'
GROUP BY 1 ORDER BY 1") |>
(\(d) d[match(c("subsample", "tow"), d$sample_type), ])()
names(mag)[1] <- "effort"
mag$effort <- c("2015 time series (Klemmedson)", "2012 sorting log (Jones)")
mag |> datatable(caption = "M. magister megalopae by effort — the log contributes examined-but-empty samples",
options = list(dom = "t"), rownames = FALSE)
mag <- dbGetQuery(con, "
SELECT COUNT(*) AS n_samples,
COUNT(*) FILTER (WHERE o.measurement_value > 0) AS n_positive_rows,
COUNT(DISTINCT CASE WHEN o.measurement_value > 0
THEN s.cruise_key || s.site_key || s.order_occ END)
AS n_positive_stations,
SUM(o.measurement_value) AS n_megalopae
FROM obs o JOIN sample s USING (sample_key)
WHERE o.taxon_key = 'worms:440388' AND o.life_stage = 'megalopa'
AND s.sample_type = 'subsample'")
stopifnot(
"24 M. magister megalopae in total" = mag$n_megalopae == 24,
"15 positive rows / 14 positive station occupations" =
mag$n_positive_rows == 15 && mag$n_positive_stations == 14)
n_log_examined <- sum(d_sorting$sorting_status == "sorted")
cat(glue("M. magister: {mag$n_megalopae} megalopae in {mag$n_positive_rows} of ",
"{mag$n_samples} samples in the 2015 time series ",
"({round(100 * mag$n_positive_rows / mag$n_samples, 1)}% occurrence), ",
"at {mag$n_positive_stations} distinct station occupations. ",
"Plus {n_log_examined} examined-but-empty samples from the 2012 log, ",
"for {mag$n_positive_rows}/{mag$n_samples + n_log_examined} ",
"({round(100 * mag$n_positive_rows / (mag$n_samples + n_log_examined), 1)}%) ",
"across both efforts"), "\n")
# replicate subsamples of one station occupation — the reason the sample PK is a
# sequential id rather than (cruise, station, order_occ)
dbGetQuery(con, "
SELECT cruise_orig, site_key, order_occ, COUNT(*) AS n_rows
FROM dungeness_sample GROUP BY 1, 2, 3 HAVING COUNT(*) > 1 ORDER BY 1, 2") |>
datatable(caption = "Station occupations examined more than once (replicate subsamples)",
options = list(dom = "t"), rownames = FALSE)
```
## Preview
```{r}
#| label: preview-sample
dbGetQuery(con, "
SELECT * EXCLUDE (geom) FROM dungeness_sample
ORDER BY dungeness_sample_id LIMIT 100") |>
datatable(caption = "dungeness_sample — first 100", rownames = FALSE, filter = "top")
```
```{r}
#| label: preview-positive
# read through the CORE, the way a consumer would: obs joined to sample and taxon,
# with the per-individual lengths from obs_attribute
dbGetQuery(con, "
SELECT s.cruise_key, s.site_key, s.order_occ, CAST(s.datetime AS DATE) AS date,
round(s.latitude, 3) AS lat, round(s.longitude, 3) AS lon,
t.scientific_name, o.life_stage, o.measurement_value AS n_magister,
(SELECT string_agg(CAST(a.bin_value AS VARCHAR), ', ' ORDER BY a.bin_value)
FROM obs_attribute a
WHERE a.sample_key = o.sample_key
AND a.measurement_type = 'carapace_length') AS carapace_mm
FROM obs o
JOIN sample s USING (sample_key)
LEFT JOIN taxon t USING (taxon_key)
WHERE o.taxon_key = 'worms:440388' AND o.measurement_value > 0
ORDER BY s.datetime") |>
datatable(caption = "The 15 removal vials (14 samples) containing M. magister megalopae, read from the core",
rownames = FALSE)
```
```{r}
#| label: preview-sorting
dbGetQuery(con, "
SELECT sorting_status, COUNT(*) AS n_samples,
COUNT(DISTINCT expedition_orig) AS n_expeditions,
MIN(date) AS date_min, MAX(date) AS date_max,
COUNT(grid_key) AS n_in_calcofi_grid
FROM dungeness_sorting_log GROUP BY 1 ORDER BY 1") |>
datatable(caption = "dungeness_sorting_log — effort by status",
options = list(dom = "t"), rownames = FALSE)
```
```{r}
#| label: preview-specimen
dbGetQuery(con, "SELECT * FROM dungeness_specimen ORDER BY specimen_num") |>
datatable(caption = "dungeness_specimen — independent species confirmation",
options = list(dom = "t"), rownames = FALSE)
```
## Questions for Data Providers
```{r}
#| label: provider-questions
# one validated read + render for every ingest: the vocabulary and the column
# order live in calcofi4db, not in 16 hand-written factor() calls
questions_datatable(
here(cc$questions_file),
caption = "Questions for the CDFW Dungeness crab data providers (ranked)")
```
## Write Outputs + Upload
```{r}
#| label: outputs
dir_create(dir_parquet)
# THE CORE, AND NOTHING ELSE (plus the two shared registries). No source table is
# published: the source workbooks are archived, so a per-dataset parquet copy of
# them is redundant, and consumers must read the core rather than choosing between
# two representations of the same data. The compat VIEWs are views over the core,
# so writing them would duplicate the same bytes under a second name.
tbls_out <- core_output_tables(con, extra = c("measurement_type", "dataset"))
cat(glue("publishing {length(tbls_out)} tables: {paste(tbls_out, collapse = ', ')}"), "\n")
write_parquet_outputs(
con = con, output_dir = dir_parquet, tables = tbls_out,
sort_by = list(obs = c("grid_key", "measurement_type")),
strip_provenance = FALSE)
# prune parquet this ingest no longer publishes. Moving to the core retired
# dungeness_measurement.parquet / dungeness_carapace.parquet (now compat VIEWs),
# and a stale file left on disk is still picked up by directory scans and by
# sync_to_gcs — so the output dir must match the manifest exactly.
stale <- setdiff(
tools::file_path_sans_ext(basename(
list.files(dir_stage, pattern = "[.]parquet$"))), tbls_out)
if (length(stale)) {
file_delete(file.path(dir_stage, paste0(stale, ".parquet")))
cat(glue("pruned {length(stale)} stale parquet: {paste(stale, collapse = ', ')}"), "\n")
}
build_relationships_json(
rels = core_relationships(tbls_out), output_dir = dir_parquet,
provider = provider, dataset = dataset)
d_tbls_rd <- read_csv(here(glue("metadata/{provider}/{dataset}/tbls_redefine.csv")))
build_metadata_json(
con = con, d_tbls_rd = d_tbls_rd, d_flds_rd = d_flds_rd,
# shared core descriptions first, this dataset's overrides second — without
# core_dictionary.csv every core table/column ships with an empty description
metadata_derived_csv = c(
here("metadata/core_dictionary.csv"),
here(glue("metadata/{provider}/{dataset}/metadata_derived.csv"))),
output_dir = dir_parquet, tables = tbls_out,
set_comments = TRUE, provider = provider, dataset = dataset,
workflow_url = cc$workflow_url, tables_owned = tables_owned)
if (publish_to_gcs) {
sync_to_gcs(local_dir = dir_stage, sidecar_dir = dir_parquet, gcs_prefix = glue("ingest/{dir_label}"),
bucket = "calcofi-db")
} else {
cat("publish_to_gcs is FALSE — parquet kept local, NOT synced to",
glue("gs://calcofi-db/ingest/{dir_label}"), "\n")
}
```
```{r}
#| label: metadata-gaps
# empty descriptions ship verbatim to consumers, so surface them as TODOs.
# Scoped to every table this ingest PUBLISHES (core + per-dataset) except the two
# shared registries `dataset` and `measurement_type`, which carry no column
# descriptions in any ingest and would be constant noise hiding the real gaps.
m <- fromJSON(file.path(dir_parquet, "metadata.json"), simplifyVector = FALSE)
gap <- function(x, f) names(Filter(function(e) !nzchar(e[[f]] %||% ""), x))
checked <- setdiff(tbls_out, c("dataset", "measurement_type"))
own <- function(keys) keys[sub("[.].*$", "", keys) %in% checked]
cat(glue("metadata.json gaps in owned tables — tables missing description: ",
"{length(own(gap(m$tables, 'description_md')))}; ",
"columns missing description: ",
"{length(own(gap(m$columns, 'description_md')))}"), "\n")
if (length(own(gap(m$columns, "description_md"))))
cat(" ", paste(own(gap(m$columns, "description_md")), collapse = ", "), "\n")
```
## Not Yet Done
This is a first pass. What remains, roughly in order:
1. ~~**Answer the one remaining blocker**, Q01.~~ **DONE, 2026-08-13.** CDFW
(C. Juhasz, via E. Satterthwaite) confirmed the data may be published under
**CC BY 4.0**, with Laura Rogers-Bennett as primary data provider and CDFW as
the current citable custodian — the agency was CFG when the 2012 work was
commissioned, but the current name is the one a reader can act on. The
sorters, E. Jones and A. Klemmedson, are credited in the citation because the
examination effort is the dataset. This unblocked both the release and the
GCS upload. (Q11, standardization, was already answered — see the Notes sheet
above.) Separately, Erin is pursuing publication through the
[CNRA data portal](https://data.cnra.ca.gov/dataset) so there is a citable
repository copy to pull from; that is additive and does not gate anything here.
2. **Review the two modelling choices** in "Emit Core Tables" (Q05): the new
`sample_type = 'subsample'` vocabulary value, and pointing `parent_sample_key`
at the matched `swfsc_ichthyo` site occupation.
3. ~~**Populate the taxon lineage.**~~ **DONE.** All three taxa had `rank` and
`parent_taxon_key` NULL, so a query for "all Decapoda" did not find the
*M. magister* records — and that was true of every dataset whose taxa resolve
through `metadata/measurement_taxon.csv` (`swfsc_cufes`, `calcofi_phyllosoma`,
`cce-lter_euphausiids`), because that path carried only `worms_id` +
`scientific_name`. `ensure_taxon_lineage()` (called in "Emit Core Tables") now
fetches the WoRMS classification, caches it in `metadata/taxon_lineage.csv`,
and stages it as the hierarchy `build_taxon_reference()` reads — which also
fills `kingdom`/`phylum`/`class`/`order_taxon`/`family`, populated by no
dataset before, not even ichthyo. "Validate" now asserts the fix (0 taxa
without lineage, and *M. magister* reachable from Decapoda) rather than the
gap. Still empty: `ncbi_id`/`inat_id`, which no source supplies — kept as
declared-but-NULL columns so the release schema does not shift under
consumers.
4. **Decide what the sorting log is for.** As absence/effort data it is the more
scientifically useful of the two efforts — 2,011 samples over 60 years — but
only if Q02 settles which historic identifications stand, and Q03 settles
whether repeated rows are jars.
5. **Parse the free-text identification notes** (Q07) into `obs_attribute` stage
and length bins.
6. ~~Move `measurement_type_new.csv` into `metadata/measurement_type.csv` and
drop `in_release: false`.~~ **DONE, 2026-08-14**, in one change, alongside
deleting the asserted `coverage_*` keys so coverage is measured like every
other dataset and flipping `publish_to_gcs`.
```{r}
#| label: cleanup
close_duckdb(con)
cat(glue("Parquet outputs written to: {dir_parquet}"), "\n")
```
