Your KPI Needs a Contract: Build Metrics That Survive Dashboards and AI
A practical metric definition covers entity, grain, time, eligibility, joins, tests, and ownership—not just a SQL expression.
Two dashboards display “monthly active customers.” One says 18,420. The other says 21,773.
Both queries run. Both charts look professional. Both teams can explain their SQL.
The disagreement usually starts before the SQL: nobody wrote down what active, customer, or month means.
A metric is not just an aggregation. It is a contract between the people who produce data and the people — or machines — that make decisions from it.
That contract matters more as metrics move beyond dashboards. Spreadsheets, embedded analytics, notebooks, APIs, and AI agents can request the same business number through different interfaces. If each tool reconstructs the logic independently, inconsistency is the default.
The industry is responding with semantic layers and open interchange formats. MetricFlow compiles reusable metric definitions into SQL. Cube models measures, dimensions, joins, and access policies for multiple consumers. Apache Ossie, formerly Open Semantic Interchange, is developing a vendor-neutral YAML specification for exchanging semantic metadata across analytics, BI, and AI systems.
Those tools are useful. But installing one does not create agreement. You still need to define the metric.
This tutorial shows how.
Start with the decision
Before writing YAML or SQL, write one sentence:
This metric helps [decision-maker] decide [action] at [cadence].
For example:
Monthly active customers helps the retention team decide which customer cohorts need intervention during the first weekly review after month-end.
That sentence exposes requirements a formula cannot. The metric is for retention, not billing. It must be stable by the first weekly review. Cohort dimensions matter. Late-arriving events need an explicit policy.
If you cannot name the decision, you may be building a number that is easy to display and hard to use.
The six parts of a metric contract
A durable metric definition needs at least six components.
1. Entity: what are we counting?
An entity is the real-world thing whose identity must remain stable: customer, order, account, subscription, patient, device, or session.
For monthly active customers, the entity might be customer_id. That raises questions immediately:
- Can one person have multiple customer IDs?
- Are test accounts excluded?
- Are deleted or merged accounts retained historically?
- Is an organization one customer, or is each user a customer?
Do not move on until the identifier and deduplication rule are explicit.
2. Grain: what does one row represent?
The source model might contain one row per event, order line, invoice, or daily account snapshot. The metric output might require one row per customer-month.
Those are different grains.
If you join an event table to a subscription-history table with overlapping effective dates, one activity event can multiply into several rows. A final count distinct may hide the multiplication without fixing every downstream measure.
Write both grains:
- input grain: one row per product event;
- metric grain: one distinct customer per calendar month.
3. Time: which timestamp and which boundary?
Most production tables contain several timestamps: event time, ingestion time, processing time, billing time, and update time.
Choose one and define the timezone, calendar or fiscal period, inclusive and exclusive boundaries, late-arriving records, and whether historical results can be restated.
A robust monthly window uses a half-open interval:
month_start <= event_time AND event_time < next_month_start
That convention avoids double-counting events exactly at midnight.
4. Eligibility: which rows qualify?
“Active” needs an observable rule. A login may count. A background sync, failed payment webhook, internal admin action, or bot event may not.
Specify positive and negative conditions. For example:
- include successful user-initiated product events;
- exclude internal accounts and synthetic monitoring;
- require the account to be in a customer state on the event date;
- exclude events marked as duplicates;
- include late events received within seven days of month-end.
Eligibility rules are where business meaning usually hides.
5. Aggregation: how is the result calculated?
Only after entity, grain, time, and eligibility are defined should you write the expression.
COUNT(DISTINCT customer_id)
For revenue, the contract may also need currency conversion, tax treatment, refunds, credits, recognition date, and rounding. SUM(amount) is rarely the full definition.
6. Dimensions and join paths: how may it be sliced?
A metric can be correct in total and wrong by segment.
List the dimensions that are safe to use and the path to each one. A customer’s current plan is not necessarily the plan they held when an event occurred. Joining to a current-state customer table can rewrite history.
For every dimension, decide whether it is event-time, period-end, current-state, or slowly changing with an effective-date join.
A vendor-neutral example
The following YAML is illustrative rather than tied to a specific product. Its purpose is to make the contract reviewable.
metric:
name: monthly_active_customers
description: Distinct paying customers with a qualifying user action
owner: retention_analytics
decision:
consumer: retention_team
action: prioritize_cohort_interventions
cadence: monthly
entity:
name: customer
key: customer_id
source:
model: product_events
input_grain: one_row_per_event
time:
column: occurred_at
timezone: UTC
grain: month
interval: half_open
late_arrival_window_days: 7
restatement_policy: revise_open_month_only
eligibility:
include:
- event_status = success
- initiated_by = user
- account_state = paying
exclude:
- is_internal = true
- is_synthetic = true
- is_duplicate = true
aggregation:
expression: count_distinct(customer_id)
dimensions:
- name: country_at_event
join_semantics: event_time
- name: plan_at_event
join_semantics: effective_dated
A semantic-layer implementation will use its own syntax. The underlying questions should survive the translation.
Turn the contract into tests
Documentation explains intent. Tests detect drift.
Test 1: uniqueness at the declared grain
Build the intermediate relation at customer-month grain and assert that the key is unique.
SELECT month_start, customer_id, COUNT(*) AS rows_at_grain
FROM metric_base
GROUP BY 1, 2
HAVING COUNT(*) > 1;
Expected result: zero rows.
This catches join multiplication before a final distinct count conceals it.
Test 2: boundary fixtures
Create deterministic fixtures around time boundaries:
- 23:59 on the last day of the month;
- exactly 00:00 on the first day of the next month;
- a daylight-saving transition if local time is used;
- an event arriving after the reporting cutoff;
- a correction inside and outside the restatement window.
Write the expected membership beside every fixture.
Test 3: eligibility cases
For every inclusion and exclusion rule, create at least one positive and one negative example. A synthetic monitoring event should fail. A successful user action should pass. An internal employee using a real account should follow the documented policy.
This is metric unit testing: small cases with obvious answers.
Test 4: reconciliation
Compare the metric with an independent trusted system at the level where the systems should agree.
For example, reconcile paying-customer eligibility to billing account status, but do not expect billing to reproduce product activity. Investigate the intersection and the difference sets, not just the totals.
A total can match while the underlying populations differ.
Test 5: invariants over time
Not every metric should be monotonic, but many have relationships that should usually hold:
- weekly active customers should not exceed monthly active customers for compatible windows;
- segmented totals should reconcile when segments are exhaustive and mutually exclusive;
- conversion count should not exceed eligible opportunity count;
- a cumulative metric should not decrease unless the contract permits reversals.
Use anomaly thresholds as investigation triggers, not automatic proof of an error.
Version the meaning, not only the code
A code diff can show that a filter changed from seven days to thirty. It cannot tell a consumer whether the metric should be backfilled, renamed, or compared with historical values.
Every material change should record what changed, why it changed, its effective date, whether history was recomputed, the expected effect, and migration instructions.
If the meaning changes substantially, create a new metric version instead of silently redefining the old one.
Where the semantic layer helps
Once the contract exists, a semantic layer can make it executable and reusable.
MetricFlow describes metrics in code and compiles requests into reusable SQL, including multi-hop joins, ratios, cumulative metrics, and time-grain changes. Cube exposes governed measures, dimensions, joins, and access rules to multiple downstream interfaces. Apache Ossie aims to standardize interchange of datasets, relationships, dimensions, metrics, and AI context across vendors.
That matters for AI because an agent pointed at raw warehouse tables must rediscover business logic on every request. A governed semantic model reduces the search space: the agent selects certified entities, dimensions, and metrics instead of inventing a new definition of revenue.
But the layer cannot rescue an ambiguous contract. Centralizing a bad definition only makes the wrong number consistent.
A review checklist
Before certifying a metric, ask:
- What decision does it support?
- What entity does it count or measure?
- What is the input grain and output grain?
- Which timestamp, timezone, and period boundary apply?
- Which rows are included and excluded?
- Which dimensions are safe, and what are their temporal join semantics?
- How are late data, corrections, and restatements handled?
- Which tests prove the definition at boundaries and after joins?
- Who owns the definition and approves changes?
- Can every downstream tool retrieve the same result from the same contract?
If these answers live only in one analyst’s head, you do not have a governed metric. You have a fragile query with a popular name.
What would make me wrong
This approach is heavier than necessary for exploratory analysis, a one-off diagnostic, or a metric with no recurring decision attached. Not every calculation deserves a governance process.
It would also be wrong to claim that a semantic layer guarantees identical numbers across tools. Differences can still come from caching, permissions, freshness, query context, unsupported functions, or vendor-specific execution semantics. The contract reduces ambiguity; it does not eliminate operational failure.
The strongest evidence against this approach would be teams maintaining reliable cross-tool metrics over time with lightweight generated documentation and tests, without a formal contract layer. In that case, the principle is unchanged: make business meaning explicit and executable, but use the smallest mechanism that keeps it true.
Sources
Found this useful? Passing it on to someone who builds is the best way to help the publication grow.