GitHub — Sources — Binom Governance

Copy formulas into the KPI Definition Card, then adapt grain and filters.

Example

PRs merged

How many pull requests were merged in the period?

COUNT(*) FROM pull_request WHERE merged_at IS NOT NULL AND merged_at IN period

Grain: Merged pull request
Fields: PullRequest.merged_at, PullRequest.state, PullRequest.user.login, Repository.full_name
Dimensions: org, repo, author, state

merged_at set = merged; count closed-without-merge separately.

Adapt: Optionally exclude bot authors (dependabot).

Example

PRs opened

How many pull requests were opened in the period?

COUNT(*) FROM pull_request WHERE created_at IN period

Grain: Opened pull request
Fields: PullRequest.created_at, PullRequest.user.login, Repository.full_name
Dimensions: org, repo, author, state

created_at; clarify draft PRs (draft=true) in the definition.

Adapt: Only default_branch-target PRs as delivery proxy.

Issues opened

How many issues were opened in the period?

COUNT(*) FROM issue WHERE created_at IN period AND pull_request IS NULL

Grain: Opened issue
Fields: Issue.created_at, Issue.state, Repository.full_name
Dimensions: org, repo, author, state

GitHub Issues API may mix PRs — filter pull_request field.

Adapt: Bug vs feature via labels as extra dim.

Workflow failures

How many workflow runs failed in the period?

COUNT(*) FROM workflow_run WHERE conclusion = 'failure' AND created_at IN period

Grain: Failed workflow run
Fields: WorkflowRun.conclusion, WorkflowRun.name, WorkflowRun.created_at, Repository.full_name
Dimensions: org, repo, workflow, state

conclusion=failure; cancelled/timed_out separately.

Adapt: Failure rate = failures / completed runs.

Active repos

How many repos had push/PR activity in the period?

COUNT(DISTINCT repository.id) WHERE pushed_at IN period OR pr.created_at IN period

Grain: Repository with activity
Fields: Repository.id, Repository.full_name, Repository.pushed_at, PullRequest.created_at
Dimensions: org, repo

Activity via pushed_at and/or PR/issue events — lock definition.

Adapt: Optionally exclude archived=false and forks.

Formula overview

Same formulas as a table — copy into KPI cards or SQL drafts.

Label	Formula	Grain	Fields
PRs merged	`COUNT(*) FROM pull_request WHERE merged_at IS NOT NULL AND merged_at IN period`	Merged pull request	PullRequest.merged_at, PullRequest.state, PullRequest.user.login, Repository.full_name
PRs opened	`COUNT(*) FROM pull_request WHERE created_at IN period`	Opened pull request	PullRequest.created_at, PullRequest.user.login, Repository.full_name
Issues opened	`COUNT(*) FROM issue WHERE created_at IN period AND pull_request IS NULL`	Opened issue	Issue.created_at, Issue.state, Repository.full_name
Workflow failures	`COUNT(*) FROM workflow_run WHERE conclusion = 'failure' AND created_at IN period`	Failed workflow run	WorkflowRun.conclusion, WorkflowRun.name, WorkflowRun.created_at, Repository.full_name
Active repos	`COUNT(DISTINCT repository.id) WHERE pushed_at IN period OR pr.created_at IN period`	Repository with activity	Repository.id, Repository.full_name, Repository.pushed_at, PullRequest.created_at

Id	Label	Grain	Notes
`org`	Organization	organization.login	Org as tenant slice for repos, teams and runs.
`repo`	Repository	repository.full_name	full_name more stable than rename-fragile id-only joins in reports.
`author`	Author	user.login / commit author	Prefer login; author email is PII.
`workflow`	Workflow	workflow_run.name / workflow_id	For failure rates and CI health.
`state`	State	PR/Issue state; run conclusion	Do not mix open/closed with success/failure.

PII hotspots

Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.

Entity	Fields	Classification	Stage treatment	Treatment
User / org member	`login, id, email, name, avatar_url`	Workforce	RAW: workforce policy; Curated: login/id only; Mart: aggregates only	GitHub user — email only with legal basis; id/login as key.
Commit / PR author	`author.email, committer.email, author.name, user.login`	Workforce	RAW: workforce policy; Curated: login/id only; Mart: aggregates only	Author emails from commits — often personal mail; hash or drop in curated.
Issue / PR body & review comments	`body, review_comment.body, discussion comments`	Quasi	RAW: rarely load; Curated: drop/redact; Mart: never	Free text can contain secrets and personal data — metadata enough for delivery KPIs.
Source code / patches / diffs	`file contents, patch, diff, blob`	Sensitive	RAW: never store; Curated: never; Mart: never	Never land source code/patches in the warehouse — repo stays source of truth.
Secret scanning / code scanning alerts	`secret, secret_type, html_url, push_protection bypass`	Sensitive	RAW: never secret values; Curated: counts/status only; Mart: aggregates	Secret scanning payloads — never store the secret value; alert metadata at most.

DSDR discovery

Where personal data can hide beyond obvious contact fields — warehouse copies included.

Focus	Notes
Match keys	user id, login, email (hashed), org id, repo id, PR number, commit sha.
Primary objects	User, organization, repository, pull request, issue, workflow run, audit event.
REST / GraphQL / Audit log exports	API exports and org audit log duplicate author emails and membership PII.
Fork / sandbox org copies	Do not mix sandbox orgs and forks with prod delivery marts.

Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.

Data quality guides 6

Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.

High PR without repository

Problem: Pull requests without repo_id break delivery joins and throughput KPIs.
Prevent: API extract: always load repository; track soft-deleted repos.
Staff must know: Data: PR without repo is an extract/delete artifact.
Checks: pull_request.repo_id IS NULL OR repo_id NOT IN dim_repository
Fix in source: Re-extract; include transferred/deleted repo tombstones.
Warehouse safety net: Quarantine; Unknown repo dim.

High Merged PR without merge commit SHA

Problem: Merged without merge_commit_sha makes change tracking and deploy joins unusable.
Prevent: Request GraphQL/REST mergeCommit/oid fields; document squash/rebase cases.
Staff must know: Engineering: Check merge method and SHA fields in extract.
Checks: merged = TRUE AND merge_commit_sha IS NULL
Fix in source: Re-fetch PR; verify merge method metadata.
Warehouse safety net: Flag missing_merge_sha; exclude from SHA-based joins.

High Workflow run without repository

Problem: Actions runs without repo_id distort CI success rates.
Prevent: Actions API: always join repository; separate archived repos.
Staff must know: Platform: Do not put run metadata without repo into prod marts.
Checks: workflow_run.repo_id IS NULL
Fix in source: Re-extract Actions runs with repository.
Warehouse safety net: Quarantine; exclude from CI KPIs.

Medium Member / outside collaborator drift

Problem: Outside collaborators without org membership distort access and contributor counts.
Prevent: Org policy: prefer org members; periodic collaborator review.
Staff must know: Admins: Review and time-box outside collaborators.
Checks: is_outside_collaborator = TRUE AND org_member = FALSE AND last_activity_at < CURRENT_DATE - 90
Fix in source: Convert to member or remove collaborator access.
Warehouse safety net: Flag stale_collaborator; separate access KPI.

Medium Secret scanning alert without repo

Problem: Alerts without repo_id break security coverage; secret values must never land.
Prevent: Security alerts extract with repository; never select secret field.
Staff must know: Security: Alert metadata only — no secret payloads.
Checks: alert.repo_id IS NULL AND state <> 'resolved'
Fix in source: Re-fetch alert list with repository context.
Warehouse safety net: Quarantine; never persist secret values.

Medium Duplicate PR number per repo in extract

Problem: Duplicate (repo_id, number) distort PR counts and cycle time.
Prevent: Idempotent load key; check pagination overlap.
Staff must know: Data: Natural key = repo_id + number.
Checks: COUNT(*) GROUP BY repo_id, number HAVING COUNT > 1
Fix in source: Re-extract; investigate API pagination.
Warehouse safety net: Dedup by repo_id+number; keep latest _loaded_at.

MDM / duplicates 2

User User duplicates / login rename

Match keys: id, login, email (hashed), node_id
Prevent in source: Prefer numeric id over login; track login renames.
Resolve in warehouse: Golden user id; login SCD2 map.
Survivorship: Active user with latest org activity wins.

Repository Repository transfer / rename

Match keys: repo id, node_id, full_name, owner+name
Prevent in source: Stable repo id; capture rename/transfer events.
Resolve in warehouse: Canonical repo id; full_name SCD2.
Survivorship: Current full_name wins; historical names as aliases.

Product settings to improve 3

Organization → Settings → Audit log Audit log streaming / export for governance events

Why: Member/repo changes become analytically traceable.
How: Enable streaming; allowlist event types; short retention for sensitive fields.

Code security → Secret scanning Secret scanning + push protection; alert metadata only in warehouse

Why: Security coverage without landing secrets.
How: Enable org-wide; extract state/severity/repo — never secret value.

Actions → General / Artifact retention Keep artifact/log retention short; warehouse run metadata only

Why: CI KPIs without log/artifact binaries.
How: Org retention defaults; omit logs/artifacts from extract.

Metadata: what exists & how to get it 4

Source-native catalogs for schema, picklists/properties and access paths — not a full dump.

REST / GraphQL schema

Where in product: GitHub Docs → REST API / GraphQL schema reference
How to access: Inventory object fields; select/fragment allowlist for extract.
Use for: Extract design, PII tags, join keys (repo, PR, user).
Watchouts: Do not select patches/blobs/secret fields.

Org audit log

Where in product: Org Settings → Audit log; audit log API / streaming
How to access: Allowlist event actions; document actor/repo keys.
Use for: Governance facts, member changes, repo transfers.
Watchouts: Audit payloads can contain emails — hash/drop.

Repository settings

Where in product: Repo → Settings (branches, visibility, security); Org repo policies
How to access: Export default branch, visibility, required checks.
Use for: Repo dim, compliance flags, delivery baselines.
Watchouts: Private repo metadata — restrict access.

Actions metadata

Where in product: Actions → Workflows / Runs API; workflow YAML locations (not bodies in WH)
How to access: Inventory run status, conclusion, timing, workflow name.
Use for: CI success rate, duration KPIs, flaky-job signals.
Watchouts: Logs/artifacts default skip — metadata only.

Object overview with short descriptions and load guidance — not a full schema dump.

Table	Description	Grain	Load
`repository` Repository	GitHub repository — full_name, visibility; prefer meta, not blob/tree.	One repository (id / full_name)	Load
`user` User	GitHub user — login; email only if exposed (PII).	One user (id / login)	Load
`organization` Organization	Org — tenant dimension for repos and teams.	One organization (login)	Load
`pull_request` Pull request	PR — state, merged_at, additions/deletions; do not load diff bodies.	One pull request (number @ repo)	Load
`issue` Issue	Issue — state, opened/closed; body text optional/redacted.	One issue (number @ repo)	Load
`commit_meta` Commit meta	Commit SHA, author, stats — skip patch/diff bodies.	One commit (sha)	Optional
`workflow_run` Workflow run	Actions run — conclusion, workflow; do not load log bulk.	One workflow run (id)	Load
`team` Team	Org team — membership and repo permissions join.	One team (slug @ org)	Optional

Most important fields to load early — not a full schema dump.

Entity	Field	Role	Why
Repository	`Repository.id`	Key	Repo join (numeric id)
Repository	`Repository.full_name`	Dimension	owner/name — repo dim
Repository	`Repository.private`	Dimension	Visibility private/public
Repository	`Repository.default_branch`	Dimension	Default branch
Repository	`Repository.pushed_at`	Measure	Last push activity
User	`User.id`	Key	User join
User	`User.login`	Dimension	GitHub handle / author dim
User	`User.email`	PII	Email if exposed / PII
Organization	`Organization.id`	Key	Org join
Organization	`Organization.login`	Dimension	Org slug / org dim
PullRequest	`PullRequest.id`	Key	PR node/id
PullRequest	`PullRequest.number`	Key	PR number @ repo
PullRequest	`PullRequest.state`	Dimension	open / closed
PullRequest	`PullRequest.merged_at`	Measure	Merge timestamp
PullRequest	`PullRequest.created_at`	Measure	Opened timestamp
PullRequest	`PullRequest.additions`	Measure	LOC additions (meta)
PullRequest	`PullRequest.deletions`	Measure	LOC deletions (meta)
PullRequest	`PullRequest.user.login`	Dimension	Author
Issue	`Issue.id`	Key	Issue join
Issue	`Issue.number`	Key	Issue number @ repo
Issue	`Issue.state`	Dimension	open / closed
Issue	`Issue.created_at`	Measure	Opened timestamp
CommitMeta	`CommitMeta.sha`	Key	Commit SHA
CommitMeta	`CommitMeta.author.email`	PII	Commit author email / PII
CommitMeta	`CommitMeta.author.login`	Dimension	Author handle
CommitMeta	`CommitMeta.stats.additions`	Measure	Commit additions (meta)
WorkflowRun	`WorkflowRun.id`	Key	Run join
WorkflowRun	`WorkflowRun.name`	Dimension	Workflow name
WorkflowRun	`WorkflowRun.conclusion`	Dimension	success / failure / cancelled
WorkflowRun	`WorkflowRun.status`	Dimension	queued / in_progress / completed
WorkflowRun	`WorkflowRun.created_at`	Measure	Run start
Team	`Team.id`	Key	Team join
Team	`Team.slug`	Dimension	Team slug
Team	`Team.organization.login`	Dimension	Org back-join

System and noise objects/fields you typically should not sync into the warehouse by default.

Skip tables / objects

Table	Category	Why
`Source blobs / file contents / patches`	System	Never land code — volume, IP and secret risk.
`Secret scanning secret values`	System	Secret values — never store; alert status at most.
`PR / issue / review comment bodies (bulk)`	System	Free-text PII and potential secrets — metadata enough.
`Actions logs / artifact binaries (bulk)`	System	CI logs/artifacts — expensive; run metadata enough for KPIs.

Skip fields / content

Source code / patch / diff content — Repo stays source of truth — no warehouse clone.
Secret scanning secret values — Never store — alert counts/status only.
Author email cleartext in marts — login/id is enough for contributor KPIs.
PR / issue body cleartext (bulk) — PII/secrets — selectively or not at all.
Actions artifact binaries — Cost without KPI value.

Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.

Landing (RAW)

RAW repository + PR landing

REST/GraphQL shape — no patches/source blobs; author as login/id.

-- Warehouse-neutral RAW from GitHub API extract (not API DDL clone)
CREATE TABLE raw_github_repository (
  repo_id              BIGINT,
  node_id              VARCHAR,
  full_name            VARCHAR,
  owner_login          VARCHAR,
  visibility           VARCHAR,
  default_branch       VARCHAR,
  archived             BOOLEAN,
  created_at           TIMESTAMP,
  _loaded_at           TIMESTAMP
);

CREATE TABLE raw_github_pull_request (
  pr_id                BIGINT,
  repo_id              BIGINT,
  number               INT,
  state                VARCHAR,
  author_user_id       BIGINT,
  author_login         VARCHAR,
  created_at           TIMESTAMP,
  merged_at            TIMESTAMP,
  merge_commit_sha     VARCHAR,
  merged               BOOLEAN,
  _loaded_at           TIMESTAMP
  -- no body, no patch, no diff
);

RAW workflow run + user landing

Actions metadata only — no logs/artifacts; email = PII in RAW.

CREATE TABLE raw_github_workflow_run (
  run_id               BIGINT,
  repo_id              BIGINT,
  workflow_name        VARCHAR,
  status               VARCHAR,
  conclusion           VARCHAR,
  run_started_at       TIMESTAMP,
  run_updated_at       TIMESTAMP,
  _loaded_at           TIMESTAMP
  -- no log/artifact binaries
);

CREATE TABLE raw_github_user (
  user_id              BIGINT,
  login                VARCHAR,
  email                VARCHAR, -- workforce / author PII
  type                 VARCHAR,
  _loaded_at           TIMESTAMP
);

CREATE TABLE raw_github_secret_alert_meta (
  alert_number         INT,
  repo_id              BIGINT,
  state                VARCHAR,
  secret_type          VARCHAR,
  created_at           TIMESTAMP,
  resolved_at          TIMESTAMP,
  _loaded_at           TIMESTAMP
  -- never store secret value
);

Curated

Curated fact github pull request

Delivery grain — repo required; no body/patch; cycle-time fields.

CREATE TABLE curated_fct_github_pull_request AS
SELECT
  p.pr_id,
  p.repo_id,
  p.number,
  p.state,
  p.author_user_id,
  p.author_login,
  p.created_at,
  p.merged_at,
  p.merge_commit_sha,
  p.merged,
  CASE WHEN p.merged THEN TRUE ELSE FALSE END AS is_merged
FROM raw_github_pull_request p
WHERE p.repo_id IS NOT NULL;

Curated dim repo / workflow fact

Repo dim; workflow fact without logs; user without email.

CREATE TABLE curated_dim_github_repository AS
SELECT
  repo_id,
  full_name,
  owner_login,
  visibility,
  default_branch,
  archived,
  created_at
FROM raw_github_repository;

CREATE TABLE curated_dim_github_user AS
SELECT
  user_id,
  login,
  type
  -- omit email from default analytics dims
FROM raw_github_user;

CREATE TABLE curated_fct_github_workflow_run AS
SELECT
  r.run_id,
  r.repo_id,
  r.workflow_name,
  r.status,
  r.conclusion,
  r.run_started_at,
  r.run_updated_at,
  CASE WHEN r.conclusion = 'success' THEN TRUE ELSE FALSE END AS is_success
FROM raw_github_workflow_run r
WHERE r.repo_id IS NOT NULL;

Curated measure SELECTs

Merged PRs, CI success rate, open secret alerts — adapt period filters.

-- Merged PRs in period
SELECT COUNT(*) AS merged_prs
FROM curated_fct_github_pull_request
WHERE is_merged = TRUE
  AND merged_at BETWEEN :period_start AND :period_end;

-- CI success rate in period
SELECT
  AVG(CASE WHEN is_success THEN 1.0 ELSE 0.0 END) AS ci_success_rate
FROM curated_fct_github_workflow_run
WHERE run_started_at BETWEEN :period_start AND :period_end;

-- Open secret scanning alerts (metadata only — no secret values)
SELECT COUNT(*) AS open_secret_alerts
FROM raw_github_secret_alert_meta
WHERE state = 'open'
  AND repo_id IS NOT NULL;

Tools

Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.

Playbooks

Related governance stories for modelling, PII and measure design around this source.

Vendor help on Resources Official docs, governance and learning paths for this source. Open Resources →