Ops Telemetry Query

Telemetry Query

Query Boundless broker telemetry across two backends:

• On/after 2026-04-25 — Amazon Redshift (provisioned, Postgres-compatible) via psql.
• Through 2026-04-24 — S3 Parquet archives (UNLOADed from the old Redshift Serverless at cutover) via DuckDB.

The UNLOAD per env happened at various times during 2026-04-24; treating all of 2026-04-24 UTC as "archive" avoids any risk of missing data that landed in the archive after midnight but before the UNLOAD moment. Parquet schemas are identical to the Redshift typed views, so column names, types, and enum values are the same on both sides. See Routing by Time Window for how to pick a backend.

Prerequisites

Before running any queries, check if REDSHIFT_URL is already exported in the shell.

If not, try to read network_secrets.toml from the repo root. If it exists, extract the telemetry credentials for the selected environment from [environments.<env>.telemetry]:

• db_url -- the Redshift hostname:port/database?sslmode path
• readonly_password -- the readonly user password

Construct the connection string: postgres://readonly:<password>@<db_url>

Also read network_address_labels.json (same directory) for translating addresses to human-readable labels in output.

If network_secrets.toml is not present, recommend the user create it -- instructions and credentials are in the Boundless runbook. Alternatively, they can provide credentials directly:

1. The Redshift endpoint (hostname or full connection string)
2. The readonly password

They can either:

• Export them as env vars: export REDSHIFT_ENDPOINT="..." and export REDSHIFT_PASSWORD="..."
• Provide them directly in the chat

If network_address_labels.json is not present, recommend the user create it -- the canonical address mapping is in the Boundless runbook. The file is plain JSON ({"0xaddr": "label", ...}).

The user may provide a full postgres://... URL or just a hostname. Normalize to a valid connection string:

• If the URL is missing /telemetry at the end of the path, append it
• If the URL is missing ?sslmode=require, append it
• If only a hostname is given, construct: postgres://readonly:<password>@<hostname>:5439/telemetry?sslmode=require

Export the result as REDSHIFT_URL before running queries.

Routing by Time Window

Telemetry was cut over from Redshift Serverless to Redshift provisioned during 2026-04-24 UTC. For routing purposes, the boundary is 2026-04-25T00:00:00Z — all of 2026-04-24 is treated as archive territory. This avoids a subtle gap: the per-env UNLOAD ran at various times on 2026-04-24 (e.g. 15:54 UTC for prod_base), so a midnight cutover would miss archive rows between 2026-04-24 00:00 and each env's UNLOAD moment.

Pick a backend based on the user's time filter (evaluated_at, completed_at, timestamp):

Archive Backend (DuckDB + S3 Parquet)

Bucket mapping

All buckets live in us-west-2. Layout inside every bucket: s3://<bucket>/<stack>/<view>/NNNN_part_00.parquet, where <view> ∈ broker_heartbeats | request_evaluations | request_completions. Each bucket contains one stack prefix — use the glob s3://<bucket>/*/<view>/*.parquet.

Schema

Parquet column names and types match the Redshift typed views exactly (Redshift did the UNLOAD; schemas were preserved). Enum values (outcomes, skip codes, fulfillment types) are defined in crates/boundless-market/src/telemetry.rs — the same source of truth as the live path. received_at is present.

Credentials

Read AWS creds from network_secrets.toml at the repo root:

• [aws.prod] for any prod_* env
• [aws.staging] for any staging_* env

Both have s3:GetObject on their respective buckets. Region is us-west-2.

Running a query

Mirror the psql heredoc pattern. Install httpfs once per duckdb invocation, set the creds, then read_parquet the glob. Heredoc keeps zsh from mangling !=.

AWS_ACCESS_KEY_ID=<from network_secrets.toml> \
AWS_SECRET_ACCESS_KEY=<from network_secrets.toml> \
duckdb <<SQL
INSTALL httpfs; LOAD httpfs;
SET s3_region='us-west-2';
SET s3_access_key_id='${AWS_ACCESS_KEY_ID}';
SET s3_secret_access_key='${AWS_SECRET_ACCESS_KEY}';

SELECT skip_code, COUNT(*) AS count
FROM read_parquet('s3://telemetry-snapshot-staging-84532-04-24-26/*/request_evaluations/*.parquet')
WHERE outcome = 'Skipped'
  AND evaluated_at BETWEEN TIMESTAMP '2026-04-18 00:00:00+00'
                       AND TIMESTAMP '2026-04-25 00:00:00+00'
GROUP BY skip_code
ORDER BY count DESC;
SQL

DuckDB vs Redshift SQL differences

When rewriting a Redshift query for the archive path, watch for these:

• Use NOW() instead of GETDATE().
• MEDIAN() and PERCENTILE_CONT() have no single-aggregate restriction — multiple percentiles in one SELECT work fine.
• DISTINCT ON and LATERAL joins are supported.
• Timestamp literal form: TIMESTAMP '2026-04-25 00:00:00+00' (note the timezone).

Fail-loud rules

• If the user asks for a pre-cutover window on an env not in the bucket table → stop and say the archive doesn't exist for that env.
• If DuckDB returns HTTP 403 / AccessDenied → stop and report the bucket name and which [aws.*] profile was used; do not silently retry.
• If network_secrets.toml is missing the needed [aws.*] block → stop and ask the user.

Known Addresses

If network_address_labels.json exists at the repo root, use it to label addresses in results. The file is plain JSON ({"0xaddr": "label", ...}) so the user can paste directly from the canonical mapping. When displaying addresses from query output, check if the address (case-insensitive) has a known label and show it alongside, e.g. 0xbdA9...5542 (BP1).

Provers we operate are labeled with a BP prefix (e.g. BP1, BP2, BPNightlyAWS). When investigating any issue, always highlight what our BP provers are doing -- did they skip, fail, drop, or fulfill? This should be called out explicitly even when the investigation is not specifically about our provers.

Connecting

Prefer heredocs for all queries (avoids shell escaping issues):

psql "$REDSHIFT_URL" <<'SQL'
SELECT ...
SQL

For simple one-liners, psql -c works but beware of shell quoting (see below):

psql "$REDSHIFT_URL" -c "SELECT 1;"