Analytics
Analytics
DORA metrics, engineering KPIs, and on-demand SQL aggregation — powered by DuckDB running in-process. No external analytics service required.
Analytics Pipeline
What & Why
DuckDB runs in-process — there is no external analytics service to provision, scale, or operate. The analytics layer lives entirely inside the Constellation API process, using DuckDB 1.1.3 as an embedded OLAP engine.
On-demand SQL aggregation — raw JSON data fetched from SaaS integration adapters (GitHub, Jira, PagerDuty, billing systems) is loaded into DuckDB in-memory tables and aggregated using SQL queries. Results are cached in Redis DB 0 with a configurable TTL so repeated queries for the same metric + time range are served instantly.
DORA metrics give engineering teams objective delivery performance data — the four DORA metrics (Deployment Frequency, Lead Time for Changes, Change Failure Rate, MTTR) are industry-standard benchmarks from the DevOps Research & Assessment program. They turn subjective assessments of engineering health into measurable numbers tied to real deployment and incident data.
Additional metric categories include:
| Category | Examples |
|---|---|
| Engineering | PR cycle time, code review turnaround, CI/CD pipeline health |
| HR | Headcount by team, on-call rotation load, incident response times |
| Finance | Cloud spend analysis, budget variance (requires billing integration) |
| Quality | Defect rates, bug aging, SLA compliance |
DORA Metrics — Four Key Measures of Software Delivery Performance
count(deployments) / time_window
Source: GitHub Actions workflow runs
merge_time − first_commit_time
Source: GitHub PR data
count(rollbacks) / count(deploys)
Source: GitHub Actions workflow runs
mean(resolved − created)
Source: Jira / PagerDuty incidents
Benchmarks from the DORA State of DevOps Report (Elite / High / Medium / Low performer tiers)
DuckDB Aggregation Flow
JSON arrays
GitHub deploys
Jira issues
PagerDuty incidents
CREATE TABLE AS
SELECT * FROM
read_json_auto(...)
GROUP BY team, repo
COUNT / AVG / MEDIAN
date_trunc windows
metric value
trend (delta %)
breakdown by team
cached in Redis DB 0
API Reference
Endpoint
POST /v1/analytics/query
Request Schema
{
"metric": "string",
"time_range": {
"start": "ISO 8601 datetime",
"end": "ISO 8601 datetime"
},
"filters": {
"team": "string (optional)",
"repo": "string (optional)",
"project": "string (optional)"
}
}
Response Schema
{
"metric": "deployment_frequency",
"value": 2.4,
"unit": "deploys/day",
"trend": {
"delta": 0.3,
"direction": "up",
"period": "vs previous 30d"
},
"breakdown": [
{ "label": "team-platform", "value": 1.8 },
{ "label": "team-backend", "value": 0.6 }
],
"time_range": {
"start": "2026-04-01T00:00:00Z",
"end": "2026-05-01T00:00:00Z"
},
"cached": false
}
Available Metric Names
| Metric Name | Category | Data Sources |
|---|---|---|
deployment_frequency | DORA | GitHub Actions workflow runs |
lead_time_for_changes | DORA | GitHub PR data (merge time, first commit time) |
change_failure_rate | DORA | GitHub Actions (rollback workflow runs) |
mttr | DORA | Jira incidents, PagerDuty alerts |
pr_cycle_time | Engineering | GitHub PR open→merge timestamps |
code_review_turnaround | Engineering | GitHub PR review request→approval timestamps |
test_coverage_trend | Engineering | CI/CD pipeline test report artifacts |
ci_pipeline_health | Engineering | GitHub Actions success/failure rates |
headcount_by_team | HR | GitHub team membership |
oncall_rotation_load | HR | PagerDuty on-call schedules |
incident_response_time | HR | PagerDuty alert → acknowledgement timestamps |
cloud_spend | Finance | Billing system integration (AWS / GCP / Azure) |
budget_variance | Finance | Billing system integration |
defect_rate | Quality | Jira bug issue counts vs closed issues |
bug_aging | Quality | Jira bug issue created → resolved times |
sla_compliance | Quality | Jira SLA fields |
Example: Query Deployment Frequency (DORA)
curl -X POST https://api.example.com/v1/analytics/query \
-H "Authorization: Bearer <token>" \
-H "Content-Type: application/json" \
-d '{
"metric": "deployment_frequency",
"time_range": {
"start": "2026-04-01T00:00:00Z",
"end": "2026-05-01T00:00:00Z"
},
"filters": {
"repo": "myorg/backend"
}
}'
Example: Query Lead Time for Changes (DORA)
curl -X POST https://api.example.com/v1/analytics/query \
-H "Authorization: Bearer <token>" \
-H "Content-Type: application/json" \
-d '{
"metric": "lead_time_for_changes",
"time_range": {
"start": "2026-04-01T00:00:00Z",
"end": "2026-05-01T00:00:00Z"
},
"filters": {
"team": "platform"
}
}'
Example: Query Change Failure Rate (DORA)
curl -X POST https://api.example.com/v1/analytics/query \
-H "Authorization: Bearer <token>" \
-H "Content-Type: application/json" \
-d '{
"metric": "change_failure_rate",
"time_range": {
"start": "2026-04-01T00:00:00Z",
"end": "2026-05-01T00:00:00Z"
}
}'
Example: Query MTTR (DORA)
curl -X POST https://api.example.com/v1/analytics/query \
-H "Authorization: Bearer <token>" \
-H "Content-Type: application/json" \
-d '{
"metric": "mttr",
"time_range": {
"start": "2026-04-01T00:00:00Z",
"end": "2026-05-01T00:00:00Z"
},
"filters": {
"project": "PLATFORM"
}
}'
CLI
# Deployment frequency for last 30 days
constellation analytics query --metric deployment_frequency \
--start 2026-04-01 --end 2026-05-01 --repo myorg/backend
# Lead time with team filter
constellation analytics query --metric lead_time_for_changes \
--start 2026-04-01 --end 2026-05-01 --team platform
# Change failure rate across all repos
constellation analytics query --metric change_failure_rate \
--start 2026-04-01 --end 2026-05-01
# MTTR from Jira project
constellation analytics query --metric mttr \
--start 2026-04-01 --end 2026-05-01 --project PLATFORM