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

Inspect Source Table Schema

Before creating a metric view, call get_table_stats_and_schema to understand available columns for dimensions and measures:

get_table_stats_and_schema(
    catalog="catalog",
    schema="schema",
    table_names=["orders"],
    table_stat_level="SIMPLE"  # Use "DETAILED" for cardinality, min/max, histograms
)

Create a Metric View

CREATE OR REPLACE VIEW catalog.schema.orders_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  comment: "Orders KPIs for sales analysis"
  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+
source: catalog.schema.table    # Required: source table/view
comment: "Description"          # Optional: metric view description
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

databricks-metric-views