A metric view is a Unity Catalog object that stores definitions, not data. It holds exactly two kinds of thing — dimensions, the fields you group by, and measures, the aggregate expressions like SUM(amount_eur) — and it deliberately has no grain of its own.
That last part is the whole idea. An ordinary view fixes its grouping when you write it: GROUP BY country, month gives you a table of countries and months, and a per-customer answer needs a second view. A metric view fixes only the arithmetic and leaves the grouping to the caller, so one object answers the monthly board pack and the per-customer drill-down without either query redefining revenue.
Why
Without a semantic layer the definition of revenue does not cease to exist — it relocates. It ends up in whichever tool the person who needed a number happened to have open: a DAX measure, a hand-written WHERE in a dashboard tile, a spreadsheet column. Each is invisible to the others, and none is reviewed.
So the object is small and the decision it represents is not. Writing gold.mv_sales takes an afternoon. Establishing that it is the only place revenue is defined is the actual work of this route, and everything below is downstream of it.
How
sqlThe smallest metric view that does anything — one dimension, one measure
CREATE OR REPLACE VIEW gold.mv_demo
WITH METRICS -- this is what makes it a metric view
LANGUAGE YAML
AS $$
version: 1.1
source: gold.fct_order_line -- the table underneath
fields: -- dimensions: things you may GROUP BY
- name: month
expr: DATE_TRUNC('MONTH', order_ts)
measures: -- aggregate expressions, evaluated at query time
- name: revenue
expr: SUM(amount_eur)
$$;
-- read it: measures come out through MEASURE(), dimensions read as columns
SELECT month, MEASURE(revenue) FROM gold.mv_demo GROUP BY month;
-- the SAME definition at a different grain. No second object, no second SUM.
SELECT MEASURE(revenue) FROM gold.mv_demo;
Four words carry the whole model: source is the table underneath, fields are the dimensions, measures are the arithmetic, and MEASURE() is how a query asks for one. Everything else in the YAML — joins, filters, display names — is refinement on top of those four.
The same idea in the tool your team already uses — a translation aid, not a feature comparison
Databricks metric view
dbt Semantic Layer (MetricFlow)
Power BI
Looker (LookML)
Cube
Written as
YAML inside CREATE VIEW … WITH METRICS
YAML: semantic_models + metrics
DAX in a semantic model
LookML view files
YAML or JS schema files
Base table named by
source:
model: ref('…')
the table in the model
sql_table_name
sql_table
Group-by fields
fields:
dimensions: (+ entities:)
model columns
dimension:
dimensions:
Aggregates
measures:
measures: + metrics:
DAX measures
measure:
measures:
How a query asks for one
MEASURE(revenue)
the Semantic Layer API / JDBC
implicit — drop it on a visual
implicit — pick it in an Explore
SQL API, REST or GraphQL
Where the maths runs
the Databricks SQL engine
compiles to SQL, runs on your warehouse
the Power BI engine
compiles to SQL, runs on your warehouse
compiles to SQL, plus its own cache
Read by an agent through
Genie, natively
the dbt MCP server — bring your own agent
Copilot
Gemini in Looker
its own semantic APIs
Lives in
Unity Catalog, beside the tables
the dbt project
a PBIP file or the workspace
a Git-backed LookML project
the Cube deployment
Read that table as a vocabulary map, not a scorecard. If someone on the team has written a LookML measure or a MetricFlow metric, they already hold the concept and simply have a different word for it — the fastest way to lose the first workshop is to teach a semantic layer to people who have shipped three.
The last row is the one that changed what a semantic layer is for. A metric view is not only something a person queries — it is a data source Genie reads directly, and its measures hand the agent a fixed, reviewed expression instead of an invitation to write SUM(amount_eur) over everything including the cancellations. That is where an undefined metric does the most damage, because nobody reads the SQL an agent wrote. The view carries metadata aimed squarely at that layer: display_name, format, and up to ten synonyms per field, so the person who asks about turnover lands on the same measure as the person who asks about revenue.
The two ecosystems answer this differently, and it is worth knowing which shape a client is buying. Databricks ships a named first-party agent — Genie is part of the platform, and the metric view is wired to it. dbt ships a protocol instead of an agent: the dbt MCP server exposes the Semantic Layer, and whichever agent the client already uses reads the same governed metrics through it. One is an assistant included in the platform; the other is an open door onto the definitions.
Doing it
Ask which semantic layer the team has already used, before writing any YAMLdbt, Looker, Cube, a Power BI model — the answer changes your vocabulary for the whole engagement, and it tells you whether you are introducing a concept or renaming one.
Check whether a definition of revenue already exists somewhere in the stackA MetricFlow metric or a LookML measure that already encodes the cancellation rule is both a head start and a competing source of truth. Find it before you create a second one.
Start with three measures, not thirtyRevenue, order count, active customers. A semantic layer with thirty measures on day one has thirty measures nobody has reconciled against the reports they replace.
Run the two-grain demonstration in front of the teamThe same MEASURE(revenue) with and without a GROUP BY. Watching one definition answer two questions is what makes 'no grain' land; the sentence alone does not.
Embedding it
Have whoever knows Looker or dbt present the concept, not youThey translate it into the team's own vocabulary in one sentence, and the idea arrives as something the team already half-owns rather than something a consultant brought.
Make an analyst write the third measureThe first two are yours as examples. If the YAML is only ever written by the data team, the semantic layer has become a ticket queue with extra steps.
Ask, in review, which report a new measure replacesA measure with no named consumer is a measure that will not be maintained. The question also surfaces the shadow definitions still living in the BI tool.
Defaults
A metric view defines arithmetic; the query defines grainIf you find yourself writing a second metric view because the first was 'at the wrong level', something has been modelled as a dimension that should have stayed a query.
It is deployed as DDL from the repository, like every other objectSame bundle, same review, same environments. The catalog placement is not decoration — it is what makes permissions and lineage work without a second system.
Use the team's existing word for it in conversation, and the Databricks word in the repo'Semantic layer' in the room, metric view in the DDL. Insisting on the product name in discussion buys nothing and costs rapport.
Gotchas
Reading 'view' and expecting a viewA metric view is not a queryable table-like object with extra columns. SELECT * does not work, a measure cannot be referenced without MEASURE(), and a measure in a GROUP BY returns METRIC_VIEW_MEASURE_IN_GROUP_BY. Every one of those reads as a broken connector to whoever hits it first.
Presenting the cross-tool table as a migration pathThe shapes correspond; the expressions do not. DAX does not translate mechanically into SQL, and a MetricFlow metric may carry semantics the YAML expresses differently. Mapping the vocabulary takes an hour; moving forty measures is a project, and conflating the two in a proposal is how the estimate goes wrong.
Building the semantic layer before anyone has agreed what revenue isThe YAML then encodes one team's answer, quietly, and the disagreement surfaces later as 'the metric view is wrong' rather than as the open business question it always was.
Promising synonyms without checking the client's runtimeAgent metadata needs DBR 17.3 and YAML 1.1. Below that the YAML does not degrade gracefully — it fails to parse, so a design that leans on synonyms for natural-language accuracy arrives as a broken deployment rather than a missing nicety.
The metric view sits over gold.fct_order_line and is deployed as DDL from the repository, like every other object. Business rule 1 — revenue excludes cancelled lines — is enforced once, in the filter, rather than in each of the eleven reports that would otherwise each have to remember it.
Why
The failure this prevents is the one every warehouse eventually has: four reports, four revenue numbers, no arbiter. One excludes cancelled lines, one excludes them only after the shipping date, one forgot. Each was defensible when it was written. Together they cost the warehouse its credibility, which is the entire product.
The second reason is Genie. A metric view is a data source Genie can read, and its measures hand Genie a fixed, correct expression instead of an invitation to write SUM(amount_eur) over everything including the cancellations. Natural-language questions are where an undefined metric does most damage, because nobody reviews the SQL.
How
sqlgold/mv_sales.sql — the whole object
CREATE OR REPLACE VIEW ${catalog}.gold.mv_sales
WITH METRICS
LANGUAGE YAML
COMMENT 'Sales metrics over gold.fct_order_line. No grain: measures aggregate at query time.'
AS $$
version: 1.1
source: ${catalog}.gold.fct_order_line
filter: status <> 'CANCELLED' # business rule 1, enforced once
joins:
- name: customer
source: ${catalog}.gold.dim_customer
on: source.customer_sk = customer.customer_sk AND customer._tech_is_current
cardinality: many_to_one
rely:
at_most_one_match: true
fields:
- name: country
expr: customer.country
display_name: Land
comment: 'DE | AT | CH | NL'
- name: customer_segment
expr: customer.customer_segment
display_name: Kundengruppe
comment: 'A | B | C'
- name: region
expr: source.region_sk
display_name: Vertriebsregion
comment: 'surrogate key on the fact - the label lives in gold.dim_sales_region; join it HERE if a report needs the name, never in the BI tool'
- name: order_date
expr: CAST(order_ts AS DATE)
display_name: Auftragsdatum
- name: month
expr: DATE_TRUNC('MONTH', order_ts)
display_name: Monat
measures:
- name: revenue
expr: SUM(amount_eur)
display_name: Umsatz (EUR)
- name: order_count
expr: COUNT(DISTINCT order_id)
display_name: Auftraege
- name: average_order_value
expr: SUM(amount_eur) / NULLIF(COUNT(DISTINCT order_id), 0)
display_name: Durchschnittlicher Auftragswert
- name: active_customers
expr: COUNT(DISTINCT CASE WHEN source.customer_sk <> 'UNKNOWN'
THEN source.customer_sk END)
display_name: Aktive Kunden
$$;
Three details there are not cosmetic:
order_count is COUNT(DISTINCT order_id), not COUNT(1)
Because the source grain is the order line.
active_customers excludes the UNKNOWN member
Business rule 7 guarantees that member will be present.
region_sk and customer_sk carry the source namespace
They exist on both the fact and the dimension, so unqualified they are ambiguous.
Identifiers stay ASCII lower_snake_case; the German the business actually says lives in display_name. One model, two vocabularies, no umlauts in a column name.
sqlReading it — measures come out through MEASURE()
-- monthly revenue by country, from a workspace bound to one catalog
SELECT
country,
month,
MEASURE(revenue) AS revenue,
MEASURE(order_count) AS orders,
MEASURE(average_order_value) AS aov
FROM gold.mv_sales
WHERE month >= '2026-01-01'
GROUP BY country, month
ORDER BY country, month;
-- same definitions, different grain, no second object
SELECT customer_segment, MEASURE(active_customers) AS customers
FROM gold.mv_sales
GROUP BY customer_segment;
Doing it
Deploy the metric view as DDL from the repo, in the same bundle as the tables it readsOne edited in the UI is invisible to code review.
Write business rule 1 into the filter, then delete the equivalent WHERE clause from every dashboard query that carries oneLeaving both means the rule is enforced twice and can be changed in one place.
Add the metric view, not gold.fct_order_line, as the Genie data sourceSo 'what was revenue in March' cannot be answered by an ad-hoc SUM over the cancellations.
Point one existing report at MEASURE(revenue) and diff it against today's numberThat difference is your first real conversation with the business.
Embedding it
Ask the team to produce the current definition of revenue from the BI tool, before you write any YAMLWatching them fail to find one, or find three, is the argument — you do not have to make it.
Have an analyst, not an engineer, write the second measureIf the YAML is only writable by the data team, the semantic layer has become another ticket queue.
In review, ask 'which report changes when this merges?'A measure nobody can name a consumer for is a measure nobody is using.
Let them add a measure that is subtly wrong and catch it with the SQL oracleThe oracle is more convincing after it has caught something.
Defaults
One metric view per business process, not per reportSales is one; procurement is another.
Measures always through MEASURE(), never a materialised copy of a measure in a table
Business rules live in the filter, once, with the rule number in a comment
display_name carries the local-language label; the identifier stays ASCII
Gotchas
Joining the fact to an SCD2 dimension without pinning to the current versioncustomer_sk is sha2(customer_id), so it repeats once per validity interval in gold.dim_customer. Drop the _tech_is_current predicate and every line fans out to that customer's version count — revenue does not look broken, it looks good, and it grows every month as more customers acquire a second version.
rely: at_most_one_match: true is a promise to the optimizer, not a checkIf the join does match more than once you get no error, only the wrong number faster.
COUNT(1) where you meant COUNT(DISTINCT order_id)gold.fct_order_line is one row per line, so the two differ by the average lines per order — 'orders' silently becomes 'lines' in every KPI tile downstream. Because the ratio is stable month to month the number looks plausible rather than broken. You catch it by counting rows in silver.sales_order and comparing, not by looking at the tile.
Counting the UNKNOWN member as a customerRule 7 sends every unresolvable line to customer_sk = 'UNKNOWN', so active_customers gains one phantom customer permanently and the drill-down shows a customer with no name.
NULL in the filter columnstatus <> 'CANCELLED' is three-valued: a row whose status is NULL is neither kept nor reported as excluded, it silently leaves revenue altogether. If status is nullable, write status IS DISTINCT FROM 'CANCELLED' — and reconcile MEASURE(revenue) against a plain SUM(amount_eur) over the fact, because the gap is the only place the dropped rows show up.
Grouping by region and getting surrogate keysregion is source.region_sk: the fact carries the key, not the name, and this view does not join gold.dim_sales_region for the label. A slicer built on it lists surrogate keys and the business assumes the report is broken. Fix it by declaring that join in this YAML, or by denormalising the region name onto gold.fct_order_line. What you must not do is let the report join gold.dim_sales_region to the metric view — any query that joins a metric view to anything fails outright with METRIC_VIEW_JOIN_NOT_SUPPORTED.
Editing the YAML in Catalog Explorer at 17:00It works, so nobody redeploys — and either the next deployment silently reverts it, or it does not and the repo no longer describes production.