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.
Each YAML key is one of the ten parts of a semantic model.
Top-level keys
What each key does
| Key | Required | What it does |
|---|---|---|
| version | Yes | The 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. |
| comment | No | What the metric view is for. Shows in Catalog Explorer and helps Genie. |
| source | Yes | A table, view, materialized view, streaming table, another metric view, or a SQL query. Sets the grain. |
| filter | No | A boolean SQL expression applied to every query: scope, such as no cancelled orders. |
| joins | No | Star 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/measures | Scalar SQL expressions to group and filter by. Can carry comment, display_name, format, synonyms (up to 10). |
| measures | One of fields/measures | Aggregate expressions. Can reference other measures with MEASURE(). Can carry a window and the same metadata as fields. |
| parameters | No | Named, typed values passed at query time when the view is called like a table function. Blocks materialization. |
| materialization | No | Schedule, 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.
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 key | What it does |
|---|---|
| order | The field that orders the window, usually a date field defined in the view. Must be deterministic. |
| range | current, cumulative, trailing N unit, leading N unit, or all. trailing and leading exclude the anchor row unless you add inclusive. |
| semiadditive | first or last (default last): what to return when the order field is not in the GROUP BY. This is how stock and balances work. |
| offset | Shifts 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
| Tool | How, Nov 2026 |
|---|---|
| AI/BI dashboards, Genie, alerts, SQL editor, notebooks | Native. |
| Power BI | Native query with MEASURE() in DirectQuery, or a wrapper view; publishing as a Power BI semantic model is Private Preview. |
| Tableau | Custom SQL with MEASURE(), or BI compatibility mode set in Initial SQL. |
| Sigma, other JDBC/ODBC tools | Custom or pass-through SQL with MEASURE(). |
| Wrapper views | A normal view over the metric view with fixed dimensions; works everywhere, but you need one per dimension set. |
Versions
Minimum Databricks Runtime per feature
| Feature | Minimum runtime |
|---|---|
| Metric views | 16.4 |
| Temporary metric views | 17.2 |
| Materialization, snowflake joins, agent metadata (YAML 1.1), JDBC/ODBC | 17.3 |
| BI compatibility mode, REFRESH MATERIALIZED VIEW | 18.0 |
| Materialization refresh_policy | 18 LTS |
| One-to-many joins, window offset, inclusive/exclusive, rely.at_most_one_match, AGG() | 18.1 |
| Parameters, wildcards | 18.2 |
| Relationships between metric views (Beta), numeric offsets, derived names | 19 (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)
- 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.