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:

Source

The source field specifies the data source for the metric view. You can use a table-like asset, such as tables, views, and metric views, or a SQL query as the source. Composability applies across metric views. When using a metric view as a source, you can reference its dimensions 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

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.

Each join definition includes the following fields:

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

dimensions:
  - 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)

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

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

Dimensions

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

Each dimension definition includes the following fields:

Example:

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

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

  # Dimension 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 fields in the source data, earlier-defined dimensions, or earlier-defined measures.

Each measure definition includes the following fields:

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

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:

Window measure example

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

version: 1.1
source: samples.tpch.orders

dimensions:
  - 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

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:

Each entry in materialized_views includes the following fields:

Materialization example

The following example defines a metric view with multiple materializations:

version: 1.1
source: samples.tpch.orders

dimensions:
  - 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 to escape the space or character. 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 table shows how to format column names depending on the characters they contain.

Use expressions with colons

Multi-line indentation

Upgrade your YAML to 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 using the # symbol.
• Unity Catalog comments: Comments stored in Unity Catalog for the metric view or its columns (dimensions and measures). These are separate from YAML comments.

Upgrade considerations

Choose the upgrade path that matches how you want to handle comments in your metric view. The following options describe the available approaches and provide examples.

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:

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

ALTER VIEW metric_view_name AS
$$
# The notebook preserves inline comments
version: 1.1
source: samples.tpch.orders
dimensions:
- 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)
$$

Option 2: Preserve Unity Catalog comments

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

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

dimensions:
- 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.