Statistics Database Guide

Overview

Every analysis script writes its results to human-readable TXT/CSV files inside the analyses/ tree and records the same numbers in a SQLite database at analyses/statistics.db. The database makes it easy to compare experiments, track how metrics change over time, and query across areas and variables without parsing text files.

The database is managed by lib/stats_db.py.

Tables

statistics

One row per (area, experiment, variable, period, layer, model, obs_source, depth_label) combination. A UNIQUE constraint on those eight columns means re-running an analysis upserts the row rather than duplicating it.

Key columns:

area, experiment, variable, period, layer, model, domain, obs_source, depth_label
rmse, bias, mae, correlation, n_points, n_profiles
mean_model, mean_obs, std_model, std_obs
err_min, err_p05, err_p25, err_median, err_p75, err_p95, err_max
run_date, version

monthly_statistics

Child rows linked to statistics.id via a foreign key. One row per month (1–12) with rmse, bias, mae, correlation.

tidal_statistics

One row per (area, experiment, constituent, model) with aggregate amplitude and phase metrics (amp_bias, amp_rmse, pha_bias, pha_rmse, n_stations).

mle_rankings

One row per (area, experiment, obs_type, variable, model) with mle_value, rank_overall, and run_date.

bc_statistics

One row per (area, experiment, variable, scenario, method, model, domain) with separate columns for the calibration and future periods (cal_bias, cal_rmse, fut_bias, fut_rmse, etc.).

How statistics are written

The database is populated automatically as analyses run:

All writes are wrapped in try/except so a database problem never aborts an analysis run.

Migrating existing results

If you have pre-existing statistics files that pre-date the database, use the import-stats subcommand of manage-analyses:

# Preview what would be imported (dry run)
manage-analyses import-stats

# Actually import
manage-analyses import-stats --apply

--analyses-dir is optional here too — see Manage Analyses: –analyses-dir.

The importer walks analyses/ for each experiment’s tables/ tree and:

1. If a YAML config file (any *.yaml except metadata.yaml) exists in the experiment directory, reads it for authoritative identity fields — area, experiment, model name, domain. Otherwise infers them from the path.
2. Imports from *_validation_statistics.csv when available (structured, direct).
3. Falls back to parsing the *_validation_statistics.txt or *_profile_validation_statistics.txt template files when no CSV exists (covers profile, 3-D, and older runs that only wrote TXT).
4. Also imports *_tidal_statistics.txt into the tidal_statistics table.

CSV always takes priority over TXT when both exist for the same file.

Querying the database

Python

from stats_db import StatsDB
from pathlib import Path

db = StatsDB.from_analyses_dir(Path("analyses"))

# All surface-temperature rows for area NS, sorted by RMSE
df = db.get_ranking("NS", variable="TEMP", layer="surface")
print(df[["experiment", "model", "rmse", "bias", "n_points"]])

# All statistics for one experiment
df = db.get_statistics(area="NS", experiment="Baseline")

# MLE rankings
df = db.get_mle_rankings(area="NS")

# Tidal constituents
df = db.get_tidal_stats(area="NS", experiment="Baseline")

SQL (sqlite3 CLI)

sqlite3 analyses/statistics.db

-- List all experiments in the database
SELECT DISTINCT area, experiment FROM statistics ORDER BY area, experiment;

-- Best RMSE per experiment for SST surface
SELECT experiment, model, rmse, bias, n_points
FROM statistics
WHERE area='NS' AND variable='SST' AND layer='surface'
ORDER BY rmse;

-- Monthly RMSE for one row
SELECT m.month, m.rmse, m.bias
FROM monthly_statistics m
JOIN statistics s ON m.stat_id = s.id
WHERE s.area='NS' AND s.experiment='Baseline' AND s.variable='TEMP'
ORDER BY m.month;

Database location and remote/local workflow

The database lives at <output.base_dir>/statistics.db on whichever machine runs the analysis scripts — normally the remote simulation host.

Remote machine:  analyses/statistics.db  ← populated by every analysis run
        ↓  manage-analyses sync  (or update-from-remote)
Local machine:   analyses/statistics.db  ← synced copy; used by ocean-reporting

The rsync rules include statistics.db, so it is transferred automatically. If multiple remote machines contribute to the same analyses tree (different areas or experiments on different hosts), run manage-analyses sync from each host in turn — because DB writes use INSERT … ON CONFLICT … DO UPDATE, the last sync wins for any row that appears on more than one remote. Rows from different areas/experiments never conflict and accumulate safely.

manage-analyses wipe leaves statistics.db untouched — it is treated like simulation_list.db as accumulated state rather than a regeneratable output.

Concurrency

StatsDB uses WAL journal mode and a per-instance threading lock. Multiple Python threads in the same process can share one StatsDB instance safely. Concurrent processes (e.g. parallel run-validation steps) are also safe because SQLite WAL allows one writer and multiple readers simultaneously.