Databricks metric views: examples and limitations

A Databricks metric view is a business number defined once, in YAML, in Unity Catalog. You write the formula, such as revenue divided by distinct customers, and the fields people may group by. Every query then asks for the measure with MEASURE() and Databricks computes it from the source rows at whatever grouping was asked. Dashboards, Genie and SQL all get the same answer.

It fixes a quiet bug most BI setups have: numbers that are right in one report and wrong when someone regroups them.

Why not a normal view?

A normal view fixes its grouping when you create it. Store revenue per customer by region and month, and anyone who wants it by region alone has to average the monthly figures, which is wrong whenever months have different numbers of customers. Distinct counts break the same way: a customer who bought in 2 months is counted twice.

Try both ways on 7 orders:

SELECT region, AVG(revenue_per_customer)
FROM sales_by_region_month
GROUP BY region

Revenue adds up, so any approach gets it right. Customer counts and ratios do not, and those are the numbers people argue about in meetings.

A complete example

This is Databricks' own example, with its comments trimmed, on the TPC-H sample data every workspace has. It joins orders to customers, adds 3 fields to group by and 3 measures:

CREATE OR REPLACE VIEW orders_metric_view WITH METRICS LANGUAGE YAML AS
$$
  version: 1.1
  comment: "Orders KPIs for sales analysis"
  source: samples.tpch.orders
  joins:
    - name: customer
      source: samples.tpch.customer
      'on': source.o_custkey = customer.c_custkey
      rely:
        at_most_one_match: true
  filter: source.o_orderdate > '1990-01-01'
  fields:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', source.o_orderdate)
    - name: Order Status
      expr: |-
        CASE
          WHEN source.o_orderstatus = 'O' THEN 'Open'
          WHEN source.o_orderstatus = 'P' THEN 'Processing'
          WHEN source.o_orderstatus = 'F' THEN 'Fulfilled'
        END
    - name: Market Segment
      expr: customer.c_mktsegment
  measures:
    - name: Order Count
      expr: COUNT(1)
    - name: Total Revenue
      expr: SUM(source.o_totalprice)
    - name: Total Revenue per Customer
      expr: SUM(source.o_totalprice) / COUNT(DISTINCT source.o_custkey)
$$;

What each part does:

You need SELECT on the source, USE CATALOG, USE SCHEMA and CREATE TABLE where the view goes, and compute on Databricks Runtime 17.3 or later for this example. Metric views in general need 16.4 or later.

Query it

SELECT
  `Market Segment`,
  MEASURE(`Total Revenue`),
  MEASURE(`Total Revenue per Customer`)
FROM orders_metric_view
GROUP BY ALL;

Change Market Segment to Order Month and revenue per customer is computed again for each month from the orders, not averaged from segments. 3 rules to remember:

• No SELECT *. Every measure must be wrapped in MEASURE(), so list the columns.
• No joins at query time. Query the metric view in a CTE, then join the CTE to other tables.
• Read the definition with DESCRIBE TABLE EXTENDED orders_metric_view AS JSON.

Power BI, Tableau and other tools

Moving from an existing BI model? Give Genie Code a Tableau (.twb, .twbx, .tds, .tdsx) or Power BI (.pbit, .tmdl) file with /importBI and it writes the metric view for you. Genie Code covers what that costs.

Limitations

• Flat columns only. Fields, measures and joins work on scalar columns. Turn ARRAY and MAP columns into rows with explode(), or into one value with array_size(), in the source query.
• Only the owner edits it. To share editing, transfer ownership to a group. That does not work for materialized metric views.
• ALTER VIEW replaces everything. Send the full YAML, not the changed lines.
• Materialization has limits. You can pre-compute aggregations to speed queries up, but with a one-to-many join a query must match a materialization exactly; it cannot be rolled up from a finer one.
• Dropping is permanent. Check the Lineage tab for dashboards and queries that use it first.

Beyond the basics, metric views also have window measures for trailing averages, period-over-period change and running totals.

Why it matters more now

Genie reads metric views. They are the part of the Genie Ontology you define yourself, so the formula you write is the one Genie One chat can use for business users. The idea is also no longer Databricks-only: Apache Spark 4.2 added CREATE VIEW ... WITH METRICS to open-source Spark (what's new in Spark 4.2).

The general idea is a 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.. To build one on a real gold table, our data engineering course has a part on metric views.