relay

An agent-operable data pipeline for US power-grid data. relay ingests raw public sources — CAISO OASIS, ERCOT MIS, EIA-930 — into a revision-aware warehouse (ClickHouse in production; DuckDB as the hermetic dev/test tier) and builds analytics marts (day-ahead/real-time price spreads, daily peak demand, fuel-mix shares) with dbt. Every surface returns typed, machine-readable results, so it can be driven equally by a human at a terminal, a scheduler, or an AI agent over MCP.

Sibling project: wattson — grid data an agent can read; relay is grid data infrastructure an agent can operate.

The operating loop

relay describe      # what exists: datasets, buckets, lags, flows (cold-start discovery)
relay plan hourly   # exactly what `run` would do, computed from watermarks — no mutation
relay run hourly    # do it; exit 0 clean, 1 if any task failed (branchable)
relay status        # freshness: watermarks, row counts, staleness per dataset
relay backfill caiso --start 2026-06-01 --end 2026-06-08   # deliberate history
relay reprocess caiso   # re-parse landed bronze with current code — no network

From a fresh clone these run as uv run relay … (the Quickstart uses that form); uv tool install . puts relay on your PATH directly. Every command takes --json and prints the underlying contract (PlanResult, RunResult, StatusResult, …) — the same Pydantic models that validate at runtime and define the MCP tool schemas. Failures carry stable codes (fetch_failed, parse_failed, quality_failed) an agent can branch on. plan and run share one pending-window computation, so the plan is a contract, not an estimate.

Quickstart

Prerequisites: uv only — curl -LsSf https://astral.sh/uv/install.sh | sh. It provisions the pinned Python 3.13 itself, so there is nothing else to install. Reviewing needs neither network nor credentials; a live ingest needs network access (and, for EIA-930 only, a free key).

Review it — no keys, no network

uv sync
uv run pytest        # 156 hermetic tests from recorded fixtures — green in seconds

That exercises the whole pipeline offline — connectors, the revision merge, DST conversion, the dbt gold build, CLI and MCP — with zero network and zero credentials, so it is the fastest way to verify the work end to end. The full CI gate is uv run ruff check && uv run pyright && uv run pytest.

Run it live

cp .env.template .env       # add EIA_API_KEY — free, ~1 min: https://www.eia.gov/opendata/
uv run relay plan hourly    # what a run would fetch, from watermarks — no network, no mutation
uv run relay run hourly     # ingest CAISO + ERCOT + EIA-930; gold marts rebuild when silver changes
uv run relay status         # freshness per dataset
duckdb data/relay.duckdb "select * from mart_dart_spread limit 5"   # brew install duckdb, or any SQL client

CAISO and ERCOT need no key. EIA-930 does — without it, its two tasks report fetch_failed and run exits 1 (by design: any failed task is a non-zero, agent-branchable exit — not a crash). Set the free key above for a fully green run, or stay keyless by ingesting only the public sources, e.g. uv run relay backfill caiso --start 2026-07-01 --end 2026-07-02 (pick a recent window — sources retain only so much history).

Or the production topology on your laptop — a served ClickHouse warehouse plus the relay image (docker compose reads .env, so the same EIA-930 note applies):

docker compose up -d clickhouse
docker compose run --rm relay run hourly
docker compose run --rm relay status

As MCP tools (Claude Code): claude mcp add relay -- uv run relay-mcp — the tools mirror the CLI 1:1.

Architecture

CAISO OASIS (zip/CSV API)  ─┐   bronze: as-received payloads,        dbt
ERCOT MIS  (zip/CSV files) ─┼─▶ immutable, content-addressed  ─▶  silver: observations ─▶ gold marts
EIA-930    (JSON API, key) ─┘   parse -> validate -> merge          (revision-aware)       + tests

• One silver schema. Everything normalizes to long/tidy observations rows: series_id (hierarchical: source.dataset.entity[.measure]), ts (UTC interval start, half-open), value, unit, plus revision metadata. Nothing about a source's shape leaks downstream.
• Revisions, not overwrites. Grid data gets restated — real-time prices are corrected, EIA republishes past hours. The merge classifies each key: new → revision 0, identical → no-op, changed → a new revision with the old row's is_current flipped. History survives; replays are idempotent; and each dataset declares a revision_lookback — the runner re-fetches that trailing overlap on every run so restatements (EIA republishing 24h, RTM price corrections) become revisions instead of silent drift.
• Watermarks own incrementality. Per-dataset high-water marks, bucket- aligned windows, per-dataset publication lag. Failed tasks don't advance their watermark — the gap stays owed and appears in the next plan. Watermarks are monotonic, so backfills never regress freshness.
• Time is handled once. All local-time conventions (hour-ending labels, ERCOT's DST flag) convert through timeconv, with tests proving the 25-hour fall-back day maps to 25 contiguous UTC hours and the spring-forward gap raises instead of silently shifting. EIA's UTC periods are hour-ending; interval start = period − 1h (convention documented and cited in the connector).
• Bronze is replayable, not just evidence. Every payload lands content-addressed and indexed with its original fetch time; reprocess re-parses bronze with today's code — corrected parses land as revisions, unchanged ones merge to no-ops, watermarks never move. A parser bug is a local replay, not a re-fetch against sources that only retain a month.
• One warehouse contract, two engines. The pipeline's semantics (revision merge, monotonic watermarks, artifact index) live in a Warehouse protocol with engine-native implementations: DuckDB flips an is_current flag inside a transaction; ClickHouse is insert-only and resolves currency at read time (argMax by revision). Both expose one observations_current relation — the only thing dbt reads — and a shared contract test suite asserts identical semantics of both. RELAY_WAREHOUSE=clickhouse selects production; the default keeps dev hermetic.
• Connectors are two functions. fetch(dataset, window, http) and parse(payload) -> rows. Landing, validation, merging, watermarks, politeness spacing, and 429 retry are the runner's, identically per source. Adding a source = one module + one registry entry.

Data sources

source	datasets	format	auth
CAISO OASIS	lmp_dam (hourly), lmp_rtm (5-min) — NP15/SP15/ZP26 hubs	zip of CSV over query API	none
ERCOT MIS	spp_dam (hourly), spp_rtm (15-min) — HB_* hubs	JSON doc listing + zip of CSV	none
EIA-930	demand, fuel_mix (hourly) — 7 BAs	JSON API v2, paginated	free key

Marts: mart_dart_spread (day-ahead vs realized real-time price per hub per hour), mart_daily_peak_demand, mart_fuel_mix_share, mart_freshness. The marts carry dbt schema tests plus singular tests (spread arithmetic, shares sum to 1, composite uniqueness) — 27 dbt tests in every gold build.

Testing

156 hermetic pytest tests (coverage-gated ≥85% in CI) + a 21-test cross-engine warehouse-contract suite (DuckDB always; ClickHouse against a real container under the opt-in clickhouse marker) + 27 dbt tests — the hermetic default has zero network, zero real sleeps: recorded fixtures for connectors (provenance documented per fixture directory — recorded-live vs synthetic-to-documented-format is always disclosed), injected clocks for the scheduler/retry paths, httpx.MockTransport at the fetch boundary, a seeded-warehouse dbt build, and CLI/MCP tests against a stubbed runner. The DST fall-back/spring-forward days and the revision lifecycle each have dedicated tests — those are the bugs this domain actually produces.

Gates (all enforced in CI): ruff format --check · ruff check · pyright (strict) · pytest.

Scheduling

GitHub Actions runs the pipeline (.github/workflows/pipeline-hourly.yml) once the PIPELINE_ENABLED repo variable is true: hourly incremental ingestion, with the gold marts (and their 27 dbt tests) rebuilt in the same run whenever silver changed. The RELAY_WAREHOUSE repo variable selects the tier: against ClickHouse, silver/gold persist in the served warehouse and only bronze rides the object-store sync; on the DuckDB tier the whole data dir syncs to any S3-compatible bucket (R2) when STATE_BUCKET / STATE_ENDPOINT_URL vars + access-key secrets exist. Without a bucket each run is stateless but still correct. Every run uploads its RunResult JSON as an artifact — the audit trail is the contract, not log grep.

Python 3.13 (pinned — dbt-core does not yet support 3.14) · uv · polars · ClickHouse + DuckDB · dbt · Typer · MCP.

Operational details — failure modes by error code, source quirks, backfill etiquette — live in docs/runbook.md. The trade-offs behind the architecture (and what would trigger revisiting each) are recorded in docs/decisions.md.

License

Apache 2.0.