Part 1: Season of hungry stars and oceans of data

If you've ever gone diving in Southeast Asia or the Red Sea, you've probably met Acanthaster planci — the Crown of Thorns starfish(COTS).

Foto from EDA

These starfish are not just highly venomous; they love to prey on coral.

On a healthy balanced reef there may be one or two crown of thorns per hectare. Their eating rate aligns with the reef’s growth speed, which favours coral diversity.

But these starfish can release millions of eggs, which if left unbalanced by diminishing populations of humphead wrasse and triton snail can lead to potential outbreaks. After six months, swarms of juvenile stars switch from an algae diet to consuming corals.

Witnessing this slow moving army in a local outbreak left an impression and a thought:

How can I learn dive site species and their invasive/endangered standing from data

Data exists, but it is a vast a disparate collection of inherently different datasets:

• the Global Biodiversity Information Facility (GBIF)

• Ocean Biogeographic Information System (OBIS)

• World Registry of Marine species (WoRMS)

• the International Union for Conservation of Nature (IUCN)

• list of invasive species as Darwin Core Archive (IUCN)

• list of divesites from official PADI website

Approaching this with two dumbest questions possible:

1. "Where can I find species X?" For a list of divesites with possible sightings

2. "What lives near dive site Y?" For a list of things that I can encounter there

Seven data sources

Five different formats. 3.5 billion+ rows once you combine GBIF and OBIS:

Source	Records	Format	Access method
GBIF	Billions	BigQuery public dataset	SQL queries
OBIS	~162M	Parquet (partitioned)	Public S3 bucket
WoRMS	~593K	DwCA zip	Authenticated HTTP
IUCN Red List	~255K	DwCA zip	HTTP download
GISD	~830	DwCA zip	HTTP download
SSI dive sites	~10,600	JSON (tiled)	Session-auth REST API
PADI dive sites	~3,400	JSON	Paginated REST API

Global Biodiversity Information Facility

GBIF publishes a giant species occurrence public BigQuery dataset. Billions of rows. My first exploratory query there was a simple LIMIT 1

That was my welcome-to-BigQuery moment which immediately cost around $2.

BigQuery prices bytes processed on the referenced data.

Because LIMIT is applied quite late in the query plan - full size of the dataset will be processed and billed. It would be nice to know that in advance, but lesson learned!

Most importantly - GBIF is the largest of the bunch. Which means whole project somehow needs to live on GCP.

DwCA - Darwin Core Archives

Darwin Core Archive format is behind lots of biological catalogues including:

• IUCN - labels for endangered species

• GISD - labels for invasive species

• WoRMS - labels for marine species - since we do not want to count seagulls just yet

Each file is a zip with several CSVs and a meta.xml descriptor pointing at the core data file. With the the help python-dwca-reader access becomes pleasantly simple:

No more processing will be added, since I want to leave data raw for future modelling.

PADI vs SSI and building dataset of dive sites

• Pros: PADI and SSI both publish dive site locations

• Cons: both have no way of a clean data export

PADI turned out to be two APIs glued together:

1. paginated "guide" which tells you what those places are

2. separate map endpoint that only returns coordinates for a given lat/lng bbox

Neither one alone has everything, so the ingestion has to fetch both and merge on id:

About 90 seconds end to end, rate-limited, and idempotent enough that a re-run just overwrites the output.

SSI was slightly harder.

Because there's no documented API at all — I reverse-engineered it from the network tab on https://www.divessi.com/en/locator/divesites

It pairs two credentials per session:

• PHPSESSID cookie set on the page load

• x-ssi-auth token embedded in the page's HTML as var SSI_APIKEY

Token from one session won't authenticate a request carrying a different session's cookie, so both have to come from the same response:

The dive-site query itself takes a bounding box, but caps results at 1,000 with no offset or page parameter — so a dense region like Southeast Asia just silently truncates.

The fix is to slice the globe into small tiles and recursively subdivide saturated:

Dense tiles split themselves down until every square returns under 1,000 divesites, then the round stops.

OBIS: 162 million rows on S3

OBIS publishes 6,788 Parquet files on a public S3 bucket. Combined: ~162M rows. I needed to download them, select the columns I need and upload to GCS. Simple in theory. In practice:

• Attempt 1 - DuckDB httpfs (78 minutes). DuckDB can read remote Parquet via httpfs. It turned out DuckDB fetched files sequentially within each batch - hundreds of sequential network waits.

• Attempt 2 - single DuckDB COPY. Letting DuckDB "parallelize everything" with a single wildcard read. Even slower.

• Attempt 3 - full parallel download. boto3 with 16 threads is genuinely fast — but 6,788 files are ~50GB raw. Cloud Run's ephemeral disk never had this much.

• Attempt 4 - the hybrid pipeline. Separate downloading (where boto3 is great) from processing (where DuckDB is great), and cap disk usage by batching so 6,788 files never sit on disk at once:

  
  

OBIS_BATCH_SIZE is just small enough to keep peak disk usage predictable on Cloud Run's storage

Nine columns kept out of the full OBIS dataset. Each batch downloads with 16 parallel boto3 threads, gets column-projected and null-filtered by DuckDB into a ZSTD-compressed part file, then the raw batch is deleted before the next one starts. Peak disk usage stays bounded by BATCH_SIZE, not by the size of the whole dataset.

Running it all in parallel

Wrapping everything in a parametrized by source cloud run job:

Each is an idempotent run, and everything runs quickly(with an obvious OBIS exception).

As a result: five clean Parquet files in GCS, plus GBIF referenced directly in BigQuery.

Together forming 3.5B+ occurrence records ready for DBT modeling.