14  Database

The source of truth for the Marine Sensitivity Toolkit is DuckDB — a columnar, embedded analytic database. It is fast enough to answer live per-cell queries while remaining a single-file artifact that can be copied, versioned and served read-only to many processes at once.

The pipeline writes a working database (sdm.duckdb) on the machine that runs it; a release is the published, immutable form of that database’s tables. Since the marine-atlas release format, each version is published as partitioned Parquet on S3 under marine-atlas/{ver}/tables/, and serving reads it through a tiny view-only DuckDB (serve.duckdb, kilobytes) whose tables are views over those files — never a multi-gigabyte copy.

Everything below is read from the release this build documents (v7), so the tables, columns and row counts are those of that version rather than of whichever version happened to be current when the page was written.

Note

Why DuckDB and not PostgreSQL? Earlier versions of the stack used PostgreSQL + PostGIS as the primary store. As the analysis stabilized around a fixed 0.05° cell grid and a small set of zone layers, live geometry operations and concurrent writes stopped being needed — only fast analytic reads. DuckDB scans tens of millions of rows in well under a second with no separate server process. PostGIS is no longer part of the serving path (see Chapter 13).

14.1 Table and column naming conventions

  • Table names are singular and lower case.
  • Unique identifier columns are suffixed with:
    • *_id for unique integer keys (stable across rebuilds);
    • *_key for unique string keys (human-readable and stable across releases);
    • *_seq for auto-incrementing sequence integer keys (internal join keys, not stable across rebuilds).
  • Column names are singular and use snake_case.
  • Foreign keys use the singular form of the referenced table, followed by the primary-key suffix (_id, _key, or _seq).
Importantvalue is a reserved word in DuckDB

Cell values are stored in a column named val, not value. The serving views expose val AS value for backward compatibility with callers written against the older schema.

Table 14.1: Tables published by v7, with row counts read from the release itself.
Table Rows Purpose
cell 662,075 Grid cells of the study area, with environmental covariates and membership flags.
taxon 17,561 One row per taxon: identity, category, extinction risk and validity flags.
model 32,315 Registry of every distribution model, keyed by the stable model identifier.
metric 43 Metric registry: key and description for each scored quantity.
cell_metric 21,961,492 Per-cell score for each metric — the surface the maps draw.
zone 73 Zone registry keyed by source table, field and value, so every spatial unit shares one schema.
zone_cell 5,214,822 Zone membership: which cells belong to which zone.
zone_metric 1,021 Pre-aggregated per-zone score for each metric — what drives the choropleth.
zone_taxon 126,835 Per-zone species list with each species’ contribution to the zone score.
dataset 9 Source datasets, with citations, vintages and whether each fed the scores.
taxon_model 31,690 Which models fed which taxon — the taxon to model relation.
listing 718 US federal listing lookup (ESA status, MMPA and MBTA protection).
NoteNot published by v7

model_asset, native_asset. Sections that depend on these are omitted from this build rather than described from another release.

14.2 Schema

The columns of each published table, read from the release:

14.2.1 cell

Column Type
cell_id INTEGER
depth_mean DOUBLE
depth_min DOUBLE
depth_max DOUBLE
oxy_b_mean DOUBLE
oxy_mean DOUBLE
prim_prod_mean DOUBLE
ice_con_ann DOUBLE
salinity_b_mean DOUBLE
salinity_mean DOUBLE
sbt_an_mean DOUBLE
sst_an_mean DOUBLE
fao_area_m DOUBLE
area_km2 DOUBLE
width_km2 DOUBLE

14.2.2 taxon

Column Type
taxon_id DOUBLE
taxon_authority VARCHAR
n_ds INTEGER
ms_merge INTEGER
sp_cat VARCHAR
mdl_seq INTEGER
scientific_name VARCHAR
common_name VARCHAR
worms_id INTEGER
redlist_code VARCHAR
extrisk_code VARCHAR
er_score INTEGER
is_mmpa BOOLEAN
is_mbta BOOLEAN
is_bcc BOOLEAN
worms_is_marine BOOLEAN
worms_is_extinct BOOLEAN
esa_code VARCHAR
esa_source VARCHAR
is_ok BOOLEAN
worms_taxonomic_status VARCHAR
is_er_spatial BOOLEAN

14.2.3 model

Column Type
mdl_seq INTEGER
ds_key VARCHAR
taxa VARCHAR
time_period VARCHAR
region VARCHAR
mdl_type VARCHAR
description VARCHAR
date_created DATE

14.2.4 metric

Column Type
metric_seq INTEGER
metric_key VARCHAR
description VARCHAR
date_created DATE
metric_title VARCHAR
metric_abbrev VARCHAR

14.2.5 cell_metric

Column Type
cell_id INTEGER
metric_seq INTEGER
value DOUBLE

14.2.6 zone

Column Type
zone_seq INTEGER
tbl VARCHAR
fld VARCHAR
value VARCHAR
date_created DATE

14.2.7 zone_cell

Column Type
zone_seq INTEGER
cell_id INTEGER
pct_covered INTEGER

14.2.8 zone_metric

Column Type
zone_seq INTEGER
metric_seq INTEGER
value DOUBLE

14.2.9 zone_taxon

Column Type
zone_tbl VARCHAR
zone_fld VARCHAR
zone_value VARCHAR
mdl_seq INTEGER
sp_cat VARCHAR
sp_common VARCHAR
sp_scientific VARCHAR
taxon_id DOUBLE
taxon_authority VARCHAR
rl_code VARCHAR
er_score INTEGER
is_er_spatial BOOLEAN
is_mmpa BOOLEAN
is_mbta BOOLEAN
is_bcc BOOLEAN
esa_code VARCHAR
esa_source VARCHAR
area_km2 DOUBLE
avg_suit DOUBLE
suit_rl DOUBLE
suit_rl_area DOUBLE
cat_suit_rl_area DOUBLE
pct_cat DOUBLE

14.2.10 dataset

Column Type
ds_key VARCHAR
name_short VARCHAR
name_original VARCHAR
description VARCHAR
citation VARCHAR
source_broad VARCHAR
source_detail VARCHAR
regions VARCHAR
response_type VARCHAR
taxa_groups VARCHAR
year_pub INTEGER
date_obs_beg DATE
date_obs_end DATE
date_env_beg DATE
date_env_end DATE
link_info VARCHAR
link_download VARCHAR
link_metadata VARCHAR
links_other VARCHAR
spatial_res_deg DOUBLE
temporal_res VARCHAR
date_created DATE
name_display VARCHAR
value_info VARCHAR
is_mask BOOLEAN
sort_order INTEGER
global_mask_priority DOUBLE

14.2.11 taxon_model

Column Type
taxon_id DOUBLE
ds_key VARCHAR
mdl_seq INTEGER

14.2.12 listing

Column Type
spp_id DOUBLE
worms_id DOUBLE
botw_id DOUBLE
extrisk_code VARCHAR
er_score INTEGER
is_mmpa BOOLEAN
is_mbta BOOLEAN
is_bcc BOOLEAN
common_name VARCHAR

14.3 How the tables feed the serving tier

  • Score rasters — per-cell scores are published as Cloud-Optimized GeoTIFFs, one per metric × subregion, and the app reads the href and rescale range straight from the release manifest. Earlier releases rendered them by sending SQL to a tile factory per request; that path is retired (see Chapter 13).
  • Per-model distributions — model_cell is published partitioned by a dense integer model id, so a tile request reads exactly one partition as a point read rather than scanning the surface.
  • Zone choropleths — zone_metric joined to zone gives the per-zone score; the geometry comes from PMTiles published per zone-set vintage, and the manifest names the vintage each release used. Only the paint specification changes when the reader switches metric.
  • Per-cell and per-area species lists — the same rows partitioned by a spatial tile instead (cell_model), because a per-cell question against a model-partitioned surface would scan everything.

See Chapter 15 for the notebooks that build each of these tables.