Event contract
Fields the query expects
| Field | Type | Why it exists |
|---|---|---|
| timestamp_utc | Timestamp | Request completion time in UTC. |
| route_template | Utf8 | Stable route template. |
| latency_ms | Float64 | Request duration in milliseconds. |
| status_code | Int64 | HTTP response status code. |
Copy the query
SELECT
route_template,
COUNT(*) AS requests,
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'
AND status_code < 500
GROUP BY route_template
HAVING COUNT(*) >= 50
ORDER BY p95_ms 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
p95 latency by route
Export requests have the slowest tail and the largest gap between typical and worst-case performance.
| route_template | requests | p50_ms | p95_ms | p99_ms |
|---|---|---|---|---|
| /api/reports/export | 50 | 800 | 800 | 800 |
| /api/projects/:id/sync | 50 | 320 | 320 | 320 |
| /api/search | 50 | 120 | 120 | 120 |
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
- 1approx_percentile_cont uses DataFusion's t-digest implementation, which is efficient for large event tables.
- 2Filtering out 5xx responses makes this a successful-request latency view. Create a separate failed-request view when failure duration matters.
- 3The difference between p50 and p95 is often more useful than either number alone: a wide gap points to intermittent slow paths.
Edge cases to decide
- Do not compare endpoints with fundamentally different work without preserving route context.
- Streaming routes and long-running exports may need separate service-level objectives.
- Keep latency in a consistent unit and use a numeric field.
Recommended dashboard
- Grouped bars: p50_ms and p95_ms by route_template
- Line chart: p95_ms over time for the slowest routes
- Table: individual requests above the route's p99
Alert guidance
Alert when a high-volume route's p95 exceeds its service objective for three complete time buckets.
Read alert setupPut the recipe to work
Related instrumentation and guides
Define the source data
Event schemas for this analysis
Continue the analysis
Calculate API Error Rate by Route
Use SQL to rank API routes by 5xx error rate while protecting the result from low-volume noise.
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.