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

A metric view is a Unity Catalog object that defines a business metric once, such as revenue per customer, apart from the fields it can be grouped by. Each query names the fields it groups by and wraps each measure in MEASURE(), and Databricks computes the measure from the rows for that grouping. That avoids the classic mistake of a table of pre-computed totals: a distinct count or a ratio per day cannot be added or averaged into a correct figure for a week.

- Page: https://tecyfy.com/data-engineering/course/serving-data/metric-views
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 8: Serving data, Metric views, revenue defined once
- Parts of this module: Serving tables and the join that counts twice (https://tecyfy.com/data-engineering/course/serving-data); Metric views, revenue defined once (https://tecyfy.com/data-engineering/course/serving-data/metric-views); Dashboards and Genie on a SQL warehouse (https://tecyfy.com/data-engineering/course/serving-data/dashboards-and-genie); Sharing tables with partners (https://tecyfy.com/data-engineering/course/serving-data/sharing); Lakebase, lookups for an app (https://tecyfy.com/data-engineering/course/serving-data/lakebase); Build it: the shop's serving layer, checked (https://tecyfy.com/data-engineering/course/serving-data/build-it)

At the end of this part you can:

- Show why a distinct count or a ratio stored per day gives a wrong answer when the days are summed or averaged
- Define a metric view in YAML, with fields and measures, and create it with CREATE VIEW ... WITH METRICS
- Query a metric view with MEASURE() at any grouping

[Part 1](/data-engineering/course/serving-data) 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`:

```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:

```text
+----------+-------+---------+--------------------+
|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:

```yaml
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:

```sql
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()`:

```sql
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 layer](https://tecyfy.com/concepts/semantic-layer) 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](/data-engineering/course/serving-data/dashboards-and-genie) puts this metric view in front of business users.

## Check yourself

1. daily_sales stores customers per day as 1, 1 and 1. Customer C01 bought on 2 of the days. How many customers bought in the 3 days?

   A. 3, the sum of the days
   B. 1, the largest day
   C. 2, because a distinct count has to be computed from the rows of the whole period
   D. It cannot be known from any table

### Answers

1. C. A customer who buys on 2 days is counted once on each day, so the sum counts C01 twice. A distinct count is not additive. Only the rows of the whole period give 2, which is what a metric view's COUNT(DISTINCT customer_id) measure computes for any grouping.

## Questions

### What is the difference between a metric view and a regular view?

A regular view fixes its grouping and aggregation when it is written, so a view of revenue per day cannot answer revenue per country. A metric view separates the measures, such as SUM(amount), from the fields they can be grouped by, and each query picks its grouping and wraps measures in MEASURE(). Databricks then computes each measure from the rows for that grouping.

### How do I query a Databricks metric view?

Select the fields to group by and wrap each measure in MEASURE(), with GROUP BY ALL, for example SELECT country, MEASURE(revenue) FROM sales_metrics GROUP BY ALL. SELECT * does not work, because every measure must be inside MEASURE(). AI/BI dashboards and Genie Agents apply MEASURE() themselves.

## Sources

- [Unity Catalog metric views](https://docs.databricks.com/aws/en/uc-semantics/metric-views)
- [Create a metric view](https://docs.databricks.com/aws/en/uc-semantics/metric-views/create)
- [Metric view YAML syntax reference](https://docs.databricks.com/aws/en/uc-semantics/metric-views/yaml-reference)
- [Query metric views](https://docs.databricks.com/aws/en/uc-semantics/metric-views/query)
- [measure aggregate function](https://docs.databricks.com/aws/en/sql/language-manual/functions/measure)
- [Additive, Semi-Additive, Non-Additive Facts](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/additive-semi-additive-non-additive-fact/)