Unity Catalog metric views: define revenue once, with an example in YAML

Part 1 built sales, the shop's serving table of 1 row per sold order, with setup_serving.py, run_sql.py and sales.sql. To start here, run those 3 from part 1: python setup_serving.py, then python run_sql.py sales.sql.

A table of totals answers 1 question

A dashboard often reads a table of totals that someone built for it, 1 row per day. Save this as daily.sql:

-- A table of totals per day, as a dashboard might be given.
CREATE OR REPLACE TABLE daily_sales USING delta AS
SELECT day,
       sum(amount) AS revenue,
       count(DISTINCT customer_id) AS customers,
       round(sum(amount) / count(DISTINCT customer_id), 2) AS revenue_per_customer
FROM sales
GROUP BY day;

SELECT * FROM daily_sales ORDER BY day;

-- The 3 days together, worked out from the daily totals.
SELECT sum(revenue) AS revenue,
       sum(customers) AS customers,
       round(avg(revenue_per_customer), 2) AS revenue_per_customer
FROM daily_sales;

-- The same 3 days, worked out from the rows.
SELECT sum(amount) AS revenue,
       count(DISTINCT customer_id) AS customers,
       round(sum(amount) / count(DISTINCT customer_id), 2) AS revenue_per_customer
FROM sales;

python run_sql.py daily.sql prints:

+----------+-------+---------+--------------------+
|day       |revenue|customers|revenue_per_customer|
+----------+-------+---------+--------------------+
|2026-10-01|25.50  |1        |25.50               |
|2026-10-02|40.00  |1        |40.00               |
|2026-10-03|18.25  |1        |18.25               |
+----------+-------+---------+--------------------+

+-------+---------+--------------------+
|revenue|customers|revenue_per_customer|
+-------+---------+--------------------+
|83.75  |3        |27.92               |
+-------+---------+--------------------+

+-------+---------+--------------------+
|revenue|customers|revenue_per_customer|
+-------+---------+--------------------+
|83.75  |2        |41.88               |
+-------+---------+--------------------+

Revenue survives: a sum of daily sums is the sum. The other 2 do not. C01 bought on 2 days, so adding the days counts C01 twice, and 3 customers is wrong. An average of daily ratios is not the ratio of the period either: 27.92 per customer, where the right answer is 83.75 for 2 customers, 41.88. Revenue is additive; a distinct count and a ratio are not, and Kimball's advice is to keep the parts of a ratio and divide at the end.

A table of totals per day answers questions per day. For a week, a country or a month, each reader has to know which columns can be added up, and someone who adds up customers gets a wrong answer with no error. The fix is to define the measure once, as a formula on the rows, and compute it for whatever grouping a reader asks for.

The measure, defined once

On Databricks, a Unity Catalog metric view does that. It is defined in YAML. The source is a table, the fields are what readers can group and filter by, and the measures are aggregate formulas over the source's rows:

version: 1.1
comment: "The shop's sales: sold orders only, 1 row per order"
source: <catalog>.shop.sales
fields:
  - name: day
    expr: day
    comment: "The day the order was placed, in UTC"
  - name: month
    expr: DATE_TRUNC('MONTH', day)
    comment: "The month the order was placed"
  - name: country
    expr: country
    comment: "Where the customer lived when they placed the order"
measures:
  - name: orders
    expr: COUNT(1)
    comment: "Sold orders"
  - name: revenue
    expr: SUM(amount)
    comment: "What customers paid, in euros"
    synonyms: ["sales", "turnover"]
  - name: customers
    expr: COUNT(DISTINCT customer_id)
    comment: "Customers with at least 1 sold order"
  - name: revenue_per_customer
    expr: SUM(amount) / COUNT(DISTINCT customer_id)
    comment: "Revenue divided by the customers who bought"

revenue_per_customer is written as the 2 parts of the ratio, never as an average of anything stored. The comments and synonyms are for people and for Genie: a question about "turnover" can find revenue.

To create it, run this in a SQL cell or the SQL editor with your catalog in place of <catalog>, the YAML above between the $$ marks:

CREATE OR REPLACE VIEW <catalog>.shop.sales_metrics
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1
source: <catalog>.shop.sales
...
$$;

You need SELECT on sales, and CREATE TABLE and USE SCHEMA on the schema. Databricks' example of this syntax needs a SQL warehouse or compute on Databricks Runtime 17.3 or above; a serverless SQL warehouse is the simplest. Catalog Explorer can build the same view without YAML: open sales, then Create, then Metric view. This part runs only on Databricks, so its code follows Databricks' docs and was not run for this course.

Ask for any grouping

A query names the fields to group by and wraps each measure in MEASURE():

SELECT country,
       MEASURE(revenue) AS revenue,
       MEASURE(customers) AS customers,
       MEASURE(revenue_per_customer) AS revenue_per_customer
FROM <catalog>.shop.sales_metrics
GROUP BY ALL
ORDER BY country;

Group by day instead, or by nothing, and the same measures are computed again from the rows of that group. For the 3 days together, customers is 2 and revenue_per_customer is 83.75 divided by 2, the figures the rows gave above, not the 3 and 27.92 that adding up daily totals gave. SELECT * does not work on a metric view, because each measure has to be inside MEASURE(). And it cannot be joined directly in a query: put the metric view's query in a WITH clause and join its result.

Where it is used

The point of defining revenue once is that every reader then uses that one definition:

• AI/BI dashboards take a metric view as a dataset and apply MEASURE() themselves, showing the comments and display names.
• Genie Agents use it to answer questions in plain words, and Databricks' advice for Genie names metric views as an effective way to give it metrics, fields and aggregations.
• Alerts, JDBC and ODBC clients, and BI tools such as Power BI and Tableau can query it.

This is the semantic layerSemantic LayerOne shared definition of business terms and metrics, such as revenue or active customer, that reports, dashboards and AI assistants all read instead of writing their own. idea: the definition of a metric lives once, beside the data, rather than in each report. When finance decides that refunds count against revenue, you change 1 measure, not every dashboard. Part 3 puts this metric view in front of business users.