Event contract
Fields the query expects
| Field | Type | Why it exists |
|---|---|---|
| timestamp_utc | Timestamp | Attempt completion time. |
| job_id | Utf8 | Stable logical job identifier. |
| job_name | Utf8 | Stable job type. |
| attempt | Int64 | One-based attempt number. |
| status | Utf8 | completed, retrying, or failed. |
Copy the query
SELECT
date_trunc('hour', timestamp_utc) AS hour,
job_name,
COUNT(*) AS attempts,
COUNT(DISTINCT job_id) AS logical_jobs,
SUM(CASE WHEN attempt > 1 THEN 1 ELSE 0 END) AS retry_attempts,
100.0 * SUM(CASE WHEN attempt > 1 THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS retry_attempt_rate_pct,
100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS failed_attempt_rate_pct
FROM job_attempts
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY date_trunc('hour', timestamp_utc), job_name
HAVING COUNT(*) >= 20
ORDER BY retry_attempt_rate_pct DESC, attempts DESC;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
Retry-attempt rate by job
CRM synchronization produces far more attempts than logical jobs and is consuming worker capacity with repeated work.
| hour | job_name | attempts | logical_jobs | retry_attempts | retry_attempt_rate_pct | failed_attempt_rate_pct |
|---|---|---|---|---|---|---|
| 2026-07-27 14:00 | sync_crm_accounts | 1,840 | 492 | 1,118 | 60.76 | 22.34 |
| 2026-07-27 14:00 | send_receipt_email | 3,180 | 3,061 | 119 | 3.74 | 0.44 |
Synthetic example output. Run the query against your own event schema and thresholds before using it for operational decisions.
How the SQL works
- 1Distinct job IDs estimate useful logical work while the raw count measures worker attempts.
- 2Attempt numbers above one isolate repeated work without treating an eventual success as a first attempt.
- 3Failure rate stays beside retry share so a deliberately retried transient dependency is distinguishable from terminal failure.
Edge cases to decide
- Fan-out jobs can legitimately create several child IDs; define the logical work identifier before comparing counts.
- Immediate retries and scheduled backoff have different capacity effects and may need separate fields.
- A retry storm can move between hourly buckets; add a shorter operational query when response time matters.
Recommended dashboard
- Bars: retry_attempt_rate_pct by job
- Trend: attempts and logical_jobs
- Table: jobs with the most attempts and newest error type
Alert guidance
Alert when retry share, attempt volume, and queue age are elevated together for consecutive complete buckets.
Read alert setupPut the recipe to work
Related instrumentation and guides
Continue the analysis
Measure Background Job Retry and Failure Rate
Find unreliable jobs by comparing successful runs, retries, failures, and tail duration.
Open recipeFind Stalled Background Jobs With SQL
Join job start and finish events to identify work that exceeded its expected completion window.
Open recipeMeasure Queue Wait Time by Job
Separate time spent waiting in a queue from execution duration and compare p50 and p95 delay by job name.
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.