Event contract
Fields the query expects
| Field | Type | Why it exists |
|---|---|---|
| timestamp_utc | Timestamp | When the request completed, normalized to UTC. |
| route_template | Utf8 | Stable route shape such as /api/projects/:id. |
| status_code | Int64 | HTTP response status code. |
| latency_ms | Float64 | Request duration in milliseconds. |
Copy the query
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
LIMIT 10;This read-only query is planned and executed against an empty typed table with Apache DataFusion 45.2.0. The deterministic sample output is synthetic and reviewed separately; validate field types, thresholds, and business definitions against your own data. Read the testing methodology.
Query result
5xx error rate by route
The checkout route is the clearest reliability risk even though search has more total traffic.
| route_template | requests | errors | error_rate_pct |
|---|---|---|---|
| /api/checkout | 25 | 5 | 20 |
| /api/search | 25 | 1 | 4 |
| /api/profile | 20 | 0 | 0 |
Synthetic example output. Run the query against your own event schema and thresholds before using it for operational decisions.
Reproduce the example
Download the public fixture
The JSON bundle includes the typed event contract, reproducible input rows, exact SQL, expected output, review notes, and engine version. The CSV contains the displayed result.
How the SQL works
- 1The CASE expression counts only responses with a 5xx status while retaining the total request count in the same group.
- 2NULLIF prevents division by zero. The HAVING clause excludes routes with fewer than 20 requests, where a single error would create an unstable percentage.
- 3Use route templates rather than raw URLs so identifiers do not split one endpoint into thousands of groups.
Edge cases to decide
- Choose a minimum request threshold that matches your traffic. Twenty requests is illustrative, not universal.
- Separate expected 5xx responses from infrastructure failures if your API intentionally uses them for workflow control.
- Segment by environment so staging traffic cannot change the production rate.
Recommended dashboard
- Bar chart: error_rate_pct by route_template
- Stat: total 5xx responses in the selected period
- Table: newest failed requests with request_id and error_type
Alert guidance
Alert when error_rate_pct exceeds 2% for a route with at least 100 requests in the evaluation window.
Read alert setupPut the recipe to work
Related instrumentation and guides
Continue the analysis
Calculate p50, p95, and p99 API Latency
Compare median and tail latency by endpoint with DataFusion-compatible percentile SQL.
Open recipeCalculate 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 recipeCompare API Reliability by Release
Compare traffic, 5xx rate, and p95 latency across application releases without attributing every post-deploy change to the deploy.
Open recipeRun it on real 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.