flakes-daily-config.yaml

metric: flakes-daily
description: Calculates flakiness for each job for the past day and the flakiest tests for each job.
query: |
  #standardSQL
  select
    job,
    build_consistency,
    commit_consistency,
    flakes,
    runs,
    commits,
    array(
      select as struct
        i.n name,
        count(i.failures) flakes
      from tt.tests i
      group by name
      having name not in ('Test', 'DiffResources', 'DumpClusterLogs', 'DumpFederationLogs')  /* uninteresting tests */
      order by flakes desc
      limit 10 /* top ten flakiest tests in this job */
    ) flakiest
  from (
    select
      job, /* name of job */
      round(sum(if(flaked=1,passed,runs))/sum(runs),3) build_consistency, /* percentage of runs that did not flake */
      round(1-sum(flaked)/count(distinct commit),3) commit_consistency, /* percentage of commits that did not flake */
      sum(flaked) flakes, /* number of times it flaked */
      sum(runs) runs, /* number of times the job ran */
      count(distinct commit) commits, /* number of commits tested */
      array_concat_agg(tests) tests /* array of flaking tests in this job */
    from (
      select
        job,
        commit,
        if(passed = runs or passed = 0, 0, 1) flaked, /* consistent: always pass or always fail */
        passed,
        safe_cast(runs as int64) runs,
        array(
          select as struct
            i.name n, /* test name */
            countif(i.failed) failures /* number of times it flaked */
          from tt.tests i
          group by n
          having failures > 0 and failures < tt.runs /* same consistency metric */
          order by failures desc
        ) tests
      from (
        select
          job,
          commit,
          sum(if(result='SUCCESS',1,0)) passed,
          count(result) runs,  /* count the number of times we ran a job on this commit for this PR */
          array_concat_agg(test) tests /* create an array of tests structs */
        from (
          SELECT
            job,
            if(substr(job, 0, 3) = 'pr:', 'pull', 'ci') kind,  /* pull or ci */
            ifnull(repo_commit, version) version, /* git version, empty for ci  */
            if(substr(job, 0, 3) = 'pr:',
              regexp_extract(
                repo,
                r'[^,]+,\d+:([a-f0-9]+)"'
              ),
              ifnull(repo_commit, version)
            ) commit,  /* repo commit for PR or version for CI */
            result,  /* SUCCESS if the build passed */
            test  /* repeated tuple of tests */
          from (
                select *,
                       ifnull(b.repos, (select i.value from b.metadata i where i.key = 'repos')) repo from `kubernetes-public.k8s_infra_kettle.week` as b
          ) as t
          where
            datetime(started) > datetime_sub(current_datetime(), interval 1 DAY)
            and (version != 'unknown' or repo_commit is not null)
            and (
              substr(job, 0, 3) = 'ci-' or
              array_length(split(replace(t.repo,', ', ''), ',')) = 2 /*serial pr jobs only (# of PR refs +1 == 2)*/
            )
        )
        group by job, commit
      ) as tt
    ) as tt
    group by job /* summarize info for this job across all commits/builds */
  ) as tt
  order by flakes desc, commit_consistency, build_consistency, job /* flakiest jobs first */

jqfilter: |
  [(.[] | {(.job): {
      consistency: (.commit_consistency|tonumber),
      flakes: (.flakes|tonumber),
      test_flakes: ([(.flakiest[] | {
        (.name): (.flakes|tonumber)}) ])| add
  }})] | add