Model metric views

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. As a result, every user across the organization reports the same value for the same KPI, which eliminates inconsistent reporting and enables flexible analysis across any fields.

The core components you define are sources, joins, filters, fields, and measures.

For a full example with joins, fields, measures, and agent metadata, see Tutorial: build a metric view with joins and data modeling.

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
Joins	Relationships between tables, views, and metric views to enrich data.	Join orders table with customers table on customer_key
Filters	Conditions applied to the source data to define scope.	status = 'completed' order_date > '2024-01-01'
Fields	Columns used to group, filter, and aggregate metrics. Includes categorical columns and unaggregated numeric columns. Also called dimensions.	Product category, Order month, Unit price
Measures	Column aggregations that produce metrics.	COUNT(o_orderkey) as Order Count, SUM(o_totalprice) as Total Revenue

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, also called dimensions, are metric view columns that you can use in SELECT, WHERE, and GROUP BY clauses at query time. A field can be a categorical column, such as region or status, or an unaggregated numeric column, such as price or quantity, that you can aggregate at query time. Each field expression must return a scalar value. It can reference columns from the source data or fields defined earlier 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

String-like metric view fields are always STRING, even when the source column is CHAR or VARCHAR. Because CHAR(n) space-padding is lost, comparisons can return different results. For example, column = 'COLLEGE' matches a CHAR(10) value in the source table (which is space-padded) but not in 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 applies to all queries that reference the metric view. To define a filter in the UI, see Step 3: Define a filter.

To define a filter in the YAML definition, write a Boolean expression. The following example shows common filter patterns:

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

To define joins in the UI, see Step 2: Add a join. To define joins in the YAML definition, use the patterns in the following sections.

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.

Model star schemas

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 fields and measures.

Specify join columns using either an on clause (Boolean expression) or a using clause (shared column names). The join must follow a many-to-one relationship. In cases of many-to-many, the engine selects the first matching row from the joined dimension table.

The following example joins orders (fact table) to customer (dimension table) and exposes customer attributes as fields. Setting rely.at_most_one_match: true declares that the join is many-to-one (each order has exactly one customer), which allows the engine to optimize queries that filter on fields from the joined table.

Warning

Set at_most_one_match: true only when the relationship is many-to-one. This property is not validated at runtime. If the join produces a fan-out, measures return incorrect results.

See Optimize joins with rely.

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 enable both detail-level and trend analysis.

Additional resources

• Joins in metric views
• Advanced techniques for metric views
• Agent metadata in metric views
• Materialization for metric views
• Metric view YAML syntax reference