Unity Catalog Metric Views

Define reusable, governed business metrics in YAML that separate measure definitions from dimension groupings for flexible querying.

When to Use

Use this skill when:

• Defining standardized business metrics (revenue, order counts, conversion rates)

• Building KPI layers shared across dashboards, Genie, and SQL queries

• Creating metrics with complex aggregations (ratios, distinct counts, filtered measures)

• Defining window measures (moving averages, running totals, period-over-period, YTD)

• Modeling star or snowflake schemas with joins in metric definitions

• Enabling materialization for pre-computed metric aggregations

Prerequisites

• Databricks Runtime 17.2+ (for YAML version 1.1)

• SQL warehouse with CAN USE permissions

• SELECT on source tables, CREATE TABLE

• USE SCHEMA in the target schema

Quick Start

Create a Metric View

CREATE OR REPLACE VIEW catalog.schema.orders_metrics WITH METRICS LANGUAGE YAML AS $$ version: 1.1 comment: "Orders KPIs for sales analysis" source: catalog.schema.orders filter: order_date > '2020-01-01' dimensions: - name: Order Month expr: DATE_TRUNC('MONTH', order_date) comment: "Month of order" - name: Order Status expr: CASE WHEN status = 'O' THEN 'Open' WHEN status = 'P' THEN 'Processing' WHEN status = 'F' THEN 'Fulfilled' END comment: "Human-readable order status" measures: - name: Order Count expr: COUNT(1) - name: Total Revenue expr: SUM(total_price) comment: "Sum of total price" - name: Revenue per Customer expr: SUM(total_price) / COUNT(DISTINCT customer_id) comment: "Average revenue per unique customer" $$

Query a Metric View

All measures must use the MEASURE() function. SELECT * is NOT supported.

SELECT Order Month, Order Status, MEASURE(Total Revenue) AS total_revenue, MEASURE(Order Count) AS order_count FROM catalog.schema.orders_metrics WHERE extract(year FROM Order Month) = 2024 GROUP BY ALL ORDER BY ALL

Reference Files

Topic File Description

YAML Syntax yaml-reference.md Complete YAML spec: dimensions, measures, joins, materialization

Patterns & Examples patterns.md Common patterns: star schema, snowflake, filtered measures, window measures, ratios

MCP Tools

Use the manage_metric_views tool for all metric view operations:

Action Description

create

Create a metric view with dimensions and measures

alter

Update a metric view's YAML definition

describe

Get the full definition and metadata

query

Query measures grouped by dimensions

drop

Drop a metric view

grant

Grant SELECT privileges to users/groups

Create via MCP

manage_metric_views( action="create", full_name="catalog.schema.orders_metrics", source="catalog.schema.orders", or_replace=True, comment="Orders KPIs for sales analysis", filter_expr="order_date > '2020-01-01'", dimensions=[ {"name": "Order Month", "expr": "DATE_TRUNC('MONTH', order_date)", "comment": "Month of order"}, {"name": "Order Status", "expr": "status"}, ], measures=[ {"name": "Order Count", "expr": "COUNT(1)"}, {"name": "Total Revenue", "expr": "SUM(total_price)", "comment": "Sum of total price"}, ], )

Query via MCP

manage_metric_views( action="query", full_name="catalog.schema.orders_metrics", query_measures=["Total Revenue", "Order Count"], query_dimensions=["Order Month"], where="extract(year FROM Order Month) = 2024", order_by="ALL", limit=100, )

Describe via MCP

manage_metric_views( action="describe", full_name="catalog.schema.orders_metrics", )

Grant Access

manage_metric_views( action="grant", full_name="catalog.schema.orders_metrics", principal="data-consumers", privileges=["SELECT"], )

YAML Spec Quick Reference

version: 1.1 # Required: "1.1" for DBR 17.2+ comment: "Description" # Optional: metric view description source: catalog.schema.table # Required: source table/view filter: column > value # Optional: global WHERE filter

dimensions: # Required: at least one

• name: Display Name # Backtick-quoted in queries expr: sql_expression # Column ref or SQL transformation comment: "Description" # Optional (v1.1+)

measures: # Required: at least one

• name: Display Name # Queried via MEASURE(name) expr: AGG_FUNC(column) # Must be an aggregate expression comment: "Description" # Optional (v1.1+)

joins: # Optional: star/snowflake schema

• name: dim_table source: catalog.schema.dim_table on: source.fk = dim_table.pk

materialization: # Optional (experimental) schedule: every 6 hours mode: relaxed

Key Concepts

Dimensions vs Measures

Dimensions Measures

Purpose Categorize and group data Aggregate numeric values

Examples Region, Date, Status SUM(revenue), COUNT(orders)

In queries Used in SELECT and GROUP BY Wrapped in MEASURE()

SQL expressions Any SQL expression Must use aggregate functions

Why Metric Views vs Standard Views?

Feature Standard Views Metric Views

Aggregation locked at creation Yes No - flexible at query time

Safe re-aggregation of ratios No Yes

Star/snowflake schema joins Manual Declarative in YAML

Materialization Separate MV needed Built-in

AI/BI Genie integration Limited Native

Common Issues

Issue Solution

SELECT * not supported Must explicitly list dimensions and use MEASURE() for measures

"Cannot resolve column" Dimension/measure names with spaces need backtick quoting

JOIN at query time fails Joins must be in the YAML definition, not in the SELECT query

MEASURE() required All measure references must be wrapped: MEASURE(
name)

DBR version error Requires Runtime 17.2+ for YAML v1.1, or 16.4+ for v0.1

Materialization not working Requires serverless compute enabled; currently experimental

Integrations

Metric views work natively with:

• AI/BI Dashboards - Use as datasets for visualizations

• AI/BI Genie - Natural language querying of metrics

• Alerts - Set threshold-based alerts on measures

• SQL Editor - Direct SQL querying with MEASURE()

• Catalog Explorer UI - Visual creation and browsing

Resources

• Metric Views Documentation

• YAML Syntax Reference

• Joins

• Window Measures (Experimental)

• Materialization

• MEASURE() Function