Where a metric definition should live, and who gets to change it

The BI tool, the transformation layer and the warehouse can all hold a metric definition. They differ in who can edit it and who has to read it.

Sources Modeled tables Transformation layer Warehouse views BI tool model Consumers Reconcile notebooks, exports against finance
Three possible homes for one definition, all feeding the same consumers and one reconcile check against finance.
On this page
  1. Four copies of “active policies”
  2. Who edits, who reads
  3. Count the consumers that aren’t the BI tool
  4. active_policies, with its test attached
  5. Then delete the copies

Every metric definition has an edit button somewhere. Before I compare features, I want to know who can press it and who is forced to read what comes out.

That’s how I sort the three usual homes for a definition: the BI tool’s modeling layer, the transformation layer, and a view in the warehouse. Their feature lists overlap more every year. Their ownership doesn’t.

Four copies of “active policies”

I’ll use an invented insurer to show the problem. Fallowmere Mutual is fictional and so are its figures.

Fallowmere’s board pack and its operations dashboard disagree on active policies. When someone finally lists every place the number is computed, there are four: two BI workbooks, one transformation model, and a spreadsheet in finance. The highest and lowest are 3.8% apart.

The whole spread comes from one kind of policy: lapsed, then reinstated. One workbook counts any policy whose status is active today, so reinstated policies are in. The other excludes anything with a lapse date on it, so they’re out. The transformation model checks whether coverage was in force on the date in question. Finance counts from premium records and has its own rule for the gap between lapse and reinstatement.

Each of the four rules is defensible on its own, and none of the authors ever looked at the other three.

Who edits, who reads

BI tool model Transformation layer Warehouse view
Who edits Analysts and workbook authors Whoever can merge to the project repo Anyone with create rights on the schema
Review path Usually none, publish and it’s live Pull request, CI, tests Whatever your SQL deployment has, often nothing
Who can read it Users of that one tool Anything that queries the semantic layer or its output tables Anything with a SQL connection
Time dimensions Chosen per workbook Declared once, sliced by day, week or month at query time Baked into the view, one view per grain
Access control The tool’s own permissions, which stop at the tool Warehouse grants plus the layer’s own API permissions Warehouse grants and row policies, applied to everyone
Lock-in High, definitions rarely export cleanly Medium, YAML in git but one engine reads it Low, it’s SQL in your dialect

The BI tool is the closest home to the people asking the question. An analyst can change a definition in the same afternoon the business changes its mind, and I don’t want to dismiss that. The cost is that the definition is invisible to everything else. A notebook can’t call a workbook’s calculated field. So the notebook author rewrites it from memory, and now you have two.

The warehouse view has the opposite profile. Every tool can read it, and access rules apply to every consumer equally. But a view is owned by whoever created it, which in practice means nobody in particular. Views get copied to _v2, the original stays, and both keep getting queried.

The transformation layer sits between them. I mean metrics declared next to the models, as in the dbt Semantic Layer, where MetricFlow turns a metric and a requested grain into SQL. The definition goes through a pull request, so every change has an author and a diff someone else approved. Model contracts help here too: put one on the table the metric reads and a renamed or retyped column fails the build before it can shift the number. The price is distance. An analyst who used to click now has to open a pull request, and some will quietly go back to the workbook.

Count the consumers that aren’t the BI tool

My rule for picking is short. List everything that needs the number and cross off the BI tool.

If nothing is left, keep the definition in the BI tool and stop feeling guilty about it. Moving it buys you review you could get by restricting who publishes certified workbooks.

If one consumer is left, a warehouse view or a materialized table is enough, provided it has a named owner and it deploys through the same repo as everything else.

With two or more, I’d put it in the transformation layer. That’s the point where rewriting from memory starts happening faster than you can catch it.

Fallowmere has two: a pricing notebook, and a quarterly export to the regulator. So the definition moves to the transformation layer. The export doesn’t speak to a semantic layer API, and it doesn’t have to. The metric is also materialized as a plain daily table, built from the same definition, and the export reads that.

active_policies, with its test attached

Wherever the definition lands, I want one page a non-engineer can read and argue with. This is the format I’d use. It isn’t any tool’s syntax, just YAML that sits beside the real definition, with a reconciliation query that runs as a test and passes when it returns no rows.

metric: active_policies
description: Policies with coverage in force at the end of the as-of date.
owner: policy-data-team
approver: finance-controller
grain: one row per policy_id per as_of_date
time_dimension: as_of_date
month_rule: a month takes the value from its last day
source: analytics.fct_policy_coverage_daily
filters:
  - "coverage_start_date <= as_of_date"
  - "coverage_end_date is null or coverage_end_date > as_of_date"
  - "policy_status <> 'quoted'"
edge_cases:
  - Reinstated policies count from the reinstatement date.
  - Days between lapse and reinstatement do not count.
consumers:
  - bi_dashboards
  - pricing_notebook
  - regulator_export
reconciliation:
  against: finance.policies_in_force_monthly
  tolerance: 0.001
  query: |
    select
      m.as_of_date,
      m.active_policies,
      f.policies_in_force
    from analytics.metric_active_policies_daily as m
    join finance.policies_in_force_monthly as f
      on f.month_end_date = m.as_of_date
    where abs(m.active_policies - f.policies_in_force)
          > 0.001 * f.policies_in_force

The edge_cases block is where the 3.8% went. Two lines of plain English, agreed with finance, replace an argument that had been running across four files.

Finance’s number gets audited, so the test compares against it, even when the data team thinks its own logic is cleaner. If the two drift past the tolerance, the build fails and someone has to explain the difference in a pull request. Sometimes the explanation is that finance changed a rule. Good. Now the spec changes too, with a date on it.

Then delete the copies

Moving a definition is the easy half. Fallowmere’s migration only counts once the two workbook calculations are deleted and both workbooks point at the governed metric. Leave them in place “for reference” and they’ll be back in a board pack within a year.

Finance keeps its spreadsheet. It stops being a fourth definition and becomes the thing the test compares against, which is a better job for it.