Investigating CI failures

The job: take one failing test or one red run and get to a verdict a developer can act on — yours / trunk-borne / flaky, and when trunk-borne: the culprit SHA, its author, the PR, and whether a fix already landed. Everything below is derivation over data that already exists; you never need to re-run CI to answer.

Two warehouse views are the substrate (both non-materialized — always current, query them freely):

• engineering_analytics_ci_failures — one row per pytest FAILED <nodeid> line from CI logs, pre-fingerprinted (fingerprint = test id + digit/hex-normalized error). Group by fingerprint to get first/last seen, occurrence count, and branch spread.
• engineering_analytics_ci_job_history — one row per job attempt with conclusion AND commit attribution: head_sha, commit_author_name, commit_message, commit_pr_number (the merged PR that produced the commit, the only PR attribution a master push run has). This is where greens live; the logs are failure-only, so every "when did it turn red / green again" question must come from here, never from the logs.

Copy-ready SQL for every step is in references/investigation-queries.md.

Start wide: what's broken right now

For "what CI failures should I care about right now" (before you have a specific test in hand), the engineering-analytics-broken-tests MCP tool does the shape classification below across all live failures at once: it groups the last 2 days of failures by fingerprint and labels each breaking_master / blocking_merge_queue / novel_burst / potentially_resolved / flaky / pr_only, most urgent first, plus breaking_master_jobs (default-branch jobs whose latest run is red). Use it as the triage entry point, then drop into the per-failure workflow below to reach a culprit. It is the automated counterpart to fingerprinting by hand; the manual queries stay the way to pin a specific failure to a boundary and author.

blocking_merge_queue is the one shape the manual table below does not cover, because it looks like a single-branch failure and is not. The merge queue runs the full suite on a gate branch (trunk-merge/pr-<n>/…) carrying the PR rebased onto trunk, so a failure there is on a commit that already passed the PR's own CI: a conflict with what landed in between, not that PR's own bug. Read it as "this stopped a merge", and diff the PR against trunk rather than reading the PR alone.

The four failure shapes

Fingerprint the failure first (query 1 in the references), then read its shape — the classification falls out of three columns:

Shape	Reading	Next step
1 branch, any window	That PR's own problem	Read its failure lines; done
1 trunk-merge/pr-<n>/… gate branch	Conflict with what landed since	Diff the PR against trunk, not the PR alone
Many branches, dense burst, hits master	Trunk break (master is/was red)	Boundary query → culprit (below)
Many branches, sporadic over days/weeks	Flaky	Corroborate with engineering-analytics-flaky-tests

Why cross-branch means trunk: PR CI runs the PR merged with master, so one bad master commit fails every concurrently-running PR. A failure appearing on many unrelated branches in a tight window is the signature of a master-merge break, not of those PRs' code. Tell the asker explicitly when their PR is not at fault — that is usually the single most valuable sentence in the answer.

Trunk break → culprit

Run the boundary query (query 2): master-only job history for the failing job, ordered by created_at. The pattern reads directly:

... success success | failure failure ... failure | success ...
                    ^ first red = the culprit row  ^ first green = the fix row

The culprit row carries everything: head_sha, commit_author_name, commit_message (which names what changed), commit_pr_number. The first-green row identifies the fix the same way. Confidence check before naming anyone: does the culprit commit plausibly touch the failing area (its message / PR diff vs the failing test's module)? A boundary landing on an unrelated commit means sharding or timing noise — widen the window and check the adjacent commit before asserting.

Then verify the failure window in ci_failures matches (first_seen just after the culprit merged, last_seen shortly after the fix as the PR queue drained). Mismatch = you're looking at two different problems sharing a test.

Flaky → corroborate, don't guess

Sporadic shape alone is suggestive, not proof. The engineering-analytics-flaky-tests MCP tool reads per-test CI spans (rerun-pass signal — a test that failed then passed on retry in the same job) and is the stronger signal where it has coverage. Counts only, never rates: passing runs below the emitter's duration threshold aren't recorded, so there is no honest denominator.

Caveats you must carry into every answer

• The logs are failure-only. No green baseline exists in ci_failures; absence of a fingerprint is weak evidence (the job may simply not have run). Greens come from ci_job_history only.
• Fingerprints are pytest-only (v1). Jest / playwright / cargo failures appear in the raw failure logs but are not in ci_failures. For those, fall back to grouped triage via the engineering-analytics-master-failures / engineering-analytics-ci-failure-logs MCP tools.
• Freshness differs per source. Logs stream in near-real-time; the warehouse jobs/runs tables arrive via webhook sync and can lag. During a live incident, start from ci_failures and check the warehouse's max(created_at) before trusting a boundary (query 5). A boundary computed against a stale warehouse names the wrong commit.
• A run's conclusion can be stale until the workflow_run webhook settles it (SPEC §7) — treat a very recent "failure-free" tail with suspicion.
• Retries: run_attempt > 1 rows are the same job re-run. A failure that clears on attempt 2 is flake signal; one that fails through attempt 5+ is deterministic.
• Reverts: a revert shows up as a new first-green (or first-red) commit whose commit_pr_number is the reverting PR — attribution follows the revert, not the original.
• Time-bound every logs query. The failure-log stream is large; unbounded scans hit the read cap. 14 days covers almost every investigation.
• Pair the warehouse twin too. A ci_job_history query windowed on created_at alone forces a full jobs scan — the parsed timestamp is a computed column the parquet scan can't prune on. Add a coarse created_at_raw >= '<YYYY-MM-DD>' string floor (a day below the window) alongside the precise created_at bound so the scan skips; created_at stays the exact filter.

Choosing a surface

Question	Use
"What's broken across CI right now?"	engineering-analytics-broken-tests MCP tool (triaged, classified)
"Why did MY PR's CI fail?"	engineering-analytics-ci-failure-logs MCP tool (PR-scoped, grouped)
"Who broke master / when did X start?"	The two views, workflow above
"Is X flaky?"	Shape from ci_failures + the flaky-tests tool
"What's failing on master right now?"	engineering-analytics-master-failures MCP tool (grouped triage feed)
"Is CI slow / expensive / PRs stuck?"	The diagnosing-ci-and-merge-bottlenecks skill
"Save this as a dashboard/insight"	The turning-engineering-analytics-into-insights skill

Output expectations

Lead with the verdict and the exoneration/blame in plain words ("not your PR — master was broken between 08:01 and 09:58 UTC by #68727; fixed by #68855"), then the evidence: the boundary rows, the fingerprint window, occurrence/branch counts. Name the author factually (they authored the culprit commit), never accusatorially — the commit message and PR link let the reader judge the change, and half the time the "culprit" was a reasonable change with an unmocked test dependency.