Analytical Parquet snapshots

Overwatch publishes a nightly Parquet snapshot of a small allowlist of analytical tables to a DigitalOcean Spaces bucket. The live Postgres database (Supabase) remains the source of truth for application reads — these snapshots exist so batch workloads can scan flat columnar files in DuckDB instead of hammering the OLTP database. Use the snapshots when you are running:

A DuckDB or dbt-duckdb model that aggregates across many days of history.
An entity-resolution batch (for example Splink) that needs a stable, immutable input.
Ad-hoc analytical queries from a notebook that should not contend with production traffic.

For real-time reads — vessel positions, port congestion, alerts — keep using the Overwatch API and the live database.

How it works

A nightly job runs DuckDB with the postgres_scanner, spatial, and httpfs extensions loaded. For each table in the allowlist it executes a COPY (SELECT * FROM pg.<table>) TO 's3://…/<table>.parquet' (FORMAT PARQUET, COMPRESSION ZSTD) against Postgres and streams the result straight into object storage. No intermediate disk staging. Each run also writes a tiny CSV manifest at s3://<bucket>/latest/<table>.path whose single snapshot_date column points consumers at the most recent dated partition. S3-compatible stores don’t support symlinks, so the manifest serves the same purpose.

Snapshotted tables

The allowlist is intentionally narrow. Raw ais_positions is never snapshotted — it is the high-volume telemetry table and its inclusion would dominate storage and I/O. For position history, query the live database (see data retention) or request a rehydration from the archive.

Table	What it holds
`metros`	Metropolitan area reference data.
`h3_cells`	H3 hex cell grid used by Locus and the cell scorer.
`pois`	Points of interest registry.
`poi_snapshots`	Per-POI observation history.
`collection_runs`	Collector run metadata.
`monitored_locations`	User-configured monitored locations.
`alert_history`	Historical alert events.
`cell_scores`	Latest per-cell scoring outputs (~50 MB/day).
`cell_score_history`	Time series of cell scores.
`locus_validation_metrics`	Locus model validation outputs.
`locus_scoring_formulas`	Configured Locus scoring formulas.
`locus_export_schedules`	Locus export schedule definitions.

Output layout

s3://axiom-parquet-snapshots/snapshots/<ISO_DATE>/<table>.parquet
s3://axiom-parquet-snapshots/latest/<table>.path

Files are Zstandard-compressed Parquet. The snapshots/<ISO_DATE>/ partitions are immutable; the latest/ manifests are overwritten on each run.

Reading a snapshot from DuckDB

Point DuckDB’s httpfs extension at the bucket and scan the Parquet file directly:

INSTALL httpfs;
LOAD httpfs;

CREATE SECRET axiom_spaces (
    TYPE s3,
    KEY_ID '...',
    SECRET '...',
    REGION 'nyc3',
    ENDPOINT 'nyc3.digitaloceanspaces.com',
    URL_STYLE 'path',
    USE_SSL true
);

-- Resolve the latest dated partition from the manifest, then scan it.
WITH latest AS (
    SELECT snapshot_date
    FROM read_csv_auto('s3://axiom-parquet-snapshots/latest/cell_scores.path')
)
SELECT *
FROM read_parquet(
    's3://axiom-parquet-snapshots/snapshots/' ||
    (SELECT snapshot_date FROM latest) ||
    '/cell_scores.parquet'
)
LIMIT 100;

For pinned reproducibility — for example a dbt run or an ER batch — read a specific dated partition directly instead of going through latest/.

Storage and retention

cell_scores is the largest table at roughly 50 MB per day; the other tables are smaller. The bucket is configured with a 90-day lifecycle policy, so dated partitions older than 90 days are deleted automatically. If you need older history, copy the partition you need into a long-term archive before it expires.

Snapshots are an analytical convenience, not a backup. The live Postgres database and the AIS archive remain the systems of record. See data retention for how raw operational data is preserved.

Configuration

The job is parameterized through environment variables. Postgres and Spaces credentials are required; everything else has a sensible default.

Variable	Default	Description
`PG_HOST`	—	Supabase Postgres host. Required.
`PG_PASS`	—	Postgres password. Required.
`SPACES_KEY`	—	DigitalOcean Spaces access key. Required.
`SPACES_SECRET`	—	DigitalOcean Spaces secret key. Required.
`PG_PORT`	`5432`	Postgres port.
`PG_USER`	`postgres`	Postgres user.
`PG_DATABASE`	`postgres`	Postgres database name.
`SPACES_REGION`	`nyc3`	Spaces region.
`SPACES_ENDPOINT`	`nyc3.digitaloceanspaces.com`	Spaces S3 endpoint.
`SPACES_BUCKET`	`axiom-parquet-snapshots`	Target bucket name.

Get started

Configuration

Methodology

Analytical Parquet snapshots

How it works

Snapshotted tables

Output layout

Reading a snapshot from DuckDB

Storage and retention

Configuration

​How it works

​Snapshotted tables

​Output layout

​Reading a snapshot from DuckDB

​Storage and retention

​Configuration

How it works

Snapshotted tables

Output layout

Reading a snapshot from DuckDB

Storage and retention

Configuration