One named metric beats three defensible derivations of active users

Three numbers went into the same board pack. Growth's dashboard reported 128,400 active users for July, finance's reported 141,700, and a text-to-SQL agent, asked in Slack the morning the deck was due, reported 133,912. The meeting spent eleven minutes on which one was right and then moved on without resolving it, which is the outcome that ends products of this kind.

A derivation here means the full set of choices that turn a phrase like "active users" into rows: which table the count runs over, what counts as being active, which column identifies the thing being counted once, where the month starts and stops, and who is left out. Change any one of those and the number changes while the phrase stays the same. None of the three derivations below contains a mistake, and that is what makes this hard: there is nothing to fix in any of them individually.

Three defensible readings of one phrase#

Each derivation is defensible in isolation:

Nothing here is a bug. Growth measures product engagement and so requires an action; finance measures billable relationships and so counts accounts. The agent's query counted distinct users with any event in the UTC month over a table it was given, which is the most literal reading of "active users" available and matches neither.

The useful thing in that table is not the bottom row. It is the gaps between the rows above it, because those gaps are what produce the bottom row, and two of the three columns are close enough to compare term by term. Growth and the agent read the same table, dedup on the same column, and drop the same internal domains. They differ in exactly three places: growth requires a qualifying action where the agent takes any event row, growth drops trial accounts where the agent keeps them, and growth cuts the month in each account's local time where the agent cuts it in UTC.

Two of those three differences only ever remove users. Requiring one of create, share or export is a filter on event rows, so a user the agent counts and growth does not is one whose July activity was entirely of some other type. Dropping trial accounts removes whoever sits on one. Neither condition can admit a user the other reading missed, so if the month boundary were shared, growth's set of users would sit strictly inside the agent's, and the excess would be exactly those two removed populations taken together. Taken together rather than added: a trial-account user whose events were all non-qualifying is removed by both conditions and is one user, not two.

The third difference behaves differently, and it is the one that stops this from being clean arithmetic. Cutting the month in account-local time does not narrow the set — it slides the window. For any account whose local offset from UTC is not zero, some events near the boundary fall into July under one reading and into June or August under the other, and this happens in both directions at once. So the strict-subset relation survives the first two differences and is broken by the third, and the honest statement of the gap is that the agent's excess over growth's figure is those two removed populations, plus or minus a boundary term whose sign nothing in the table determines.

What that licenses is a direction and not a measurement. It says the agent's number should come out at or above growth's once the boundary term is set aside, and it does. It does not license reading the gap as a count of anything in particular, because attributing it would require the trial population and the boundary term separately, and the table gives neither. A reader who computes the difference between those two figures and reports it as "non-qualifying activity" has produced a fourth number with no derivation behind it.

Finance is not on that ladder at all. It reads a different table, counts a different kind of thing, uses a different column to deduplicate, and keeps trial accounts. No subset relation holds between it and either of the others in either direction, so its gap to them decomposes into nothing.

That last point is where the strongest wrong instinct in this table lives. A user belongs to an account, several users can belong to one account, and so it feels as though a count of accounts must come out below a count of users — which would put finance's figure lowest of the three. It is the highest. The expected inequality holds only when both counts range over the same population in the same window, and these two do not. fct_sessions records authenticated sessions, and a session exists whether or not it produced a single row in fct_events, so an account that signed in and read for half a minute without creating, sharing or exporting anything is visible to finance and invisible to both of the others. Finance also keeps the trial accounts that growth drops. Both of those reach a population the event-based readings never see, and either one is enough to lift the account count above the user count. There is no arithmetic error here to correct.

Where the checks stop#

Run the checks from the last two chapters over the agent's query and they all pass. The grain is right — fct_events at [event_id], deduplicated with COUNT(DISTINCT user_id). The literals exist, the result is non-empty and not truncated, no nullable column is negated, there is no currency. The query is correct. The question is what was under-specified, and no amount of inspecting rows recovers a meaning that was never fixed anywhere.

It is worth being precise about why those checks cannot help, because the instinct on meeting this failure is to add more of them. Every check in that list is answerable from two inputs: the statement, and the schema of the tables it touches. Whether a grain matches, whether a literal appears in a column's domain, whether a negation crosses a nullable column, whether the result was cut off by a limit — each of those is decided by reading the query against the catalogue. None of them consults the question. The question is not an input to any of them.

That is what makes this failure the shape it is. The verifier's pass rate stays pinned at the top across all three derivations, because all three are valid statements over tables that exist, while the thing that decides whether the number means anything varies across three incompatible readings. A quantity that does not move while the dangerous property moves freely is not a defence, however reassuring it is to watch. Every green check on the agent's query is telling the truth, and none of it is evidence about the number.

Follow that one step further and the fix appears on its own. A check that could catch this would need the meaning of "active users" as a second input: something stating which action counts, which accounts are in, and where the month begins. Writing that down in a form a program can read is the whole of what a registry is. The remedy is not another verifier — it is giving the verifiers a definition to check against.

Where a definition can live#

There are three places to put it, and only one of them survives a second question.

The prompt is the first and worst. A paragraph in the system prompt saying that active users means distinct user_id with a qualifying action in the account-local calendar month, excluding internal domains and trial accounts, works for a while. It competes for budget with the schema block, it applies probabilistically, and it multiplies: forty metrics is forty paragraphs and about 3,000 tokens of policy that the generator reads on every question, including the ones about shipping delays.

Each of those three faults does separate damage. Competing for budget means the definition and the schema block are rivals, so a longer policy section buys its precision by making the tables less visible. Applying probabilistically means the definition holds on most questions and there is no point at which it holds by construction, which is exactly the property that makes the disagreement above reappear intermittently rather than never. Multiplying means the cost scales with the number of metrics while the benefit of any one paragraph is felt only on the questions that touch that metric, so the ratio gets worse with every metric added.

The second place is the example queries from chapter 2, which is better because it shows rather than tells, and still fails on the tail. A question phrased differently enough to retrieve a different example gets a different definition, and there is no point in the system where a person can read what the current definition is. Retrieval is choosing the example, so phrasing is choosing the definition, and a user who rewords a question they did not like the answer to is selecting between meanings without knowing it.

The third is a semantic layer: a registry where each metric is a named object carrying its own SQL expression, its source model, its filters, its grain and its time semantics, and where the generator's job changes from writing SQL over raw tables to selecting a metric by name.

# metrics/active_users.yml
metric: active_users
label: Active users
owner: growth-analytics
description: >
  Distinct users who performed a qualifying action. The company-standard
  engagement measure; approved 2025-03-11 (see ADR-114). Not the same as
  billable accounts — use billable_accounts for revenue-facing questions.
model: fct_events
aggregation: count_distinct
expression: user_id
filters:
  - "event_type IN ('create', 'share', 'export')"
  - "is_internal = FALSE"
  - "account_status <> 'trial'"
time_dimension:
  column: occurred_at
  timezone: account_local        # not UTC: see ADR-114 §3
  grains: [day, week, month, quarter]
  complete_periods_only: true
dimensions: [plan, country_code, acquisition_channel, account_id]
certified: true

Read that file against the growth column of the table above and every choice in the column is present as a line: the qualifying action list, the internal exclusion, the trial exclusion, the dedup on user_id, the account-local month. What was a set of decisions distributed across a dashboard's query, a BI tool's settings and somebody's memory is now a set of decisions in one file, each on its own line, each reviewable.

The description field carries the thing a SQL definition cannot: what the metric is for and what it is not, with a pointer to the decision record. That line about billable_accounts is what prevents the finance question from being answered with the growth metric, and it is retrieved into the prompt alongside the metric name.

None of this is new, which is worth knowing before designing one from scratch. Business Objects shipped the "universe" in 1992 for exactly this problem: business users writing their own reports produced their own definitions, so the vendor put a mapping layer between the report builder and the tables. Looker's LookML in the 2010s made the same idea version-controlled and reviewable, and the current generation — dbt's MetricFlow, Cube, and the metric layers inside the warehouses themselves — expose it over an API so that something other than a BI front end can call it. The consumer changed from a human dragging fields onto a canvas to a model emitting JSON, and the reason the layer exists did not change at all. A model re-deriving a metric per question is the 1992 problem with a faster report builder.

The generator emits a spec, not SQL#

The path through the system changes shape. Instead of producing a statement, the model produces a small object naming a metric, its dimensions, its filters and its time grain, and the semantic layer compiles that object to SQL.

{
  "metric": "active_users",
  "time": {"grain": "month", "range": "2025-07-01/2025-08-01"},
  "group_by": ["plan"],
  "filters": [{"dimension": "country_code", "op": "in", "values": ["DE", "AT", "CH"]}],
  "limit": 100
}

-- compiled by the layer, not written by the model
SELECT p.plan,
       COUNT(DISTINCT e.user_id) AS active_users
FROM analytics_marts.fct_events e
JOIN analytics_marts.dim_accounts a ON a.account_id = e.account_id
JOIN analytics_marts.dim_plans p    ON p.plan_id    = a.plan_id
WHERE e.event_type IN ('create','share','export')
  AND e.is_internal = FALSE
  AND a.account_status <> 'trial'
  AND a.country_code IN ('DE','AT','CH')
  AND CONVERT_TIMEZONE('UTC', a.timezone, e.occurred_at)
        >= '2025-07-01'
  AND CONVERT_TIMEZONE('UTC', a.timezone, e.occurred_at)
        <  '2025-08-01'
GROUP BY p.plan
LIMIT 100;

Those two blocks are worth reading against each other clause by clause, because the relationship between them is exact. The select list comes from the registry's aggregation and expression plus the spec's group_by. The FROM is the registry's model. Three of the four WHERE conditions on event type, internal flag and account status are the registry's filters, copied in order. The country condition is the spec's filter. The two timezone conversions are the registry's timezone: account_local applied to its occurred_at column, and the two dates bounding them are the spec's range, half-open so that the last instant of July belongs to July and the first of August does not. The GROUP BY and the LIMIT are the spec's. The two joins are the layer's own work, required because plan, country_code and account_status do not live on fct_events at all.

Every clause of the compiled statement traces to exactly one of those two sources, and nothing appears that traces to neither. That closure is what licenses the strongest claim in this chapter: compilation is a total function of registry plus spec, so two questions that produce the same spec produce the same SQL, character for character, however differently they were phrased. The distribution of numbers for one metric does not narrow — it stops existing.

The closure runs one way and not the other, and the residual is worth naming. Every clause of the SQL comes from the registry or the spec; not every field of the registry reaches the SQL. complete_periods_only leaves no trace here because July was already complete when the query ran, so the flag was satisfied without needing to be expressed. grains decides which specs are accepted rather than what an accepted one compiles to. certified, owner, label and description never compile to anything. Reading the generated statement therefore tells you what ran; it does not tell you that the whole policy was applied, and a review that only reads compiled SQL will miss a registry setting that silently accepted a spec it should have rejected.

The model never sees the timezone conversion, the trial exclusion or the join to dim_accounts, and cannot forget them. Its output surface shrinks from arbitrary SQL to a JSON object over a closed vocabulary, which changes what validation means for this path: the spec is checked against the registry — does this metric exist, is this a declared dimension of it, is this grain supported — and a spec that passes compiles to SQL the layer wrote. The structural checks of chapter 3 still run over the compiled statement, because defence in depth costs 3ms, but they have almost nothing left to catch.

The shrinking of that output surface is the mechanical reason validation gets easier, and it is a change in kind. "Is this SQL right for this question" has no procedure behind it, because the space of statements is unbounded and correctness depends on an intent nobody wrote down. "Is active_users a metric, is plan one of its declared dimensions, is month in its grain list" is three lookups against a file, each with a definite answer. A closed vocabulary is checkable by enumeration; an open one is not.

Three consequences follow that are easy to underrate. Two different phrasings of the same question compile to identical SQL, so the agent stops producing a distribution of numbers for one metric. The dashboards can be rebuilt on the same registry, at which point growth's dashboard and the agent agree by construction rather than by coincidence. And when the definition changes — the qualifying action list gains comment in September — one file changes and everything downstream moves together, with the change reviewable in a pull request by the metric's owner.

Finding the metric is a retrieval problem again#

The registry does not remove the selection step from chapter 1; it moves it. Forty metrics fit in a prompt as cards — name, label, description, dimensions, grains — for roughly 4,000 tokens, so at this size the whole registry can be shown and selection is the model's job. Above a few hundred metrics, which is where a mature registry lands, the cards get retrieved and the same all-or-nothing recall metric applies to metric selection: the share of evaluation questions where the metric the gold answer uses was among those shown.

Set that token figure beside the one from the prompt-policy option and the comparison goes the wrong way. The same forty metrics cost about 3,000 tokens as paragraphs of policy and roughly 4,000 as cards, so the registry is the more expensive of the two at this size, and a proposal that sells it as a saving on prompt budget will be contradicted by the first measurement. What the registry buys at forty metrics is that the definition exists in one place, versioned and readable by a person. The token saving arrives later, at the few hundred metrics where the whole set stops fitting and only the retrieved cards are shown.

Metric cards retrieve better than tables did, for a reason worth noting. They are written in business vocabulary by the people who use the words, so the vocabulary gap that accounted for 52 of the 87 misses in chapter 1 largely does not arise. The failure that replaces it is confusability: active_users and billable_accounts retrieve for the same question, and choosing between them is a judgement about intent rather than about text. That is what the "not the same as" line in the description is for, and it is the field most worth writing carefully.

Notice that confusability is the original disagreement in a new place. Growth and finance were never arguing about text; they were arguing about which of two real measures a question wanted. The registry does not settle that question. It changes it from an argument nobody can point at into a choice between two named objects, made once per question, with a written line in each description saying when to prefer the other.

A question that maps to no metric must be recognised as such rather than forced onto the nearest one. Requiring the model to return an explicit "metric": null with a reason, and treating a low-confidence match the same way, is what routes a question to the fallback path instead of answering it with a metric that is 80% related.

Definitions need tests, not just owners#

A metric file is code that produces numbers, and it decays the same way. Two tests keep it honest, and both are cheap enough to run on every pull request that touches the registry.

# metrics/tests/active_users.yml
- metric: active_users
  window: {grain: month, start: "2025-04-01", end: "2025-05-01"}
  filters: []
  expect: 129842          # pinned 2025-05-12, approved by @growth-analytics
  tolerance: 0            # historical months are closed; any drift is a change
- metric: active_users
  window: {grain: month, start: "2025-04-01", end: "2025-05-01"}
  group_by: [plan]
  expect_sum_equals_total: true   # dimensional breakdown must reconcile

The pinned value turns a redefinition into a visible diff: whoever adds comment to the qualifying action list sees April move from 129,842 to 134,110 in CI and has to update the pin deliberately, with the approval of the owner, rather than discovering it when someone asks why the year-on-year chart bent in September. The reconciliation test catches the other common defect, where a dimension join drops rows so the breakdown no longer sums to the headline, which is the version of this bug that survives longest because each number looks fine alone.

A zero tolerance is unusual enough to justify. April is a closed month, so the rows underneath it do not change, which means there is no measurement noise for a tolerance to absorb and any movement at all is a change somebody made. That reasoning is specific to a closed window. Pinning a metric over a period still filling would need a tolerance, and pinning it with a tolerance of zero would fail every night for reasons that are not defects.

The direction of the April movement is also fixed in advance, which is a useful thing to know before reading a CI diff. Adding an event type to an IN list can only admit rows, never remove them, so a user who qualified before still qualifies and some who did not now do. The count can therefore only rise. A change of that shape that made April fall would be reporting a second defect somewhere else, and the pin would be catching it by accident.

The second test is the more interesting of the two, because what it asserts is larger than it looks. It runs the metric grouped by plan, adds the numbers in that column, and requires the sum to equal the metric run ungrouped. That equality is not automatic, and the reason is the aggregation: count_distinct is not additive. Adding the distinct counts of two groups gives the size of their union only when the groups have no member in common.

So a passing reconciliation asserts two things at once. The join out to dim_plans neither dropped a counted user nor duplicated one — that is the defect the comment names. And every counted user carries exactly one plan value across the window, which is a claim about the data rather than about the join. A user whose July events sit under two accounts on different plans lands in both buckets, the sum then comes out above the headline, and every individual number in the breakdown is still correct.

What that licenses when it passes is narrow and genuinely useful: the plan breakdown can be quoted next to the headline without a caveat. What it does not license is the same confidence for any other dimension. Country and acquisition channel need their own reconciliation tests, because "one value per counted user per window" is a property of the dimension, not of the metric — and a metric whose plan breakdown reconciles perfectly can have a channel breakdown that oversums by every user who arrived twice. The residual underneath both tests is the one neither can reach: a definition with the wrong filter in it pins cleanly and reconciles cleanly, because both tests check the metric against itself.

Coverage and the questions a registry declines#

The cost is organisational and it is the part engineering cannot do alone. Getting growth and finance to agree took the five weeks described above, and that is the normal shape of it. The registry does not make definitions correct; it makes them singular, named, owned and reviewable, which is a smaller claim and the one that matters.

The other cost is coverage. A registry answers questions someone anticipated, and the first month of real traffic is full of questions nobody did — comparisons across two metrics with different grains, a filter on a dimension that was never declared, a question about a table that has no metric at all. Measured over 900 questions, 40 certified metrics covered 71% of volume, leaving 29% that a strict registry-only system must decline.

Those two shares account for the whole of that traffic with nothing left over, and the exactness is the finding rather than a coincidence of rounding. A registry-only system has two states and no third one: a question either maps to a metric and is answered with a guaranteed definition, or it does not and is refused. There is no partially-covered category absorbing the difference, which is why the design can be argued about honestly — the 29% is a known, countable set of questions somebody has to decide what to do with.

The coverage column of that table cannot be read down as a single quantity, and reading it that way is the mistake it invites. The first row reports an attempt rate: the share of questions the design will produce something for. The second reports a guarantee rate: the share it will produce something with a fixed definition for. An attempt is not an answer and an answer is not a correct one, so the distance between those two entries is not a distance in usefulness — it is the distance between two different measurements that happen to be written as percentages in the same column.

The third row is what makes the column legible. The hybrid runs the same registry over the same question mix, so its certified path covers the same share the second row reports, and it attempts the remainder as generated SQL, reaching the same total as the first row. Read that way, the first row's extra reach over the second is exactly the uncertified tail — which the hybrid also reaches, and additionally labels. And the same reading says something about the roughly five per cent that none of the three attempts: those questions are outside the warehouse's answering ability altogether, and no choice among these designs touches them.

The hybrid is what most teams end up running, and its critical feature is the label rather than the routing. An answer produced from active_users says so and links to the definition; an answer produced by generated SQL over raw marts says that it was generated, shows the statement, and is presented as a draft rather than a figure. Users calibrate on that distinction quickly, and the second class stops being quoted in board packs without someone checking it.

A definition that is wrong everywhere at once#

There is one failure the layer introduces that direct generation does not have. A metric that is defined wrongly — a filter that excludes a segment it should include — is now applied uniformly to every question that touches it, so the error is consistent, invisible, and reproduced across the dashboards too. Consistency makes errors systematic, which is better than random because it can be found and fixed once, and worse in the interval before anyone looks. Metric definitions therefore need the same treatment as code: version control, an owner, review on change, and a test that pins the value for a fixed historical window so that a redefinition shows up as a diff rather than as a drifting number.

Treating a metric as code is a fair comparison and it has a limit worth marking, because the limit is where the intuition it carries stops being useful. A constant duplicated across a codebase announces itself the moment two copies disagree: something fails, or a test does, or two screens show different values side by side. A metric re-derived per question produces numbers that are each internally consistent with their own derivation, so nothing fails anywhere. The three figures at the top of this chapter did not break a single check between them. That is why the remedy for duplicated constants — more tests over the places that use them — does not transfer, and the remedy that does is reducing the number of places the definition exists to one.

Three derivations of one phrase are not three bugs. They are one under-specified phrase, which is why every check in this topic passed on all three and why adding more of them would have changed nothing: a check reads a statement against a schema, never against a question. What closes the gap is giving the definition a name, an owner and a test, so that using it is a lookup and re-deriving it is not something anybody is asked to do. The guarantee that buys is consistency rather than correctness, which is the smaller of the two promises and the only one a registry can keep.

Everything through this chapter has been components: retrieval with a metric, a schema block, a validator, a role, a grain check, a result check, a registry. Whether they add up to a product that should exist is a separate question, and it is decided before any of them is built.