DataFusion SQL Reference for Telemetry

Telemetry queries structured event tables with Apache DataFusion SQL. This reference covers the patterns used throughout the SQL recipe library. Field availability and types come from your own event contract, so adapt table and column names before running an example.

Bound the time range

Most operational queries should start with a UTC time filter:

WHERE timestamp_utc >= now() - INTERVAL '24 hours'

Use date_trunc('hour', timestamp_utc) or date_trunc('day', timestamp_utc) to create trend buckets. Keep the raw timestamp for detailed investigation tables.

Conditional counts and safe rates

Conditional CASE expressions calculate several outcomes from the same grouped rows:

SELECT
  route_template,
  COUNT(*) AS requests,
  SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS errors,
  100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS error_rate_pct
FROM api_requests
GROUP BY route_template
HAVING COUNT(*) >= 20;

NULLIF protects division from a zero denominator. A HAVING minimum prevents a small group from looking important because of one failure.

Percentiles and distributions

Use approx_percentile_cont(latency_ms, 0.95) for an efficient p95 estimate. Compare p50 and p95 to distinguish a generally slow workflow from a long tail. For a histogram-like result, group a numeric measurement into explicit buckets and include a readable label in the output.

Windows

Window functions retain row or bucket detail while comparing adjacent values. LAG(value) OVER (ORDER BY bucket) supports change calculations; a framed AVG supports a rolling baseline. Guard incomplete windows and zero denominators before turning the result into an alert.

Query-review checklist

Confirm the time zone, table name, field types, units, category values, and definition of a unique actor. Decide how retries, duplicates, late events, and nulls should behave. Validate the result with synthetic rows whose expected output you can calculate by hand.

The recipe pages include a schema, copyable SQL, deterministic example output, a visualization, edge cases, dashboard suggestions, and an alert interpretation. Start with API reliability recipes, product analytics recipes, or AI and LLM recipes.