5 Keys and integrity

A database is only as trustworthy as its joins. This chapter states how the integrated database’s tables are keyed, what each key promises, and — because a promise is worth little unstated — what the release actually measures before it ships. Every table below is generated from the promoted release’s own sidecars, so the page and the database cannot disagree.

5.1 The rules

The suffix says what kind of value a column holds (Table 5.1), and this is checked in review, because a character-valued identifier named *_id is a promise the data cannot keep:

Table 5.1: What each column-name suffix promises about the value the column holds.

suffix	value	examples
`*_key`	a string natural key — meaningful, stable, human-readable	`cruise_key`, `site_key`, `grid_key`, `sample_key`, `taxon_key`, `dataset_key`, `ship_key`
`*_id`	an integer key — a source counter or a surrogate the release mints	`cast_id`, `bottle_id`, `obs_id`, `root_id`
`*_uuid`	the provider’s own identifier for that record, as they mint it (RFC 4122)	`cruise_uuid`, `source_uuid`, `station_uuid`
`*_seq`	an auto-incrementing sequence integer	—

(hex_id is the one integer that is neither a counter nor a surrogate: an H3 cell index at resolution 10, so coarser hexagons are h3_cell_to_parent(hex_id, res) rather than a column per resolution.)

Natural keys carry the integration. Four of them are worth stating exactly, because every dataset joins to the others through them:

cruise_key = YYYY-MM-NODC (1998-02-33JD): the month SWFSC designates for the cruise and the ship’s NODC code — never the month a cast or tow happened to fall in. A cruise routinely straddles a calendar boundary (5508BD ran 7 Aug – 25 Sep 1955) and the neighbouring month is usually a real cruise of the same ship, so keying by event month silently moved casts onto the wrong cruise with no foreign key ever failing. Events are keyed by containment in the reference cruise’s observed date span first, then by the source’s own designation, then by event month as a last resort, and which rule fired is recorded (cruise_key_method). The cruise reference itself is completed at release: one row per key an event names that SWFSC’s export has no station row for (cruise_key_method = 'derived'), which is why cruise holds more rows than SWFSC’s list.
sample_key = {dataset_key}:{sample_type}:{id} (calcofi_bottle:cast:7644): globally unique across datasets and across event levels, so a site, a tow, a net, a cast and a bottle share one table without collision. The hierarchy is an adjacency list — parent_sample_key points at the containing event, root_sample_key at the top of the chain — so counting distinct events at any level is one GROUP BY.
site_key is the CalCOFI station as "line station" ("090.0 062.0"); grid_key is the cell of the CalCOFI grid a position falls in — one cell per official station — and, unlike site_key, names a polygon that can differ between releases (The grid key changed in October 2026); dataset_key = {provider}_{dataset} is the provenance stamp on every observation.
taxon_key is a lowercase authority prefix plus that authority’s accepted id — worms:<AphiaID>, or itis:<TSN> for birds, whose WoRMS taxonomy lags (see The database).

The release mints one sequential integer, root_id on sample_root: a dense integer for each root sampling event (a sample row with no parent), numbered deterministically so a rebuild reproduces it, and carried on obs_bio and obs_env so a browser can join observations to their event without the string key.

5.1.1 Why not UUID primary keys?

NOAA’s own CalCOFI database keys cruises, stations and nets by UUID, and its curator has argued — from experience — that a compound natural key is a liability: when a ship is swapped or a date corrected, the key and the fields it was built from can disagree, and nothing stops a partial correction. The argument is right for a database that is edited. This one is not: a release is read-only, its keys are minted from the corrected fields at the freeze (create_cruise_key() refuses a blank or malformed NODC code outright), and the gate below fails the release if a key disagrees with its parts. What a natural key buys in return is legibility — 2019-07-39C2 says which cruise it is; a UUID says nothing until joined — and stability across the the datasets whose sources mint no UUID at all.

The provider’s identifiers are released as typed columns beside the keys, never dropped: they are the join back to the provider’s own database.

cruise.cruise_uuid is NOAA/SWFSC’s CruiseId, populated for every SWFSC cruise — a public join key to NOAA’s CalCOFI database.
sample.source_uuid is the provider’s identifier for that event, exactly as shipped (SWFSC’s site_uuid on a site row, tow_uuid on a tow, net_uuid on a net), and NULL for the datasets whose sources mint none.
sample.station_uuid is the SWFSC station occupation any event belongs to, resolved at release, with station_uuid_method saying how (its own site, its parent’s, a match on cruise + station + occupation order, or the one ichthyoplankton occupation within 24 hours).

Wrangling-time provenance (_source_file, _source_row, …) is a different thing and is stripped at the freeze.

5.2 The contract, per table

Every released table declares a primary key, and every foreign key between released tables — every arrow in Figure 4.1, the core family’s diagram in the Database chapter — is declared in the release’s relationships.json (calcofi4db::core_relationships() for the core family and the tables the release builds; metadata/relationships_cross.csv for the edges that cross datasets). Table 5.2 reads that file for v2026.10.14.

Table 5.2: Keys declared for every table of v2026.10.14. A NULL in a foreign-key column is permitted (an env observation has no taxon; an ungridded sample has no grid cell); a non-NULL value must resolve.

table	tier	rows	primary key	foreign keys	referenced by
climatology	core	1,828,215	dataset_key, site_key, month, depth_bin, measurement_type	grid_key → grid; measurement_type → measurement_type; dataset_key → dataset	0
cruise	core	845	cruise_key	ship_key → ship	7
dataset	core	19	dataset_key		11
dataset_taxon	core	1,940	ds_taxon_key	taxon_key → taxon; dataset_key → dataset	0
fish	core	10,796	sample_key, species_id	sample_key → sample; taxon_key → taxon	0
grid	core	225	grid_key		9
grid_crosswalk	core	673	prev_grid_key, grid_key	grid_key → grid	0
lookup	core	26	lookup_id		0
measurement_type	core	231	measurement_type		8
obs_attribute	core	455,300	obs_attribute_id	sample_key → sample; taxon_key → taxon; measurement_type → measurement_type; dataset_key → dataset	0
obs_bio	core	1,586,795	obs_id	root_id → sample_root; sample_key → sample; dataset_key → dataset; grid_key → grid; cruise_key → cruise; measurement_type → measurement_type; taxon_key → taxon	0
obs_env	core	31,739,611	obs_id	root_id → sample_root; sample_key → sample; dataset_key → dataset; grid_key → grid; cruise_key → cruise; measurement_type → measurement_type	0
region	core	4	region_key		0
sample	core	1,467,016	sample_key	parent_sample_key → sample; root_sample_key → sample; grid_key → grid; cruise_key → cruise; dataset_key → dataset; station_uuid → sample	9
sample_measurement	core	658,009	sample_measurement_id	sample_key → sample; measurement_type → measurement_type; dataset_key → dataset	0
sample_spatial	core	1,994,263	root_sample_key, spatial_key	root_sample_key → sample; spatial_key → spatial; root_id → sample_root	0
ship	core	49	ship_key		1
spatial	core	3,390	spatial_key		2
spatial_attribute	core	37,943	spatial_key, fld	spatial_key → spatial	0
taxon	core	2,606	taxon_key	parent_taxon_key → taxon	6
taxon_group	core	438	taxon_group_key, taxon_key	taxon_key → taxon	0
obs	deprecated	33,326,406	obs_id	sample_key → sample; taxon_key → taxon; measurement_type → measurement_type; grid_key → grid; cruise_key → cruise; dataset_key → dataset	0
obs_ctd_full	supplemental	281,888,334	obs_id	sample_key → sample; dataset_key → dataset; measurement_type → measurement_type; cruise_key → cruise; grid_key → grid	0
obs_mets_full	supplemental	19,926,523	obs_id	dataset_key → dataset; measurement_type → measurement_type; cruise_key → cruise; grid_key → grid	0
sample_root	supplemental	419,050	root_id	root_sample_key → sample; dataset_key → dataset; cruise_key → cruise; grid_key → grid	3

5.3 Measured, this release

Declaring a key is cheap. Since 2026-09-08 the release measures every declaration on the assembled tables before it freezes — calcofi4db::check_release_relationships() — and writes the result beside catalog.json as integrity.json. A duplicate or NULL primary key, or any non-NULL foreign key with no match, stops the release. The schema browser reads the same file, so a ✓ on a key there means measured, not declared.

For v2026.10.14: 25 primary keys and 60 foreign keys measured, 0 skipped, every key ok.

Table 5.3: Primary keys, measured — rows, distinct key values, duplicates, and rows with a NULL anywhere in the key.

table	key	rows	distinct	status
climatology	dataset_key,site_key,month,depth_bin,measurement_type	1,828,215	1,828,215	ok
cruise	cruise_key	845	845	ok
dataset	dataset_key	19	19	ok
dataset_taxon	ds_taxon_key	1,940	1,940	ok
fish	sample_key,species_id	10,796	10,796	ok
grid	grid_key	225	225	ok
grid_crosswalk	prev_grid_key,grid_key	673	673	ok
lookup	lookup_id	26	26	ok
measurement_type	measurement_type	231	231	ok
obs	obs_id	33,326,406	33,326,406	ok
obs_attribute	obs_attribute_id	455,300	455,300	ok
obs_bio	obs_id	1,586,795	1,586,795	ok
obs_ctd_full	obs_id	281,888,334	281,888,334	ok
obs_env	obs_id	31,739,611	31,739,611	ok
obs_mets_full	obs_id	19,926,523	19,926,523	ok
region	region_key	4	4	ok
sample	sample_key	1,467,016	1,467,016	ok
sample_measurement	sample_measurement_id	658,009	658,009	ok
sample_root	root_id	419,050	419,050	ok
sample_spatial	root_sample_key,spatial_key	1,994,263	1,994,263	ok
ship	ship_key	49	49	ok
spatial	spatial_key	3,390	3,390	ok
spatial_attribute	spatial_key,fld	37,943	37,943	ok
taxon	taxon_key	2,606	2,606	ok
taxon_group	taxon_group_key,taxon_key	438	438	ok

Table 5.4: Foreign keys, measured — rows, rows whose key is NULL (permitted), and orphans (a non-NULL value with no match; any orphan fails the release).

edge	rows	NULL	status
climatology.dataset_key → dataset.dataset_key	1,828,215	0	ok
climatology.grid_key → grid.grid_key	1,828,215	0	ok
climatology.measurement_type → measurement_type.measurement_type	1,828,215	0	ok
cruise.ship_key → ship.ship_key	845	0	ok
dataset_taxon.dataset_key → dataset.dataset_key	1,940	0	ok
dataset_taxon.taxon_key → taxon.taxon_key	1,940	0	ok
fish.sample_key → sample.sample_key	10,796	0	ok
fish.taxon_key → taxon.taxon_key	10,796	0	ok
grid_crosswalk.grid_key → grid.grid_key	673	0	ok
obs.cruise_key → cruise.cruise_key	33,326,406	151,661	ok
obs.dataset_key → dataset.dataset_key	33,326,406	0	ok
obs.grid_key → grid.grid_key	33,326,406	308,251	ok
obs.measurement_type → measurement_type.measurement_type	33,326,406	0	ok
obs.sample_key → sample.sample_key	33,326,406	0	ok
obs.taxon_key → taxon.taxon_key	33,326,406	31,740,374	ok
obs_attribute.dataset_key → dataset.dataset_key	455,300	0	ok
obs_attribute.measurement_type → measurement_type.measurement_type	455,300	0	ok
obs_attribute.sample_key → sample.sample_key	455,300	0	ok
obs_attribute.taxon_key → taxon.taxon_key	455,300	781	ok
obs_bio.cruise_key → cruise.cruise_key	1,586,795	142,508	ok
obs_bio.dataset_key → dataset.dataset_key	1,586,795	0	ok
obs_bio.grid_key → grid.grid_key	1,586,795	200,389	ok
obs_bio.measurement_type → measurement_type.measurement_type	1,586,795	0	ok
obs_bio.root_id → sample_root.root_id	1,586,795	0	ok
obs_bio.sample_key → sample.sample_key	1,586,795	0	ok
obs_bio.taxon_key → taxon.taxon_key	1,586,795	763	ok
obs_ctd_full.cruise_key → cruise.cruise_key	281,888,334	0	ok
obs_ctd_full.dataset_key → dataset.dataset_key	281,888,334	0	ok
obs_ctd_full.grid_key → grid.grid_key	281,888,334	0	ok
obs_ctd_full.measurement_type → measurement_type.measurement_type	281,888,334	0	ok
obs_ctd_full.sample_key → sample.sample_key	281,888,334	0	ok
obs_env.cruise_key → cruise.cruise_key	31,739,611	9,153	ok
obs_env.dataset_key → dataset.dataset_key	31,739,611	0	ok
obs_env.grid_key → grid.grid_key	31,739,611	107,862	ok
obs_env.measurement_type → measurement_type.measurement_type	31,739,611	0	ok
obs_env.root_id → sample_root.root_id	31,739,611	0	ok
obs_env.sample_key → sample.sample_key	31,739,611	0	ok
obs_mets_full.cruise_key → cruise.cruise_key	19,926,523	0	ok
obs_mets_full.dataset_key → dataset.dataset_key	19,926,523	0	ok
obs_mets_full.grid_key → grid.grid_key	19,926,523	16,128	ok
obs_mets_full.measurement_type → measurement_type.measurement_type	19,926,523	0	ok
sample.cruise_key → cruise.cruise_key	1,467,016	14,721	ok
sample.dataset_key → dataset.dataset_key	1,467,016	0	ok
sample.grid_key → grid.grid_key	1,467,016	23,099	ok
sample.parent_sample_key → sample.sample_key	1,467,016	419,050	ok
sample.root_sample_key → sample.sample_key	1,467,016	0	ok
sample.station_uuid → sample.source_uuid	1,467,016	435,122	ok
sample_measurement.dataset_key → dataset.dataset_key	658,009	0	ok
sample_measurement.measurement_type → measurement_type.measurement_type	658,009	0	ok
sample_measurement.sample_key → sample.sample_key	658,009	0	ok
sample_root.cruise_key → cruise.cruise_key	419,050	14,131	ok
sample_root.dataset_key → dataset.dataset_key	419,050	0	ok
sample_root.grid_key → grid.grid_key	419,050	8,408	ok
sample_root.root_sample_key → sample.sample_key	419,050	0	ok
sample_spatial.root_id → sample_root.root_id	1,994,263	0	ok
sample_spatial.root_sample_key → sample.sample_key	1,994,263	0	ok
sample_spatial.spatial_key → spatial.spatial_key	1,994,263	0	ok
spatial_attribute.spatial_key → spatial.spatial_key	37,943	0	ok
taxon.parent_taxon_key → taxon.taxon_key	2,606	21	ok
taxon_group.taxon_key → taxon.taxon_key	438	0	ok

Three things Table 5.3 and Table 5.4 do not show, on purpose:

A NULL is not an orphan. Where a column is NULL the row has nothing to point at — an environmental observation has no taxon, a region-pooled sample has no grid cell, a root sample has no parent. The counts are shown so a reader can see how nullable an edge is; they are not failures.
One key column is declared nullable. climatology.depth_bin is NULL on the per-cast rows (a mixed-layer depth has no depth), so relationships.json lists it under nullable_key_columns and check_release_relationships() reads it as a value, not a missing key. A NULL there is never matched by =, so a join on a depth bin cannot reach a per-cast row by accident (The database).
One edge is knowingly undeclared. obs_mets_full.sample_key would point at sample, but the full-resolution meteorology mints one key per underway record and only the headline series’ events reach sample: 73,607 of its 2,168,850 distinct keys resolve. Declaring the edge would fail every release on some nineteen million orphans; leaving it silent would hide a real gap. It is recorded in the release notes and as CalCOFI/workflows#78 until the supplemental’s events either join sample or carry a documented per-record key.

5.4 What the release checks before it ships

Keys are one family of gate. The full list — what each checks, and whether it fails the release or only ratchets — is on the Releases page; the ones that concern keys are:

Table 5.5: The gates that stop a release outright, of which the first five are key gates.

gate	what it measures	on failure
`check_release_relationships()`	every declared primary key unique and non-NULL; every non-NULL foreign key resolving; written to `integrity.json`	stops the release
`check_core_pk_unique()`	the core family’s primary keys, before the release-built tables exist (the gate added after v2026.08.25 shipped 4,855 duplicate `sample_key`s)	stops the release
`check_cruise_key_integrity()`	`cruise_key` format, its month against the NODC code, the `sample`/`obs` → `cruise` edge, `cruise_uuid` hygiene, every event’s date within its cruise’s span (± 31 days, named exceptions only)	stops the release; three ratchets (span overlaps, derived cruises, NULL keys per dataset) may only ever go down
`check_grid_key_assignment()`	every `sample.grid_key` is the cell of `grid` its position falls in (a shared edge takes the key that sorts first) — the fault a foreign-key check cannot see, because a surviving key name resolves while naming other water	stops the release
`check_grid_crosswalk()`	`grid_crosswalk` has every cell of both grids, no repeated pair, no key of neither grid, and each previous cell is covered	stops the release
`validate_for_release()`	NULLs in any `_id` / `_key` / `*_uuid` column; latitude, longitude and counts in range; no empty table	NULLs stop the release; ranges warn
`check_obs_pair_parity()`	`obs_bio` + `obs_env` reproduce `obs` — row counts, distinct ids, a hash of every column, per realm and dataset	stops the release
`check_sample_hex7()`	`sample.hex7` is present exactly where the position is finite, is a resolution-7 cell, equals `sample_root.hex7` for every root, and equals the cell of every observation at the same position	stops the release
`test_release.qmd` consumer contract	every query shape the apps and packages depend on, run against the frozen release three ways (objects, view, pair), plus the presence of an all-ok `integrity.json`	`latest.txt` is not promoted
`test_release.qmd` climatology checks	a per-cast cell exists for a derived product; every monthly `climatology` cell also carries its seasonal baseline; a NULL `depth_bin` occurs only on a per-cast type; one window is stamped on every row; every `obs_env` row of CalCOFI 2607 is in quarter 3	`latest.txt` is not promoted
`test_release.qmd` package examples	the client packages’ own README examples — `calcofi4r`’s `README.Rmd` knit, `calcofi4py`’s `README.md` blocks executed — against the frozen release, pointed there by `CALCOFI_RELEASE_VERSION`	`latest.txt` is not promoted

The rows of Table 5.5 down to check_grid_crosswalk() are the key gates proper — the last two exist because a grid_key can resolve and still name the wrong cell (the grid key); the rows after it catch a key problem on its way to a consumer.

5.5 Walking the keys

The joins worth memorising, as SQL against any release (see Access the data for the connection):

-- an observation to its sampling event, and up the hierarchy
obs_bio.sample_key          -> sample.sample_key
sample.parent_sample_key    -> sample.sample_key      -- the containing event
sample.root_sample_key      -> sample.sample_key      -- the top of the chain

-- the dense integer twin the browser objects use instead of the string key
obs_bio.root_id             -> sample_root.root_id
sample_spatial.root_id      -> sample_root.root_id    -- which polygons a root sample falls in

-- provenance and place, denormalized onto the observation so a rollup needs no join
obs_env.dataset_key         -> dataset.dataset_key
obs_env.cruise_key          -> cruise.cruise_key
obs_env.grid_key            -> grid.grid_key

-- what was measured, and of what
obs_env.measurement_type    -> measurement_type.measurement_type
obs_bio.taxon_key           -> taxon.taxon_key
(dataset_key, ds_taxa_code) -> dataset_taxon          -- the per-dataset vocabulary

-- back to the provider
cruise.cruise_uuid                                    -- NOAA's CruiseId
sample.source_uuid                                    -- the provider's own event id
sample.station_uuid         -> sample.source_uuid     -- the SWFSC station occupation

A worked example: every net tow in a cruise with the number of larvae observations under it, walking sample’s adjacency list once.

WITH tows AS (
  SELECT sample_key, root_sample_key, datetime
  FROM sample
  WHERE dataset_key = 'swfsc_ichthyo' AND sample_type = 'tow' AND cruise_key = '2019-07-39C2'
)
SELECT t.sample_key, t.datetime, count(o.obs_id) AS n_larvae_obs
FROM tows t
LEFT JOIN sample n ON n.parent_sample_key = t.sample_key          -- the nets under each tow
LEFT JOIN obs_bio o ON o.sample_key = n.sample_key AND o.life_stage = 'larva'
GROUP BY 1, 2
ORDER BY 2;

5.6 The grid key changed in October 2026

Until v2026.10.01, grid was the cells of an idealized lattice, clipped by a coarse coastline and patched by hand: an inshore cell could hold two to four of the real stations, which is why the climatology moved to site_key (The database). The release that follows it ships a rebuilt grid: one cell per official station — the Voronoi tessellation of the station positions CalCOFI publishes at calcofi.org/sampling-info/station-positions, confined to the footprint of the previous cells it replaces — beside the previous cells that lay beyond the official pattern, kept exactly as they were, so a historical position outside the pattern keys as it always did. Both end at the OpenStreetMap coastline. The table’s columns are unchanged; grid.geom_ctr is now the station itself rather than a centroid. The build, its rules and the evidence are in the workflows notebook explore_grid_voronoi and calcofi4r::cc_grid_build().

A grid_key keeps its form (st{station}-ln{line}) but is no longer a name for the same water across releases. A station’s cell is named for its station, and a name that already existed can now be a different polygon.

For v2026.10.14 the crosswalk holds 218 previous keys and 225 current ones, and grid has 225 cells. 196 keys exist in both grids; 22 previous keys are no longer a key at all (retired) and 29 current keys are new. A key present in both is not necessarily the same polygon: Table 5.6 lists the ones that kept the least of their previous cell.

Table 5.6: The keys of v2026.10.14 that exist in both grids but keep the least of their previous cell. prev_frac is the share of the previous cell still inside the cell of the same name, grid_frac the share of today’s cell that was inside it; a kept cell reads close to one on both (its coastline moved, not its boundary).

grid_key	overlap_km2	prev_frac	grid_frac
st90-ln66.7	2,778	0.50	1.00
st80-ln66.7	2,769	0.50	1.00
st70-ln66.7	2,760	0.50	1.00
st30-ln90	1,225	0.52	1.00
st35-ln86.7	1,243	0.54	1.00
st35-ln90	1,594	0.64	0.98
st30-ln93.3	1,770	0.66	0.99
st60-ln66.7	2,745	0.67	1.00

5.6.1 What stays the same, and what to do about what does not

Inside one release nothing changes. sample.grid_key (and obs.grid_key) is the cell the event’s position falls in under that release’s grid. A position on an edge two cells share takes the key that sorts first, in the database (calcofi4db::assign_grid_key()) and in R (calcofi4r::cc_grid_key()) alike. calcofi4db::check_grid_key_assignment() recomputes the cell of every sample position before the release freezes and stops it on a key that is not that cell. It exists because the foreign-key check cannot see this fault: an ingest staged against the previous grid names cells that still exist, so every grid_key resolves while naming the wrong water.
Re-read the key, do not translate it. Anything you derived from samples — a count, a mean, a series per cell — is recomputed from the new release’s sample.grid_key and needs no crosswalk.
A key you stored from an earlier release — a per-cell statistic, a cache, a saved link such as an Explorer station= — is the case the crosswalk is for. Never match it by name.
A station is not a polygon. If you want the station, key on site_key ("090.0 060.0"), which is the same in every release; the climatology is keyed on it and no mean moved. grid.line and grid.station are no longer whole numbers for some cells, so a line is never matched by rounding it (a section of line 93.3 must not pick up the station on line 93.4).

grid_crosswalk has one row per overlapping pair of a previous and a current cell: prev_grid_key, grid_key, overlap_km2 (in an equal-area projection), prev_frac (the overlap as a share of the previous cell) and grid_frac (as a share of the current one). The primary key is the pair and grid_key references grid (Table 5.2). The shares are of the whole cell and are never rescaled, so a previous cell’s shares add to one less the part of it that is land under the finer coastline. calcofi4db::check_grid_crosswalk() fails the release when a cell of either grid is missing from the crosswalk, a pair repeats, or a previous cell is covered by less than its min_cover.

Table 5.7 is the previous cell that fans out to the most current ones in v2026.10.14, st35-ln86.7: its water is shared by 8 cells, and a value stored under that key belongs to all of them in the proportions prev_frac gives.

Table 5.7: Where one previous cell went in v2026.10.14, read from grid_crosswalk — the previous cell with the most successors (up to eight shown, largest share first).

grid_key	overlap_km2	prev_frac	grid_frac
previous key `st35-ln86.7`
st35-ln86.7	1,243	0.54	1.00
st35.8-ln85.4	428	0.19	0.46
st33-ln86.7	286	0.12	0.92
st32.5-ln86.8	191	0.08	0.84
st30.1-ln88.5	121	0.05	0.12
st37-ln90	11	0.00	0.00
st40-ln86.7	8	0.00	0.00
st35-ln90	1	0.00	0.00

Carry a total (a count, an effort) from the previous keys onto the current cells by the share of each previous cell’s area that each current cell received. Dividing by the previous cell’s summed share keeps the total whole where the finer coastline took a sliver of land out of it:

-- old_counts(prev_grid_key, n): a per-cell count you kept from an earlier release
WITH share AS (
  SELECT prev_grid_key, grid_key,
         prev_frac / sum(prev_frac) OVER (PARTITION BY prev_grid_key) AS w
  FROM grid_crosswalk)
SELECT s.grid_key, sum(o.n * s.w) AS n
FROM old_counts o
JOIN share s ON s.prev_grid_key = o.prev_grid_key
GROUP BY s.grid_key
ORDER BY s.grid_key;

Carry a mean (a temperature, a density) as an area-weighted mean of the previous cells’ means:

-- old_means(prev_grid_key, mean): a per-cell mean you kept from an earlier release
SELECT x.grid_key, sum(o.mean * x.overlap_km2) / sum(x.overlap_km2) AS mean
FROM old_means o
JOIN grid_crosswalk x ON x.prev_grid_key = o.prev_grid_key
GROUP BY x.grid_key
ORDER BY x.grid_key;

Both assume the quantity was spread evenly across each previous cell, which a station-sampled quantity was not: the mean in particular says nothing about where in the old cell the samples fell. Where you still have the samples, recompute from them with the new sample.grid_key; the crosswalk is for what you can no longer recompute. The previous cells themselves, with the keys every release through v2026.10.01 carries, are calcofi4r::cc_grid_v1.