Advanced techniques for metric views

Advanced techniques for metric views let you express complex business logic and reuse definitions across your semantic layer. This page explains two such techniques:

  • Window measures: for time-series calculations such as moving averages, running totals, and period-over-period changes.
  • Composability: for building complex measures by referencing other measures rather than rewriting their logic.

This page assumes familiarity with basic metric view modeling concepts. See Model metric views.

Note

The examples on this page use the TPC-H sample dataset, which models a wholesale supply chain. For more information about the TPC-H dataset, see tpch. For an end-to-end tutorial using this dataset with metric views, see Tutorial: Build a complete metric view with joins.

Window measures

Important

This feature is Experimental.

Window measures enable you to define measures with windowed, cumulative, or semiadditive aggregations in your metric views, supporting calculations such as moving averages, period-over-period changes, and running totals.

Define a window measure

A window measure includes the following required fields:

  • order: The dimension that determines the ordering of the window.

  • range: Defines the extent of the window. Supported values include current, cumulative, trailing, leading, and all. For full syntax and descriptions, see Supported range values. For details on the inclusive and exclusive modifiers on trailing and leading, see Include or exclude the anchor row.

  • semiadditive: Specifies how to aggregate the measure when the order field is not included in the query's GROUP BY. Possible values: first and last.

A window measure also supports the following optional field:

  • offset: Shifts the window frame backward or forward along the order dimension by a fixed interval. Use this for period-over-period measures such as month-over-month or year-over-year. For syntax, supported units, and constraints, see Window measures.

How offset shifts the window frame

Requires Databricks Runtime 18.1 and YAML version 1.1 or newer. See version requirements.

The range field defines the shape of the window relative to the anchor row, and offset slides that frame by the specified interval along order. The following table shows the frame for each range value with and without an offset of k, relative to the anchor row t:

range Frame without offset Frame with offset: k
current [t, t] [t + k, t + k]
cumulative (-infinity, t] (-infinity, t + k]
trailing N [t - N, t) [t + k - N, t + k)
leading N (t, t + N] (t + k, t + k + N]
all entire partition entire partition (unchanged)

offset is independent of semiadditive. The first or last choice still controls how the measure collapses when order is not in the query's GROUP BY.

For best results, match offset to the natural grain of order. For monthly data, offset: -12 month is preferred over offset: -365 day because month and year arithmetic respects variable-length months and leap years, while day arithmetic does not.

Include or exclude the anchor row

Requires Databricks Runtime 18.1 and YAML version 1.1 or newer. See version requirements.

For trailing and leading ranges, the optional inclusive or exclusive keyword controls whether the anchor row's window value (for example, today) is part of the rolling window:

Keyword Meaning Anchor row in range?
inclusive n units including the anchor row. Yes
exclusive (default) n units not including the anchor row. No

The following example shows how inclusive and exclusive affect the rolling window for the anchor date 2025-01-05 with trailing 3 day.

Assume the underlying data has one row per day with the following values:

Date Value
2025-01-02 1
2025-01-03 4
2025-01-04 2
2025-01-05 (anchor) 5

Each modifier selects three days of rows relative to the anchor and sums their values:

Modifier Dates in window Values Sum
trailing 3 day inclusive 01-03, 01-04, 01-05 4 + 2 + 5 11
trailing 3 day exclusive 01-02, 01-03, 01-04 1 + 4 + 2 7

leading ranges follow the same logic in the opposite direction.

Trailing, moving, or leading window measure example

The following example calculates a rolling 7-day count of unique customers who placed orders. This metric tracks customer engagement trends over time by showing how many distinct customers made purchases in the week leading up to each date.

version: 1.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
  - name: date
    expr: o_orderdate

measures:
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: date
        range: trailing 7 day
        semiadditive: last

For this example, the following configuration applies:

  • order: date specifies that the date dimension orders the window.
  • range: trailing 7 day defines the window as the 7 days before each date, excluding the date itself.
  • semiadditive: last returns the last value in the 7-day window when date is not a grouping column.

Period-over-period window measure example

The following example calculates day-over-day sales growth by comparing today's revenue (sum of all order prices) to yesterday's revenue. This metric identifies daily sales trends and shows the percentage change in revenue.

version: 1.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
  - name: date
    expr: o_orderdate
measures:
  - name: previous_day_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: trailing 1 day
        semiadditive: last
  - name: current_day_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: current
        semiadditive: last
  - name: day_over_day_growth
    expr: (MEASURE(current_day_sales) - MEASURE(previous_day_sales)) / MEASURE(previous_day_sales) * 100

For this example, the following configuration applies:

  • Two window measures are used: one for calculating total sales on the previous day and one for the current day.
  • A third measure calculates the percentage change (growth) between the current and previous days.

Year-over-year window measure example using offset

The offset modifier is the building block for period-over-period measures. Define a shifted copy of a base measure, then compose the two to express deltas, ratios, or growth rates directly in the metric view.

The following example calculates year-over-year sales growth by comparing each month's sales to the same month in the prior year. The shifted measure uses offset: -12 month to look back 12 months along the month dimension.

version: 1.1
source: main.default.monthly_sales

dimensions:
  - name: month
    expr: month
  - name: category
    expr: category

measures:
  - name: monthly_sales
    expr: SUM(sales)
    window:
      - order: month
        range: current
        semiadditive: last

  - name: monthly_sales_py
    expr: SUM(sales)
    window:
      - order: month
        range: current
        semiadditive: last
        offset: -12 month

  - name: yoy_growth
    expr: MEASURE(monthly_sales) - MEASURE(monthly_sales_py)

  - name: yoy_growth_pct
    expr: (MEASURE(monthly_sales) - MEASURE(monthly_sales_py))
      / NULLIF(MEASURE(monthly_sales_py), 0)

For this example, the following configuration applies:

  • monthly_sales is the base measure, summing sales for the current month.
  • monthly_sales_py is the same measure shifted backward by 12 months using offset: -12 month. For January 2025, it returns the value for January 2024.
  • yoy_growth and yoy_growth_pct compose the two measures to express the absolute and percentage change. Using NULLIF avoids divide-by-zero errors when the prior-year value is zero.

Cumulative (running) total measure example

The following example calculates cumulative sales revenue from the beginning of the dataset up to each date. This running total shows how much total revenue has been generated over time, useful for tracking progress toward annual revenue goals or analyzing long-term growth patterns.

version: 1.1
source: samples.tpch.orders

filter: o_orderdate > DATE'1998-01-01'

dimensions:
  - name: date
    expr: o_orderdate
  - name: customer
    expr: o_custkey

measures:
  - name: running_total_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last

For this example, the following configuration applies:

  • order: date orders the window chronologically.
  • range: cumulative defines the window as all data from the beginning of the dataset up to and including each date.
  • semiadditive: last returns the most recent cumulative value when date is not included in the query's GROUP BY, rather than summing across all dates.

Period-to-date measure example

The following example calculates year-to-date (YTD) sales revenue. This measure shows the cumulative revenue generated from January 1st of each year up to the current date, resetting at the beginning of each new year.

version: 1.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1997-01-01'

dimensions:
  - name: date
    expr: o_orderdate
  - name: year
    expr: DATE_TRUNC('year', o_orderdate)
measures:
  - name: ytd_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last
      - order: year
        range: current
        semiadditive: last

For this example, the following configuration applies:

  • Two window specifications are used: one for the cumulative sum over the date dimension and another to limit the sum to the current year.
  • The year dimension restricts the cumulative sum so that it resets at the beginning of each new year.

Semiadditive measure example

The following example calculates account balances, which should not be summed across dates (you can't add Monday's balance to Tuesday's balance to get total balance). Instead, when aggregating across multiple days, the measure returns the most recent balance. However, the measure can still be summed across customers to show total balance across all accounts on a given day.

version: 1.1

dimensions:
  - name: date
    expr: date
  - name: customer
    expr: customer_id

measures:
  - name: semiadditive_balance
    expr: SUM(balance)
    window:
      - order: date
        range: current
        semiadditive: last

For this example, the following configuration applies:

  • order: date orders the window chronologically.
  • range: current restricts the window to a single day with no aggregation across days.
  • semiadditive: last returns the most recent balance when aggregating over multiple days.

Note

This window measure still sums over all customers to get the overall balance per day.

Query a window measure

You can query a metric view with a window measure like any other metric view. The following example queries a metric view:

SELECT
   state,
   DATE_TRUNC('month', date),
   MEASURE(t7d_customers) as m
FROM my_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL

Composability

Metric views are composable. You can build new dimensions and measures that reference existing ones rather than rewriting logic from scratch. This reduces duplication and makes complex metric definitions easier to maintain.

Composability works at two levels: within a single metric view, and across metric views when one metric view is used as the source for another.

Composability supports the following reference patterns:

  • Earlier dimensions in new dimensions.
  • Dimensions and earlier measures in new measures.
  • Dimensions from metric views used as source in new dimensions.
  • Dimensions and measures from metric views used as source in new measures.

Define measures with composability

In the measures section, you can reference measures from the source metric view or measures defined earlier in the same metric view. This approach improves consistency, auditability, and maintenance of your semantic layer.

Measure type Description Example
Atomic A simple, direct aggregation on a source column. These form the building blocks. SUM(o_totalprice)
Composed An expression that mathematically combines one or more other measures using the MEASURE() function. MEASURE(total_revenue) / MEASURE(order_count)

Example: Average Order Value (AOV)

The following example defines Average Order Value (AOV) using two atomic measures: total_revenue (sum of order prices) and order_count (number of orders). The avg_order_value measure references both atomic measures.

version: 1.1

source: samples.tpch.orders

measures:
  # Total Revenue
  - name: total_revenue
    expr: SUM(o_totalprice)

  # Order Count
  - name: order_count
    expr: COUNT(1)

  # Composed Measure: Average Order Value (AOV)
  - name: avg_order_value
    # Defines AOV as Total Revenue divided by Order Count
    expr: MEASURE(total_revenue) / MEASURE(order_count)

If the total_revenue definition changes (for example, to exclude tax), avg_order_value automatically uses the updated definition.

Composability with conditional logic

You can use composability to create complex ratios, conditional percentages, and growth rates without relying on window functions for simple period-over-period calculations.

Example: Fulfillment Rate

The following example calculates fulfillment rate: the percentage of orders with status 'F' (fulfilled). The measure divides fulfilled orders by total orders.

version: 1.1

source: samples.tpch.orders

measures:
  # Total Orders (denominator)
  - name: total_orders
    expr: COUNT(1)

  # Fulfilled Orders (numerator)
  - name: fulfilled_orders
    expr: COUNT(1) FILTER (WHERE o_orderstatus = 'F')

  # Composed Measure: Fulfillment Rate (Ratio)
  - name: fulfillment_rate
    expr: MEASURE(fulfilled_orders) / MEASURE(total_orders)
    format:
      type: percentage

Best practices for composability

  1. Define atomic measures first: Establish fundamental measures (SUM, COUNT, AVG) before defining measures that reference them.
  2. Use MEASURE() for references: Use the MEASURE() function when referencing another measure in an expr. Don't repeat aggregation logic manually. For example, avoid SUM(a) / COUNT(b) if measures for both values already exist.
  3. Prioritize readability: Compose measures using clear mathematical formulas. For example, MEASURE(gross_profit) / MEASURE(total_revenue) is clearer than a single complex SQL expression.
  4. Add semantic metadata: Use semantic metadata to format composed measures (for example, percentages or currency) for downstream tools. See Agent metadata in metric views.

Next steps

  • Last updated on