> ## Documentation Index
> Fetch the complete documentation index at: https://docs.axiomancer.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Analytical Parquet snapshots

> Nightly DuckDB export of allowlisted Overwatch tables to Parquet in object storage, for batch analytics, dbt models, and entity-resolution workloads.

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](/overwatch/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

```text theme={null}
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:

```sql theme={null}
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.

<Note>
  Snapshots are an analytical convenience, not a backup. The live Postgres database and the AIS archive remain the systems of record. See [data retention](/overwatch/data-retention) for how raw operational data is preserved.
</Note>

## 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.                           |