Home › Data engineering › Metric views › 4 · Parts

Data engineering · BI and analytics · free guide · 4 · Parts

The parts of a metric view

A metric view is a view in Unity Catalog whose body is YAML instead of a SELECT. You create it with CREATE VIEW ... WITH METRICS LANGUAGE YAML, grant SELECT on it like any view, and query it with MEASURE(). Here is every part, mapped back to the ten parts of the idea.

CREATE VIEW sales_mv WITH METRICS LANGUAGE YAML AS $$version: 1.1which YAML specsource: gold.fact_sales_linethe grain: one row = one invoice linefilter: status <> 'CANCELLED'scope: what always countsjoins: dealer, productrelationships to dimensionsfields: region, month, segmentdimensions: ways to slicemeasures: net_revenue, margin_pctaggregations, composed, windowsdisplay_name / synonyms / formatbusiness names for people and Geniematerialization: (optional)speed layer: pre-aggregates$$

Each YAML key is one of the ten parts of a semantic model.

Top-level keys

What each key does

KeyRequiredWhat it does
versionYesThe YAML spec version (1.1 today), not your own revision number. 1.1 is needed for agent metadata, one-to-many joins, wildcards and window offsets.
commentNoWhat the metric view is for. Shows in Catalog Explorer and helps Genie.
sourceYesA table, view, materialized view, streaming table, another metric view, or a SQL query. Sets the grain.
filterNoA boolean SQL expression applied to every query: scope, such as no cancelled orders.
joinsNoStar joins (name, source, on or using); nested joins for a snowflake; cardinality many_to_one (default) or one_to_many; rely.at_most_one_match.
fields (or dimensions)One of fields/measuresScalar SQL expressions to group and filter by. Can carry comment, display_name, format, synonyms (up to 10).
measuresOne of fields/measuresAggregate expressions. Can reference other measures with MEASURE(). Can carry a window and the same metadata as fields.
parametersNoNamed, typed values passed at query time when the view is called like a table function. Blocks materialization.
materializationNoSchedule, mode: relaxed, and a list of aggregated or unaggregated materialized views. A managed Lakeflow pipeline keeps them fresh.

Writing one

A complete metric view

CREATE OR REPLACE VIEW main.sales_gold.sales_mv
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1
comment: "Tyre sales to dealers. One row per invoice line. Owner: Sales Finance."
source: main.sales_gold.fact_sales_line
filter: status <> 'CANCELLED'
joins:
  - name: dealer
    source: main.sales_gold.dim_dealer
    on: source.dealer_id = dealer.dealer_id
    rely:
      at_most_one_match: true        # a promise: dim_dealer has one row per dealer_id
  - name: product
    source: main.sales_gold.dim_product
    on: source.product_id = product.product_id
fields:
  - name: invoice_month
    expr: DATE_TRUNC('MONTH', invoice_date)
    display_name: Invoice month
  - name: region
    expr: dealer.region
    synonyms: ['zone', 'sales region']
  - name: segment
    expr: CASE product.segment WHEN 'PCR' THEN 'Passenger car' WHEN 'TBR' THEN 'Truck and bus' ELSE 'Two-wheeler' END
measures:
  - name: net_revenue
    expr: SUM(net_amount)
    display_name: Net revenue
    synonyms: ['revenue', 'sales']
  - name: gross_margin
    expr: SUM(net_amount - cost_amount)
  - name: margin_pct
    expr: MEASURE(gross_margin) / NULLIF(MEASURE(net_revenue), 0)
    display_name: Margin %
$$;

Names with spaces need backticks in queries; quote any expression that contains a colon; use | for long multi-line expressions.

Asking it questions

How to query a metric view

-- 1. Basic: fields plus MEASURE(), then GROUP BY ALL
SELECT region, invoice_month, MEASURE(net_revenue) AS revenue, MEASURE(margin_pct) AS margin
FROM main.sales_gold.sales_mv
WHERE invoice_month >= '2025-01-01'
GROUP BY ALL
ORDER BY region, invoice_month;

-- 2. Joining with another table: not allowed directly, so use a CTE
WITH m AS (
  SELECT region, MEASURE(net_revenue) AS revenue FROM main.sales_gold.sales_mv GROUP BY ALL
)
SELECT m.region, m.revenue, t.target_amount, m.revenue / t.target_amount AS attainment
FROM m JOIN main.sales_gold.region_target_2025 t USING (region);

-- 3. See the stored YAML and metadata
DESCRIBE TABLE EXTENDED main.sales_gold.sales_mv AS JSON;

Measures are always wrapped in MEASURE(); fields are used like normal columns in SELECT, WHERE and GROUP BY. Under the hood Unity Catalog turns your query into plain SQL over the source and its joins.

SELECT region,MEASURE(margin_pct)... GROUP BY ALLUnity Catalogexpands the measureand the joinsplain SQL on gold:SUM(...)/SUM(...)fact JOIN dealerSQL warehouse(Photon) runs itas the usermaterialized view(if one matches)rewrite if it fitsNo match: it reads the gold tables live. Match: it reads the pre-computed answer.

From a MEASURE() query to SQL on gold tables, or to a materialized answer.

Building on measures

Composed and window measures

Write atomic measures first (one aggregation each), then compose them with MEASURE(name), so a change to net revenue flows into margin and growth automatically. Window measures add time logic: trailing and leading windows, running totals, last year, and semi-additive balances.

measures:
  - name: net_revenue
    expr: SUM(net_amount)
  - name: net_revenue_ly                # same month last year
    expr: SUM(net_amount)
    window:
      - order: invoice_month
        range: current
        offset: -12 month
  - name: yoy_growth_pct
    expr: (MEASURE(net_revenue) - MEASURE(net_revenue_ly)) / NULLIF(MEASURE(net_revenue_ly), 0)
  - name: dealers_last_28_days          # distinct dealers in a trailing window
    expr: COUNT(DISTINCT dealer_id)
    window:
      - order: invoice_date
        range: trailing 28 day inclusive
Window keyWhat it does
orderThe field that orders the window, usually a date field defined in the view. Must be deterministic.
rangecurrent, cumulative, trailing N unit, leading N unit, or all. trailing and leading exclude the anchor row unless you add inclusive.
semiadditivefirst or last (default last): what to return when the order field is not in the GROUP BY. This is how stock and balances work.
offsetShifts the frame, such as -12 month for last year. Needs YAML 1.1.

For people and Genie

Agent metadata

With YAML 1.1, fields and measures take display_name (up to 255 characters), format (currency, percent, number), synonyms (up to 10) and comment. Genie and dashboards use them to understand questions like 'sales by zone'. Unity Catalog tags and the certified status sit on the view itself, as on any table.

Several metric views

Relationships between metric views (Beta)

-- Relationships between metric views (Beta, runtime 19+ or a SQL warehouse):
-- metadata only, tells Genie and BI tools how two metric views fit together.
ALTER VIEW main.sales_gold.sales_mv
  ADD CONSTRAINT sales_to_dealer MANY TO ONE RELATIONSHIP TO main.sales_gold.dealer_mv
  USING (dealer_id);

One metric view per business process (sales lines, stock snapshots, targets) is cleaner than one giant view. Relationships tell tools how they connect through shared dimensions. They are not enforced and not used for query speed.

Outside Databricks

How other tools read metric views

ToolHow, Nov 2026
AI/BI dashboards, Genie, alerts, SQL editor, notebooksNative.
Power BINative query with MEASURE() in DirectQuery, or a wrapper view; publishing as a Power BI semantic model is Private Preview.
TableauCustom SQL with MEASURE(), or BI compatibility mode set in Initial SQL.
Sigma, other JDBC/ODBC toolsCustom or pass-through SQL with MEASURE().
Wrapper viewsA normal view over the metric view with fixed dimensions; works everywhere, but you need one per dimension set.

Versions

Minimum Databricks Runtime per feature

FeatureMinimum runtime
Metric views16.4
Temporary metric views17.2
Materialization, snowflake joins, agent metadata (YAML 1.1), JDBC/ODBC17.3
BI compatibility mode, REFRESH MATERIALIZED VIEW18.0
Materialization refresh_policy18 LTS
One-to-many joins, window offset, inclusive/exclusive, rely.at_most_one_match, AGG()18.1
Parameters, wildcards18.2
Relationships between metric views (Beta), numeric offsets, derived names19 (or a SQL warehouse)

SQL warehouses get new features first. Check the current list before you rely on a feature on job clusters.

← 3 · Limits · Next: 5 · Grain →

FAQ

Questions people ask

How do I query a metric view?

SELECT the fields you want, wrap each measure in MEASURE(), and end with GROUP BY ALL. SELECT * is not allowed, and the metric view can't be joined to other tables directly; wrap it in a CTE first.

What is the difference between a field and a measure?

A field is a scalar expression you group or filter by, like region or month. A measure is an aggregate, like SUM(net_amount), that is evaluated for whatever grouping the query asks for.

Can a measure use another measure?

Yes. Write MEASURE(other_measure) inside the expression, for example MEASURE(gross_margin) / NULLIF(MEASURE(net_revenue), 0). This keeps each recipe in one place.

Sources

Checked against (11 Nov 2026)

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