Skip to content
Telemetry
API reliability SQL recipe

Rank error fingerprints by customer impact

Rank normalized application errors by occurrences and affected accounts instead of letting one retry loop dominate the incident view.

Beginnerapi_requestsReviewed 2026-07-27Tested with Apache DataFusion 45.2.0

Reviewed by the Telemetry product team on . We checked the SQL syntax, required event fields, sample results, and limits on using the query. Who reviews this page

Question answered

Which error groups affect the most customer accounts?

An error fingerprint groups equivalent failures. Counting both events and distinct accounts separates noisy repetition from broad customer impact.

Event schema

Fields the query expects

FieldTypeWhy it exists
timestamp_utcTimestampFailure time.
error_fingerprintUtf8Stable normalized error group.
team_idUtf8Safe affected account identifier.
status_codeInt64HTTP status code.
DataFusion SQL

Copy the query

sql
SELECT
  error_fingerprint,
  COUNT(*) AS occurrences,
  COUNT(DISTINCT team_id) AS affected_teams
FROM api_requests
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND status_code >= 500
  AND error_fingerprint IS NOT NULL
GROUP BY error_fingerprint
ORDER BY affected_teams DESC, occurrences DESC
LIMIT 20;

This read-only query is planned and executed against an empty typed table with Apache DataFusion 45.2.0. We review the synthetic sample output separately. Check field types, thresholds, and counting rules against your own data. Read the testing methodology.

Query result

Affected teams by error fingerprint

The tax-service timeout has less repetition than the OAuth error but much broader impact.

error_fingerprintoccurrencesaffected_teams
checkout.tax_service_timeout18473
sync.oauth_token_expired92018
report.pdf_render_failed4416

Synthetic example output. Run the query against your own event schema and thresholds before using it for operational decisions.

Affected teams by error fingerprint: static chart of synthetic affected_teams values from the Rank error fingerprints by customer impact example result
Download this SVG chart of the sample results for an article, runbook, or design review. Please credit Telemetry.

Reproduce the example

Download the sample data

The JSON bundle includes the event schema with field types, illustrative input rows, exact SQL, expected output, review notes, and engine version. The CSV contains the displayed result.

Sample JSONExpected output CSVStatic SVG chart

How the SQL works

  1. 1A stable fingerprint should remove request IDs and other volatile message fragments.
  2. 2COUNT DISTINCT team_id measures breadth while occurrences measures repetition.
  3. 3Ordering by affected accounts makes the first triage view closer to user impact.

Edge cases to check

  • Hashing an entire raw error message creates a new group whenever identifiers change.
  • Anonymous or unauthenticated requests need a different impact dimension.
  • Do not include raw stack traces or personal data in the grouping field.

Recommended dashboard

  • Bar: affected_teams by fingerprint
  • Table: occurrences and newest release
  • Trend: top fingerprints over time

Alert guidance

Alert on a new fingerprint affecting multiple accounts, not on every individual exception.

Read alert setup

Set up the events this query needs

Related instrumentation and guides

Sensitive-data redactionAPI error monitor templateAPI reliability recipes

Continue the analysis

Calculate API error rate by route

Rank API routes by 5xx error rate. Exclude routes with fewer than 20 requests so one failure does not dominate the results.

Open recipe

Calculate p50, p95, and p99 API latency

Compare median and tail latency by endpoint with DataFusion-compatible percentile SQL.

Open recipe

Calculate API error-budget burn rate

Turn hourly request failures into an SLO burn-rate series that shows how quickly the allowed error budget is being consumed.

Open recipe

Run it on your events

Create a table, adapt the fields, and save the result

Start free, send structured events, and use the query result as a chart, shared dashboard widget, or alert input.

Get an API key