/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 what data the product reads so it can be queried directly with SQL. The engineering analytics dashboard
  and MCP tools run curated HogQL privately over per-team GitHub warehouse tables; this skill teaches discovering
  those tables via engineering-analytics-sources, replicating the curated column semantics in HogQL, reading the
  exposed engineering_analytics_* warehouse views where product logic is involved (CI cost, fingerprinted failure
  lines, commit attribution), saving the query 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 GitH