SQL for Observability and Event Analytics

SQL observability means answering operational and product questions from structured events with visible, reviewable queries. Instead of searching an unbounded message and hoping every producer formatted it the same way, you define useful fields such as route, status_code, latency_ms, account_id, release, and outcome, then aggregate those columns directly.

This does not make text logs, metrics, or traces obsolete. It gives software teams a relational analysis layer for questions that cross reliability, product behavior, cost, and customer impact.

The short version

A useful SQL observability workflow has five parts:

1. Record one terminal event for a meaningful operation.
2. Keep a small stable set of dimensions, measures, and correlation identifiers.
3. Store the event in a typed table with a trustworthy timestamp.
4. Use read-only SQL to filter, group, join, and compare the rows.
5. Turn the result into a chart, dashboard, alert, or scheduled report tied to a decision.

Telemetry follows that shape: send JSON events, inspect the inferred table, run DataFusion SQL, and publish the result. The SQL recipe library makes the complete path inspectable with typed contracts, synthetic inputs, expected outputs, charts, and edge cases.

Why SQL is useful for observability

Operational questions often become relational even when they begin as logs:

• Which routes became slower after release 2026.07.28?
• Which accounts were affected by a payment incident?
• Did retries recover webhook delivery, or only increase load?
• Which AI feature has the highest cost per accepted output?
• Which queue jobs repeatedly fail after their visibility timeout?

These questions need grouping, conditional aggregation, joins, stable identity, and explicit time windows. SQL makes those choices visible. A reviewer can see whether a rate uses rows or unique requests, whether the newest partial bucket is included, and whether an inner join silently removed unmatched data.

SQL is also portable as a skill. Function names and timestamp syntax vary between engines, but SELECT, WHERE, GROUP BY, CASE, joins, and window functions form a durable mental model.

Model operations as events, not messages

Start with a meaningful operation such as an API request, checkout attempt, background job, webhook delivery, or model response. Emit one terminal event when its outcome is known.

{
  "event_name": "api_request_completed",
  "request_id": "req_01J...",
  "account_id": "acct_42",
  "route": "/v1/orders/:id",
  "method": "GET",
  "status_code": 503,
  "latency_ms": 842,
  "release": "2026.07.28",
  "region": "us-west",
  "outcome": "error",
  "error_type": "upstream_timeout"
}

This is a wide event: it keeps the context needed to explain the operation without requiring a chain of fragile message parses. Use bounded categories for fields such as route, error_type, and outcome. Keep secrets, raw request bodies, prompt text, and private customer content out by default.

Telemetry adds timestamp_utc during ingestion. If you also send a business timestamp, name it for its meaning—such as scheduled_at, completed_at, or invoice_period_start—instead of creating a second ambiguous timestamp.

Dimensions, measures, and identity

A practical contract separates three kinds of fields:

Choose identity deliberately. Counting rows is correct only when one row equals the unit you want to count. Retry telemetry, multi-step workflows, and periodic snapshots commonly produce several rows for one logical operation.

For schema design details, use Designing an Event Schema, Correlation IDs, and High-Cardinality Fields.

Five SQL patterns cover many investigations

1. Filter to the decision window

SELECT *
FROM api_request_completed
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND route = '/v1/orders/:id'
ORDER BY timestamp_utc DESC
LIMIT 200;

Begin with raw rows. Confirm units, nullability, route normalization, and outcome values before building an aggregate.

2. Calculate a rate with an explicit denominator

SELECT
  route,
  COUNT(*) AS requests,
  SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS errors,
  ROUND(
    100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
      / NULLIF(COUNT(*), 0),
    2
  ) AS error_rate_pct
FROM api_request_completed
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY route
ORDER BY error_rate_pct DESC, requests DESC;

The denominator is all matching request rows. If producers emit retries as new rows, decide whether the dashboard should show attempts or unique logical requests.

3. Compare a release boundary

SELECT
  release,
  COUNT(*) AS requests,
  approx_percentile_cont(latency_ms, 0.95) AS p95_latency_ms,
  ROUND(
    100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
      / NULLIF(COUNT(*), 0),
    2
  ) AS error_rate_pct
FROM api_request_completed
WHERE timestamp_utc >= now() - INTERVAL '7 days'
GROUP BY release
ORDER BY release;

Release comparisons need traffic share and rollout time. A low-volume canary should not be interpreted like a complete production rollout.

4. Join technical impact to a stable account

SELECT
  r.account_id,
  a.plan,
  COUNT(*) AS failed_requests
FROM api_request_completed r
LEFT JOIN account_dimension a
  ON r.account_id = a.account_id
WHERE r.timestamp_utc >= now() - INTERVAL '2 hours'
  AND r.status_code >= 500
GROUP BY r.account_id, a.plan
ORDER BY failed_requests DESC;

The left join preserves affected accounts even if enrichment is late. Keep the dimension’s freshness and one-row-per-key rule explicit.

5. Reconstruct a correlated timeline

Use request_id, job_id, or another workflow identifier to union or join events in time order. A timeline is often more useful during an incident than a single aggregate because it shows the attempt, retry, dependency outcome, and terminal state.

The connected SQL Lab provides six synthetic tables and guided lessons for joins, funnels, AI cost, reliability, and recovery. It runs entirely in the browser.

Match the visualization to the question

The query result should determine the visual, not the other way around.

Always keep the result table available beside a chart. Tooltips and axes can hide rounding, nulls, and small denominators that are obvious in the underlying rows.

Every public Telemetry recipe includes a crawlable result table, a visual preview, JSON and CSV fixtures, and a static chart. Start with API error rate by route, slow database queries by fingerprint, or LLM cost by feature.

Where SQL should complement other telemetry

Use metrics for cheap, continuous aggregate signals and alerting with tightly controlled labels. Use traces to understand a request’s distributed critical path. Use text logs when the exact diagnostic message matters or the shape is not known in advance. Use structured event tables when the team needs flexible dimensions, business context, joins, or auditable calculations.

The systems can share correlation IDs. A SQL result can identify the affected release and account cohort; a trace can then explain a representative slow request; a text log can show the exact dependency error.

Do not copy every trace span or log line into a second store without a decision it supports. Duplicate collection creates cost and conflicting definitions.

A safe adoption sequence

Choose one uncertain workflow and write down the decision the data should support. Define its terminal event, instrument it in shadow mode, compare event counts to an existing source, and inspect raw rows. Only then publish the aggregate.

For a gradual cutover from message search, follow Migrate from Ad Hoc Logs to Structured Events and SQL. If the current team uses LogQL, KQL, or SPL, the query-language migration guide maps common patterns without pretending the languages are interchangeable.

Review checklist

Before a query becomes operational:

• confirm the row grain and denominator;
• document null and late-arrival behavior;
• bound the time range;
• validate units and timestamp timezone;
• preserve unmatched rows when enrichment can lag;
• decide how retries and duplicates count;
• exclude or annotate incomplete time buckets;
• test the threshold on historical or synthetic data;
• keep a link from the chart back to the SQL and event contract.

The SQL testing methodology explains what Telemetry’s automated checks prove and what still requires business judgment.