Ingest Farallon Institute Bird & Mammal Census

Published

2026-08-14

1 Overview

Source: whales-seabirds-turtles/bird-mammal-census/ (CCE-LTER DataZoo 255, PI Bill Sydeman). Bird & mammal observations along CalCOFI/NMFS/CPR cruise transects, 1987-2021.

Provider: calcofi (tentative — curated via CCE-LTER; see questions)
Tables: bird_mammal_transect (effort) ⨝ bird_mammal_observation (counts) on gis_key, plus bird_mammal_species (ITIS taxonomy) and bird_mammal_behavior lookups.

Code

graph LR
  T[transects] --> TR[bird_mammal_transect<br/>effort + position]
  O[observations] --> OB[bird_mammal_observation<br/>counts]
  OB -.gis_key.-> TR
  OB -.species_code.-> SP[bird_mammal_species]
  OB -.behavior_code.-> BH[bird_mammal_behavior]

graph LR
  T[transects] --> TR[bird_mammal_transect<br/>effort + position]
  O[observations] --> OB[bird_mammal_observation<br/>counts]
  OB -.gis_key.-> TR
  OB -.species_code.-> SP[bird_mammal_species]
  OB -.behavior_code.-> BH[bird_mammal_behavior]

2 Setup

Code

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, units, quiet = T)
options(readr.show_col_types = F)
options(DT.options = list(scrollX = TRUE))
source(here("libs/ingest.R"))

cc           <- read_calcofi_meta(here("ingest_farallon_bird-mammal.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}/whales-seabirds-turtles/bird-mammal-census"))

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")

3 Read Source Data

Code

stopifnot("source dir not found" = dir_exists(dir_src))
sync_to_gcs(local_dir = dir_src, gcs_prefix = glue("archive/{provider}/{dataset}"),
            bucket = "calcofi-files-public", exclude = c(".DS_Store", "*.tmp", "*.gdoc"))

# A tibble: 0 × 4
# ℹ 4 variables: file <chr>, action <chr>, size <dbl>, reason <chr>

Code

rd <- function(f) read_csv(file.path(dir_src, glue("CalCOFI_bird-mammal-census_{f}.csv")),
                           col_types = cols(.default = "c")) |> clean_names()
d_tr <- rd("transects"); d_ob <- rd("observations")
d_sp <- rd("allspecieslist"); d_bh <- rd("behaviorcodes")
cat(glue("transects {nrow(d_tr)}, observations {nrow(d_ob)}, species {nrow(d_sp)}, behaviors {nrow(d_bh)}"), "\n")

transects 60715, observations 82418, species 200, behaviors 4

Code

cat("transect clean names:", paste(names(d_tr), collapse=", "), "\n")

transect clean names: gis_key, cruise, transect_number, bin_number, date, time_sec, latitude_start_o, longitude_start_o, latitude_mid_o, longitude_mid_o, latitude_stop_o, longitude_stop_o, length_m, width_m, area_m2, depth_m, julian_date, julian_day, svy, season

4 Build Lookups (species, behavior)

Code

b01 <- function(x) !is.na(x) & x %in% c("1","TRUE","true","Y","y")

d_species <- d_sp |>
  transmute(
    species_code = species, common_name, scientific_name = latin_name,
    itis_id = suppressWarnings(as.integer(itis)),
    is_bird = b01(bird), is_mammal = b01(mammal), is_fish = b01(fish),
    is_large_bird = b01(large_bird), is_unidentified = b01(unidentified),
    include_flag = b01(include), nmfs_code = nmfs, comment)
dbWriteTable(con, "bird_mammal_species", d_species, overwrite = TRUE)

d_behavior <- d_bh |> transmute(behavior_code = id, behavior, description)
dbWriteTable(con, "bird_mammal_behavior", d_behavior, overwrite = TRUE)
cat(glue("species {nrow(d_species)}, behaviors {nrow(d_behavior)}"), "\n")

species 200, behaviors 4

5 Build Transect (effort) Table

Code

d_transect <- d_tr |>
  transmute(
    gis_key,
    cruise_label    = cruise,
    transect_number = suppressWarnings(as.integer(transect_number)),
    bin_number      = suppressWarnings(as.integer(bin_number)),
    date            = suppressWarnings(as.Date(date)),
    time_sec        = suppressWarnings(as.numeric(time_sec)),
    latitude        = suppressWarnings(as.numeric(latitude_mid_o)),
    longitude       = suppressWarnings(as.numeric(longitude_mid_o)),
    latitude_start  = suppressWarnings(as.numeric(latitude_start_o)),
    longitude_start = suppressWarnings(as.numeric(longitude_start_o)),
    latitude_stop   = suppressWarnings(as.numeric(latitude_stop_o)),
    longitude_stop  = suppressWarnings(as.numeric(longitude_stop_o)),
    length_m        = suppressWarnings(as.numeric(length_m)),
    width_m         = suppressWarnings(as.numeric(width_m)),
    area_m2         = suppressWarnings(as.numeric(area_m2)),
    bottom_depth_m  = suppressWarnings(as.numeric(depth_m)),
    julian_date     = suppressWarnings(as.numeric(julian_date)),
    julian_day      = suppressWarnings(as.integer(julian_day)),
    svy, season,
    # datetime from date + time_sec (Q01: tz unconfirmed, treated as given)
    datetime_start_utc = as_datetime(date) + dseconds(coalesce(time_sec, 0)))

dbWriteTable(con, "bird_mammal_transect", d_transect, overwrite = TRUE)
cat(glue("bird_mammal_transect: {nrow(d_transect)} rows"), "\n")

bird_mammal_transect: 60715 rows

6 Build Observation (counts) Table

Code

d_observation <- d_ob |>
  transmute(gis_key, species_code = species, behavior_code = behavior,
            count = suppressWarnings(as.integer(count))) |>
  mutate(observation_id = row_number(), .before = 1)
dbWriteTable(con, "bird_mammal_observation", d_observation, overwrite = TRUE)
cat(glue("bird_mammal_observation: {nrow(d_observation)} rows"), "\n")

bird_mammal_observation: 82418 rows

7 Resolve cruise_key + Spatial

The source records a survey label (CAC1987_05), not a cruise, and carries no ship column — so cruise_key has to be recovered from where the observers actually were. Parsing year-month out of the label is not enough: it is ambiguous whenever several ships sailed in one month (1998-10 had four), and it is wrong outright for a survey straddling a month boundary (CAC2014_01 ran 2014-01-29 → 02-04 and belongs to 2014-02-3322). See Q02.

The observers ride a CalCOFI ship, so each transect sits on that day’s station track. match_cruise_by_track() matches every transect to the nearest station occupied the same day, then takes the majority vote per survey label — one label is one cruise — and applies the winner to all of that label’s transects.

The reference track is the swfsc_ichthyo sample shard (already this notebook’s declared dependency). That choice matters for referential integrity: its cruise_keys are exactly the cruise reference table’s, so every key emitted here resolves. Tracks assembled from other shards include cruises absent from cruise (e.g. 2021-07-33P4), which would ship a dangling FK.

Code

# geometry + grid from the transect midpoint
load_prior_tables(con, parquet_dir = cc_stage_path("parquet", "swfsc_ichthyo"),
                  tables = c("grid"), geom_tables = c("grid"), as_view = TRUE)

# A tibble: 1 × 3
  table  rows has_geom
  <chr> <dbl> <lgl>   
1 grid    218 TRUE

Code

add_point_geom(con, "bird_mammal_transect", lon_col = "longitude", lat_col = "latitude")
assign_grid_key(con, "bird_mammal_transect") |> datatable(caption = "Grid assignment")

Code

# cruise track: one row per (cruise, day, station position). matched on `date`,
# not datetime_start_utc, since the source timezone is unconfirmed (Q01) and the
# observer is aboard for the whole day regardless.
dbExecute(con, glue(
  "CREATE OR REPLACE VIEW cruise_track AS
   SELECT DISTINCT cruise_key, datetime, latitude, longitude
   FROM read_parquet('{cc_stage_path('parquet','swfsc_ichthyo','sample.parquet')}')
   WHERE cruise_key IS NOT NULL AND datetime IS NOT NULL"))

[1] 0

Code

cruise_match <- match_cruise_by_track(
  con, "bird_mammal_transect", "cruise_track",
  datetime_col     = "date",     lon_col = "longitude", lat_col = "latitude",
  ref_datetime_col = "datetime",
  group_col        = "cruise_label")

cat(glue("cruise_key resolved on {cruise_match$matched}/{cruise_match$total} ",
         "transects ({cruise_match$pct}%)"), "\n")

cruise_key resolved on 60010/60715 transects (98.8%)

Code

# surveys with no cruise sit at the top: they are the ones to eyeball, not the
# 100%-unanimous majority
cruise_match$groups |>
  datatable(caption = "Survey label -> cruise_key (vote share; NULL = unresolved)",
            rownames = FALSE, filter = "top") |>
  formatPercentage("share", 1)

Code

grp <- cruise_match$groups
n_unresolved <- sum(is.na(grp$cruise_key))

# a survey is one cruise on one ship, so two labels claiming the same cruise
# would mean the vote collapsed two distinct surveys together
dupe <- grp$cruise_key[!is.na(grp$cruise_key)]
dupe <- dupe[duplicated(dupe)]

stopifnot(
  "cruise_key must resolve on >95% of transects" =
    cruise_match$pct > 95,
  "every survey label must resolve to a distinct cruise_key" =
    length(dupe) == 0,
  "resolved cruise_key must exist in the cruise reference (no dangling FK)" =
    dbGetQuery(con, "
      SELECT COUNT(*) FROM bird_mammal_transect t
      WHERE t.cruise_key IS NOT NULL AND t.cruise_key NOT IN (
        SELECT cruise_key FROM cruise_track)")[[1]] == 0)

cat(glue(
  "{nrow(grp) - n_unresolved}/{nrow(grp)} survey labels resolved; ",
  "{n_unresolved} left NULL: ",
  "{paste(grp$cruise_label[is.na(grp$cruise_key)], collapse=', ')}"), "\n")

117/119 survey labels resolved; 2 left NULL: CAC2021_7, Fronts_0711

The unresolved labels are correct as NULL, not gaps to be filled:

Fronts_0711 (2011-06-21 → 07-16) is a CCE-LTER Fronts process cruise; no CalCOFI cruise sailed in that window (2011-04 ended 04-27, the next began 07-27), so there is no CalCOFI cruise to point at.
CAC2021_7 (2021-07-19 → 08-02) did ride a CalCOFI cruise — 2021-07-33P4, an exact date-range match — but that cruise is missing from the cruise reference table, which is built from the ichthyo source alone. Assigning it would ship a dangling FK. It resolves for free once cruise covers every cruise the other datasets reference (workflows#75 — 296 of 987 referenced keys are missing, so that gap is worth closing on its own merits rather than for this one survey).

8 Load Dataset Metadata + Schema

Code

d_dataset <- ingest_yaml_to_dataset_df(read_ingest_yaml(here()))
dbWriteTable(con, "dataset", d_dataset, overwrite = TRUE)

bmc_rels <- list(
  primary_keys = list(
    bird_mammal_transect = "gis_key", bird_mammal_observation = "observation_id",
    bird_mammal_species = "species_code", bird_mammal_behavior = "behavior_code"),
  foreign_keys = list(
    list(table="bird_mammal_observation", column="gis_key",       ref_table="bird_mammal_transect", ref_column="gis_key"),
    list(table="bird_mammal_observation", column="species_code",  ref_table="bird_mammal_species",  ref_column="species_code"),
    list(table="bird_mammal_observation", column="behavior_code", ref_table="bird_mammal_behavior", ref_column="behavior_code")))
# the SOURCE shape, documenting the wrangling below. The tables this ingest
# actually publishes are the consolidated core (see "Emit Core Tables"), so
# relationships.json is written there, from core_relationships().
cc_erd(con, tables = c("bird_mammal_transect","bird_mammal_observation","bird_mammal_species","bird_mammal_behavior","dataset"),
       rels = bmc_rels,
       colors = list(lightblue = c("bird_mammal_transect","bird_mammal_observation"),
                     lightyellow = c("bird_mammal_species","bird_mammal_behavior"), white = "dataset"))

9 Validate + Preview

Code

results <- validate_for_release(con, checks = "all", strict = FALSE)
cat("Validation:", ifelse(results$passed, "PASSED", "FAILED"), "\n")

Validation: FAILED

Code

# orphan check: observations whose gis_key is not in transects
n_orphan <- dbGetQuery(con, "SELECT COUNT(*) FROM bird_mammal_observation o
  LEFT JOIN bird_mammal_transect t USING(gis_key) WHERE t.gis_key IS NULL")[[1]]
cat(glue("observation gis_key orphans (no transect): {n_orphan}"), "\n")

observation gis_key orphans (no transect): 0

Code

cols <- dbGetQuery(con, "SELECT column_name FROM information_schema.columns
  WHERE table_name='bird_mammal_transect' AND data_type NOT LIKE 'GEOMETRY%'")$column_name
dbGetQuery(con, glue("SELECT {paste(cols, collapse=', ')} FROM bird_mammal_transect LIMIT 100")) |>
  datatable(caption = "bird_mammal_transect — first 100", rownames = FALSE, filter = "top")

10 Questions for Data Providers

Code

# 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 Farallon bird/mammal data providers (ranked)")

11 Emit Core Tables

Project this dataset into the shared consolidated core model (design_env-bio-consolidation.md). These core tables are this ingest’s output: release_database.qmd concatenates the per-dataset shards rather than re-deriving the core from per-dataset tables, so there is exactly one projection to keep correct.

The seabird/mammal source records one row per (transect, species, behavior). The obs headline is one row per (transect, species) with count summed across behaviors — otherwise the same birds are counted once per behavior code — and the behavior breakdown becomes obs_attribute rows carrying the behavior label.

Code

ds_key <- "farallon_bird-mammal"
# registries that resolve taxon_key at ingest time (seabirds -> itis:, marine
# mammals -> worms: via the override table). Filter the crosswalk to THIS
# dataset -- an unfiltered read leaks other datasets' taxa into this shard (the
# retired emit_core_tables() wrapper filtered internally).
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"), show_col_types = FALSE)

# This projection lives here, in the notebook that owns the dataset, not in a
# switch(dataset_key, ...) arm inside calcofi4db. The reusable SHAPE stays in the
# package (sample_arm_self), so this is a declaration.

# 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)

append_sample(con, sample_arm_self(
  ds_key, "bird_mammal_transect", "gis_key", "transect"))

# obs — the occurrence headline: one row per (transect, SPECIES CODE), count
# SUMmed across behaviors.
#
# Grouping is on the source species_code, NOT on taxon_key alone: only 156 of the
# 207 observed codes resolve to a taxon (the rest are excluded by include_flag or
# are coarse unidentified categories), so grouping by taxon_key alone would sum
# every unresolved species into a single NULL-taxon row per transect, silently
# merging distinct species. taxon_key is functionally determined by species_code
# (dataset_taxon is unique on ds_taxa_code within a dataset), so carrying both
# does not split the grain.
#
# The behavior breakdown is sub-occurrence detail and goes to obs_attribute — it
# must NOT ride on the headline's life_stage, or the same bird is counted once
# per behavior code.
append_obs(con, glue("
  SELECT 'bio', '{ds_key}', {ns_key(ds_key, 'transect', 'tr.gis_key')},
         tr.grid_key, tr.cruise_key, tr.latitude, tr.longitude,
         CAST(tr.datetime_start_utc AS TIMESTAMP), 0::DOUBLE, 0::DOUBLE,
         dt.taxon_key, NULL::VARCHAR, 'count', CAST(SUM(o.count) AS DOUBLE),
         NULL::VARCHAR, NULL::DOUBLE
  FROM bird_mammal_observation o JOIN bird_mammal_transect tr USING (gis_key)
  LEFT JOIN dataset_taxon dt ON dt.dataset_key = '{ds_key}'
                            AND dt.ds_taxa_code = CAST(o.species_code AS VARCHAR)
  GROUP BY tr.gis_key, tr.grid_key, tr.cruise_key, tr.latitude, tr.longitude,
           tr.datetime_start_utc, o.species_code, dt.taxon_key"))

# obs_attribute — the behavior breakdown, one row per source (transect, species,
# behavior): measurement_type = 'behavior', bin_label = the behavior description,
# bin_value NULL (behavior is categorical, not a numeric bin).
append_obs_attribute(con, glue("
  SELECT '{ds_key}', {ns_key(ds_key, 'transect', 'tr.gis_key')},
         dt.taxon_key, NULL::VARCHAR, 'behavior',
         NULL::DOUBLE, bb.description, CAST(o.count AS INTEGER), NULL::VARCHAR
  FROM bird_mammal_observation o JOIN bird_mammal_transect tr USING (gis_key)
  LEFT JOIN dataset_taxon dt ON dt.dataset_key = '{ds_key}'
                            AND dt.ds_taxa_code = CAST(o.species_code AS VARCHAR)
  LEFT JOIN bird_mammal_behavior bb ON bb.behavior_code = o.behavior_code"))

core <- 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]],
  taxon         = n_taxon,
  dataset_taxon = n_ds_taxon,
  taxon_group   = n_tx_group)
cat(glue(
  "core projection — sample={core$sample %||% 0} obs={core$obs %||% 0} ",
  "obs_attribute={core$obs_attribute %||% 0} ",
  "taxon={core$taxon %||% 0} dataset_taxon={core$dataset_taxon %||% 0} ",
  "taxon_group={core$taxon_group %||% 0}\n"))

core projection — sample=60715 obs=66344 obs_attribute=82418 taxon=259 dataset_taxon=156 taxon_group=127

Code

# the headline must collapse behaviors, and the attribution must add back up
n_obs <- core$obs
# one headline row per (transect, species_code) — NOT per behavior, and NOT
# collapsed by taxon_key (only 156 of 207 codes resolve; grouping on taxon_key
# alone would merge every unresolved species into one row per transect)
n_exp <- dbGetQuery(con, "
  SELECT COUNT(*) FROM (
    SELECT tr.gis_key, o.species_code
    FROM bird_mammal_observation o JOIN bird_mammal_transect tr USING (gis_key)
    GROUP BY 1, 2)")[[1]]
n_mismatch <- dbGetQuery(con, "
  WITH a AS (SELECT sample_key, taxon_key, SUM(count) s FROM obs_attribute
             WHERE measurement_type = 'behavior' GROUP BY 1, 2),
       o AS (SELECT sample_key, taxon_key, SUM(measurement_value) v FROM obs
             GROUP BY 1, 2)
  SELECT COUNT(*) FROM a JOIN o USING (sample_key, taxon_key) WHERE a.s <> o.v")[[1]]
# regression guard: grouping the headline on taxon_key alone (instead of
# species_code) merged every unresolved species on a transect into ONE NULL-taxon
# row. If that ever comes back, transects with several unresolved species collapse
# to a single row and this max drops to 1.
n_null_max <- dbGetQuery(con, "
  SELECT COALESCE(MAX(n), 0) FROM (
    SELECT sample_key, COUNT(*) n FROM obs WHERE taxon_key IS NULL GROUP BY 1)")[[1]]
n_null <- dbGetQuery(con, "SELECT COUNT(*) FROM obs WHERE taxon_key IS NULL")[[1]]
stopifnot(
  "obs must be one row per (transect, species), not per behavior" = n_obs == n_exp,
  "obs_attribute behavior counts must sum to the obs headline"    = n_mismatch == 0,
  "unresolved species must stay distinct, not merge into one NULL-taxon row per transect" =
    n_null_max > 1,
  "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,
  "every obs.taxon_key must RESOLVE in taxon (not merely be non-NULL)" =
    dbGetQuery(con, "SELECT COUNT(*) FROM obs o LEFT JOIN taxon t USING (taxon_key)
                     WHERE o.taxon_key IS NOT NULL AND t.taxon_key IS NULL")[[1]] == 0,
  "behavior is the only obs_attribute type here" =
    dbGetQuery(con, "SELECT COUNT(*) FROM obs_attribute
                     WHERE measurement_type <> 'behavior'")[[1]] == 0)
cat(glue("obs parity: {format(n_obs, big.mark = ',')} headline rows ",
         "({format(n_null, big.mark = ',')} without taxon_key, up to ",
         "{n_null_max} distinct unresolved species per transect); ",
         "behavior attribution reconciles"), "\n")

obs parity: 66,344 headline rows (1,316 without taxon_key, up to 3 distinct unresolved species per transect); behavior attribution reconciles

12 Write Outputs + Upload

Code

dir_create(dir_parquet)
tbls_out <- core_output_tables(con, extra = "dataset")
write_parquet_outputs(
  con = con, output_dir = dir_parquet, tables = tbls_out,
  sort_by = list(obs = c("grid_key", "measurement_type")),
  strip_provenance = FALSE)

# A tibble: 7 × 5
  table          rows file_size path                                 partitioned
  <chr>         <dbl>     <dbl> <chr>                                <lgl>      
1 sample        60715   1859473 /Users/bbest/_big/calcofi/parquet/f… FALSE      
2 obs           66344   2115529 /Users/bbest/_big/calcofi/parquet/f… FALSE      
3 obs_attribute 82418    212985 /Users/bbest/_big/calcofi/parquet/f… FALSE      
4 taxon           259     13048 /Users/bbest/_big/calcofi/parquet/f… FALSE      
5 dataset_taxon   156      5366 /Users/bbest/_big/calcofi/parquet/f… FALSE      
6 taxon_group     127      1408 /Users/bbest/_big/calcofi/parquet/f… FALSE      
7 dataset          16     11099 dataset.parquet                      FALSE

Code

build_relationships_json(
  rels = core_relationships(tbls_out), output_dir = dir_parquet,
  provider = provider, dataset = dataset)

[1] "/Users/bbest/Github/CalCOFI/workflows/data/parquet/farallon_bird-mammal/relationships.json"

Code

d_tbls_rd <- read_csv(here("metadata/farallon/bird-mammal/tbls_redefine.csv"))
d_flds_rd <- read_csv(here("metadata/farallon/bird-mammal/flds_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
  metadata_derived_csv = c(
    here("metadata/core_dictionary.csv"),
    here("metadata/farallon/bird-mammal/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)

metadata.json documentation gaps (7 tables, 90 columns) — these render blank in cc_describe_table() / cc_db_catalog():
tables with no description_md: 1    dataset
columns with no description_md: 17    dataset.provider, dataset.dataset, dataset.dataset_name, dataset.dataset_name_short, dataset.category, dataset.color, dataset.description, dataset.citation_main, dataset.citation_others, dataset.link_calcofi_org, dataset.link_data_source, dataset.link_others (+5 more)
measurement columns with no units: 21    dataset.dataset_name_short, dataset.category, dataset.color, dataset.citation_main, dataset.citation_others, dataset.link_calcofi_org, dataset.link_data_source, dataset.link_others, dataset.tables, dataset.coverage_temporal, dataset.coverage_spatial, dataset.license (+9 more)
  backfill via metadata/{provider}/{dataset}/flds_redefine.csv, then re-run

[1] "/Users/bbest/Github/CalCOFI/workflows/data/parquet/farallon_bird-mammal/metadata.json"

Code

sync_to_gcs(local_dir = dir_stage, sidecar_dir = dir_parquet, gcs_prefix = glue("ingest/{dir_label}"), bucket = "calcofi-db")

# A tibble: 4 × 4
  file    action    size reason        
  <chr>   <chr>    <dbl> <chr>         
1 <rsync> uploaded    NA parallel rsync
2 <rsync> uploaded    NA parallel rsync
3 <rsync> uploaded    NA parallel rsync
4 <rsync> uploaded    NA parallel rsync

Code

close_duckdb(con)
cat(glue("Parquet outputs written to: {dir_parquet}"), "\n")

Parquet outputs written to: /Users/bbest/Github/CalCOFI/workflows/data/parquet/farallon_bird-mammal