Metric view YAML syntax reference

This page explains the complete YAML grammar for metric views. Metric view definitions follow standard YAML notation syntax.

For minimum runtime and YAML specification version requirements for each feature, see Metric view feature availability.

See YAML Specification 1.2.2 documentation to learn more about YAML specifications.

YAML overview

The YAML definition for a metric view includes the following top-level fields:

Field	Type	Description
`version`	String	Required. Version of the metric view specification. See YAML specification versions.
`comment`	String	Optional. Description of the metric view.
`source`	String	Required. The source data for the metric view. Can be any table-like Unity Catalog asset including a metric view or a SQL query. See Source.
`filter`	String	Optional. A SQL boolean expression that applies to all queries. See Filter.
`joins`	Array	Optional. Star schema and snowflake schema joins. See Joins.
`fields`	Array	Conditional. Field definitions including name, expression, and optional semantic metadata. Required if no `measures` are specified. See Fields. The `dimensions` keyword is accepted as a synonym for backward compatibility.
`measures`	Array	Conditional. Measure definitions including name, aggregate expression, and optional semantic metadata. Required if no `fields` are specified. See Measures.
`materialization`	Object	Optional. Configuration for accelerating queries with materialized views. Includes refresh schedule and materialized view definitions. See Materialization.

Source

The source field specifies the data source for the metric view. Supported sources include tables, views, metric views, and SQL queries. Composability applies across metric views. When using a metric view as a source, you can reference its fields and measures in the new metric view. See Composability.

Table-like asset source

Reference a table-like asset using its three-part name:

source: catalog.schema.source_table

SQL query source

To use a SQL query, write the query text directly in the YAML:

source: SELECT * FROM samples.tpch.orders o
  LEFT JOIN samples.tpch.customer c
  ON o.o_custkey = c.c_custkey

Note

When using a SQL query as a source with a JOIN clause, set primary and foreign key constraints on underlying tables and use the RELY option for optimal query performance. For more information, see Declare primary key, foreign key, and unique constraints and Query optimization using primary key and unique constraints.

Filter

A filter in the YAML definition applies to all queries that reference the metric view. Write filters as SQL boolean expressions.

# Single condition filter
filter: o_orderdate > '2024-01-01'

# Multiple conditions with AND
filter: o_orderdate > '2024-01-01' AND o_orderstatus = 'F'

# Multiple conditions with OR
filter: o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH'

# Complex filter with IN clause
filter: o_orderstatus IN ('F', 'P') AND o_orderdate >= '2024-01-01'

# Filter with NOT
filter: o_orderstatus != 'O' AND o_totalprice > 1000.00

# Filter with LIKE pattern matching
filter: o_comment LIKE '%express%' AND o_orderdate > '2024-01-01'

Joins

Joins in metric views support both direct joins from a fact table to dimension tables (star schema) and multi-hop joins across normalized dimension tables (snowflake schemas). You can also join to a SQL query using a SELECT statement. See Use a SQL query as a source.

Note

Joined tables can't include MAP type columns. To unpack values from MAP type columns, see Explode nested elements from a map or array.

Each join definition includes the following fields:

Field	Type	Description
`name`	String	Required. Alias for the joined table or SQL query. Use this alias when referencing columns from the joined table in fields or measures.
`source`	String	Required. Three-part name of the table to join. Can also be a SQL query.
`on`	String	Conditional. Boolean expression defining the join condition. Required if `using` is not specified.
`using`	Array	Conditional. List of column names present in both the parent table and joined table. Required if `on` is not specified.
`joins`	Array	Optional. A list of nested join definitions for snowflake schema modeling. See Metric view feature availability for minimum runtime requirements.
`rely`	Map	Optional. Promises about the join that the analyzer can rely on to produce more efficient query plans. See Optimize joins with `rely`.

Star schema joins

In a star schema, the source is the fact table and joins with one or more dimension tables using a LEFT OUTER JOIN. Metric views join the fact and dimension tables needed for the specific query, based on the selected columns.

Specify join columns using either an ON clause or a USING clause:

ON clause: Uses a boolean expression to define the join condition.
USING clause: Lists columns with the same name in both the parent table and the joined table.

The join should follow a many-to-one relationship. In cases of many-to-many, the first matching row from the joined dimension table is selected.

version: 1.1
source: samples.tpch.lineitem

joins:
  - name: orders
    source: samples.tpch.orders
    on: source.l_orderkey = orders.o_orderkey

  - name: part
    source: samples.tpch.part
    on: source.l_partkey = part.p_partkey

fields:
  - name: Order Status
    expr: orders.o_orderstatus

  - name: Part Name
    expr: part.p_name

measures:
  - name: Total Revenue
    expr: SUM(l_extendedprice * (1 - l_discount))

  - name: Line Item Count
    expr: COUNT(1)

Note

The source namespace references columns from the metric view's source, while a join's name refers to columns from that joined table. For example, in source.l_orderkey = orders.o_orderkey, source refers to lineitem and orders refers to the joined table. If no prefix is provided in an on clause, the reference defaults to the joined table.

Snowflake schema joins

A snowflake schema extends a star schema by normalizing dimension tables and connecting them to subdimensions. This creates a multi-level join structure. See Metric view feature availability for minimum runtime requirements.

To define a snowflake schema, nest joins inside a parent join definition:

version: 1.1
source: samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

fields:
  - name: customer_nation
    expr: customer.nation.n_name

Optimize joins with `rely`

Use the rely field on a join to declare guarantees about the relationship that the query analyzer uses when planning queries. These guarantees allow the engine to plan queries more efficiently and reduce data scanned, especially when fields from the joined table are referenced in filters.

The rely map supports the following fields:

Field	Type	Description
`at_most_one_match`	Boolean	Optional. Defaults to `false`. When `true`, declares that at most one row in the joined table matches each row in the source (a many-to-one relationship that does not fan out).

Warning

Set at_most_one_match: true only when the join is many-to-one. This relationship is not validated at runtime. If multiple rows in the joined table match a single source row, measures (such as SUM and COUNT) return incorrect results.

The following example enables at_most_one_match on a many-to-one join from orders to customer. Queries that filter or group by customer attributes benefit the most:

version: 1.1
source: samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey
    rely:
      at_most_one_match: true

fields:
  - name: Customer name
    expr: customer.c_name
  - name: Customer market segment
    expr: customer.c_mktsegment

measures:
  - name: Total revenue
    expr: SUM(o_totalprice)

Fields

Note

fields and dimensions are equivalent keywords in a metric view definition. fields is the preferred term and is used throughout this documentation. Existing metric views that use dimensions continue to work, and both keywords are accepted on new or updated definitions.

Fields are metric view columns used in SELECT, WHERE, and GROUP BY clauses at query time. Each expression must return a scalar value. Fields can reference columns from the source data or earlier-defined fields in the metric view.

A field can be either:

A categorical or grouping column, such as a region, status, or department.
An unaggregated numeric column, such as an age, price, or quantity. Numeric fields can be aggregated at query time using SQL functions such as SUM or AVG.

Each field definition includes the following properties:

Property	Type	Description
`name`	String	Required. The column alias for the field.
`expr`	String	Required. A SQL expression that can reference columns from the source data or a previously defined field.
`comment`	String	Optional. Description of the field. Appears in Unity Catalog and documentation tools.
`display_name`	String	Optional. Label that appears in visualization tools. Limited to 255 characters. Requires YAML spec 1.1. See Metric view feature availability.
`format`	Map	Optional. Format specification for how values are displayed. Requires YAML spec 1.1. See Format specifications.
`synonyms`	Array	Optional. Alternative names for AI and BI tools to discover the field. Up to 10 synonyms, each limited to 255 characters. Requires YAML spec 1.1. See Synonyms.

Example:

fields:
  # Basic field
  - name: order_date
    expr: o_orderdate
    comment: 'Date the order was placed'
    display_name: 'Order Date'

  # Field with SQL expression
  - name: order_month
    expr: DATE_TRUNC('MONTH', o_orderdate)
    display_name: 'Order Month'

  # Field with synonyms
  - name: order_status
    expr: CASE
      WHEN o_orderstatus = 'O' THEN 'Open'
      WHEN o_orderstatus = 'P' THEN 'Processing'
      WHEN o_orderstatus = 'F' THEN 'Fulfilled'
      END
    display_name: 'Order Status'
    synonyms: ['status', 'fulfillment status']

Measures

Measures are expressions that produce results without a pre-determined level of aggregation. They must be expressed using aggregate functions. To reference a measure in a query, use the MEASURE function. Measures can reference base columns in the source data, earlier-defined fields, or earlier-defined measures.

Each measure definition includes the following fields:

Field	Type	Description
`name`	String	Required. The alias for the measure.
`expr`	String	Required. A SQL expression containing one or more aggregate functions.
`comment`	String	Optional. Description of the measure. Appears in Unity Catalog and documentation tools.
`display_name`	String	Optional. Label that appears in visualization tools. Limited to 255 characters. Requires YAML spec 1.1. See Metric view feature availability.
`format`	Map	Optional. Format specification for how values are displayed. Requires YAML spec 1.1. See Format specifications.
`synonyms`	Array	Optional. Alternative names for AI and BI tools to discover the measure. Up to 10 synonyms, each limited to 255 characters. Requires YAML spec 1.1. See Metric view feature availability.
`window`	Array	Optional. Window specifications for windowed, cumulative, or semiadditive aggregations. When not specified, the measure behaves as a standard aggregate. See Window measures.

See Aggregate functions for a list of aggregate functions.

Example:

measures:
  # Simple count measure
  - name: order_count
    expr: COUNT(1)
    display_name: 'Order Count'

  # Sum aggregation measure with synonyms
  - name: total_revenue
    expr: SUM(o_totalprice)
    comment: 'Gross revenue from all orders'
    display_name: 'Total Revenue'
    synonyms: ['revenue', 'total sales']

  # Distinct count measure
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: 'Unique Customers'

  # Calculated measure combining multiple aggregations
  - name: avg_order_value
    expr: SUM(o_totalprice) / COUNT(DISTINCT o_orderkey)
    display_name: 'Avg Order Value'
    synonyms: ['AOV', 'average order']

  # Filtered measure with WHERE condition
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: 'Open Order Revenue'
    synonyms: ['backlog', 'outstanding revenue']

Window measures

Important

This feature is Experimental.

The window field defines windowed, cumulative, or semiadditive aggregations for measures. For detailed information about window measures and use cases, see Window measures.

Each window specification includes the following fields:

Field	Type	Description
`order`	String	Required. The field that determines the ordering of the window. (1)
`range`	String	Required. The extent of the window. See Supported `range` values.
`semiadditive`	String	Required. Aggregation method. Supported values: `first` or `last`.
`offset`	String	Optional. Requires Databricks Runtime 18.1 and YAML version 1.1 or newer. Shifts the window frame backward or forward along the `order` field by a fixed interval. The value is of the form `<n> <period>`, where `n` is a signed integer (negative looks backward, positive looks forward) and `period` is one of `day`, `days`, `month`, `months`, `year`, or `years`. Examples: `-12 month`, `1 year`, `-3 days`, `7 day`. The `order` field must be a date or timestamp column. `offset` has no effect on `range: all`. If the shifted frame falls outside the available data, the measure evaluates to `NULL`. For usage and worked examples, see How `offset` shifts the window frame.

(1) The referenced field should be deterministic. Non-deterministic expressions such as rand(), uuid(), or current_timestamp() produce unpredictable window ordering and can lead to incorrect aggregation results.

Supported `range` values

current: Rows where the window ordering value equals the anchor row's value.
cumulative: All rows where the window ordering value is less than or equal to the anchor row's value.
trailing <value> <unit> [inclusive | exclusive]: Rows from the anchor row going backward by the specified time units, for example trailing 7 day. The optional inclusive or exclusive modifier requires Databricks Runtime 18.1 and YAML version 1.1 or newer, and controls whether the anchor row is included in the window. The default is exclusive. See Include or exclude the anchor row.
leading <value> <unit> [inclusive | exclusive]: Rows from the anchor row going forward by the specified time units, for example leading 3 month. The optional inclusive or exclusive modifier requires Databricks Runtime 18.1 and YAML version 1.1 or newer, and controls whether the anchor row is included in the window. The default is exclusive. See Include or exclude the anchor row.
all: All rows regardless of the window ordering value.

Window measure example

The following example calculates a rolling 7-day count of unique customers:

version: 1.1
source: samples.tpch.orders

fields:
  - name: order_date
    expr: o_orderdate

measures:
  - name: rolling_7day_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: '7-Day Rolling Customers'
    window:
      - order: order_date
        range: trailing 7 day
        semiadditive: last

Materialization

Important

This feature is Experimental.

The materialization field configures automatic query acceleration using materialized views. For detailed information about how materialization works, requirements, and best practices, see Materialization for metric views.

The materialization field includes the following top-level fields:

Field	Type	Description
`schedule`	String	Required. Refresh schedule. Uses the same syntax as the schedule clause on materialized views. The `TRIGGER ON UPDATE` clause is not supported.
`mode`	String	Required. Must be set to `relaxed`.
`materialized_views`	Array	Required. List of materialized views to materialize. Each entry requires the fields described below.

Each entry in materialized_views includes the following fields:

Field	Type	Description
`name`	String	Required. The name of the materialization.
`type`	String	Required. Type of materialization. Supported values: `aggregated` (requires `dimensions`, `measures`, or both) or `unaggregated`.
`dimensions`	Array	Conditional. List of field names to materialize. Required if `type` is `aggregated` and no `measures` are specified.
`measures`	Array	Conditional. List of measure names to materialize. Required if `type` is `aggregated` and no `dimensions` are specified.

Note

The materialization block uses the dimensions: keyword rather than fields:. Use dimensions: when listing fields to materialize, even if your top-level definition uses fields:.

Materialization example

The following example defines a metric view with multiple materializations:

version: 1.1
source: samples.tpch.orders

fields:
  - name: order_date
    expr: o_orderdate
  - name: order_status
    expr: o_orderstatus

measures:
  - name: total_revenue
    expr: SUM(o_totalprice)
  - name: order_count
    expr: COUNT(1)

materialization:
  schedule: every 6 hours
  mode: relaxed
  materialized_views:
    - name: baseline
      type: unaggregated

    - name: daily_status_metrics
      type: aggregated
      dimensions:
        - order_date
        - order_status
      measures:
        - total_revenue
        - order_count

Column name references

When referencing column names that contain spaces or special characters in YAML expressions, enclose the column name in backticks. If the expression starts with a backtick and is used directly as a YAML value, wrap the entire expression in double quotes. Valid YAML values cannot start with a backtick.

Formatting examples

Use the following examples to learn how to format YAML correctly in common scenarios.

Reference a column name

The following examples show how to format column references depending on the characters they contain.

No spaces

Source column: revenue

expr: "revenue"
expr: 'revenue'
expr: revenue

Use double quotes, single quotes, or no quotes around the column name.

Column name with spaces

Source column: `First Name`

expr: '`First Name`'

Use backticks to escape spaces. Enclose the entire expression in double quotes.

Column names with spaces in a SQL expression

Source columns: `First Name`, `Last Name`

expr: CONCAT(`First Name`, ' ', `Last Name`)

If the expression doesn't start with a backtick, double quotes are not required.

Column name containing quotes

Source column: "name"

expr: '`"name"`'

Use backticks to escape the double quotes in the column name. Enclose the expression in single quotes.

Expressions with colons

expr: "CASE WHEN `Customer Tier` = 'Enterprise: Premium' THEN 1 ELSE 0 END"

Note

YAML interprets unquoted colons as key-value separators. Always use double quotes around expressions that include colons.

Multi-line expressions

expr: |
  CASE WHEN
    revenue > 100 THEN 'High'
  ELSE 'Low'
  END

Note

Use the | block scalar after expr: for multi-line expressions. All lines must be indented at least two spaces beyond the expr key for correct parsing.

Upgrade to YAML 1.1

Upgrading a metric view to YAML specification version 1.1 requires care, because comments are handled differently than in earlier versions.

Types of comments

YAML comments (#): Inline or single-line comments written directly in the YAML file.
Unity Catalog comments: Comments stored in Unity Catalog for the metric view or its columns. These are separate from YAML comments.

Upgrade considerations

Choose the upgrade path that matches how you want to handle comments in your metric view.

Option 1: Preserve YAML comments using notebooks or the SQL editor

If your metric view contains YAML comments (#) that you want to keep, use the following steps:

Use the ALTER VIEW command in a notebook or SQL editor.
Copy the original YAML definition into the $$..$$ section after AS. Change the value of version to 1.1.
Save the metric view.

ALTER VIEW metric_view_name AS
$$
# The notebook preserves inline comments
version: 1.1
source: samples.tpch.orders
fields:
- name: order_date # The notebook preserves inline comments
  expr: o_orderdate
measures:
# The notebook preserves commented out definitions
# - name: total_orders
#   expr: COUNT(o_orderid)
- name: total_revenue
  expr: SUM(o_totalprice)
$$

Warning

Running ALTER VIEW removes Unity Catalog comments unless they are explicitly included in the comment fields of the YAML definition. To preserve comments shown in Unity Catalog, see Option 2.

Option 2: Preserve Unity Catalog comments

Note

The following guidance applies only when using the ALTER VIEW command in a notebook or SQL editor. If you upgrade your metric view to version 1.1 using the YAML editor UI, the YAML editor UI automatically preserves your Unity Catalog comments.

Copy all Unity Catalog comments into the appropriate comment fields in your YAML definition. Change the value of version to 1.1.
Save the metric view.

ALTER VIEW metric_view_name AS
$$
version: 1.1
source: samples.tpch.orders
comment: "Metric view of order (Updated comment)"

fields:
- name: order_date
  expr: o_orderdate
  comment: "Date of order - Copied from Unity Catalog"

measures:
- name: total_revenue
  expr: SUM(o_totalprice)
  comment: "Total revenue"
$$

For YAML specification version history and minimum runtime requirements for each feature, see Metric view feature availability.

Feedback

Was this page helpful?

Last updated on 2026-06-02

Metric view YAML syntax reference

YAML overview

Source

Table-like asset source

SQL query source

Filter

Joins

Star schema joins

Snowflake schema joins

Optimize joins with rely

Fields

Measures

Window measures

Supported range values

Window measure example

Materialization

Materialization example

Column name references

Formatting examples

Reference a column name

No spaces

Column name with spaces

Column names with spaces in a SQL expression

Column name containing quotes

Expressions with colons

Multi-line expressions

Upgrade to YAML 1.1

Types of comments

Upgrade considerations

Option 1: Preserve YAML comments using notebooks or the SQL editor

Option 2: Preserve Unity Catalog comments

Feedback

Additional resources

Optimize joins with `rely`

Supported `range` values