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 metric view with joins and data modeling.

Window measures

Experimental

This feature is Experimental.

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

You can add a window measure in the Catalog Explorer editor or in YAML.

Add a window measure in the editor

On the UI tab of the metric view editor, click + Window while editing a measure. + Window is available in both Builder and Custom mode. The window options in the editor correspond to the YAML fields described in Define a window measure.

For more information about creating and editing measures, see Create a metric view.

Define a window measure

A window measure includes the following required fields:

order: The field that determines the ordering of the window.
range: Defines the extent of the window. Supported values include current, cumulative, trailing, leading, and all. A trailing or leading range takes a time unit on a date or timestamp order field, such as trailing 7 day, or no unit at all on a consecutive integer order column, such as trailing 3. 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 field by a fixed amount. Use a dated offset such as -12 month on a date or timestamp order field for period-over-period measures such as month-over-month or year-over-year. Use a unitless numeric offset such as -1 on a consecutive integer order column to compare along a business period that is not a calendar date. For syntax, supported units, and constraints, see Window measures.

You can also reference an integer parameter as the value of a window measure's range or offset, so a caller passes the window size in at query time. See Pass a window size as a parameter.

How `offset` shifts the window frame

See Metric view feature availability for minimum compute and YAML specification 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)

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

See Metric view feature availability for minimum compute and YAML specification 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 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.

YAML
version: 1.1

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

fields:
  - 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 field 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.

Create the metric view using SQL

To create this metric view outside Catalog Explorer, wrap the YAML in CREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML AS and place the definition between the $$ delimiters:

SQL
CREATE OR REPLACE VIEW catalog.schema.rolling_customers WITH METRICS LANGUAGE YAML AS
$$
  version: 1.1

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

  fields:
    - name: date
      expr: o_orderdate

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

The other complete definitions on this page follow the same pattern.

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.

YAML
version: 1.1

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

fields:
  - 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:

The example uses two window measures: 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 field.

YAML
version: 1.1
source: main.default.monthly_sales

fields:
  - 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.

Window measures on a numeric index column

Requires Databricks Runtime 19 or above and YAML specification version 1.1 or above.

A dated offset such as -12 month steps along a date or timestamp column. A date column no longer fits when your comparison axis is a business period that is not a calendar date. Fiscal weeks, ISO weeks, and 4-4-5 accounting periods are all examples. Instead, order a window measure on an integral index column and apply a unitless numeric offset or range. A numeric offset does integer arithmetic on the index, so offset: -1 means the previous position in the sequence, and range: trailing 3 means the last three positions.

A numeric offset or range returns the correct result only when the order column meets all of the following requirements. Only the first requirement is validated at query time. A column that breaks either of the other two returns wrong numbers rather than an error, so verify them yourself:

Integral: The column is a TINYINT, SMALLINT, INT, or BIGINT. DATE, TIMESTAMP, DECIMAL, DOUBLE, and STRING columns are not valid index columns. To compare along a date, use a dated offset instead. See How offset shifts the window frame.
Monotonic and consecutive (dense): The value increases by exactly 1 from each real period to the next, including across the year or quarter boundary. A gap or a repeated value makes offset: -1 skip or double-count a period. A raw label, such as a fiscal_week that resets to 1 each year, is not a valid index on its own.
Matched to the comparison grain: The offset or range steps in the grain of the index. On a month index, offset: -1 is the previous month, and range: trailing 3 is the last three months. For a coarser comparison, such as year-over-year, order a separate measure on a year index or use a same-position-prior-cycle measure. Don't reuse a finer (month) index and group up. See Same position in a prior cycle (two window specs, no index).

Get an index column

You can name a pre-computed index column from the source, or derive one in the metric view. The recipe depends on the calendar shape:

Calendar shape	Index recipe
Uniform periods per parent (12 months per year, 4 quarters per year)	`expr: year * 12 + month` (months) or `expr: year * 4 + quarter` (quarters). This is exact and needs no extra table.
Variable periods per parent (ISO 52- or 53-week, 4-4-5 at week grain)	Use a calendar lookup table that maps `(year, period)` to a dense integer. A naive `year * 53 + week` drifts on 52-week years.
String key (`'YYYYPP'`, `'YYYY-Www'`)	Parse the string to integers first, then apply one of the recipes above.

Calendar shape	Index recipe
Uniform periods per parent (12 months per year, 4 quarters per year)	`expr: year * 12 + month` (months) or `expr: year * 4 + quarter` (quarters). This is exact and needs no extra table.
Variable periods per parent (ISO 52- or 53-week, 4-4-5 at week grain)	Use a calendar lookup table that maps `(year, period)` to a dense integer. A naive `year * 53 + week` drifts on 52-week years.
String key (`'YYYYPP'`, `'YYYY-Www'`)	Parse the string to integers first, then apply one of the recipes above.

Align the dimensions the index depends on

Give a range: all order spec to every metric view field that the index is computed from, such as order_year and order_month for month_index = order_year * 12 + order_month. This applies whether the index is derived in the metric view or pre-calculated upstream as a bare column. The range: all spec makes those fields ignored during window construction, as intended, when a query groups or filters on them.

These specs are not optional. Without them, a query that groups on order_year or order_month partitions the window by those fields, which confines the shifted frame to a single period, and the comparison measure comes back NULL.

Every other field gets no range: all spec. A field that is independent of the index, such as region, groups normally, and the window partitions per value.

Consecutive comparison or rolling window (index plus numeric range)

Use an index with a numeric offset or range when "adjacent in the sequence" is what you mean, such as the previous period, a rolling N periods, or period-to-date. Order on the index, apply a numeric offset or range, and add a range: all spec for each calendar column the index depends on. In the following example, the index depends on order_year and order_month:

YAML
version: 1.1
source: samples.tpch.orders

fields:
  - name: order_year
    expr: YEAR(o_orderdate) # INT breakdown column
  - name: order_month
    expr: MONTH(o_orderdate) # INT breakdown column
  - name: month_index # consecutive monotonic index across years
    expr: order_year * 12 + order_month

measures:
  - name: monthly_sales
    expr: SUM(o_totalprice)
    window:
      - order: order_year # breakdown, so range: all keeps GROUP BY order_year working
        range: all
        semiadditive: last
      - order: order_month # breakdown
        range: all
        semiadditive: last
      - order: month_index # the axis you compare along
        range: current
        semiadditive: last

  - name: monthly_sales_prev # previous month: same specs plus a unitless offset of -1 on the index
    expr: SUM(o_totalprice)
    window:
      - order: order_year
        range: all
        semiadditive: last
      - order: order_month
        range: all
        semiadditive: last
      - order: month_index
        range: current
        offset: -1 # one position back, crosses the year boundary correctly
        semiadditive: last

  - name: sales_trailing_3mo # rolling 3 months: unitless range on the index
    expr: SUM(o_totalprice)
    window:
      - order: order_year
        range: all
        semiadditive: last
      - order: order_month
        range: all
        semiadditive: last
      - order: month_index
        range: trailing 3 # the three index positions before the current month
        semiadditive: last

  - name: mom_pct_change
    expr: (MEASURE(monthly_sales) - MEASURE(monthly_sales_prev))
      / NULLIF(MEASURE(monthly_sales_prev), 0) * 100

For this example, the following configuration applies:

month_index is a consecutive monotonic index that increases by 1 each month across year boundaries.
monthly_sales orders on the index with range: current and gives the order_year and order_month breakdown columns a range: all spec so that grouping by them works.
monthly_sales_prev adds offset: -1 on the index to look back one month, which crosses the year boundary correctly.
sales_trailing_3mo uses the unitless range trailing 3 to sum three index positions. Because the index is consecutive, three positions is always three months, with no time-unit arithmetic involved. The range defaults to exclusive, so it covers the three months before the current one. Add inclusive to include the current month instead.
mom_pct_change composes the base and shifted measures. Using NULLIF avoids divide-by-zero errors when the prior value is zero.

Query the measure by grouping on the aligned breakdown columns, not the raw index:

SQL
SELECT
  order_year,
  order_month,
  MEASURE(monthly_sales),
  MEASURE(monthly_sales_prev),
  MEASURE(sales_trailing_3mo),
  MEASURE(mom_pct_change)
FROM catalog.schema.monthly_sales_mv
GROUP BY ALL

Same position in a prior cycle (two window specs, no index)

A comparison such as "the same month last year" is aligned by position, not by absolute distance, so it needs no index column. Use two window specs: one shifts the parent cycle, and one holds the inner position.

YAML
version: 1.1
source: samples.tpch.orders

fields:
  - name: order_year
    expr: YEAR(o_orderdate)
  - name: order_month
    expr: MONTH(o_orderdate)

measures:
  - name: monthly_sales
    expr: SUM(o_totalprice)

  - name: monthly_sales_py # same month, prior year
    expr: SUM(o_totalprice)
    window:
      - order: order_year # shift the year coordinate
        range: current
        offset: -1
        semiadditive: last
      - order: order_month # hold the month fixed
        range: current
        semiadditive: last

  - name: yoy_pct_change
    expr: (MEASURE(monthly_sales) - MEASURE(monthly_sales_py))
      / NULLIF(MEASURE(monthly_sales_py), 0) * 100

For this example, the following configuration applies:

monthly_sales_py uses two window specs. The order_year spec shifts the year coordinate back by one with offset: -1, and the order_month spec holds the month fixed with range: current.
The comparison aligns by position, so it needs no index column.
yoy_pct_change composes the two measures to express the percentage change.

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.

YAML
version: 1.1
source: samples.tpch.orders

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

fields:
  - 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.

YAML
version: 1.1

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

fields:
  - name: date
    expr: o_orderdate
  - name: month
    expr: DATE_TRUNC('MONTH', date)
  - name: year
    expr: DATE_TRUNC('year', date)
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:

The example uses two window specifications: one for the cumulative sum over the date field and another to limit the sum to the current year.
The year field restricts the cumulative sum so that it resets at the beginning of each new year.
The month and year fields form a date hierarchy on the order field date: each is defined on the date field by name, not on the underlying o_orderdate column, so queries can group this measure by them. See Group by a date hierarchy field.

Semiadditive measure example

The following example calculates account balances, which must 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.

YAML
version: 1.1

fields:
  - 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. A window measure is computed along its order field, so a query that breaks results down over time must reference that field, either directly or through a date hierarchy field defined on it. When the query doesn't reference the order field, the semiadditive keyword determines the value returned, as described in Semiadditive measure example.

The following example groups a window measure by state and by a month expression over the order field date:

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

Group by a date hierarchy field

A date hierarchy rolls the order field up to coarser grains, such as week, month, or year. Define each level as a field over the order field by name, not over the underlying source column:

YAML
fields:
  - name: date
    expr: o_orderdate
  # Date hierarchy: each level is defined on the order field `date`,
  # not on the underlying o_orderdate column.
  - name: month
    expr: DATE_TRUNC('MONTH', date)
  - name: year
    expr: DATE_TRUNC('year', date)

Grouping a window measure by a hierarchy level returns the measure at that grain. Assuming the period-to-date example is created as ytd_metric_view as it is in the Period-to-date measure example, the following query returns the YTD value as of the last date in each month:

SQL
SELECT month, MEASURE(ytd_sales) AS ytd_sales
FROM ytd_metric_view
GROUP BY month
ORDER BY month;

warning

Defining a hierarchy level on the underlying source column, such as DATE_TRUNC('MONTH', o_orderdate), breaks its link to the order field date, even though the expressions look equivalent. Grouping a window measure by such a field returns incorrect results.

Composability

Metric views are composable. You can build new fields 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 fields in new fields.
Fields and earlier measures in new measures.
Fields from metric views used as source in new fields.
Fields 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)`

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.

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

YAML
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

Define atomic measures first: Establish fundamental measures (SUM, COUNT, AVG) before defining measures that reference them.
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.
Prioritize readability: Compose measures using clear mathematical formulas. For example, MEASURE(gross_profit) / MEASURE(total_revenue) is clearer than a single complex SQL expression.
Add semantic metadata: Use semantic metadata to format composed measures (for example, percentages or currency) for downstream tools. See Agent metadata in metric views.

Window measures​

Add a window measure in the editor​

Define a window measure​

How offset shifts the window frame​

Include or exclude the anchor row​

Trailing, moving, or leading window measure example​

Period-over-period window measure example​

Year-over-year window measure example using offset​

Window measures on a numeric index column​

Get an index column​

Align the dimensions the index depends on​

Consecutive comparison or rolling window (index plus numeric range)​

Same position in a prior cycle (two window specs, no index)​

Cumulative (running) total measure example​

Period-to-date measure example​

Semiadditive measure example​

Query a window measure​

Group by a date hierarchy field​

Composability​

Define measures with composability​

Example: Average order value (AOV)​

Composability with conditional logic​

Example: Fulfillment rate​

Best practices for composability​

Additional resources​

Window measures

Add a window measure in the editor

Define a window measure

How `offset` shifts the window frame

Include or exclude the anchor row

Trailing, moving, or leading window measure example

Period-over-period window measure example

Year-over-year window measure example using `offset`

Window measures on a numeric index column

Get an index column

Align the dimensions the index depends on

Consecutive comparison or rolling window (index plus numeric range)

Same position in a prior cycle (two window specs, no index)

Cumulative (running) total measure example

Period-to-date measure example

Semiadditive measure example

Query a window measure

Group by a date hierarchy field

Composability

Define measures with composability

Example: Average order value (AOV)

Composability with conditional logic

Example: Fulfillment rate

Best practices for composability

Additional resources