/turning-engineering-analytics-into-insights

name: turning-engineering-analytics-into-insights
description: >
  Converts engineering analytics (PR / CI) data into saved PostHog insights, dashboards, and subscriptions, and
  explains how to query the product data directly with SQL. Covers discovering per-team GitHub warehouse tables via
  engineering-analytics-sources, replicating curated column semantics in HogQL, reading exposed
  engineering_analytics_* warehouse views where product logic is involved (CI cost, fingerprinted failure lines,
  commit attribution), saving queries with insight-create, and scheduling delivery with subscriptions-create. Use
  when asked to "save this as an insight", "put CI health / merge times on a dashboard", "email me PR throughput
  weekly", "chart CI cost", "track time to first review", "subscribe to these numbers", "alert on CI success rate",
  or "what data/tables/views does engineering analytics read". For ad-hoc CI and merge questions use
  diagnosing-ci-and-merge-bottlenecks; to investigate one specific CI failure use investigating-ci-failures.

Turning engineering analytics into insights and subscriptions

The engineering analytics dashboard and MCP tools (`pull-requests`, `workflow-health`, `pr-lifecycle`, `engineering-analytics-broken-tests`, …) run curated HogQL privately: nothing in the UI or the tool output names the underlying tables, and the endpoints cannot themselves be saved as insights or subscribed to. The data, however, is queryable directly, through two substrates:

• **Raw warehouse tables** — `<prefix>github_pull_requests`, `<prefix>github_workflow_runs`,

`<prefix>github_workflow_jobs`, `<prefix>github_reviews`, and `<prefix>github_teams` / `<prefix>github_team_members` (org team membership — the author→team map) — ordinary team-scoped tables you query with HogQL.

• **Three curated warehouse views** with fixed names — `engineering_analytics_job_costs`,

`engineering_analytics_ci_job_history`, `engineering_analytics_ci_failures` — provisioned per team from the connected GitHub source(s). Non-materialized: computed at query time, always current, and they back insights and subscriptions like any table.

The views exist for exactly one reason: they render **product code** into SQL — the runner-tier cost model, the failure-fingerprint recipe, the jobs↔runs commit-attribution rules — logic that would silently drift if hand-rolled, re-rendered into the team's view whenever the code changes. Everything else is just table data, and **pure HogQL over the raw tables is always enough**: never create additional warehouse views for engineering analytics data, and never re-derive in SQL what the three views already encode.

| What the product shows | Where the data actually lives | Can it back an insight? | | ----------------------------------------------------------- | ------------------------------------------------------------------------------------ | ---------------------------------------------- | | PR list, merge times, CI status, workflow health | Data warehouse tables `<prefix>github_pull_requests`, `<prefix>github_workflow_runs` | **Yes** (SQL insight over the tables) | | Reviews and approvals | `<prefix>github_reviews` | **Yes** | | Team-level PR metrics (author→team attribution) | `<prefix>github_team_members` semi-joined against the PR authors | **Yes** (team aggregates only) | | Job durations, queue times, runner tiers | `<prefix>github_workflow_jobs` | **Yes** | | CI cost (runner-tier price ladder) | `engineering_analytics_job_costs` view | **Yes** (query the view, never recompute cost) | | Per-job CI history with commit attribution | `engineering_analytics_ci_job_history` view | **Yes** | | Grouped (fingerprinted) CI failure lines | `engineering_analytics_ci_failures` view (reads the Logs product, short retention) | **Yes**, for short recent windows | | Thinned CI failure logs for a PR or run | Logs product (`service_name = 'github-ci-logs'`) + thinning logic | No (use the MCP tools ad hoc) | | Flaky-test leaderboard, broken-tests triage, team CI health | CI trace spans + ranking/classification logic in product code | No (use the MCP tools ad hoc) |

So the job splits cleanly: warehouse-backed metrics (raw tables or the three views) become SQL insights (then dashboards, then subscriptions); everything computed by product logic at request time stays on the MCP tools, delivered recurringly via an AI subscription if needed.

Step 1: discover the team's tables

Warehouse table names carry a user-chosen prefix, so never hardcode them. Call the `engineering-analytics-sources` MCP tool: each connected GitHub source returns its `id`, `repo`, and `prefix`. The tables are `<prefix>github_pull_requests`, `<prefix>github_workflow_runs`, `<prefix>github_workflow_jobs`, `<prefix>github_reviews`, and `<prefix>github_teams` / `<prefix>github_team_members`; an empty prefix means the plain `github_*` names. With multiple sources, ask which repo the user means; each source is one repo.

The three `engineering_analytics_*` views need no discovery: their names are fixed (no prefix), and they cover all of the team's GitHub sources at once — filter on `repo_owner` / `repo_name` (`job_costs`, `ci_job_history`) or `repo` (`ci_failures`, which reads the Logs produ