Metric Consistency and Reconciliation
One number, everywhere, every time
Metric consistency is the discipline of making every consumer compute the same number the same way — through reconciliation, shared definitions, tests and a single certified source of truth.
What you will learn
- Run a reconciliation between source systems and the BI layer
- Detect and prevent definition drift across teams
- Build confidence gates that block inconsistent numbers
What is this?
Metric consistency means a given metric has exactly one meaning and value regardless of who asks or where they look. It is engineered through: a single semantic definition, reconciliation checks that compare BI output against source/ledger totals, tests on definitions, and governance that retires rogue copies.
Why do you need it?
- Inconsistent numbers erode trust — the most expensive asset in BI.
- Reconciliation turns 'the dashboard is probably right' into a verified statement.
- Drift is silent: two dashboards can diverge for months before anyone notices.
- Consistent metrics make forecasts, targets and incentives actually comparable.
Mental model
Two airlines publishing different arrival times
Inconsistent metrics
Every passenger learns not to trust either schedule. Once a business sees two versions of 'revenue', every number gets discounted — reconciliation is how you win trust back and keep it.
How it works
- A semantic layer defines each metric once with a version, owner and certification.
- Reconciliation jobs compare the metric's result set to an independent source of truth (ledger, source counts).
- Tests (known values, grain, nulls) fail a change before it reaches consumers.
- Consumption logging flags reports that bypass the semantic layer or redefine metrics.
- Retirement: stale copies are deprecated and consumers migrated to the certified measure.
Consistency enforced at the seams.
In production
- Nightly, a reconciliation job compares warehouse revenue vs the billing ledger and pages finance on any gap > 0.01%.
- An analytics platform blocks a dashboard publish unless its source tables carry passing data-quality tests.
- A multi-tenant SaaS offers its clients a certified 'standard metrics' catalog so every client's version of revenue matches the contractual definition.
Best practices
- Pick one authoritative source per metric family and reconcile against it.
- Reconcile to a defined tolerance (absolute vs %), and alert on breach, not on any diff.
- Version definitions and test every change; make consumers subscribe to versions.
- Audit consumption quarterly: find and retire dashboards with forked definitions.
Common mistakes
- Reconciling only when someone complains — by then the damage is done.
- Tolerances so tight that noise triggers false alarms (and desensitizes).
- Multiple 'sources of truth' each defended by a team — the definition of inconsistent.
- Exporting to spreadsheets, where the number gets forked irrecoverably.
Common issues
Nightly reconciliation keeps alerting a tiny constant gapA rounding or exclusion difference in the two definitions
Align definitions exactly or certify the delta as expected with a documented tolerance.
Two teams each rebuilt 'revenue' in their dashboardNo discoverable certified measure
Publish the certified measure in the catalog and deprecate forked copies with migration.
Debugging tips
- When a gap appears, bisect by dimension (region, plan, date) to isolate the fork.
- Compare by grain first: daily-vs-monthly as-of explain most reconciliation deltas.
- Check timezone and currency assumptions before suspecting the pipeline.
Interview checkpoint
Quick quiz
What is the strongest signal that a metric is consistent?
A persistent small gap between two systems is usually harmless and can be ignored.