pr-consistency-config.yaml

metric: pr-consistency
description: Calculate PR flakiness for each day.
query: |
  select /* Calculate the prob a commit flaked on at least one PR job at some point. + prob that a `/test all` command flakes. */
    timestamp_to_sec(day) day,
    round(sum(consistent)/count(consistent),4) commit_consistency,
    sum(consistent) consistent_commits,
    count(consistent) commits,
    round(sum(consistent_runs)/sum(runs), 4) run_consistency, /* should be approx. the nth root of consistency if there are n PR jobs and assumptions hold (job flake independence over the same commit). */
    sum(consistent_runs) consistent_runs,
    sum(runs) runs,
    sum(builds) builds
  from ( /* For each day, count whether a (commit) flaked */
    select
      day,
      commit,
      min(if(passed == runs or passed == 0, 1, 0)) consistent,
      max(runs) builds,
      sum(runs) runs,
      sum(if(passed==0, runs, passed)) consistent_runs
    from (
      select /* For each day, count runs and passes for a (job, commit) */
        cast(day as timestamp) day,
        commit,
        sum(if(result=='SUCCESS',1,0)) passed,
        INTEGER(count(result)) runs
      from (
        SELECT /* Find jobs, noting its commit, day it ran, and whether it passed */
          job,
          regexp_extract(metadata.value, r'^[^,]+,\d+:([^,"]+)') commit, /* git hash of pr */
          result,
          cast(date(started) as date) day
        FROM [k8s-gubernator:build.all]
        where
          /* Select results that have occurred on or after the day of <LAST_DATA_TIME> (those results were
             for the day before), but don't include results from today, only return results for complete days. */
          cast(date(started) as date) >= cast(date(sec_to_timestamp(<LAST_DATA_TIME>)) as date)
          and cast(date(started) as date) < cast(current_date() as date) /* Drop results from partial day */
          and metadata.key = 'repos'
          and length(regexp_replace(replace(metadata.value, ", ", ""), r'[^,]+', '')) = 1
          and job in ( /* only consider merge-blocking jobs */
            'pr:pull-kubernetes-bazel-build',
            'pr:pull-kubernetes-bazel-test',
            'pr:pull-kubernetes-e2e-gce',
            'pr:pull-kubernetes-e2e-gce-100-performance',
            'pr:pull-kubernetes-e2e-kops-aws',
            'pr:pull-kubernetes-integration',
            'pr:pull-kubernetes-kubemark-e2e-gce-big',
            'pr:pull-kubernetes-node-e2e',
            'pr:pull-kubernetes-typecheck',
            'pr:pull-kubernetes-verify',
          )
      )
      group by day, job, commit
    )
    group by day, commit
  )
  group by day
  order by day

jqfilter: |
  ([(.[] | .day|tonumber)] | max) as $newestday |
  [(.[] | select((.day|tonumber)==$newestday) | {
    day: .day,
    commit_consistency: (.commit_consistency|tonumber),
    commits: (.commits|tonumber),
    consistent_commits: (.consistent_commits|tonumber),
    run_consistency: (.run_consistency|tonumber),
    runs: (.runs|tonumber),
    consistent_runs: (.consistent_runs|tonumber),
    builds: (.builds|tonumber)
  })]