Duplicated metrics: the silent problem killing trust in BI
Three dashboards, three different revenue numbers, one very anxious CFO. Why this happens, what it costs you, and a one-week exercise to fix it.
Here's a scene I've watched play out at least a dozen times: the CFO opens the executive dashboard before a board meeting, sees revenue at $4.2M, and asks the BI lead to confirm. The BI lead opens their own dashboard, sees $4.05M, and thinks "that's close enough, it's probably timezone." The head of sales opens his pipeline tool an hour later, sees $4.31M, and copies that into the deck.
Three numbers. Three definitions. Zero alignment. By the time someone notices and asks "wait , which one is right?" the deck is already with the board.
This is the single most common source of executive frustration with data teams that I encounter. It's also, paradoxically, one of the easiest to fix, once people are willing to admit it's the problem.
How a single metric ends up duplicated
Duplicated metrics rarely arrive in a single dramatic event. They accumulate slowly through entirely reasonable local decisions:
- The CRM has its own "deals closed this month" calculation. Someone built a report on that.
- Finance imports payments from Stripe and recognises revenue on a different schedule. They built their own dashboard.
- The growth team needed something faster than waiting on the warehouse, so they wired Looker straight to a transactional Postgres replica with their own SQL.
- The new BI lead arrives, sees three numbers, builds a "harmonised" dashboard with a fourth definition.
Each step was rational. The aggregate result is chaos. And no one wakes up responsible for it because no one wakes up owning the metric, they own their dashboard.
What it actually costs you
The visible cost is the awkward meeting where two charts disagree. The invisible cost, much higher, shows up over months:
- Decision paralysis. Executives stop trusting any number they can't double-check personally, which means they stop using dashboards at all and revert to asking the BI team for one-off pulls.
- Data team burnout. The same questions get re-answered weekly because nobody trusts the previous answer. Your most expensive analysts spend their week reproducing reconciliations instead of building new things.
- Cultural decay. "Let me check the numbers" becomes a two-day project instead of a thirty-second lookup. Speed of decision-making slows. The data team becomes a bottleneck rather than a leverage point.
A one-week exercise to surface duplicates
I run a version of this exercise with most clients in the first month. It's simple, it's uncomfortable, and it almost always finds 5–10 duplicated metrics that nobody knew were duplicated.
Day 1, list every dashboard
Make a spreadsheet of every dashboard, report, and recurring SQL query in production. Power BI, Looker, Metabase, Tableau, the Notion page where someone screenshots a number every Monday, all of it.
Days 2–3, pull every metric definition
For the top 20 most-viewed dashboards, write down the metrics they expose. For each metric: what's the human-readable name, what's the underlying SQL, and what's the source table.
Day 4, cluster by name
Group metrics by the name a non-technical person would call them. "Revenue", "monthly active users", "churn rate", "pipeline value". You'll typically find 2–4 different definitions hiding behind each name.
Day 5, pick a winner
For each cluster, decide on the canonical definition. Not "the right one", "the one we'll use." Document it in a doc that lives next to the warehouse. Rename every other version "X (legacy CRM)", "X (sales view)", etc., or kill them.
I've run this audit dozens of times. It's faster, and significantly less political, to do with someone who isn't on your team. A 1–2 week engagement usually surfaces 5–10 duplicated metrics and gives you a single-source-of-truth roadmap.
Run this exercise with meThe lasting fix: a semantic layer
Cleaning up the existing duplicates is a one-time effort. Stopping new ones from forming is the actual prize. The mechanism is a semantic layer, a place in your stack where each metric is defined exactly once and every downstream tool consumes from there.
Concretely, that usually means dbt mart models that expose canonical fact and metric tables, plus a metrics layer (dbt Semantic Layer, Cube, MetricFlow, or a thin wrapper of your own) that ensures every BI tool reads from the same definition. New analysts can still write SQL, but they write it on top of the canonical layer, not against raw tables.
The semantic layer doesn't make duplicates impossible. It makes them obvious. When someone bypasses it, the deviation shows up in code review. That's the only sustainable defence I've found against this problem in the long run.