Aggregation engines: origin-anchored dynamic day granularity (Xd) for date_bucket dimensions

Sreeram Narayananrequested to merge
609138-xd-granularity-framework into master

What does this MR do and why?

Dashboards need to compare "last 30 days" against "the previous 30 days" in a single aggregation request. Calendar granularities (daily/weekly/monthly) cannot express windows anchored at arbitrary dates, so this is the first of two MRs implementing https://gitlab.com/gitlab-org/gitlab/-/work_items/609138, which adds origin-anchored dynamic day granularity (Xd) to date_bucket dimensions.

This MR adds the framework capability only and changes no production behavior: no aggregation engine adopts the new values (their explicit parameters: declarations are unchanged), there is no GraphQL schema change, no generated artifact changes, no database migrations, and no changelog entry (the user-facing entry ships with part 2). Part 2 removes the per-engine granularity declarations, introduces built-in default parameters on the dimension, and exposes the feature through GraphQL.

The framework layer in lib/gitlab/database/aggregation/ gains:

  • ClickHouse::DateBucketDimension accepts a dynamic day granularity matching /\A(?<days>\d{1,3})d\z/ (for example 30d), capped at 1 to 366 days, compiling to toStartOfInterval(column, INTERVAL 30 DAY) (buckets aligned to the Unix epoch).
  • An optional origin (:datetime) parameter anchors dynamic day buckets via the three-argument form. ClickHouse requires the origin to be on or before every bucketed value, so the engine shifts the user origin to its phase-equivalent anchor near the Unix epoch (origin Unix timestamp modulo the interval) and emits toStartOfInterval(column, INTERVAL 30 DAY, toDateTime64('<anchor>', 6, 'UTC')). Bucket boundaries are identical to anchoring at the user origin; rows older than the origin land in earlier buckets instead of failing the query; origin never filters rows. The three-argument form requires ClickHouse 24.9 or later (CI runs 25.12 and 26.2).
  • Origin values are normalized (String, Date, Time, ActiveSupport::TimeWithZone all become UTC whole-second times), so a GraphQL Time argument and the same value as a JSON string in orderBy.parameters produce identical result keys.
  • ParameterizedDefinition in: allowlists accept Regexp entries mixed with strings. Patterns are matched with RE2 through Gitlab::UntrustedRegexp (no backtracking, so request values cannot trigger ReDoS), compiled once per definition at class-load time. Ruby flags are translated (/i to (?i), /m to RE2's s); extended mode (/x) and ^/$ anchors are rejected when the definition is built (use \A/\z).
  • Validations: day count outside 1..366 rejected (the digit-bounded pattern also rejects arbitrarily long digit strings before integer parsing); origin with a calendar granularity or without a granularity rejected; unparseable origin rejected; one error per field.

Generated SQL, for database reviewers:

-- calendar granularity (unchanged)
toStartOfInterval(column, INTERVAL 1 month)

-- new: dynamic day granularity, epoch-aligned
toStartOfInterval(column, INTERVAL 30 DAY)

-- new: dynamic day granularity with origin (anchor = origin mod interval, near 1970)
toStartOfInterval(column, INTERVAL 30 DAY, toDateTime64('1970-01-26 00:00:00', 6, 'UTC'))

References

Screenshots or screen recordings

Before After

How to set up and validate locally

  1. Set up ClickHouse in GDK (GDK howto): gdk config set clickhouse.enabled true, set clickhouse.bin, then gdk reconfigure && gdk start clickhouse, and:

    echo "CREATE DATABASE IF NOT EXISTS gitlab_clickhouse_development" | curl -s --data-binary @- http://127.0.0.1:8123/
bundle exec rake gitlab:clickhouse:migrate

  2. Seed rows in rails console (any project works):

    project = Project.first
path = project.project_namespace.traversal_path(with_organization: true).to_s
rows = [[9001, 80], [9002, 40], [9003, 10]].map do |id, days|
  t = days.days.ago.utc.strftime('%Y-%m-%d %H:%M:%S')
  "(#{id}, 100, #{project.id}, '#{path}', 'main', 1, 'success', 60, '#{t}', '#{t}', now(), false)"
end.join(', ')
ClickHouse::Client.execute(<<~SQL, :main)
  INSERT INTO siphon_p_ci_pipelines
    (id, partition_id, project_id, traversal_path, ref, source, status, duration,
     started_at, finished_at, _siphon_replicated_at, _siphon_deleted)
  VALUES #{rows}
SQL

  3. Build a throwaway engine in the same console. On this branch the parameters must be declared explicitly (built-in defaults arrive in part 2):

    engine_class = Gitlab::Database::Aggregation::ClickHouse::Engine.build do
  self.table_name = 'siphon_p_ci_pipelines'
  dimensions do
    date_bucket :started_at, :datetime, parameters: {
      granularity: { type: :string,
        in: ['daily', 'weekly', 'monthly',
          Gitlab::Database::Aggregation::ClickHouse::DateBucketDimension::DYNAMIC_GRANULARITY_FORMAT] },
      origin: { type: :datetime }
    }
  end
  metrics { count }
end
engine = engine_class.new(context: { scope: ClickHouse::Client::QueryBuilder.new('siphon_p_ci_pipelines') })

origin = 30.days.ago.beginning_of_day.utc
request = Gitlab::Database::Aggregation::Request.new(
  dimensions: [{ identifier: :started_at, parameters: { granularity: '30d', origin: origin } }],
  metrics: [{ identifier: :total_count }]
)
response = engine.execute(request)
response[:data].to_a

    Expected: three rows with bucket values origin - 60.days, origin - 30.days, and origin, one per seeded pipeline — the two pipelines older than the origin land in earlier buckets instead of failing the query. Swap the parameters for { granularity: 'monthly', origin: origin } or { granularity: '367d' } to see the validation errors (response.error? / response[:errors]).

  4. Or run the specs directly (needs a ClickHouse server on 127.0.0.1:8123 and config/click_house.yml created from the example file, with localhost changed to 127.0.0.1): 18 examples in date_bucket_dimension_spec.rb and 29 in parameterized_definition_spec.rb, all executed against a real ClickHouse server:

    bundle exec rspec spec/lib/gitlab/database/aggregation/click_house/date_bucket_dimension_spec.rb \
                  spec/lib/gitlab/database/aggregation/parameterized_definition_spec.rb

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.

Related to #609138

Summary

This update adds two new features to the date bucketing dimension used in analytics aggregations:

Dynamic day granularity: Instead of only supporting fixed calendar periods like "weekly" or "monthly," users can now specify custom intervals in days (e.g., 30d, 7d, 366d). Valid values are between 1 and 366 days. Buckets are aligned to the Unix epoch by default.

Origin parameter: Users can optionally provide an origin datetime to anchor bucket boundaries to a specific point in time (e.g., "today"), which is useful for current-vs-previous period comparisons. The origin is automatically shifted back to a phase-equivalent point near the Unix epoch so that older data still falls into valid buckets rather than causing query errors. Origin only works with dynamic day granularities, not calendar-based ones.

Security improvements: The allowlist validation logic was hardened to support Regexp entries safely. Any regular expressions used in allowlists are compiled using RE2 (which prevents catastrophic backtracking / ReDoS attacks), and patterns using unsafe anchors (^/$) or extended mode are rejected at startup rather than at request time.

Appropriate validation error messages are surfaced for all invalid combinations, and the new .pot locale file entries make those messages translatable.

Edited by Sreeram Narayanan

Merge request reports