Model metric views

This page describes how to define the core components of a metric view: sources, fields, measures, filters, and joins.

Metric views create a semantic layer for your data, transforming tables and views into standardized business metrics. They define what to measure, how to aggregate it, and how to segment it, so that every user across the organization reports the same value for the same KPI, eliminating inconsistent reporting and enabling flexible analysis across any fields.

For a full example with joins, fields, measures, and agent metadata, see Tutorial: Build a complete metric view with joins.

Core components

A metric view consists of the following elements:

Component	Description	Example
Source	The base table, view, or SQL query containing the data.	`samples.tpch.orders`
Fields	Column attributes used to segment or group metrics.	Product category, Order month, Customer region
Measures	Column aggregations that produce metrics.	`COUNT(o_orderkey)` as Order Count, `SUM(o_totalprice)` as Total Revenue
Filters	Conditions applied to the source data to define scope.	`status = 'completed'` `order_date > '2024-01-01'`
Joins	Relationships between tables, views, and metric views to enrich data.	Join `orders` table with `customers` table on `customer_key`

Define a source

You can use a table-like asset or a SQL query as the source for your metric view. You must have at least SELECT privileges on any referenced asset.

A table-like asset is any Unity Catalog object that exposes a tabular schema and supports SELECT queries, including tables, views, materialized views, streaming tables, foreign tables, system tables, and metric views.

Use a table-like asset as a source

To use a table-like asset as a source, specify the fully-qualified name. For example: samples.tpch.orders.

Use a metric view as a source

You can use an existing metric view as the source for a new metric view:

version: 1.1

source: views.examples.source_metric_view

fields:
  - name: Order month
    expr: '`Order Month`'

measures:
  - name: Latest order month
    expr: MAX(`Order month`)
  - name: Latest order year
    expr: "DATE_TRUNC('year', MEASURE(`Latest order month`))"

When using a metric view as a source, the same composability rules apply for referencing fields and measures. See Composability.

Use a SQL query as a source

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

version: 1.1

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

fields:
  - name: Order key
    expr: o_orderkey

measures:
  - name: Order Count
    expr: COUNT(o_orderkey)

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. See Declare primary key, foreign key, and unique constraints and Query optimization using primary key and unique constraints.

Fields

Fields are 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 previously defined fields in the metric view. Each field consists of two components:

name: The alias of the column
expr: A SQL expression that references the source data or previously defined fields in the metric view

Warning

Metric view fields are always STRING, even when the source column is CHAR or VARCHAR. The CHAR(n) space-padding applied at the table boundary is lost, so comparisons can differ from the source table. For example, a CHAR(10) value 'COLLEGE' is stored as 'COLLEGE ', so column = 'COLLEGE' returns true on the table but false on the metric view field.

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 consists of the following components:

name: The alias of the measure
expr: An aggregate SQL expression that can include SQL aggregate functions

The following example demonstrates common measure patterns for analyzing order and revenue data. These examples use the TPC-H orders table, which contains sales transaction data including order prices (o_totalprice), customer identifiers (o_custkey), order keys (o_orderkey), order dates (o_orderdate), and priority levels (o_orderpriority):

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

  # Sum aggregation measure
  - name: Total Revenue
    expr: SUM(o_totalprice)

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

  # Calculated measure combining multiple aggregations
  - name: Average Order Value
    expr: SUM(o_totalprice) / COUNT(DISTINCT o_orderkey)

  # Filtered measure with WHERE condition
  - name: High Priority Order Revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderpriority = '1-URGENT')

  # Measure using a field
  - name: Average Revenue per Month
    expr: SUM(o_totalprice) / COUNT(DISTINCT DATE_TRUNC('MONTH', o_orderdate))

See Aggregate functions for a list of aggregate functions.

Apply filters

A filter in the YAML definition applies to all queries that reference the metric view. The following example shows how to write filters as Boolean expressions:

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

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

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

Work with joins

Metric views support joins to enrich your source data with attributes from related tables. You can model star schemas (fact table joined to dimension tables), snowflake schemas (multi-level dimension joins), and one-to-many relationships (fact expansion from a dimensional source). For details on join types, cardinality, schema patterns, and restrictions, see Joins in metric views.

The following example joins the orders fact table to the customer dimension table:

version: 1.1
source: samples.tpch.orders

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

fields:
  - name: Customer name
    expr: customer.c_name

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

YAML syntax and formatting

Metric view definitions follow standard YAML notation syntax. See Metric view YAML syntax reference for the required syntax and formatting.

Best practices

Use the following guidelines when modeling metric views:

Model atomic measures: Start by defining the simplest measures first (for example, SUM(revenue), COUNT(DISTINCT customer_id)). Build complex measures using composability.
Standardize field values: Use transformations (such as CASE statements) to convert database codes into clear business names (for example, convert order status 'O' to 'Open' and 'F' to 'Fulfilled').
Define scope with filters: If a metric view should only include completed orders, define that filter in the metric view so users can't accidentally include incomplete data.
Use clear naming: Metric names should be recognizable to business users (for example, "Customer Lifetime Value" instead of cltv_agg_measure).
Separate time fields: Include granular time fields (such as "Order Date") and truncated time fields (such as "Order Month" or "Order Week") to support both detail-level and trend analysis.

Next steps

Feedback

Was this page helpful?

Last updated on 2026-06-11