p24-infra Docs

atrax-analytics-etl-operations

5 min read

Atrax analytics ETL — operations

Design + implementation of the fuel-consumption / ecodriving analytics layer over Atrax GPS/telematics data. Tracked in #6138.

This doc covers the implemented piece (Track B — the Parquet analytics export) and points to the design for the rest. The full multi-track design lives in the ## Code-change-design comment on #6138.

⚠️ Hard dependency — #5921 (read first)

Tracks A, C, D (anything reading avg_consumption_l100km, fuel_used_liters, fuel_level_*, ecodriving scores) are blocked on #5921: the Atrax GET /api/exploitation and POST /api/statistics/daily/drivers endpoints reject every request with an invalid-signature/hash error, so the n8n collectors atrax-daily-stats-collector (Wi3ZJcPw0YbxLXwa) and atrax-ecodriving-daily (zdzyIQ8UBsDAF0wI) have never populated p24_gps_daily_stats / p24_driver_ecodriving_daily.

The ETL below is built and ready anyway — the moment #5921 unblocks and those tables start filling, the nightly export begins producing non-empty Parquet with no further change. Until then it runs cleanly and exports 0 rows for the blocked tables (expected, not an error). p24_atrax_drivers_daily is not blocked (its collector needs no signature) and exports normally.

Track B — Parquet analytics export (scripts/nightly-etl/export_atrax.py)

What it does

Reads the per-day Atrax daily tables out of Supabase (PostgREST, paginated) and writes one Snappy-compressed Parquet file per table per day to Wasabi:

s3://p24-infra/reporting/atrax/{table}/{YYYY-MM-DD}.parquet

This is the analytics-friendly denormalized copy for repeat querying via the planned report-query-api (FastAPI + DuckDB httpfs, strategia §5) — so future fuel/ecodriving analysis does not re-hit Supabase or the Atrax API. It is separate from and does not replace scripts/atrax-archival.py, which keeps the raw JSON audit trail (10-year retention) at s3://p24-infra/et-operational-platform/atrax-data-sources/….

Exported tables (each filtered to the target day on its date column):

TableDate columnNotes
p24_gps_daily_statsstat_dateblocked on #5921 (empty until then)
p24_driver_ecodriving_dailystat_dateblocked on #5921 (empty until then)
p24_atrax_drivers_dailystat_dateflowing — exports normally
p24_gps_daily_anomaliesstat_dateTrack C output, optional — a missing table is a warning, not a failure

Empty result → the write is skipped (no schema-less empty Parquet is written). Re-running a day overwrites the same object key (idempotent).

Usage

# Default: exports yesterday (UTC)
python3 scripts/nightly-etl/export_atrax.py --env-file /opt/p24-infra/monitoring/.env
 
# Specific day
python3 scripts/nightly-etl/export_atrax.py --date 2026-08-10 --env-file /opt/p24-infra/monitoring/.env
 
# Dry-run (fetch + report counts, no upload; Wasabi creds not required)
python3 scripts/nightly-etl/export_atrax.py --dry-run --env-file /opt/p24-infra/monitoring/.env
 
# Subset of tables
python3 scripts/nightly-etl/export_atrax.py --tables p24_atrax_drivers_daily --env-file …

Environment

Reuses existing keys — no new secrets (per #6138 acceptance):

VarSource
SUPABASE_URL, SUPABASE_SERVICE_KEY (alias SUPABASE_SERVICE_ROLE_KEY)monitoring .env
P24_INFRA_WASABI_ACCESS_KEY / _SECRET_KEY (alias WASABI_ACCESS_KEY / _SECRET_KEY)same Wasabi keys atrax-archival.py uses
WASABI_BUCKET / WASABI_ENDPOINT / WASABI_REGIONoptional overrides
P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URLoptional; error alert
GH_TOKEN / GITHUB_TOKENoptional; file a GH issue on error

Dependencies

scripts/nightly-etl/requirements.txt — requests, boto3, certifi, pyarrow. pyarrow is imported lazily inside rows_to_parquet_bytes() so the module stays importable (and unit-testable) on hosts/CI without it.

Error handling (Error Notification Standard)

On a required-table failure the run exits non-zero and, best-effort, posts a Discord embed via P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URL and files a bug issue in radieu/p24-infra. One failing table does not abort the others.

Tests

scripts/tests/test_export_atrax.py — date resolution, S3 key building, table-subset selection, env validation, paginated fetch, optional-vs-required missing-table handling, and export dispatch. The Parquet round-trip is pytest.importorskip("pyarrow")-guarded (skipped in the scripts CI job, which does not install pyarrow).

Deployment status — SCHEDULED on vps-i1 (#6570)

Live since 2026-08-20 (#6570 — the export/retention half of #6194 Handoff 2, split off from the bms-4 norm-jobs schedule in PR #6568 because it cannot run on bms-4). This is the canonical, single schedule for the whole export: Track B’s 4 tables + #6194’s 4 route/driver norm tables + the --prune-snapshots retention prune.

Host: vps-i1 (not bms-4). export_atrax.py writes to the Wasabi reporting bucket (s3://p24-infra/reporting/atrax/…), which needs P24_INFRA_WASABI_ACCESS_KEY / P24_INFRA_WASABI_SECRET_KEY. Those live in vps-i1’s monitoring/.env; bms-4’s generic WASABI_ACCESS_KEY is a different key Wasabi rejects (InvalidAccessKeyId) against that bucket.

Cron: /etc/cron.d/atrax-etl (root, 0644), 01:00 UTC daily, after the nightly collectors:

# /etc/cron.d/atrax-etl on vps-i1
0 1 * * * root /usr/bin/python3.12 /opt/p24-infra/scripts/nightly-etl/export_atrax.py \
    --prune-snapshots --env-file /opt/p24-infra/monitoring/.env >> /var/log/atrax-etl.log 2>&1

Interpreter pin — /usr/bin/python3.12 is mandatory. vps-i1’s default python3 is 3.9 (and lacks pyarrow); the sibling norm chain (PR #6568) established 3.12 as the required interpreter for this stack. Runtime deps (requests, boto3, certifi, pyarrow>=14,<21) are installed into the system python3.12 site (python3.12 -m pip install -r scripts/nightly-etl/requirements.txt, as root).

Deploy verification (2026-08-20, #6570):

  • Dry-run + real run against a real day (2026-08-20): p24_gps_fuel_snapshots exported 5460 rows → s3://p24-infra/reporting/atrax/p24_gps_fuel_snapshots/2026-08-20.parquet (confirmed by S3 list). The other 7 tables logged empty/missing (expected — their producers have not populated them yet: trips/anomalies/norm first run via PR #6568’s bms-4 chain on 2026-08-21; daily_stats/ecodriving stay empty until #5921).
  • --prune-snapshots on that run logged verified-empty, nothing to prune for 2026-07-16 (nothing has aged past the 35-day window yet — snapshots only began 2026-08-20), i.e. it deleted nothing, which is correct.
  • The §5a “archive-then-verify-then-delete” gate — a delete is issued only after the archived row count equals the live source count, and is skipped on a count mismatch — is covered by unit tests (scripts/tests/test_export_atrax.py::test_prune_snapshots_*, incl. the mismatch failure path). A live failure-path test is intentionally not run — it would require corrupting the production source table.

First real prune: p24_gps_fuel_snapshots began accumulating 2026-08-20; the 35-day retention prune has nothing to delete until ~2026-09-24. Until then the table grows in Supabase and the nightly prune step is a verified no-op.

Remaining follow-ups:

  • dev_r_services registration — attempted at deploy; see the compliance note below.
  • Add the report-query-api + run_etl.sh orchestrator + ReportingETLStale Prometheus alert from strategia §5 (out of scope for #6138 Track B — the single exporter).
  • Optional: logrotate for /var/log/atrax-etl.log (one small append per day — low priority).

Tracks A / C / D / E / F

Designed in the #6138 ## Code-change-design comment; not implemented in the Track B PR (A/C/D blocked on #5921, E is a separate design spike, F is an et-operational-platform UI issue). See that comment for the schema/logic of the consumption norm, anomaly detection, alerting rollup, per-route ecodriving, and the dashboard request.

Graph View

Backlinks