YAML Reference

Skill: databricks-metric-views

What You Can Build

The metric view YAML spec defines everything about a metric — its source table, join relationships, dimension expressions, measure aggregations, filters, window calculations, and materialization config. This page shows you how to construct each section with the exact field names, types, and constraints the parser expects.

In Action

“Write a complete metric view definition with joins, derived dimensions, filtered measures, and materialization.”

CREATE OR REPLACE VIEW catalog.schema.sales_metrics
WITH METRICS LANGUAGE YAML
AS $$
  version: 1.1
  comment: "Sales metrics with customer and product dimensions"
  source: catalog.schema.fact_sales
  filter: sale_date >= '2023-01-01'
  joins:
    - name: customer
      source: catalog.schema.dim_customer
      on: source.customer_id = customer.id
    - name: product
      source: catalog.schema.dim_product
      on: source.product_id = product.id
  dimensions:
    - name: Sale Month
      expr: DATE_TRUNC('MONTH', sale_date)
      comment: "Month of sale"
    - name: Customer Type
      expr: CASE WHEN customer.tier = 'A' THEN 'Enterprise' ELSE 'SMB' END
    - name: Product Category
      expr: product.category
  measures:
    - name: Total Revenue
      expr: SUM(amount)
    - name: Open Revenue
      expr: SUM(amount) FILTER (WHERE status = 'O')
    - name: Revenue per Customer
      expr: SUM(amount) / COUNT(DISTINCT customer_id)
  materialization:
    schedule: every 1 hour
    mode: relaxed
    materialized_views:
      - name: monthly_summary
        type: aggregated
        dimensions: [Sale Month, Product Category]
        measures: [Total Revenue]
$$

Key decisions:

• version is optional — defaults to 1.1 on DBR 17.2+, use 0.1 for DBR 16.4-17.1
• source requires fully qualified three-level namespace (catalog.schema.table)
• filter applies a global WHERE clause to all queries against this metric view
• comment at top level and on individual dimensions/measures adds documentation visible in Unity Catalog
• Dimension expr can reference other dimensions, joined columns, and SQL functions but cannot contain aggregates
• Measure expr must contain at least one aggregate function

More Patterns

Define dimensions with SQL transformations

“Write YAML dimensions that use date truncation, CASE expressions, and joined column references.”

dimensions:
  - name: Region
    expr: region_name
    comment: "Sales region"
  - name: Order Month
    expr: DATE_TRUNC('MONTH', order_date)
  - name: Customer Type
    expr: CASE WHEN tier = 'A' THEN 'Enterprise' ELSE 'SMB' END
  - name: Nation
    expr: customer.c_name

Dimensions define the categorical axes for grouping and filtering. They support direct column references, SQL functions, multi-line CASE expressions, and references to joined table columns using join_name.column_name. They cannot use aggregate functions.

Configure star and snowflake schema joins

“Write YAML joins for a star schema with two dimension tables, and a snowflake schema with nested customer-nation-region hierarchy.”

# Star schema
joins:
  - name: customer
    source: catalog.schema.dim_customer
    on: source.customer_id = customer.id
  - name: product
    source: catalog.schema.dim_product
    on: source.product_id = product.id

# Snowflake schema (DBR 17.1+)
joins:
  - name: customer
    source: catalog.schema.customer
    on: source.customer_key = customer.customer_key
    joins:
      - name: nation
        source: catalog.schema.nation
        on: customer.nation_key = nation.nation_key
        joins:
          - name: region
            source: catalog.schema.region
            on: nation.region_key = region.region_key

In on expressions, reference the fact table as source and joined tables by their name. Use either on (expression) or using (column list), not both. Nested joins create snowflake traversals for multi-level hierarchies.

Add window measures for time-based calculations

“Write YAML window measures for a running total, a 7-day trailing count, and year-to-date sales.”

measures:
  - name: Running Total
    expr: SUM(total_price)
    window:
      - order: date
        range: cumulative
        semiadditive: last

  - name: 7-Day Customers
    expr: COUNT(DISTINCT customer_id)
    window:
      - order: date
        range: trailing 7 day
        semiadditive: last

  - name: YTD Sales
    expr: SUM(total_price)
    window:
      - order: date
        range: cumulative
        semiadditive: last
      - order: year
        range: current
        semiadditive: last

Window ranges include current (matching value only), cumulative (up to and including current), trailing N unit (N units before, excludes current), leading N unit (N units after), and all (every row). Year-to-date composes two windows: cumulative over date, scoped to the current year. Every window block requires order, range, and semiadditive fields.

Configure materialization for faster queries

“Write YAML materialization config that pre-computes two aggregation levels on a 6-hour schedule.”

materialization:
  schedule: every 6 hours
  mode: relaxed
  materialized_views:
    - name: baseline
      type: unaggregated
    - name: revenue_breakdown
      type: aggregated
      dimensions: [category, region]
      measures: [total_revenue, order_count]

unaggregated materializes the full data model (source + joins + filter) for expensive joins. aggregated pre-computes specific dimension/measure combinations for the most common query patterns. Requires serverless compute and DBR 17.2+.

Watch Out For

• Using version: 0.1 on DBR 17.2+ — version 0.1 is the legacy spec. Use 1.1 to get comment support, improved validation, and all current features. Window measures still require 0.1 as an experimental feature.
• Mixing on and using in the same join — pick one. on takes a SQL expression; using takes a column list. Specifying both raises a parse error.
• Referencing measures with SELECT instead of MEASURE() — metric views require MEASURE(\Total Revenue`)syntax. PlainSELECT `Total Revenue“ returns an error.
• Adding MAP columns in joined table sources — joins don’t support MAP types. Use scalar, STRING, or STRUCT columns for dimension expressions from joined tables.
• Forgetting semiadditive in window blocks — every window spec requires semiadditive: first or semiadditive: last. Omitting it causes a validation error.