Skip to main content

Model metric view data

This page describes how to model metric views and best practices for working with them.

Metric views help to create a semantic layer for your data, transforming raw tables into standardized, business-friendly metrics. They define what to measure, how to aggregate it, and how to segment it, ensuring that every user across the organization reports the same number for the same Key Performance Indicator (KPI). The goal is to create a single source of truth for business metrics.

By modeling your data as metric views, you abstract away complex SQL, table structures, and data quality issues, allowing analysts to focus purely on analysis.

Core components​

Modeling a metric view involves defining the following elements over your source data:

Component

Description

Example

Source

The base table, view, or SQL query containing the raw transactional data.

samples.tpch.orders

Dimensions

The column attributes used to segment or group the metrics

Product category, Order month, Customer region

Measures

The column aggregations that produce the metrics. These measures are what you usually report on.

COUNT(o_order_id) as Order Count, SUM(o_total_price) as Total Revenue.

Filters

Persistent conditions applied to the source data to define scope.

  • status = 'completed'
  • order_date > '2024-01-01'

Define a source​

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

Use a table as a source​

To use a table as a source, include the fully-qualified table name, as in the following example.

YAML
source: samples.tpch.orders

Use a SQL query as a source​

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

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, Databricks recommends setting primary and foreign key constraints on underlying tables and using the RELY option for optimal performance at query time, if applicable. For more information about using primary and foreign key constraints, see Declare primary key and foreign key relationships and Query optimization using primary key constraints.

Use metric view as a source​

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

YAML
version: 1.1
source: views.examples.source_metric_view

dimensions:
  # Dimension referencing dimension from source_metric_view
  - name: Order date
    expr: order_date_dim

measures:
  # Measure referencing dimension from source_metric_view
  - name: Latest order month
    expr: MAX(order_date_dim_month)

  # Measure referencing measure from source_metric_view
  - name: Latest order year
    expr: DATE_TRUNC('year', MEASURE(max_order_date_measure))

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

Dimensions​

Dimensions are columns used in SELECT, WHERE, and GROUP BY clauses at query time. Each expression must return a scalar value. Dimensions are defined as an array. Each dimension consists of two components:

  • name: The alias of the column.

  • expr: A SQL expression on the source data that defines the dimension or a previously defined dimension in the metric view.

note

Starting from version 1.1, you can also define semantic metadata (display name, format, and synonyms) for each dimension. See Use semantic metadata in metric views for details.

Measures​

Measures are columns defined as an array of 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, you must use the MEASURE function. Measures can reference base fields in the source data or earlier-defined dimensions. 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:

YAML
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 dimension
  - 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.

See measure aggregate function.

note

Starting from version 1.1, you can also define semantic metadata (display name, format, and synonyms) for each measure. See Use semantic metadata in metric views for details.

Apply filters​

A filter in the YAML definition of a metric view applies to all queries that reference it. It must be written as a SQL boolean expression and is equivalent to using a WHERE clause in a SQL query.

The following example demonstrates common filter patterns:

YAML
# 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'

You can also add filters when you query or consume metric views.

Advanced modeling capabilities​

Metric view modeling supports advanced techniques to create sophisticated and highly reusable metrics.

Joins​

Joins allow you to enrich your metric view with descriptive attributes from related tables. You can use joins to model relationships from the fact table to dimension tables (star schema) and to traverse from dimensions to subdimensions, allowing multi-hop joins across normalized dimension tables (snowflake schema).

See Use joins in metric views.

Window measures​

Experimental

This feature is Experimental.

Window measures enable you to define measures with windowed, cumulative, or semiadditive aggregations in your metric views. These types of measures allow for more complex calculations, such as moving averages, period-over-period changes, and running totals. See Use window measures in metric views for examples that demonstrate how to use window measures in metric views.

Composability​

Metric views are composable, allowing you to build complex logic by referencing previously defined elements. You can reference previously defined dimensions in new dimensions, reference any dimension or previously defined measures in new measures, and reference columns from joins defined in the metric view.

See Composability in metric views.

Semantic metadata​

Semantic metadata helps consuming tools understand how to display and treat measures and dimensions. This includes properties such as:

Semantic metadata

Example

Display names

Total Revenue instead of sum_o_price.

Display format

Standardize formatting for currency, percentages, and dates.

Comments

Explain the metric's business definition in natural language.

When you define semantic metadata, it travels with the metric. For example, when analysts use Total Revenue in a dashboard, it automatically displays as currency.

See Use semantic metadata in metric views.

YAML syntax and formatting​

Metric view definitions follow standard YAML notation syntax. See YAML syntax reference to learn about the required syntax and formatting to define a metric view. See YAML Specification 1.2.2 documentation to learn more about YAML specifications.

Window measures​

Window measures calculate a value over a defined window, or range of rows related to the current row. You can use window measures for time-series and comparative analysis, allowing you to define metrics such as:

  • Rolling 30-Day Total Revenue: Sum of revenue over the last 30 days
  • Year-to-Date (YTD) Revenue: Cumulative sum from the start of the year
  • Previous Period Comparison: Revenue from the prior month

See Use window measures in metric views.

Best practices for modeling metric views​

Use the following guidelines when modeling metric views:

  • Model atomic measures: Start by defining the simplest, non-calculated measures first (for example, SUM(revenue), COUNT(DISTINCT customer_id)). Build complex measures (like AOV) using composability.
  • Standardize dimension values: Use transformations (such as CASE statements or expressions) to convert cryptic database codes into clear business names (for example, convert order status 'O' to 'Open' and 'F' to 'Fulfilled').
  • Define scope with filters: Be intentional about persistent filters. If a metric view should only ever include completed orders, define that filter in the metric view so users cannot accidentally include incomplete data.
  • Use business-friendly naming: Metric names should be immediately recognizable to business users (for example, Customer Lifetime Value vs. cltv_agg_measure).
  • Separate time dimensions: Always include granular time dimensions (such as Order Date) and truncated time dimensions (such as Order Month or Order Week) to support both detail-level and trend analysis.
Last updated on