metric-views

Original：🇺🇸 English
Translated

Unity Catalog metric views: define, create, query, and manage governed business metrics in YAML. Use when building standardized KPIs, revenue metrics, order analytics, or any reusable business metrics that need consistent definitions across teams and tools.

1installs
Sourcedatabricks-solutions/ai-dev-kit
Added on

NPX Install

npx skill4agent add databricks-solutions/ai-dev-kit metric-views

Tags

Translated version includes tags in frontmatter
metric-viewsunity-catalogdatabricksyaml-configurationbusiness-metrics

SKILL.md Content

View Translation Comparison →

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

sql
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.
sql
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

TopicFileDescription
YAML Syntaxyaml-reference.mdComplete YAML spec: dimensions, measures, joins, materialization
Patterns & Examplespatterns.mdCommon patterns: star schema, snowflake, filtered measures, window measures, ratios

MCP Tools

Use the 
manage_metric_views
tool for all metric view operations:
ActionDescription
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

python
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

python
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

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

Grant Access

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

YAML Spec Quick Reference

yaml
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

DimensionsMeasures
PurposeCategorize and group dataAggregate numeric values
ExamplesRegion, Date, StatusSUM(revenue), COUNT(orders)
In queriesUsed in SELECT and GROUP BYWrapped in 
MEASURE()
SQL expressionsAny SQL expressionMust use aggregate functions

Why Metric Views vs Standard Views?

FeatureStandard ViewsMetric Views
Aggregation locked at creationYesNo - flexible at query time
Safe re-aggregation of ratiosNoYes
Star/snowflake schema joinsManualDeclarative in YAML
MaterializationSeparate MV neededBuilt-in
AI/BI Genie integrationLimitedNative

Common Issues

IssueSolution
SELECT * not supportedMust explicitly list dimensions and use MEASURE() for measures
"Cannot resolve column"Dimension/measure names with spaces need backtick quoting
JOIN at query time failsJoins must be in the YAML definition, not in the SELECT query
MEASURE() requiredAll measure references must be wrapped: 
MEASURE(\
name`)`
DBR version errorRequires Runtime 17.2+ for YAML v1.1, or 16.4+ for v0.1
Materialization not workingRequires 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