Architecture + ERDs for the v8 Marine Atlas DuckDB databases (serve · sdm · merge · spp)

Published

2026-07-16

The v8 Marine Atlas pipeline flows through four DuckDB databases. The one consumers query is the tiny serve.duckdb — a set of views over partitioned Parquet on S3 (KB on disk, never a multi-GB monolith). It is fed by the multi-GB build database sdm.duckdb, which in turn is assembled from the merge intermediate merge.duckdb and the taxonomy authority spp.duckdb.

0.1 Architecture

Code 
flowchart TB
  src["ingest_*.qmd — one per source<br/>AquaMaps · BirdLife · IUCN/FWS/NMFS ranges · SWOT turtles"]
  spp[("<b>spp.duckdb</b><br/>WoRMS · GBIF · ITIS<br/>IUCN · BirdLife<br/><i>taxonomy authority</i>")]
  merge[("<b>merge.duckdb</b><br/>taxon · taxon_model · taxon_flags<br/>mc_parts · model_cell<br/><i>two-surface merge intermediate</i>")]
  sdm[("<b>sdm.duckdb</b> — build (multi-GB)<br/>cell · model · model_cell · taxon<br/>metric · cell_metric · zone* · native_asset")]
  s3[("<b>S3</b> marine-atlas/v8/<br/>tables/ · serve/ · dist_merged/ · registry/")]
  serve[("<b>serve.duckdb</b> — KB<br/><i>VIEWs over S3 Parquet</i>")]
  titiler["titiler-v8<br/>tile server"]
  stac["STAC catalog"]
  apps["species · scores<br/>Shiny apps"]

  src -->|"dist/dataset={ds_key}/*.parquet<br/>(mdl_key, cell_id, val)"| merge
  spp -.->|"sp_cat · is_marine · WoRMS ids"| merge
  merge -->|"merge_taxon → score_zones →<br/>score_cell_metrics → score_zone_metrics"| sdm
  sdm -->|"release_marine-atlas"| s3
  s3 --> serve
  serve --> titiler
  s3 --> stac
  titiler --> apps
  stac --> apps

  classDef db fill:#1e3a5f,stroke:#3bc9db,color:#e6edf3;
  class spp,merge,sdm,serve db;

Figure 1: v8 Marine Atlas data flow across the four DuckDB databases (click to zoom)

Key conventions. The public identifier is the stable mdl_key ({ds_key}|{sp_id}, pipe separator; merged taxa are ms_merge|{authority}:{id}). The dense integer mdl_id is a serving optimization only (partition-prunes titiler reads) and renumbers per release, so it never appears in URLs. Values live in a val column (value is a DuckDB reserved word); serving views expose a value alias for titiler. Scoring runs over in_usa cells; is_valid_global uses the whole-range cell tally so species outside the US still list in the app.

1 serve.duckdb — public serving surface

The database consumers query: a few KB of views over the partitioned Parquet published to s3://oceanmetrics.io-public/marine-atlas/v8/. titiler-v8 resolves mdl_key → mdl_id and reads a single serve/model_cell/mdl_id=*/…parquet partition per tile (anonymous HTTP range read).

serve.duckdb matches the documented schema.

1.1 Schema

Code 
erDiagram
  dataset {
    VARCHAR ds_key        PK "dataset key"
    VARCHAR name_short        "short display name"
    VARCHAR response_type     "suitability, range, …"
    VARCHAR native_format     "native SDM format"
  }
  model {
    VARCHAR mdl_key       PK "stable {ds_key}|{sp_id}"
    INTEGER mdl_id           "dense serving id"
    VARCHAR ds_key        FK "-> dataset"
    VARCHAR sci_name
    DOUBLE  er_score
    VARCHAR sp_cat
  }
  model_cell {
    VARCHAR mdl_key       FK "-> model"
    INTEGER mdl_id           "serving partition key"
    INTEGER cell_id       FK "-> cell"
    DOUBLE  val              "suitability 0-100"
  }
  cell {
    INTEGER cell_id       PK "0.05° grid id"
    DOUBLE  lon
    DOUBLE  lat
    DOUBLE  area_km2
    BOOLEAN in_usa
    BOOLEAN in_pra
  }
  taxon {
    VARCHAR ms_merge_key  PK "merged model id"
    VARCHAR taxon_authority
    VARCHAR taxon_id
    VARCHAR scientific_name
    DOUBLE  er_score
    VARCHAR sp_cat
    BOOLEAN is_valid_global "whole-range validity"
    BOOLEAN is_valid_usa    "n_usa>0"
  }
  native_asset {
    VARCHAR ms_merge_key  FK "-> taxon"
    VARCHAR mdl_key       FK "-> model"
    VARCHAR ds_key        FK "-> dataset"
    VARCHAR asset_type       "COG | PMTiles"
    VARCHAR representation   "native | model"
    VARCHAR asset_url        "public S3/titiler URL"
  }
  metric {
    INTEGER metric_seq    PK
    VARCHAR metric_key
  }
  cell_metric {
    INTEGER cell_id       FK "-> cell"
    INTEGER metric_seq    FK "-> metric"
    DOUBLE  val
  }
  zone {
    INTEGER zone_seq      PK
    VARCHAR tbl              "program areas / ecoregions"
    VARCHAR val              "zone key"
  }
  zone_cell {
    INTEGER zone_seq      FK "-> zone"
    INTEGER cell_id       FK "-> cell"
    INTEGER pct_covered
  }
  zone_metric {
    INTEGER zone_seq      FK "-> zone"
    INTEGER metric_seq    FK "-> metric"
    DOUBLE  val
  }

  dataset ||--|{ model        : ds_key
  model   ||--|{ model_cell   : mdl_key
  model   ||--o{ native_asset : mdl_key
  taxon   ||--o{ native_asset : ms_merge_key
  cell    ||--|{ model_cell   : cell_id
  cell    ||--|{ cell_metric  : cell_id
  cell    ||--|{ zone_cell    : cell_id
  metric  ||--|{ cell_metric  : metric_seq
  metric  ||--|{ zone_metric  : metric_seq
  zone    ||--|{ zone_cell    : zone_seq
  zone    ||--|{ zone_metric  : zone_seq

Figure 2: serve.duckdb entity relationship diagram (click to zoom)

1.2 Tables

Every table above is a view (SELECT * FROM read_parquet('s3://…/tables/{table}.parquet')), so its columns mirror the sdm.duckdb core tables documented next (with live row counts + value ranges) — plus, on the serving views, the titiler value alias of val and the mdl_id join on model_cell. Row counts here would require an anonymous S3 scan and are omitted.

2 sdm.duckdb — build database

The multi-GB database release_marine-atlas exports to S3. Same core tables as serve (serve is a view over its Parquet); model_cell here is (mdl_key, cell_id, val) — the mdl_id join and value alias are added at serve time. Also holds build-only scratch/experimental tables (hex*, wtmp*, v7_cat, v7_ok) not shown here.

sdm.duckdb matches the documented schema.

2.1 Tables

3 merge.duckdb — two-surface merge intermediate

merge_models writes both surfaces here: a global viz surface (am ∪ range) and the US-scoped v7-faithful scoring surface (model_cell). merge_taxon derives per-taxon validity (taxon, from taxon_cell + taxon_cell_global), governing extinction risk (taxon_er), and the has_am/has_range flags (taxon_flags) that drive the no-EEZ AquaMaps constraint. score_zones copies taxon from here into sdm.duckdb.

merge.duckdb matches the documented schema.

3.1 Schema

Code 
erDiagram
  taxon_model {
    VARCHAR mdl_key       PK "raw input model"
    VARCHAR ms_merge_key  FK "-> taxon (merged)"
    VARCHAR taxon_authority
    VARCHAR taxon_id
  }
  taxon {
    VARCHAR ms_merge_key  PK "merged model id"
    VARCHAR scientific_name
    INTEGER er_score
    VARCHAR sp_cat
    BOOLEAN is_valid_global
    BIGINT  n_global
  }
  taxon_flags {
    VARCHAR ms_merge_key  FK "-> taxon"
    BOOLEAN has_am           "has AquaMaps model"
    BOOLEAN has_range        "has range polygon"
  }
  taxon_cell {
    VARCHAR mdl_key       FK "-> taxon (ms_merge_key)"
    BIGINT  n_ocean
    BIGINT  n_usa
    DOUBLE  range_km2
  }
  taxon_cell_global {
    VARCHAR mdl_key       FK "-> taxon (ms_merge_key)"
    BIGINT  n_global         "whole-range cells"
  }
  taxon_er {
    VARCHAR ms_merge_key  FK "-> taxon"
    VARCHAR extrisk_code
    INTEGER er_score
    BOOLEAN is_mmpa
  }
  mc_parts {
    BIGINT  mkey_id       FK "-> mkey_map"
    VARCHAR ms_merge_key
    VARCHAR ds_key
    INTEGER cell_id       FK "-> us_cells"
    DOUBLE  val
  }
  model_cell {
    VARCHAR mdl_key       PK "merged US scoring surface"
    INTEGER cell_id       FK "-> us_cells"
    DOUBLE  value
  }
  mkey_map {
    VARCHAR ms_merge_key  PK
    INTEGER mkey_id          "dense id"
  }
  turtle_src {
    VARCHAR ms_merge_key  FK "-> taxon"
    VARCHAR ds_key
    INTEGER cell_id
    DOUBLE  val
  }
  us_cells {
    INTEGER cell_id       PK "in_usa grid cells"
  }
  listing {
    VARCHAR sci           PK "scientific name"
    VARCHAR nmfs_esa
    VARCHAR fws_esa
    BOOLEAN is_mmpa
  }

  taxon ||--|{ taxon_model       : ms_merge_key
  taxon ||--|| taxon_flags       : ms_merge_key
  taxon ||--|| taxon_er          : ms_merge_key
  taxon ||--o{ turtle_src        : ms_merge_key
  taxon ||--|| taxon_cell        : ms_merge_key
  taxon ||--|| taxon_cell_global : ms_merge_key
  mkey_map ||--|{ mc_parts       : mkey_id
  us_cells ||--|{ mc_parts       : cell_id
  us_cells ||--|{ model_cell     : cell_id

Figure 3: merge.duckdb entity relationship diagram (click to zoom)

3.2 Tables

4 spp.duckdb — taxonomy authority

Read-only reference of five taxonomic sources (each with a _vernacular common-name table). merge_taxon reads WoRMS class/phylum for the taxonomy-based sp_cat, isMarine/isExtinct flags, and BirdLife (botw) for the marine-bird determination.

spp.duckdb matches the documented schema.

4.1 Schema

Code 
erDiagram
  worms {
    INTEGER taxonID          PK "WoRMS AphiaID"
    VARCHAR scientificName
    VARCHAR class
    VARCHAR phylum
    VARCHAR family
    BOOLEAN isMarine
    BOOLEAN isExtinct
  }
  worms_vernacular {
    INTEGER taxonID          FK "-> worms"
    VARCHAR vernacularName
  }
  botw {
    DOUBLE  taxonID          PK "BirdLife id"
    VARCHAR scientificName
    VARCHAR redlist_code
    VARCHAR family
  }
  botw_vernacular {
    DOUBLE  taxonID          FK "-> botw"
    VARCHAR vernacularName
  }
  gbif {
    INTEGER taxonID          PK "GBIF id"
    VARCHAR canonicalName
    VARCHAR taxonRank
    VARCHAR family
  }
  gbif_vernacular {
    DOUBLE  taxonID          FK "-> gbif"
    VARCHAR vernacularName
  }
  itis {
    INTEGER taxonID          PK "ITIS TSN"
    VARCHAR scientificName
    VARCHAR taxonRank
  }
  itis_vernacular {
    INTEGER taxonID          FK "-> itis"
    VARCHAR vernacularName
  }
  iucn_redlist {
    INTEGER sis_taxon_id     PK "IUCN SIS id"
    VARCHAR taxon_scientific_name
    VARCHAR red_list_category_code
    BOOLEAN latest
  }
  iucn_vernacular {
    DOUBLE  taxonID          FK "-> iucn_redlist"
    VARCHAR vernacularName
  }

  worms        ||--|{ worms_vernacular : taxonID
  botw         ||--|{ botw_vernacular  : taxonID
  gbif         ||--|{ gbif_vernacular  : taxonID
  itis         ||--|{ itis_vernacular  : taxonID
  iucn_redlist ||--|{ iucn_vernacular  : "sis_taxon_id = taxonID"

Figure 4: spp.duckdb entity relationship diagram (click to zoom)

4.2 Tables