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

ChallengeWhat it looks likeHow to handle it
Definition driftThe 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-outA 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 grainsTargets, stock and sales forced into one view.One metric view per business process; CTE joins or relationships between them.
Freshness disagreementMaterialized and live answers differ after a load.Refresh right after the gold load; show 'data as of' on dashboards.
Security vs speedRow filters block materialization.Layout first; secured own-aggregate tables for hot paths.
Tool reachA BI tool can't send MEASURE().Native query/custom SQL; wrapper views for the few fixed shapes; watch partner support.
Breaking changesA 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 differencesA 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.
OwnershipOne engineer owns the view and leaves; only the owner can edit.Owner = a service principal (or group, if not materialized); deploy from CI.
Genie qualityGenie picks the wrong measure for 'sales'.Synonyms, display names and comments; Genie benchmarks with known answers.
Cost surprisesMaterialization pipelines refresh hourly for slices nobody reads.Query history review monthly; drop unused materializations; tag warehouses.
Accidental dropDROP VIEW is permanent.Restricted MANAGE rights; re-create from git; lineage check first.

Anti-patterns

Fourteen anti-patterns, and the fix

Don'tDo
A metric view per dashboardOne per business process, shared by every dashboard.
Business logic copied into DAX or the BI toolThin readers; measures match the metric view one to one.
Source is silverSource is atomic gold, tested, with keys.
Source is a pre-summed gold tableAtomic source; pre-aggregation is a speed layer, not the model.
Stored percentages summed or averagedRatios composed from additive measures.
Distinct counts added across periodsCOUNT(DISTINCT) at the asked grain; exact-match materialization only.
YAML filter used as securityRow filters / ABAC on gold, entitlements table.
at_most_one_match set without a uniqueness testTest first, then declare.
Editing the production view in the UIYAML in git, deploy from CI by a service principal.
One giant metric view with every table joinedSmall views per process; relationships for navigation.
Cryptic names (amt_net_v2, rgn)Business names, display names, synonyms, comments.
Materialize everythingMaterialize the measured hot slices only.
Wildcards in production viewsExplicit 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

  1. Metrics first. Write the metric list (name, definition, owner, grain, slices) with the business before building gold; build gold to serve it.
  2. One metric view per business process (sales lines, stock snapshots, targets), each at its natural grain.
  3. Atomic measures, then composed ones, all through MEASURE(), so each recipe lives once.
  4. Scope in the filter (no cancelled, no test dealers) so nobody can forget it.
  5. Names for humans and Genie: display_name, format, synonyms, comment on every field and measure.
  6. Security on gold, driven by one entitlements table; SELECT granted only on metric views.
  7. Git, CI, service principal: the YAML file is the truth; ALTER VIEW replaces the whole definition, so deploy the whole file.
  8. Tests: golden numbers, join uniqueness, RLS per persona, same number in every tool.
  9. Certify with Unity Catalog tags and an owner; uncertified views are drafts.
  10. Measure, then materialize: look at query history, pre-aggregate only the hot slices, align refresh to loads.
  11. Names are an API: version changes, deprecate before removing, check lineage first.

Release flow

From a YAML file to a certified metric

YAML in gitone file per metric viewpull requestreview + lintCI testsgolden numbers, row countsdeployALTER VIEW by aservice principalcertifytag + owner + docswatchquery history, costNobody edits a production metric view in the UI. The file in git is the truth.

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 == 0

Add 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

  1. Is the source atomic gold at the finest grain any measure needs?
  2. Is every join key unique in the joined table, and is that tested?
  3. Are ratios composed from additive measures, never stored percentages?
  4. Are semi-additive measures windowed with semiadditive: last?
  5. Is scope in the filter, and security on the gold tables?
  6. Does every field and measure have a business name, comment and (where useful) synonyms?
  7. Do golden-number tests pass, and does each persona see only their rows?
  8. Is the YAML in git, deployed by a service principal, on a runtime that supports every feature used?
  9. Is it certified, with a named business owner?
  10. 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)

Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.