How to Validate Metric Definitions Across Your Data Team

Every team hits this wall eventually. Marketing says ARR is $1.2M. Finance says it's $980k. The CEO's board deck splits the difference at $1.1M and nobody questions it because the vibes feel right. Then a board member asks how churn is calculated and three people give three different answers in the same meeting.

The numbers aren't wrong. They're just computed differently. Marketing includes trialing accounts. Finance excludes them. Product counts "active users" as anyone who logged in; the growth team counts anyone who performed a core action. Same column names, same tables, different WHERE clauses buried in dashboards that nobody audits.

I've fixed this at three different companies. The pattern is always the same: audit what exists, build a validation layer, then lock it down with a semantic layer so drift can't creep back in.

Step 1: Audit your existing metric definitions

Before you can fix anything, you need to know how bad it is. Most teams are surprised. The goal here is to find every query that touches revenue columns and compare the filtering logic.

If you're on Postgres (or any warehouse with information_schema), start by identifying which tables and columns people use for revenue calculations:

-- Find all columns that look like revenue across your schema
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name ILIKE ANY(ARRAY[
    '%revenue%', '%mrr%', '%arr%', '%amount%',
    '%price%', '%total%', '%billing%'
])
AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

That gives you the surface area. Now the harder part — finding the actual definitions people use. If you have a BI tool with query logs (Metabase, Looker, Mode), pull the query history and grep for those column names. If you're running raw SQL in notebooks or dbt, search the codebase:

# Find every SQL file that references revenue-related columns
grep -rn "mrr\|monthly_recurring\|arr\|annual_recurring\|revenue" \
  --include="*.sql" --include="*.py" --include="*.ipynb" \
  ./dbt/ ./notebooks/ ./reports/

What you'll find is ugly. Three dbt models that each define MRR differently. A Metabase saved question from 2024 that excludes refunds. A Python notebook that includes trial revenue. A finance spreadsheet that uses a completely different exchange rate for multi-currency subscriptions.

Document every variant in a table. Metric name, source file, definition, owner, last modified. You'll typically find 3-5 definitions for any metric that matters.

Step 2: Build a validation layer that catches drift

Once you know what the "correct" definition should be (that's a meeting, not a technical problem), you need something that screams when a new query deviates from it. Create a metric_definitions table as your registry, then write validation queries that compare outputs against expected bounds.

-- Metric definition registry
CREATE TABLE metric_definitions (
    metric_name    TEXT PRIMARY KEY,
    description    TEXT NOT NULL,
    sql_definition TEXT NOT NULL,
    owner          TEXT NOT NULL,
    valid_from     DATE NOT NULL DEFAULT CURRENT_DATE,
    bounds_lower   NUMERIC,  -- alert if result falls below
    bounds_upper   NUMERIC,  -- alert if result exceeds
    change_pct_max NUMERIC DEFAULT 0.20  -- alert on >20% swing
);
 
INSERT INTO metric_definitions VALUES (
    'mrr',
    'Monthly recurring revenue: sum of active subscription amounts, '
    'excluding trials, normalized to monthly. Annual plans divided by 12. '
    'Multi-currency converted at invoice-time rate.',
    $$
    SELECT SUM(
        CASE WHEN billing_interval = 'year'
             THEN amount_usd / 12
             ELSE amount_usd
        END
    ) AS mrr
    FROM subscriptions
    WHERE status = 'active'
      AND trial_end IS NULL OR trial_end < NOW()
    $$,
    'finance-team',
    '2026-01-15',
    50000,   -- we'd notice if MRR dropped below $50k
    500000,  -- or jumped above $500k overnight
    0.15     -- >15% month-over-month change triggers alert
);

Then a nightly job runs each registered definition and compares it to the previous day's snapshot:

-- Drift detection: compare today's metric values to yesterday's
WITH today AS (
    SELECT metric_name,
           sql_definition,
           bounds_lower,
           bounds_upper,
           change_pct_max
    FROM metric_definitions
),
snapshots AS (
    SELECT metric_name,
           metric_value,
           snapshot_date,
           LAG(metric_value) OVER (
               PARTITION BY metric_name ORDER BY snapshot_date
           ) AS prev_value
    FROM metric_snapshots
    WHERE snapshot_date >= CURRENT_DATE - INTERVAL '2 days'
)
SELECT s.metric_name,
       s.metric_value AS current_value,
       s.prev_value,
       ROUND(ABS(s.metric_value - s.prev_value)
           / NULLIF(s.prev_value, 0) * 100, 2) AS change_pct,
       CASE
           WHEN s.metric_value < t.bounds_lower THEN 'BELOW LOWER BOUND'
           WHEN s.metric_value > t.bounds_upper THEN 'ABOVE UPPER BOUND'
           WHEN ABS(s.metric_value - s.prev_value)
               / NULLIF(s.prev_value, 0) > t.change_pct_max
               THEN 'EXCESSIVE DRIFT'
           ELSE 'OK'
       END AS status
FROM snapshots s
JOIN today t ON t.metric_name = s.metric_name
WHERE s.snapshot_date = CURRENT_DATE;

This catches two categories: gradual drift (someone quietly changed a filter three weeks ago and MRR has been creeping up) and sudden breaks (a migration dropped a column and churn reads as zero). But it's reactive. The real fix is preventing deviation in the first place.

Step 3: Enforce definitions with a semantic layer

A semantic layer sits between your raw data and every consumer — dashboards, notebooks, ad-hoc queries, AI agents. You define each metric once, with its exact SQL logic, and every downstream query references the definition instead of reimplementing it.

This is where most teams stall. They know they need it but building one from scratch feels like a six-month project. It doesn't have to be. The minimum viable semantic layer is a set of metric definitions with validation rules attached.

In Fastero, a metric definition looks like this:

metrics:
  mrr:
    label: "Monthly Recurring Revenue"
    description: >
      Sum of active subscription amounts normalized to monthly.
      Excludes trials. Annual plans divided by 12.
      Multi-currency converted at invoice-time USD rate.
    sql: |
      SUM(
        CASE WHEN billing_interval = 'year'
             THEN amount_usd / 12
             ELSE amount_usd
        END
      )
    filters:
      - "status = 'active'"
      - "trial_end IS NULL OR trial_end < NOW()"
    source_table: subscriptions
    owner: finance
    validation:
      bounds: [50000, 500000]
      max_daily_change_pct: 15
      required_dimensions: [currency, plan_type]

When anyone queries MRR — through a dashboard, an API call, or an AI agent asking "what's our MRR by plan type?" — the semantic layer compiles the canonical SQL. No one writes their own WHERE clause. No one "forgets" to exclude trials. The definition is the query.

If you're already using dbt, you can import your existing metric definitions directly. Fastero's dbt integration reads your metrics: blocks from dbt YAML files so you don't have to redefine what you've already built. It adds the validation and governance layer on top — bounds checking, drift alerts, ownership tracking, change history.

The governance piece nobody wants to talk about

Validation is technical. Governance is political. Someone has to own each metric definition, and changing it requires a review process. Not a heavyweight JIRA workflow — just a clear answer to "who decides what MRR means and how do I propose a change?"

The pattern that works: each metric has an owner (usually a team, not a person). Changes go through a PR-like review. The semantic layer keeps a changelog so when the board asks "why did MRR jump 8% this quarter?" you can say "3% was organic growth, 5% was a definitional change approved on March 15 to include usage-based overages" instead of "uh, let me check."

Fastero tracks this automatically. Every definition change is versioned. You can diff any two versions and see exactly what changed — the SQL, the filters, the bounds. Dashboards update automatically. Old snapshots retain the definition that was active when they were computed, so historical comparisons stay apples-to-apples.

Where most teams go wrong

They try to boil the ocean. They audit 200 metrics, spend three months building a taxonomy, and ship nothing.

Start with the five metrics your CEO asks about. MRR, churn, active users, CAC, LTV. Audit just those. Get the definitions agreed upon in one meeting. Put them in a semantic layer. Wire up drift detection. Ship it in a week.

Then expand. Add the metrics your board deck uses. Then the ones marketing reports on. You'll hit 80% coverage within a month, and by then the habit of defining metrics centrally is baked in.

The metrics layer approach doesn't require ripping out your existing stack. It's additive. Your dashboards still work. Your dbt models still run. You just have one place where "MRR" means exactly one thing, and everyone queries against that definition instead of rolling their own.

If your team is still arguing about whether MRR from Stripe should include trials, you don't have a data problem. You have a definition problem. Fix the definition, enforce it with a semantic layer, and the arguments stop.

Try Fastero free — define every metric once, validate it automatically, and stop arguing about which MRR number is right. No credit card required.