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 (parsed from the squash-merge suffix — 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.