Where metric definitions go wrong
| Dimension | Symptom | Control |
|---|---|---|
| Two versions of revenue | Different departments report different totals for the same period. | One certified definition with an owner and a written calculation. |
| Doubled totals after a join | Aggregates inflate when a new dimension is added. | Relationship cardinality documented and tested in the model. |
| History changes unexpectedly | Last year’s figures move when an attribute is updated. | History handling decided per attribute; snapshot versus current value. |
| Access breaches | A user sees rows they should not. | Row-level rules tested with real identities and role changes. |
| Stale numbers presented as current | Reports show old data without saying so. | Refresh state visible to consumers with a failure alert and responder. |
What a metric definition must state
A definition is usable when someone can implement it from the text alone: the calculation, the grain it is measured at, included and excluded cases, the time basis, the currency or unit conventions, and the owner who resolves disputes. Anything less leaves room for two reasonable readings and produces two valid-looking numbers.
- Calculation expressed against named columns
- Grain, filters, exclusions and time basis stated
- Owner named, with a change process
Where the model sits in the architecture
The semantic model should consume governed data rather than re-deriving it, so definitions are inherited by every consumer rather than repeated per report. That keeps one source of truth for measures and lets BI tools stay presentation choices. Where a semantic layer cannot live in the platform, the tool becomes the de facto layer — which is workable if it is treated as production software.
Relationships, hierarchies and time
Models fail in predictable places: many-to-many relationships that double-count, snapshot tables joined to transactions, fiscal calendars differing from the reporting calendar, and currency conversion applied at the wrong grain. Each deserves an explicit decision recorded in the model documentation instead of a formula nobody can trace.
A common double count: a sales fact joined to a promotions table where one order can belong to two promotions. Summing revenue across that join counts the order twice. The fix is a bridge table with an allocation rule, or measures that aggregate at order grain before the promotion breakdown is applied.
Certification and change control
Certification only means something if it is enforced by stated quality rules instead of a badge: a certified dataset is one others may depend on, and changes to it go through review, testing and a release path. Deprecation matters too. A measure nobody uses any more should be retired deliberately, because leaving it available guarantees future disagreement.
Access, security and the model
Row-level and column-level access rules belong to the model, not to individual reports. Test them with the identities real consumers hold, including role changes and revoked access. A hidden report page is not an access control, and an untested rule is an assumption.
Illustrative example · hypothetical organisation
Worked example: writing one definition
Consider an illustrative definition of an active customer, written so it can be implemented from the text alone.
Active customer: a customer account with at least one invoiced order in the 90 days ending on the reporting date, excluding internal, test and fraud-flagged accounts. Measured per account, not per contact. Time basis: invoice date, in the account's billing time zone. Owner: head of customer operations.
With that text, two analysts working independently should produce the same count. If they do not, the definition is ambiguous somewhere and is fixed before the measure is certified. A metric defined this precisely also becomes the reference for measures built on it, such as retention.
Operating the model
Give the model a refresh contract: what must complete before it is usable, how failures are surfaced, and who responds. Monitor usage, because an unused certified measure is either mis-marketed or wrong. Review disputes as they arise and record the resolution in the definition, so the same argument does not recur next quarter.
Questions and trade-offs
Question
How many metrics should be certified?
Far fewer than teams expect. Certify the measures that appear in recurring decisions and that people argue about; leave exploratory calculations uncertified and clearly labelled. A small, enforced set that people trust is more valuable than a large catalogue that quietly disagrees with itself.
Question
Do we need a dedicated tool for this?
Not necessarily. A governed warehouse layer, a BI semantic model or a metrics-as-code approach can each work. What matters is that one definition exists, it is version-controlled, an owner answers for it, and consumers can tell certified from exploratory content.
Question
What is a metrics layer?
A metrics layer, often called a semantic layer, defines business measures once in a central place, frequently as code, so BI tools, notebooks and applications query the same definitions. The dbt Semantic Layer, LookML and Power BI semantic models are examples. Where it lives matters less than having one owned definition per measure.
