Potential Inconsistency in "DORA Details - Time to Restore Service" Dashboard Queries

[rodrigoluizs]

🔍 Observation

Hi everyone,

While reviewing the "DORA Details - Time to Restore Service" dashboard, I noticed a potential inconsistency compared to the main DORA dashboard. I’d like to confirm if this is intentional or something that might need adjustment.

In the first two panels of the dashboard:

1. Panel: Median Time to Restore Service
2. Panel: Incident Count

A CTE named _deployments is defined, but it is never used anywhere in the query.

Here’s the query from the Median Time to Restore Service panel:

--  ***** 2021 report ***** --
-- Metric 4: Median time to restore service 
with _deployments as ( -- UNUSED CTE
  SELECT
    cdc.cicd_deployment_id as deployment_id,
    max(cdc.finished_date) as deployment_finished_date
  FROM
    cicd_deployment_commits cdc
    JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
    and pm.`table` = 'cicd_scopes'
  WHERE
    pm.project_name in ($project)
    and cdc.result = 'SUCCESS'
    and cdc.environment = 'PRODUCTION'
  GROUP BY
    1
  HAVING
    $__timeFilter(max(cdc.finished_date))
),
-- Used CTEs below
_incidents as (
  -- get the incidents created within the selected time period in the top-right corner
  SELECT
    distinct i.id,
    cast(lead_time_minutes as signed) as lead_time_minutes
  FROM
    incidents i
    join project_mapping pm on i.scope_id = pm.row_id
    and pm.`table` = i.`table`
  WHERE
    pm.project_name in (${project})
    and $__timeFilter(i.resolution_date)
),
_median_mttr_ranks as(
  SELECT
    *,
    percent_rank() over(
      order by
        lead_time_minutes
    ) as ranks
  FROM
    _incidents
),
_median_mttr as(
  SELECT
    max(lead_time_minutes) as median_time_to_resolve
  FROM
    _median_mttr_ranks
  WHERE
    ranks <= 0.5
)
SELECT
  median_time_to_resolve / 60 AS median_time_to_resolve_in_hours
FROM
  _median_mttr

The same happens in the Incident Count panel — _deployments is declared and not referenced.

In the third panel (Time to Restore Service Details), there is no _deployments CTE at all, and the query is solely based on incidents.

---

⚖️ Comparison with Main DORA Dashboard

In contrast, the main DORA dashboard filters incidents by associated deployments using the project_incident_deployment_relationships table, here's part of the query taken from the "Failed Deployment Recovery Time In Hours" panel:

FROM
  incidents i
  LEFT JOIN project_incident_deployment_relationships pim ON i.id = pim.id
  JOIN _deployments fd ON pim.deployment_id = fd.deployment_id

This ensures that only incidents tied to a successful production deployment are considered.

💡 Side note: Because the join to _deployments is an inner join, any incident without a related deployment in project_incident_deployment_relationships will be excluded. In practice, this means the preceding LEFT JOIN to project_incident_deployment_relationships has no effect — the query behaves like an inner join overall.

---

❓ My Question

Should the metrics in the "DORA Details - Time to Restore Service" dashboard also be filtered based on associated deployments (like in the main DORA dashboard)? Or is the current implementation (which doesn’t consider deployments at all) intentional?

Thanks in advance for any clarification!

[Startrekzky]

Hi @rodrigoluizs , good catch.

It seems in DORA dashboard, the incidents calculated in MTTR are the ones that have been mapped to deployments; while in MTTR's detailed dashboard, the incidents are filtered only by their resolution date.

I think both approaches make sense and could apply to the specific scenarios.