2 Access the data

The integrated database is published as parquet files on a public bucket, and every way in — R, Python, a SQL shell, a browser tab — reads those same bytes through the same catalog. There is no server to be slow, no credentials to request and nothing to download whole: DuckDB reads only the columns and row groups a query touches, over HTTPS. This chapter is the one guide to both paths CalCOFI.io offers, the public release and the team’s working store:

Table 2.1: The two ways into CalCOFI data. Everything else on this page is the left-hand column.

	the public release	the working store
what	every dataset, integrated, frozen per version, cited by DOI	the CTD team’s multi-user PostgreSQL: originals verbatim, a flag ledger beside them — built and running, not yet the team’s practice (CTD QA/QC)
who	anyone	the CTD team and the data team
how	DuckDB over HTTPS: this page	an SSH tunnel: Server access
when it changes	at each release, never in place	continuously, once adopted; accepted flags would reach the next release through a nightly snapshot, never a live read

Table 2.1 is the whole choice: unless you are on the CTD team, the public release is the only path you need.

2.1 Where the data lives

A release is a versioned folder of sidecars — the record of what it is — and its tables are parquet objects the release’s catalog.json points at:

gs://calcofi-db/ducklake/
├── releases/
│   ├── latest.txt              # the promoted version
│   ├── versions.json           # every version: date, tables, rows, DOI; consolidated / retired
│   ├── RELEASES.md             # what changed between versions and why, newest first
│   └── {version}/
│       ├── catalog.json        # every table, its rows and its objects[] — where the bytes live
│       ├── metadata.json       # table and column descriptions, units, datasets, measurement types
│       ├── relationships.json  # primary and foreign keys        ┐ Keys and integrity
│       ├── integrity.json      # the keys, measured               ┘
│       ├── datasets.json       # one record per dataset — what every portal reads
│       ├── eml/, stac/, coverage.json, …
│       └── RELEASE_NOTES.md
└── tables/                     # the objects, content-addressed and shared between versions
    ├── {table}/{content_hash}/{table}.parquet
    └── {table}/{col}={value}/{content_hash}/data_0.parquet

The public HTTPS root is https://storage.googleapis.com/calcofi-db/; every path in a catalog is relative to it (https://storage.calcofi.io/calcofi-db/ serves the same bucket and redirects a legacy per-release URL to its object where one exists). The promoted version is releases/latest.txt; what a version is, which are kept and how to pin one are in Releases.

Resolve a table through the catalog; never build a path by hand. Each table’s objects[] lists one parquet object for a single-file table and one per partition for a partitioned one, each with its bucket-relative path, bytes, sha256 and since. The packages’ resolvers — calcofi4r::cc_release_sources(), calcofi4py.release_sources() — turn a catalog entry into URLs for any version, and the browser example below does it in six lines of JavaScript. A typed releases/{version}/parquet/… path works only while that version’s copy is kept, and the objects it points at may be shared, moved or thinned; the catalog is the contract.

2.2 What you are reading

Two tables hold the observations, and one view unites them:

obs_bio — every biological occurrence headline: a taxon (taxon_key), its life stage, the measurement (measurement_type, value), the gear and effort of its own sampling event (tow_type, std_haul_factor, prop_sorted, volume_sampled_m3) and the two standardized densities (density_per_10m2, density_per_1000m3). One object; one taxon question is one file.
obs_env — every environmental headline: one measured scalar per row, one object per measurement_type, so one variable is one small fetch — what a browser needs.
obs — the two under one name, realm = bio or env and value as measurement_value, as a view the catalog carries (views.obs). cc_get_db() in R and Python and the query site create it, so FROM obs keeps working; its own objects are deprecated and drop in the next release.

Every observation points at its sampling event in sample (sample_key; parent_sample_key walks net → tow → site and bottle → cast) and carries its provenance and place — dataset_key, cruise_key, grid_key, position, time, hex_id — so a rollup needs no join. Event-level effort such as std_haul_factor is in sample_measurement; sub-occurrence detail (length bins, stages) in obs_attribute; the references are cruise, ship, grid, taxon, dataset, measurement_type, spatial. The database describes each; Keys and integrity says how they join.

Table 2.2: The tables of v2026.09.11 as the catalog lays them out. A partitioned table reads as an explicit list of objects (with hive_partitioning = true to recover the partition column); a twin is one whole-table object a browser reads instead of the list.

table	tier	rows	objects	partitioned by	twin
climatology	core	736,916	76	measurement_type
cruise	core	842	1
dataset	core	16	1
dataset_taxon	core	1,917	1
grid	core	218	1
lookup	core	26	1
measurement_type	core	200	1
obs_attribute	core	458,184	1
obs_bio	core	1,258,665	1
obs_env	core	29,838,093	94	measurement_type
region	core	4	1
sample	core	1,469,151	1
sample_measurement	core	589,603	1
sample_spatial	core	929,632	1
ship	core	49	1
spatial	core	13,206	1
spatial_attribute	core	148,461	1
taxon	core	2,614	1
taxon_group	core	441	1
obs	deprecated	31,096,758	16	dataset_key	yes
obs_ctd_full	supplemental	275,231,999	134	cruise_key
obs_mets_full	supplemental	19,926,523	49	cruise_key
sample_root	supplemental	421,450	1

Table 2.2 is the whole inventory, tier by tier. A supplemental table (obs_ctd_full, the full-resolution CTD scans; obs_mets_full; the sample_root join twin) is hosted and catalogued but left out of cc_get_db()’s default set — obs_ctd_full alone is ~270 M rows. Ask for it (supplemental = TRUE) and filter early.

2.3 From R

calcofi4r registers every release table as a view over the remote parquet — resolving each through the catalog, handling httpfs, the partitioned tables, the obs view and a content-addressed local cache, so a table unchanged between releases is fetched once:

# remotes::install_github("calcofi/calcofi4r")
library(calcofi4r)
library(dplyr)

con <- cc_get_db()                       # the promoted release, every table as a view
con <- cc_get_db("v2026.08.25")          # pinned; a retired version errors, naming the replacement
con <- cc_get_db(supplemental = TRUE)    # + obs_ctd_full, obs_mets_full, sample_root
DBI::dbListTables(con)

tbl(con, "taxon") |> filter(scientific_name == "Sardinops sagax")          # lazy dbplyr
cc_query("SELECT dataset_key, count(*) AS n FROM obs_env GROUP BY 1 ORDER BY 2 DESC")

cc_list_versions()                       # versions.json
cc_release_notes()                       # this release's notes
cc_describe_table("sample")              # columns, units, descriptions from metadata.json

If that install stops at s2

calcofi4r needs sf, sf needs s2, and s2 is C++. CRAN has no macOS arm64 binaries for R 4.6 yet, so on Apple Silicon it compiles, and the compile needs cmake:

brew install cmake

brew install abseil will not do instead, though s2’s own failure message recommends it: its configure defaults S2_FORCE_BUNDLED_ABSEIL=true, always builds its vendored Abseil with cmake, and never asks pkg-config about a system copy.

To avoid the compile, take prebuilt binaries from Posit Package Manager — in ~/.Rprofile:

options(repos = c(P3M = "https://packagemanager.posit.co/cran/latest"))

To hand a table’s URLs to DuckDB (or anything else) yourself, resolve them through the catalog:

cat_ <- cc_catalog("latest")                     # or a pinned "vYYYY.MM.DD"
src  <- cc_release_sources(cat_, "obs_env")      # list(urls, hive, canonical, hashes, local_paths, single_file)
src$urls                                         # one https URL per partition
cc_read_parquet_sql(src)                         # read_parquet([...], hive_partitioning = true) — paste into any SQL

2.4 From Python

calcofi4py mirrors the R package, verb for verb:

# pip install "calcofi4py @ git+https://github.com/CalCOFI/calcofi4py"
import calcofi4py as cc

con = cc.cc_get_db()                              # the promoted release; cc_get_db("v2026.08.25") to pin
df  = con.sql("SELECT * FROM taxon WHERE common_name ILIKE '%sardine%'").df()
cc.cc_query("SELECT count(*) FROM obs_bio").fetchone()

catalog = cc.cc_catalog("latest")
src     = cc.release_sources(catalog, "obs_env")  # {"urls", "hive", "canonical", "hashes", "local_paths", "single_file"}
cc.read_parquet_sql(src)                          # the read_parquet(...) fragment for any DuckDB

2.5 From SQL, anywhere

Any DuckDB — the command-line shell, a notebook, another language’s binding — reads the release after INSTALL httpfs; LOAD httpfs; (and spatial for ST_* functions). Take the table’s objects from the catalog and hand them to read_parquet(); a partitioned table is an explicit list with hive_partitioning = true, and that runs in every DuckDB, the browser included:

INSTALL httpfs; LOAD httpfs;

-- one object: a single-file table (the path is catalog.json ▸ tables[name = 'taxon'] ▸ objects[0].path)
SELECT taxon_key, scientific_name, common_name, worms_id, rank
FROM read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/tables/taxon/{content_hash}/taxon.parquet')
WHERE scientific_name = 'Sardinops sagax';

-- many objects: a partitioned table, one URL per partition, in catalog order
SELECT measurement_type, count(*) AS n
FROM read_parquet([
    'https://storage.googleapis.com/calcofi-db/ducklake/tables/obs_env/measurement_type=temperature/{hash}/data_0.parquet',
    'https://storage.googleapis.com/calcofi-db/ducklake/tables/obs_env/measurement_type=salinity/{hash}/data_0.parquet'
  ], hive_partitioning = true)
GROUP BY 1;

Because the partition column is in the path, a filter on it lets DuckDB prune whole objects: a one-variable query over obs_env opens one file. Releases before v2026.09 have no objects[]; their partitioned tables are directories that plain HTTPS cannot glob, so they are read through an anonymous s3-style glob (SET s3_endpoint = 'storage.googleapis.com'; SET s3_url_style = 'path'; then read_parquet('s3://calcofi-db/…/obs/**/*.parquet', hive_partitioning = true)) — the resolvers return that form for those versions automatically.

2.6 From your browser

DuckDB compiles to WebAssembly, so the same engine runs in a browser tab with nothing installed. Three ready-made ways in:

the Explorer — six lenses over the whole database, every view a URL, every download with its SQL (Explore);
calcofi.io/db-query — a library of queries to start from, including the three biology-to-environment matches below, and a free SQL shell;
shell.duckdb.org — DuckDB’s own shell, for a one-off query.

To embed DuckDB in your own page, the boilerplate is short: load the bundle, instantiate, resolve a table through the catalog:

<script type="module">
  import * as duckdb from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.0/+esm";
  const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
  const db     = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), await duckdb.createWorker(bundle.mainWorker));
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  const conn = await db.connect();
  await conn.query("INSTALL httpfs; LOAD httpfs;");

  const root    = "https://storage.googleapis.com/calcofi-db";
  const version = (await (await fetch(`${root}/ducklake/releases/latest.txt`)).text()).trim();
  const catalog = await (await fetch(`${root}/ducklake/releases/${version}/catalog.json`)).json();

  // a table's objects → a read_parquet() fragment. A partitioned table is a list (hive_partitioning);
  // if it also publishes a whole-table twin (the object WITHOUT partition_by), a browser reads the
  // twin instead of the list — never both, which would count every row twice.
  const readParquet = (name) => {
    const t    = catalog.tables.find(t => t.name === name);
    const twin = t.partitioned ? t.objects.find(o => !o.partition_by) : null;
    if (twin) return `read_parquet('${root}/${twin.path}')`;
    const urls = t.objects.map(o => `'${root}/${o.path}'`).join(", ");
    return `read_parquet([${urls}]${t.partitioned ? ", hive_partitioning = true" : ""})`;
  };

  const result = await conn.query(`
    SELECT scientific_name, common_name, worms_id FROM ${readParquet("taxon")}
    WHERE common_name ILIKE '%sardine%'`);
  console.log(result.toArray());
</script>

2.7 Apply the quality flags

measurement_qual carries each dataset’s own flag vocabulary, uninterpreted — bottle (6 = OK but from CTD, 8 = suspect, 9 = missing), CTD cast files (1/2 = use the primary/secondary sensor, 8 = questionable, 9 = bad/missing), DIC WOCE (2 = good, 3 = questionable, 4 = bad, 9 = missing); the registry is metadata/measurement_qual.csv. A flagged value is still a row. obs_bio and obs_env carry the verdict as a column, qual_ok, and the packages expose the predicate it was computed with, so a filter reads the same in every language:

WHERE qual_ok                          -- obs_bio, obs_env: the materialized verdict

calcofi4r::cc_qual_ok_sql("o")         # the predicate, for obs / obs_ctd_full / sample_measurement

calcofi4py.qual_ok_sql("o")

The predicate is NULL-safe — an unflagged row is kept — and every CalCOFI app applies it. A 2.18 ml/L oxygen spike at 1,144 m that had been flagged suspect since 1955 reached a plot in 2026 because no consumer did; that is why the column exists.

2.8 Matching biology to environment

Relating a net tow’s catch to the water it was towed through is the question the database exists to answer, and calcofi4r answers it in one call: the three wrappers of Table 2.3 match biological observations to environmental ones in time and space, on the fly, against the release.

Table 2.3: The three matching wrappers and the biological side each takes.

function	biological side
`cc_match_ichthyo_by_name()`	ichthyoplankton, filtered by scientific name
`cc_match_ichthyo_by_taxon()`	ichthyoplankton, a WoRMS taxon and all its descendants (a recursive walk of `taxon.parent_taxon_key`)
`cc_match_zooplankton_biomass()`	net-tow displacement-volume biomass (`totalplankton` / `smallplankton`)

d <- cc_match_ichthyo_by_name(
  "Sardinops sagax", env_var = "temperature", life_stage = "larva",
  date_min = "2018-01-01", date_max = "2018-03-31",
  relax_matching = TRUE)                 # 5 km / 72 h instead of the default 2 km / 6 h

cat(attr(d, "sql"))                      # the exact, portable SQL that produced it
str(attr(d, "query_meta"))               # release version, parameters, source URLs

Each returns one row per biological observation with the matched environmental value, and attaches the exact SQL that produced it: paste attr(d, "sql") into the DuckDB shell, Python or the query site and the same rows come back. return_sql = TRUE gives the SQL without running it. The wrappers are thin shells over cc_match_bio_env(bio, env, …), which takes a biological and an environmental SELECT and performs the match — call it when the wrappers’ filters are not yours. Its knobs: max_dist_km and max_time_hr (2 km / 6 h; relax_matching = TRUE widens to 5 km / 72 h), join_method ("nearest_time", "nearest_dist" or "average" over the window), life_stage, date_min/date_max, depth_m_min/depth_m_max, version.

Note

The example asks for 2018 because the environmental and biological series end at different times in the release: the bottle series covers 1949-02 to 2021-05, the ichthyoplankton series 1951-01 to 2023-01 (measured, not typed — The database). Pick a period both cover.

2.9 Reproducible

Every query is plain SQL against immutable, versioned, public parquet, so a CalCOFI result is reproducible by anyone who has the version and the SQL. Pin a consolidated version (Releases) — its bytes are kept indefinitely — and keep the SQL: what the matching helpers attach as attr(d, "sql"), what the Explorer copies beside every download, what the query site shows in its SQL panel and what a hand-written query is are one and the same thing, in R, Python, the shell and the browser.

The retired API, endpoint by endpoint

The Plumber API at api.calcofi.io served pre-built match tables from a PostgreSQL database that no longer exists. Every endpoint has a replacement that reads the release directly:

Table 2.4: What each retired api.calcofi.io endpoint became.

retired endpoint	now
`/variables`	`cc_list_measurement_types()`
`/timeseries`, `/cruise_lines`, `/cruise_line_profile`, `/raster`	SQL over `obs_env` and `sample`, or the Explorer’s sections and contours
`/cruises`	`cc_read_cruise()`
`zooplankton_biomass`	`cc_match_zooplankton_biomass()`
`itis_ichthyodata`	`cc_match_ichthyo_by_taxon()` — WoRMS, recursive
`ichthyodata`	`cc_match_ichthyo_by_name()`
`relax_matching`, `cruiseymd_min/max`, `stage`	`relax_matching`, `date_min/max`, `life_stage`

Appendix A keeps the old endpoint reference in full; Table 2.4 is the short form.