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.

Resolve arrays and maps in the source

Fields, measures, and joins all operate on flat, scalar columns. If your source data has ARRAY or MAP type columns, resolve them to flat columns in the source query before referencing them elsewhere in the metric view. There are two transformation strategies, depending on whether you want one row per array element or a single value per source row. Both apply whether the array is in the top-level source or in a table you join to. See Transform complex data types for the full set of transformation functions.

No dataset in the samples catalog has an array column, so the examples in this section use an orders view that has a line_items array of structs. Use the following example to create a view with a field that is an array. Replace catalog.schema with the catalog and schema you want to write to. You must have permissions to create objects in that schema.

CREATE OR REPLACE VIEW catalog.schema.orders AS
SELECT
  o.o_orderkey,
  o.o_custkey,
  o.o_orderdate,
  o.o_orderstatus,
  collect_list(named_struct(
    'product_id', l.l_partkey,
    'quantity', cast(l.l_quantity as int)
  )) AS line_items
FROM samples.tpch.orders o
JOIN samples.tpch.lineitem l ON o.o_orderkey = l.l_orderkey
GROUP BY o.o_orderkey, o.o_custkey, o.o_orderdate, o.o_orderstatus;

Flatten an array into rows

To analyze each array element as its own row, use explode() in the source query to unpack the array. Each element becomes a separate row, and the source row's other columns repeat for each element. See Explode nested elements from a map or array.

The following example unpacks the line_items array so each item becomes a row:

version: 1.1
source: |
  SELECT o_orderkey, o_custkey, item.product_id, item.quantity
  FROM catalog.schema.orders
  LATERAL VIEW explode(line_items) AS item

fields:
  - name: Product
    expr: product_id

measures:
  - name: Total quantity
    expr: SUM(quantity)
  - name: Line item count
    expr: COUNT(1)

Exploding the array in the source multiplies the source rows, so an aggregation such as COUNT(1) counts array elements, not the original rows. To also measure the original rows without fan-out, model the exploded table as a one_to_many join instead. See One-to-many joins.

Aggregate an array into a single value

To reduce an array to one value per source row without changing the row count, apply a scalar array function in the source query, such as aggregate(), array_size(), or reduce(). Each source row keeps its grain, and the computed column is available to fields and measures.

The following example computes the item count and total quantity of the line_items array per order:

version: 1.1
source: |
  SELECT o_orderkey, o_custkey,
    array_size(line_items) AS item_count,
    aggregate(line_items, 0, (acc, x) -> acc + x.quantity) AS total_quantity
  FROM catalog.schema.orders

measures:
  - name: Total quantity
    expr: SUM(total_quantity)
  - name: Average items per order
    expr: AVG(item_count)

Because the source query reduces the array before the metric view processes it, the source keeps one row per order and measures aggregate across orders as usual.

Resolve an array in a joined table

The same rule applies when the array lives in a table you want to join, not in the top-level source. A join operates on flat columns, so resolve the array in the joined table's own source subquery before the join. Write the join source as a SQL query that flattens or aggregates the array, then join on the resulting columns. See Joins in metric views.

The following example uses customer as the source and joins the orders view with cardinality: one_to_many. The join source aggregates each order's line_items array to a scalar total_quantity before the join, so the metric view can sum it per customer without duplicating customer rows:

version: 1.1
source: samples.tpch.customer

joins:
  - name: orders
    source: |
      SELECT o_orderkey, o_custkey,
        aggregate(line_items, 0, (acc, x) -> acc + x.quantity) AS total_quantity
      FROM catalog.schema.orders
    on: orders.o_custkey = source.c_custkey
    cardinality: one_to_many

fields:
  - name: Customer name
    expr: c_name

measures:
  - name: Total quantity
    expr: SUM(orders.total_quantity)
  - name: Order count
    expr: COUNT(orders.o_orderkey)

To instead treat each array element as its own row in the joined table, flatten the array with explode() in the join source in the same way. See Flatten an array into rows.

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 ARRAY or MAP type columns. To resolve arrays or maps to flat columns before joining, see Resolve arrays and maps in the source.

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