Home › Data engineering › Metric views › 8 · Production
Data engineering · BI and analytics · free guide · 8 · Production
Metric views in production: challenges, anti-patterns, best practices
Writing a metric view takes an afternoon. Keeping a few dozen of them right for years, with many teams, tools and Genie spaces reading them, is the real work. This page collects what goes wrong, the patterns to avoid, and how to run them.
Twelve challenges
Production challenges and how to handle them
| Challenge | What it looks like | How to handle it |
|---|---|---|
| Definition drift | The same KPI is redefined in a dashboard, a notebook and Power BI. | One home (metric views), thin readers, a same-number test against Power BI. |
| Silent fan-out | A dimension gains duplicate keys after an SCD2 change; revenue doubles for some dealers. | Uniqueness tests on every join key in CI; join on current rows; never set at_most_one_match without the test. |
| Mixed grains | Targets, stock and sales forced into one view. | One metric view per business process; CTE joins or relationships between them. |
| Freshness disagreement | Materialized and live answers differ after a load. | Refresh right after the gold load; show 'data as of' on dashboards. |
| Security vs speed | Row filters block materialization. | Layout first; secured own-aggregate tables for hot paths. |
| Tool reach | A BI tool can't send MEASURE(). | Native query/custom SQL; wrapper views for the few fixed shapes; watch partner support. |
| Breaking changes | A measure is renamed and three dashboards and a Genie space break. | Treat names as an API: add new, deprecate old, remove later; lineage before every change. |
| Runtime differences | A YAML 1.1 feature works on the SQL warehouse but fails on a job cluster on an older runtime. | Pin the minimum runtime per feature; test on every compute that reads the view. |
| Ownership | One engineer owns the view and leaves; only the owner can edit. | Owner = a service principal (or group, if not materialized); deploy from CI. |
| Genie quality | Genie picks the wrong measure for 'sales'. | Synonyms, display names and comments; Genie benchmarks with known answers. |
| Cost surprises | Materialization pipelines refresh hourly for slices nobody reads. | Query history review monthly; drop unused materializations; tag warehouses. |
| Accidental drop | DROP VIEW is permanent. | Restricted MANAGE rights; re-create from git; lineage check first. |
Anti-patterns
Fourteen anti-patterns, and the fix
| Don't | Do |
|---|---|
| A metric view per dashboard | One per business process, shared by every dashboard. |
| Business logic copied into DAX or the BI tool | Thin readers; measures match the metric view one to one. |
| Source is silver | Source is atomic gold, tested, with keys. |
| Source is a pre-summed gold table | Atomic source; pre-aggregation is a speed layer, not the model. |
| Stored percentages summed or averaged | Ratios composed from additive measures. |
| Distinct counts added across periods | COUNT(DISTINCT) at the asked grain; exact-match materialization only. |
| YAML filter used as security | Row filters / ABAC on gold, entitlements table. |
| at_most_one_match set without a uniqueness test | Test first, then declare. |
| Editing the production view in the UI | YAML in git, deploy from CI by a service principal. |
| One giant metric view with every table joined | Small views per process; relationships for navigation. |
| Cryptic names (amt_net_v2, rgn) | Business names, display names, synonyms, comments. |
| Materialize everything | Materialize the measured hot slices only. |
| Wildcards in production views | Explicit fields; wildcards are fixed at create time and carry no metadata. |
| No tests because 'it is only YAML' | Golden-number tests, uniqueness tests, RLS tests in CI. |
Best practices
What good looks like
- Metrics first. Write the metric list (name, definition, owner, grain, slices) with the business before building gold; build gold to serve it.
- One metric view per business process (sales lines, stock snapshots, targets), each at its natural grain.
- Atomic measures, then composed ones, all through MEASURE(), so each recipe lives once.
- Scope in the filter (no cancelled, no test dealers) so nobody can forget it.
- Names for humans and Genie: display_name, format, synonyms, comment on every field and measure.
- Security on gold, driven by one entitlements table; SELECT granted only on metric views.
- Git, CI, service principal: the YAML file is the truth; ALTER VIEW replaces the whole definition, so deploy the whole file.
- Tests: golden numbers, join uniqueness, RLS per persona, same number in every tool.
- Certify with Unity Catalog tags and an owner; uncertified views are drafts.
- Measure, then materialize: look at query history, pre-aggregate only the hot slices, align refresh to loads.
- Names are an API: version changes, deprecate before removing, check lineage first.
Release flow
From a YAML file to a certified metric
The release flow. The YAML file in git is the truth.
# deploy_metric_views.py — run by CI with a service principal.
# Reads every metric_views/*.yaml file and (re)creates the view with the whole YAML.
import pathlib
from databricks import sql # databricks-sql-connector
def ddl(name: str, yaml_text: str) -> str:
return f"CREATE OR REPLACE VIEW {name} WITH METRICS LANGUAGE YAML AS $$\n{yaml_text}\n$$"
with sql.connect(server_hostname=HOST, http_path=WAREHOUSE_PATH, access_token=TOKEN) as c, c.cursor() as cur:
for f in sorted(pathlib.Path("metric_views").glob("*.yaml")):
cur.execute(ddl(f"main.sales_gold.{f.stem}", f.read_text()))
cur.execute(f"ALTER VIEW main.sales_gold.{f.stem} SET TAGS ('certified' = 'true', 'owner' = 'sales-finance')")Deploy the whole file every time: ALTER VIEW and CREATE OR REPLACE replace the whole definition. Upgrading to YAML 1.1 with ALTER VIEW drops Unity Catalog comments unless they are in the YAML comment fields.
Tests
Number tests, table-driven
Golden numbers are figures the business has signed off. Run them on every change, against a test copy of gold with fixed data (a clone or seeded tables), so a recipe change that moves a number fails the build instead of a board meeting.
# test_metric_views.py — table-driven golden-number tests (pytest).
# Each row: a question, the metric-view query, the number finance signed off.
import pytest
CASES = [
# name, query, expected
("revenue_2025_total", "SELECT MEASURE(net_revenue) FROM main.sales_gold.sales_mv WHERE year(invoice_month)=2025", 1_234_500.00),
("revenue_north_jan_2025", "SELECT MEASURE(net_revenue) FROM main.sales_gold.sales_mv WHERE region='North' AND invoice_month='2025-01-01'", 98_400.00),
("margin_pct_2025", "SELECT MEASURE(margin_pct) FROM main.sales_gold.sales_mv WHERE year(invoice_month)=2025", 0.2810),
("cancelled_never_counted", "SELECT MEASURE(net_revenue) FROM main.sales_gold.sales_mv WHERE invoice_id='INV-CANCELLED-1'", None),
]
@pytest.mark.parametrize("name,query,expected", CASES, ids=[c[0] for c in CASES])
def test_golden_number(cursor, name, query, expected):
got = cursor.execute(query).fetchone()[0]
if expected is None:
assert got is None
else:
assert got == pytest.approx(expected, rel=1e-6)
# Join keys must be unique, or measures fan out silently.
UNIQUE_KEYS = [("main.sales_gold.dim_dealer", "dealer_id"), ("main.sales_gold.dim_product", "product_id")]
@pytest.mark.parametrize("table,key", UNIQUE_KEYS)
def test_join_key_unique(cursor, table, key):
dupes = cursor.execute(f"SELECT COUNT(*) FROM (SELECT {key} FROM {table} GROUP BY {key} HAVING COUNT(*) > 1)").fetchone()[0]
assert dupes == 0Add the RLS persona tests from the security page and a Power BI same-number test if Power BI reads the same metrics.
Review checklist
Ten questions before a metric view is certified
- Is the source atomic gold at the finest grain any measure needs?
- Is every join key unique in the joined table, and is that tested?
- Are ratios composed from additive measures, never stored percentages?
- Are semi-additive measures windowed with semiadditive: last?
- Is scope in the filter, and security on the gold tables?
- Does every field and measure have a business name, comment and (where useful) synonyms?
- Do golden-number tests pass, and does each persona see only their rows?
- Is the YAML in git, deployed by a service principal, on a runtime that supports every feature used?
- Is it certified, with a named business owner?
- Is materialization (if any) justified by query history and aligned with the load?
← 7 · Speed · Next: 9 · Example →
FAQ
Questions people ask
How should metric views be deployed?
Keep each metric view's YAML in git, review it in a pull request, run number and uniqueness tests in CI, and deploy the whole file with CREATE OR REPLACE VIEW ... WITH METRICS by a service principal. Never hand-edit the production view in the UI.
How do I test a metric view?
Table-driven golden-number tests (query, expected figure signed off by the business) on fixed test data, uniqueness tests on every join key, and row-level security tests per persona.
Should I build one big metric view for everything?
No. Build one per business process at its natural grain (sales lines, stock snapshots, targets) and connect them through shared dimensions, CTE joins or relationships.
Sources
Checked against (11 Nov 2026)
- Databricks: Unity Catalog metric views and the YAML reference
- Materialization · Advanced techniques · Relationships · BI tools · Feature availability
- Row filters and column masks · ABAC evaluation through views (KB)
- dbt semantic models · MetricFlow open-sourced · Apache Ossie
Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.