Aggregate DORA metrics from ClickHouse behind a flag

What does this MR do and why?

DORA metrics for large groups time out: Dora::AggregateMetricsService reads dora_daily_metrics for the environments of every project in the hierarchy. This MR is step 2 of the plan in https://gitlab.com/gitlab-org/gitlab/-/work_items/636172. Behind the new dora_metrics_clickhouse_aggregation flag (gitlab_com_derisk, off by default, checked against the root namespace), it aggregates the same dora_daily_metrics rows in ClickHouse instead, scoped by traversal_path.

Builds on !268292 (merged) (merged), which replicates dora_daily_metrics to ClickHouse through Siphon, and !266291 (merged) (merged), which added the result cache.

  • Analytics::DoraMetricsAggregator chooses the source. GraphQL doraMetrics, REST dora/metrics, VSA and Insights all go through it, and the result cache wraps both paths.
  • Analytics::DoraMetricsAggregator::ClickHouseQuery reads the latest version of each siphon_dora_daily_metrics row, joins siphon_environments for the tier and project filters, and aggregates the rows the same way Dora::DailyMetrics.aggregate_for! does.
  • The AggregateMetricsService cache key includes the source the aggregator actually uses, so results from the two backends never share an entry.

ClickHouse is used only when all of these hold. Otherwise the request stays on PostgreSQL:

  • the caller passes the container. DoraPerformanceScoreCalculator doesn't, so performance scores stay on PostgreSQL.
  • ClickHouse is enabled for analytics and the flag is on for the root namespace.
  • Gitlab::ClickHouse.siphon_enabled? is true for environments and dora_daily_metrics.

If the ClickHouse query fails, the error goes to error tracking and the request falls back to PostgreSQL. The checks above can't catch an outage, because siphon_enabled? caches a positive result for the life of the process.

Because ClickHouse aggregates the stored rows, the results match PostgreSQL exactly, including the dora_configurations branch filter and how incidents are stored per environment. Both are applied when PostgreSQL refreshes the rows.

Database

PostgreSQL
SELECT
    date AS interval_date,
    SUM(deployment_frequency) AS metric_deployment_frequency,
    (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lead_time_for_changes_in_seconds)) AS metric_lead_time_for_changes,
    (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY time_to_restore_service_in_seconds)) AS metric_time_to_restore_service,
    SUM(incidents_count)::float / GREATEST(SUM(deployment_frequency), 1) AS metric_change_failure_rate
FROM "dora_daily_metrics"
WHERE "dora_daily_metrics"."environment_id" IN (
        SELECT "environments"."id"
        FROM "environments"
        WHERE "environments"."project_id" IN (
                SELECT "projects"."id"
                FROM "projects"
                WHERE "projects"."namespace_id" IN (
                        SELECT "namespaces"."id"
                        FROM UNNEST(
                            COALESCE(
                                (SELECT ids FROM (
                                    SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids
                                    FROM "namespace_descendants"
                                    WHERE "namespace_descendants"."outdated_at" IS NULL
                                      AND "namespace_descendants"."namespace_id" = 9970
                                ) cached_query),
                                (SELECT ids FROM (
                                    SELECT ARRAY_AGG("namespaces"."id") AS ids
                                    FROM (
                                        SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                                        FROM "namespaces"
                                        WHERE "namespaces"."type" = 'Group'
                                          AND (traversal_ids @> ('{9970}'))
                                    ) namespaces
                                ) consistent_query)
                            )
                        ) AS namespaces(id)
                    )
            )
          AND "environments"."tier" = 0
          AND "environments"."tier" IS NOT NULL
    )
  AND "dora_daily_metrics"."date" BETWEEN '2026-05-10' AND '2026-11-06'
GROUP BY "dora_daily_metrics"."date"
ORDER BY interval_date ASC;
ClickHouse
SELECT
    toDate(daily_metrics.date) AS interval_date,
    sumOrNull(daily_metrics.deployment_frequency) AS deployment_frequency,
    quantileExactInclusiveOrNull(0.5)(daily_metrics.lead_time_for_changes_in_seconds) AS lead_time_for_changes,
    quantileExactInclusiveOrNull(0.5)(daily_metrics.time_to_restore_service_in_seconds) AS time_to_restore_service,
    sumOrNull(daily_metrics.incidents_count) / greatest(ifNull(sumOrNull(daily_metrics.deployment_frequency), 1), 1) AS change_failure_rate
FROM
(
    SELECT
        siphon_dora_daily_metrics.id AS id,
        tupleElement(argMax(tuple(siphon_dora_daily_metrics.environment_id), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS environment_id,
        tupleElement(argMax(tuple(siphon_dora_daily_metrics.date), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS date,
        tupleElement(argMax(tuple(siphon_dora_daily_metrics.deployment_frequency), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS deployment_frequency,
        tupleElement(argMax(tuple(siphon_dora_daily_metrics.lead_time_for_changes_in_seconds), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS lead_time_for_changes_in_seconds,
        tupleElement(argMax(tuple(siphon_dora_daily_metrics.time_to_restore_service_in_seconds), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS time_to_restore_service_in_seconds,
        tupleElement(argMax(tuple(siphon_dora_daily_metrics.incidents_count), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS incidents_count
    FROM siphon_dora_daily_metrics
    WHERE startsWith(siphon_dora_daily_metrics.traversal_path, '1/9970/') AND (siphon_dora_daily_metrics.date >= toDate32('2026-05-10')) AND (siphon_dora_daily_metrics.date <= toDate32('2026-11-06'))
    GROUP BY siphon_dora_daily_metrics.id
    HAVING argMax(siphon_dora_daily_metrics._siphon_deleted, siphon_dora_daily_metrics._siphon_replicated_at) = 0
) AS daily_metrics
INNER JOIN
(
    SELECT *
    FROM
    (
        SELECT
            siphon_environments.id AS id,
            tupleElement(argMax(tuple(siphon_environments.project_id), siphon_environments._siphon_replicated_at), 1) AS project_id,
            tupleElement(argMax(tuple(siphon_environments.tier), siphon_environments._siphon_replicated_at), 1) AS tier
        FROM siphon_environments
        WHERE startsWith(siphon_environments.traversal_path, '1/9970/')
        GROUP BY siphon_environments.id
        HAVING argMax(siphon_environments._siphon_deleted, siphon_environments._siphon_replicated_at) = 0
    ) AS environments
    WHERE environments.tier IN (0)
) AS environments ON environments.id = daily_metrics.environment_id
GROUP BY interval_date
ORDER BY interval_date ASC
How the query matches dora_daily_metrics
  • Scope: PostgreSQL filters dora_daily_metrics by the environments of the projects in scope and their tier. The query does the same with siphon_environments, scoped by traversal_path and filtered by tier and group_project_ids.
  • Range: date is in the ClickHouse key and never changes, so the date range is applied before the dedup, inclusive on both ends like BETWEEN.
  • Aggregates: sumOrNull for deployment frequency and incidents, quantileExactInclusiveOrNull(0.5) for the medians, which matches PERCENTILE_CONT(0.5), and SUM(incidents) / GREATEST(SUM(deployments), 1) for change failure rate.
  • Interval all: with no data, PostgreSQL still returns one row of NULLs, and so does the query.
  • Replicated rows: rows are deduplicated with argMax(..., _siphon_replicated_at), and deleted rows are dropped. Nullable columns go through tuple(), because plain argMax skips NULL values.
Verified on GitLab.com
  • Siphon replication of dora_daily_metrics is complete.
  • Query cost on gitlab-org: the ClickHouse query above ran in 592 ms and read 573,742 rows (35.19 MB), compared with the 23 M rows the earlier source-table version read.

No changelog entry: the change is behind a flag that is off by default.

References

Screenshots or screen recordings

https://gitlab.com/gitlab-org/gitlab/-/work_items/636172#note_4159743344

How to set up and validate locally

  1. Make sure ClickHouse and Siphon run in your GDK, and Admin > Settings > General > Analytics > Use ClickHouse for analytics is on.

  2. Run bundle exec rake gitlab:clickhouse:migrate, then gdk reconfigure and gdk restart siphon, so dora_daily_metrics replicates (from !268292 (merged)).

  3. Pick a group with DORA data, and note its numbers on Analyze > Analytics dashboards > DORA metrics.

  4. In the Rails console, enable the flag for the group's root namespace:

    group = Group.find_by_full_path('your-group')
    Feature.enable(:dora_metrics_clickhouse_aggregation, group.root_ancestor)
  5. Compare both sources for the same range:

    params = { metrics: Dora::DailyMetrics::AVAILABLE_METRICS, interval: 'daily',
               start_date: 90.days.ago.to_date, end_date: Date.current, environment_tiers: %w[production] }
    pg = Analytics::DoraMetricsAggregator.aggregate_for(projects: group.all_projects, **params)
    ch = Analytics::DoraMetricsAggregator.aggregate_for(container: group, project_ids: [], projects: group.all_projects, **params)
    pg == ch
  6. Reload the dashboard. The numbers are unchanged, and log/development.log shows no dora_daily_metrics query.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Serhii Yarynovskyi

Merge request reports

Loading
Loading