DataFusion SQL Reference for Telemetry

Telemetry queries structured event tables with Apache DataFusion SQL. Telemetry's public recipes are currently planned and executed with Apache DataFusion 45.2.0 before publication. This reference describes the patterns exercised by that pinned test suite.

Field names and types still come from your event contract. Adapt every example to the table, units, statuses, identity rules, and time semantics that your application actually uses.

Start with a bounded read

Operational queries should usually start with a UTC time filter and return only fields needed by the question:

SELECT
  route_template,
  status_code,
  latency_ms,
  timestamp_utc
FROM api_requests
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
ORDER BY timestamp_utc DESC
LIMIT 100;

timestamp_utc is the server-managed query timestamp. A source-provided timestamp field is normalized during ingestion and should not replace the generated query column in recipes.

Use a LIMIT while inspecting raw rows. For a complete export, use the asynchronous Query API instead of removing every safeguard from an interactive query.

Create complete time buckets

date_trunc creates a stable grain for a trend:

SELECT
  date_trunc('hour', timestamp_utc) AS hour,
  COUNT(*) AS requests
FROM api_requests
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY date_trunc('hour', timestamp_utc)
ORDER BY hour;

The newest hour or day may still be filling. Exclude that bucket from an alert when partial volume would make the result misleading. Use the same time zone, bucket size, and completeness rule in every comparison.

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
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY route_template
HAVING COUNT(*) >= 20
ORDER BY error_rate_pct DESC;

NULLIF protects division from a zero denominator. Multiplying by 100.0 keeps percentage arithmetic from becoming integer division. A HAVING minimum prevents one failure in a quiet group from outranking a busy route with meaningful impact.

Percentiles and distributions

Use approx_percentile_cont(latency_ms, 0.95) for an efficient p95 estimate:

SELECT
  route_template,
  approx_percentile_cont(latency_ms, 0.50) AS p50_ms,
  approx_percentile_cont(latency_ms, 0.95) AS p95_ms,
  approx_percentile_cont(latency_ms, 0.99) AS p99_ms
FROM api_requests
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY route_template;

Compare p50 with p95 or p99. A similar increase across the distribution suggests a generally slower workflow; a larger tail change points to a subset of unusually slow operations. Percentiles are estimates, so avoid presenting insignificant decimal precision.

For a histogram-like result, use CASE to assign a numeric value to explicit buckets. Keep bucket labels and ordering columns separate so "1000+" does not sort before "250–499".

CTEs make definitions reviewable

Common table expressions separate a business definition from the final aggregation:

WITH account_activity AS (
  SELECT
    account_id,
    MIN(CASE WHEN event_name = 'signup_completed' THEN timestamp_utc END)
      AS signed_up_at,
    MIN(CASE WHEN event_name = 'activation_completed' THEN timestamp_utc END)
      AS activated_at
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
  GROUP BY account_id
)
SELECT
  COUNT(*) AS signed_up_accounts,
  SUM(CASE WHEN activated_at IS NOT NULL THEN 1 ELSE 0 END)
    AS activated_accounts
FROM account_activity
WHERE signed_up_at IS NOT NULL;

Inspect an intermediate CTE by temporarily selecting from it. This is often the fastest way to catch duplicated identities, unexpected nulls, or a milestone definition that includes the wrong rows.

Joins need an explicit grain

Before joining event tables, state what one row represents on each side. A many-to-many join can multiply counts while still returning valid SQL.

Pre-aggregate to one row per account, request, job, webhook delivery, or billing period before joining when that is the unit of analysis. Use stable identifiers created for correlation, and never join on a display label merely because it looks unique.

After a join, compare:

row count before and after
distinct identifier count before and after
unmatched rows on both sides
totals against a fixture with a known result

Window functions

Window functions retain row or bucket detail while comparing adjacent values:

WITH daily AS (
  SELECT
    date_trunc('day', timestamp_utc) AS day,
    COUNT(*) AS completed_jobs
  FROM job_events
  WHERE status = 'completed'
    AND timestamp_utc >= now() - INTERVAL '30 days'
  GROUP BY date_trunc('day', timestamp_utc)
)
SELECT
  day,
  completed_jobs,
  LAG(completed_jobs) OVER (ORDER BY day) AS previous_day_jobs,
  AVG(completed_jobs) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_bucket_average
FROM daily
ORDER BY day;

LAG exposes a previous value. A framed AVG creates a rolling baseline. Early rows have incomplete windows; decide whether to show them, suppress them, or label them before creating an alert.

Nested fields and identifiers

Telemetry exposes nested JSON through dotted field paths. If a dotted path needs quoting in the current table schema, use a double-quoted identifier such as "data.tool.name". Read Querying nested JSON and inspect the table schema before copying a nested-field query.

Use snake_case field names and avoid reserved or ambiguous words when designing new events. If an existing field requires quoting, quote it consistently rather than creating two spellings of the same concept.

Supported patterns and pinned limits

The tested recipe suite exercises SELECT, CTEs, joins, CASE, common aggregates, date_trunc, intervals, approximate percentiles, LAG, framed windows, NULLIF, COALESCE, ordering, grouping, and limits.

DataFusion is not PostgreSQL, MySQL, BigQuery, or Snowflake. Similar-looking functions can have different names or signatures. The pinned planner used by Telemetry's recipe audit does not accept every aggregate modifier or date helper found in those systems. Prefer syntax demonstrated in this reference and the tested SQL recipe library, then run a small query before adapting an example from another dialect.

Query review checklist

Before saving a query or using it in an alert:

Confirm the table, columns, types, units, and UTC time range.
Define the actor or workflow identifier and verify the row grain.
Decide how retries, duplicates, late events, nulls, and incomplete buckets behave.
Protect ratios with a denominator check and a minimum meaningful volume.
Inspect intermediate CTEs and joined row counts.
Test synthetic success, failure, retry, duplicate, and boundary cases.
Record the definition, owner, threshold, and expected response beside the result.

Every recipe includes a schema, copyable SQL, deterministic synthetic output, a visualization, interpretation notes, edge cases, dashboard suggestions, and alert guidance. Read the SQL testing methodology, then start with API reliability, product analytics, data quality, or infrastructure.

Use this doc with your coding agent