KPI Design
Advanced 13 min read KPI Design

KPI Validation and Reconciliation

A KPI is a promise — test the promise

KPIs are worth nothing un-validated. Tests, control totals, an approval workflow and change audit keep the number real — because a red-light KPI that's 'wrong' poisons the whole dashboard.

validationreconciliationcontrol totalsgoldenapprovalaudit
Prerequisites: KPI Fundamentals Metric Definitions That Scale
Before we start

What you will learn

  • Design validation: definition reviews, control totals, statistical taste
  • Run reconciliation gates in CI
  • Add approval + audit to KPI changes
Introduction

What is this?

KPI validation proves the number means what it says: definition reviewed, formula reconciled to a control total, edge cases tested, drift gates in CI, and changes approved and audited. Unvalidated KPIs are vandalism on the board — a wrong red light makes the entire dashboard untrustworthy, so validation has to be part of the definition, not an afterthought.

Motivation

Why do you need it?

  • One wrong KPI silently poisons trust in the whole deck.
  • Reconciliation catches errors a dashboard review sneezes past.
  • An approval/audit trail turns 'KPIs change' into a governed act.
  • Validation is what execs feel as faith in the red light.
Think about it

Mental model

Imagine

An airline's pre-flight checklist

Think of

KPI validation

Nobody 'approves' a flight by smelling the cabin. The checklist runs: instruments, fuel, control surfaces, margins — every flight, mechanically. KPIs fly on the same contract: definition, control total, edge tests, drift, signoff before the board sees it.

Mechanics

How it works

  • Definition review: formula, grain, filters, direction documented and approved.
  • Control total: reconcile the KPI to an independent aggregate within a delta.
  • Edge cases: zero-dividers, empty periods, snaps of grain — tested.
  • CI gates: drift thresholds, freshness and schema checks on the KPI query.
  • Approval + audit: changes flow through review; every version logged.

Validated KPI from definition to board.

Define
Reconcile
Edge-test
Approve
Audit
Hands-on

Examples

A validation telemetry row

A validation telemetry row
kpi: activation_pct
definition_reviewed: 2026-01-15 (vov)
control_total: raw_activation table SUM matches
delta_gate: <= 1% (pass)
edge_cases: empty cohort -> NULL handled, 0->render '--'
ci: freshness daily, drift weekly, schema tests green
approved_by: platform-leads (2026-01-16)
history: [v0 2025-11 init, v1 2026-01 filter fix]

The full validation contract: defined, reconciled, edge-tested, approved, audited — so the red light can be believed.

Real world

In production

  • A revenue KPI re-reconciles nightly against the control; a +2% drift blocks the board deck.
  • An activation KPI edge-case rule (blank cohort → 'no data') stops zero-from-nobody silliness.
  • KPI version audit logs answer 'why did revenue change' in a PR, not a guessing game.
Guidelines

Best practices

  • Build the control total into the same pipeline so reconciliation is continuous.
  • Gate KPI changes (approval + CI) like code changes.
  • Test edge cases explicitly: zero, empty, grain snap, division guards.
  • Ships changes with a versioned change log; audits forever.
Watch out

Common mistakes

  • Dashboard 'review' passes, control-total test never runs.
  • Zero-from-empty cohorts rendering as red '0%' — a fake crisis.
  • KPI drifted from its stated definition with no one approving.
  • Only reviewing the happy path; denominators crash later.
When things break

Debugging tips

  • KPI suddenly off by a hair → run the reconciliation gate; suspect grain/definition drift.
  • Red light for an empty slice → the edge-case test should have blocked it.
  • Everyone 'remembers' changing something → no audit trail; add versioning.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which gate proves the KPI matches an independent aggregate?

2

Empty-period cohorts should render as a dramatic red zero.

Go deeper

Official documentation

dbt - testing & assertions
docs