topic Re: Metric Views + Window Functions not supported in Warehousing & Analytics

Metric Views + Window Functions not supported

wrosa — Mon, 23 Feb 2026 20:21:12 GMT

Hi @Hubert-Dudek I am getting following error:

METRIC_VIEW_WINDOW_FUNCTION_NOT _SUPPORTED The metric view is not allowed to use window function (...)

With the following definition:

- name: Sales net Total
- expr: SUM(MEASURE(`Sales net`)) OVER())

However following query works fine:

```

SELECT

category,

MEASURE(`Sales net`) as sales_net,

SUM(MEASURE(`Sales net`)) OVER() AS sales_net_total

FROM sales_metric_view

GROUP BY category

ORDER BY sales_net DESC

```

Re: Metric Views + Window Functions not supported

SteveOstrowski — Sun, 08 Mar 2026 07:39:14 GMT

Hi @wrosa,

The error you are seeing (METRIC_VIEW_WINDOW_FUNCTION_NOT_SUPPORTED) is expected behavior. Metric views do not allow raw SQL window functions like SUM(...) OVER() directly in a measure's expr definition. This is by design because metric views use their own declarative window specification in the YAML definition rather than inline SQL window syntax.

WHAT IS HAPPENING

When you define a measure as:

- name: Sales net Total
expr: SUM(MEASURE(`Sales net`)) OVER()

The metric view engine rejects this because OVER() is standard SQL window function syntax, which is not permitted inside a measure expression. Measure expressions in metric views must be aggregate expressions (like SUM(), COUNT(), AVG(), etc.), optionally with FILTER clauses or arithmetic, but not SQL window functions.

HOW TO ACHIEVE THE SAME RESULT

You have two options depending on what you need.

OPTION 1: USE THE METRIC VIEW WINDOW MEASURE YAML SYNTAX

Metric views support "window measures" as a declarative feature. Instead of writing OVER() in the expr, you define a window block in the YAML. For example, if you want a grand total of Sales net across all rows, you would define it like this:

measures:
- name: Sales net
expr: SUM(sales_net_column)
- name: Sales net Total
expr: SUM(sales_net_column)
window:
- order: <some_dimension>
range: all
semiadditive: last

The "range: all" specification gives you the equivalent of OVER() (the entire dataset). The "order" field must reference one of your defined dimensions, and "semiadditive" controls how the value is summarized when that order dimension is not in the GROUP BY of your query.

Here is a more concrete example. If you have a date dimension:

dimensions:
- name: category
expr: category
- name: order_date
expr: order_date
measures:
- name: Sales net
expr: SUM(sales_net_column)
- name: Sales net Total
expr: SUM(sales_net_column)
window:
- order: order_date
range: all
semiadditive: last

Then you would query it:

SELECT
category,
MEASURE(`Sales net`) as sales_net,
MEASURE(`Sales net Total`) as sales_net_total
FROM sales_metric_view
GROUP BY category
ORDER BY sales_net DESC

This gives you the per-category sales net alongside the grand total, similar to what SUM(...) OVER() would produce.

Note: Window measures are currently an experimental feature. See the documentation for full details on available range options (current, cumulative, trailing, leading, all).

OPTION 2: USE THE WINDOW FUNCTION AT QUERY TIME INSTEAD

As you already discovered, you can use window functions in the query that reads from the metric view, just not inside the metric view definition itself:

SELECT
category,
MEASURE(`Sales net`) as sales_net,
SUM(MEASURE(`Sales net`)) OVER() AS sales_net_total
FROM sales_metric_view
GROUP BY category
ORDER BY sales_net DESC

This approach works because the OVER() is applied at query time after the metric view engine has resolved the MEASURE() calls. This is a perfectly valid pattern and may be simpler if you only need this calculation in one specific query.

DOCUMENTATION REFERENCES

Metric views overview:
https://docs.databricks.com/aws/en/metric-views/

Window measures in metric views:
https://docs.databricks.com/aws/en/metric-views/data-modeling/window-measures

Creating metric views:
https://docs.databricks.com/aws/en/metric-views/create/

In summary: the metric view definition language intentionally uses its own declarative window specification rather than SQL window function syntax. You can either define window measures in YAML using the window block, or apply standard SQL window functions at query time when reading from the metric view.

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.

Re: Metric Views + Window Functions not supported

lpolanco85 — Wed, 06 May 2026 18:46:31 GMT

Hi Steve, I have a similar problem , I am trying to translate the following query into yaml

SELECT

-- ATTRIBUTES

FechaReporte_Crm,

zonaventa_CRM,

ClaseOperacionComercialId_Crm,

--BusinessPartner_Crm,

-- MEASURES

MEASURE(ContadorReporte_Crm) AS ContadorReporte_Crm,

MEASURE(Efectividad_CRM) AS Efectividad_CRM,

-- % DISTRIBUTION

MEASURE(ContadorReporte_Crm) * 100.0

/ SUM(MEASURE(ContadorReporte_Crm)) OVER (

PARTITION BY

---ATTRIBUTES IN SELECT LIST

FechaReporte_Crm

, zonaventa_CRM

-- , ClaseOperacionComercialId_Crm

) AS Pct_Distribucion_Abs_Crm,

---ATTRIBUTES IN SELECT LIST

MEASURE(Efectividad_CRM) * 100.0

/ SUM(MEASURE(Efectividad_CRM)) OVER (

PARTITION BY FechaReporte_Crm

, zonaventa_CRM

--, ClaseOperacionComercialId_Crm

) AS Pct_Distribucion_Efectividad_Crm

FROM datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx

WHERE 1=1

AND zonaventa_CRM IS NOT NULL

AND FechaReporte_Crm = '20260505'

GROUP BY ALL

ORDER BY 2;

Here is how my yaml looks like:

---FIX PROD CATALOG FROM PRD2 TO PRD

CREATE OR REPLACE VIEW datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx

WITH METRICS

LANGUAGE YAML

version: 1.1

source: datalakekof_dev.comercial_vista.com_vw_crm_efectividad_mx

dimensions:

- name: ReporteId_Crm

expr: ReporteId_Crm

- name: Responsable_Crm

expr: Responsable_Crm

- name: BusinessPartner_Crm

expr: BusinessPartner_Crm

- name: ClienteTexto_Crm

expr: ClienteTexto_Crm

- name: FlagCancelacion_Crm

expr: FlagCancelacion_Crm

- name: MedioIngreso_Crm

expr: MedioIngreso_Crm

- name: Prioridad_Crm

expr: Prioridad_Crm

- name: Canal_Crm

expr: Canal_Crm

- name: Encuestador_Crm

expr: Encuestador_Crm

- name: CodigoProducto_Crm

expr: CodigoProducto_Crm

- name: FechaReporte_Crm

expr: FechaReporte_Crm

- name: RutaPreventa_Crm

expr: RutaPreventa_Crm

- name: NivelMercadeo_Crm

expr: NivelMercadeo_Crm

- name: UnidadOperativaId_Crm

expr: UnidadOperativaId_Crm

- name: UnidadOperativaId1_Crm

expr: UnidadOperativaId1_Crm

- name: AgenteOperadorId_Crm

expr: AgenteOperadorId_Crm

- name: AgenteOperadorTexto_Crm

expr: AgenteOperadorTexto_Crm

- name: NumeroParte_Crm

expr: NumeroParte_Crm

- name: EstadoReporteTexto_Crm

expr: EstadoReporteTexto_Crm

- name: Dia

expr: Dia

- name: Mes

expr: Mes

- name: Filtro2_Crm

expr: Filtro2_Crm

- name: Filtro1_Crm

expr: Filtro1_Crm

- name: PaisAbv_Crm

expr: PaisAbv_Crm

- name: GrupoVendedor_CRM

expr: GrupoVendedor_CRM

- name: GrupoOficina_CRM

expr: GrupoOficina_CRM

- name: DistribuidoraRepartoTexto_ECC

expr: DistribuidoraRepartoTexto_ECC

- name: RutaReparto_CRM

expr: RutaReparto_CRM

- name: ZonaVenta_CRM

expr: ZonaVenta_CRM

- name: ZonaVentaTexto_Crm

expr: ZonaVentaTexto_Crm

- name: ModoVenta_Crm

expr: ModoVenta_Crm

- name: AreaId_Crm

expr: AreaId_Crm

- name: MotivoId_Crm

expr: MotivoId_Crm

- name: MotivoTipoTexto_Crm

expr: MotivoTipoTexto_Crm

- name: MotivoCodigoId_Crm

expr: MotivoCodigoId_Crm

- name: MotivoTexto_Crm

expr: MotivoTexto_Crm

- name: CodigoGrupoTexto_Crm

expr: CodigoGrupoTexto_Crm

- name: MotivoGrupoId_Crm

expr: MotivoGrupoId_Crm

- name: ClaseOperacionComercialId_Crm

expr: ClaseOperacionComercialId_Crm

- name: ClaseOperacionComercialTexto_Crm

expr: ClaseOperacionComercialTexto_Crm

- name: ClaseOperacionComercialTextoLargo_Crm

expr: ClaseOperacionComercialTextoLargo_Crm

measures:

- name: ContadorReporte_Crm

expr: SUM(ContadorReporte_Crm)

- name: ContadorReporteResolucion_Crm

expr: SUM(ContadorReporteResolucion_Crm) * 100

- name: Efectividad_Crm

expr: COALESCE(TRY_DIVIDE(ContadorReporteResolucion_Crm,ContadorReporte_Crm),0)

;

The tricky part here is that I am not able to dinamycally transalate the dimensions that participate in the PARTITION BY clause so I need to do it at the metric view level.

Also notice level of detail in PARTITION BY contains minus 1 column than in SELECT columns list, so when adding an extra column to SELECT, we need to have the same columns in PARTITION BY minus the one lastly added.

Do you have any idea how to achieve this behavior?

Thanks in advance

Hi @lpolanco85, Good news: you can express this entirely...

SteveOstrowski — Sat, 30 May 2026 01:29:55 GMT

Hi @lpolanco85,

Good news: you can express this entirely inside the metric view. I reproduced your exact pattern (a percent-of-total whose PARTITION BY has one fewer column than the SELECT list) on a SQL warehouse and it works. The two pieces that unlock it are composable measures and a windowed expression that wraps MEASURE().

TWO RULES THAT MAKE THIS WORK

1. A measure's expr must be an aggregate, and to reference another measure you wrap it in MEASURE(). So your Efectividad measure should compose the two underlying measures rather than reference the raw columns directly:

measures:
  - name: ContadorReporte_Crm
    expr: SUM(ContadorReporte_Crm)

  - name: ContadorReporteResolucion_Crm
    expr: SUM(ContadorReporteResolucion_Crm) * 100

  - name: Efectividad_Crm
    expr: COALESCE(TRY_DIVIDE(MEASURE(ContadorReporteResolucion_Crm),
                              MEASURE(ContadorReporte_Crm)), 0)

2. For the percent-of-total columns, you can use SUM(MEASURE(x)) OVER (PARTITION BY ...) directly in the measure expr. The important detail I confirmed by testing: inside the OVER clause you reference the DIMENSION names, not the raw source columns. This is exactly what gives you the "PARTITION BY contains one fewer column than the SELECT" behavior you described, and you define it only once:

measures:
  - name: Pct_Distribucion_Abs_Crm
    expr: MEASURE(ContadorReporte_Crm) * 100.0
        / SUM(MEASURE(ContadorReporte_Crm)) OVER (PARTITION BY FechaReporte_Crm, ZonaVenta_CRM)

  - name: Pct_Distribucion_Efectividad_Crm
    expr: MEASURE(Efectividad_Crm) * 100.0
        / SUM(MEASURE(Efectividad_Crm)) OVER (PARTITION BY FechaReporte_Crm, ZonaVenta_CRM)

(FechaReporte_Crm and ZonaVenta_CRM here are the dimension names you defined in the dimensions block, not the underlying table columns.)

HOW IT BEHAVES WHEN YOU QUERY IT

With the measures defined once as above, the consumer query controls the level of detail and you never touch the metric view again:

SELECT
  FechaReporte_Crm,
  ZonaVenta_CRM,
  ClaseOperacionComercialId_Crm,
  MEASURE(ContadorReporte_Crm)        AS ContadorReporte_Crm,
  MEASURE(Efectividad_Crm)            AS Efectividad_CRM,
  MEASURE(Pct_Distribucion_Abs_Crm)   AS Pct_Distribucion_Abs_Crm,
  MEASURE(Pct_Distribucion_Efectividad_Crm) AS Pct_Distribucion_Efectividad_Crm
FROM datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx
WHERE ZonaVenta_CRM IS NOT NULL
  AND FechaReporte_Crm = '20260505'
GROUP BY ALL
ORDER BY 2;

ClaseOperacionComercialId_Crm is in the SELECT, but the percent stays partitioned at the FechaReporte/ZonaVenta grain, so the distribution sums to 100 percent within each FechaReporte + ZonaVenta combination across all the Clase values. Add or remove a column in the SELECT and the partition stays fixed at the columns you named in the measure. In my test on sample data this produced exactly that result (percentages summing to 100 within each fixed partition).

If instead you want the denominator to follow whatever the query groups on (a true "percent of the visible total"), use an empty window and the denominator recomputes at the query grain automatically:

expr: MEASURE(ContadorReporte_Crm) * 100.0
    / SUM(MEASURE(ContadorReporte_Crm)) OVER ()

TWO THINGS TO KEEP IN MIND

- Reference dimension names (not raw source columns) inside PARTITION BY. Using the underlying table column there returns a grouping error, while the dimension name works.
- Every dimension you list in PARTITION BY needs to be present in the query's GROUP BY. If you partition by FechaReporte_Crm and ZonaVenta_CRM, those two must be in the SELECT / GROUP BY of the query. Filters in the WHERE clause are applied before the window, so the percentages are computed over the filtered, visible rows.

DOCS

Composability (defining measures in terms of other measures with MEASURE()):
https://docs.databricks.com/aws/en/metric-views/data-modeling/composability

Metric view YAML reference:
https://docs.databricks.com/aws/en/metric-views/yaml-ref

Querying metric views (MEASURE(), GROUP BY ALL):
https://docs.databricks.com/aws/en/business-semantics/metric-views/query

This keeps everything in one metric view, defined once, with the query free to add or drop dimensions without you having to maintain a separate measure per partition shape. Hope that helps, and feel free to share your final YAML if you would like another set of eyes on it.

If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.

Re: Metric Views + Window Functions not supported

svetla87 — Mon, 22 Jun 2026 12:58:07 GMT

Hello @SteveOstrowski
Having a table cards_daily_snapshot with columns time_key, month_id, account_id, card_id, credit_limit, account_status. Need to create a metric for total credit limit, by getting the max value of it per account_id and snapshot date (time_key). It should achieve similar result as in the query with a metric view.

with cte as (
select time_key,month_id,account_id,max(credit_limit) as credit_limit
from demo_cat.demo.cards_daily_snapshot
where 1=1
and account_status='A'
group by 1,2,3
)
select month_id, sum(credit_limit) as credit_limit from cte group by 1 order by 1;

I'm trying with a window measure which gets the credit_limit on account level, and then another measure which aggregates it across the data set. Getting an error during querying the aggregating measure
select month_id,measure(credit_limit_agg) as lmt from demo_cat.demo.credit_card_metric_view
group by month_id;

[METRIC_VIEW_WINDOW_MEASURE_REFERENCES_WINDOW_MEASURE] Window measure `credit_limit_agg` cannot reference another window measure `account_credit_limit`. SQLSTATE: 0A000

My Yaml looks something like below:

measures:
- name: account_credit_limit
expr: MAX(credit_limit) FILTER(WHERE derived_account_status='A')
window:
- order: account_id
semiadditive: last
range: cumulative
- order: time_key
semiadditive: last
range: cumulative
display_name: Account Credit Limit
- name: credit_limit_agg
expr: sum(MEASURE(account_credit_limit)) over()
display_name: Total Credit Limit

Re: Metric Views + Window Functions not supported

balajij8 — Mon, 22 Jun 2026 15:56:53 GMT

Hi @svetla87

You can follow below

Create the metric view for MAX credit_limit per account/date (account_credit_limit).
Aggregate with SUM at query time using SQL CTE or subquery pattern

-- Get MAX credit per account per day from metric view WITH account_maxes AS ( SELECT month_id, time_key, account_id, MEASURE(account_credit_limit) as max_credit_limit FROM card_metric_view GROUP BY month_id, time_key, account_id ) -- SUM across accounts to get monthly total SELECT month_id, SUM(max_credit_limit) as total_credit_limit FROM account_maxes GROUP BY month_id ORDER BY month_id;

Re: Metric Views + Window Functions not supported

svetla87 — Tue, 23 Jun 2026 04:04:38 GMT

This definitely works, however I was trying to push all my KPIs to the semantic layer as this is a best practice recommendation. And on top of that once I manage to calculate total_credit_limit I have several more metrics such as utilization ratio, which are composite metrics calculated on top of other metric (averages, ratios, etc).

So I'm predominantly looking to have my full universe of KPIs defined as measures in the metric view.

Re: Metric Views + Window Functions not supported

balajij8 — Tue, 23 Jun 2026 05:11:21 GMT

The error occurs as nesting a window measure inside another window measure exceeds the functional boundaries

Re: Metric Views + Window Functions not supported

SteveOstrowski — Fri, 26 Jun 2026 04:46:05 GMT

Hi svetla87,

Good instinct to push the full KPI universe into the semantic layer. You don't need window functions for composite metrics like a utilization ratio. Metric views support composability: a measure can reference other measures with the MEASURE() function, so you can define your atomic measures once and then build ratios, conditional percentages, and growth rates on top of them. That keeps the whole KPI set living in the metric view as proper measures rather than as workarounds.

Here is the shape of it, where utilization_ratio is defined purely in terms of two other measures:

version: 1.1
measures:
  - name: total_credit_limit
    expr: SUM(credit_limit)

  - name: total_balance
    expr: SUM(balance)

  - name: utilization_ratio
    expr: MEASURE(total_balance) / MEASURE(total_credit_limit)
    format:
      type: percentage

A few things worth knowing as you build out the rest of the set. A measure can reference base columns, earlier-defined fields, or earlier-defined measures, so order your definitions so each composite metric comes after the measures it depends on. For genuinely time-based metrics (trailing averages, period-over-period, cumulative totals) you would reach for window measures instead, via the window: block with order, range, and offset. But for averages and ratios composed from other measures, plain composability is the recommended path and avoids the self-join cost that window measures carry.

Sources:

Advanced techniques for metric views (composability, ratios, window measures)
Model metric views (measure syntax and referencing other measures)
Unity Catalog metric views (overview)

Re: Metric Views + Window Functions not supported

SteveOstrowski — Fri, 26 Jun 2026 04:46:25 GMT

Thanks balajij8, that matches what I have been seeing. To close the loop for anyone landing on this thread: the METRIC_VIEW_WINDOW_FUNCTION_NOT_SUPPORTED error is expected behavior. Metric views do not accept raw SQL window functions (for example SUM(...) OVER (...)) directly in a measure's expr, and as balajij8 notes, you also cannot nest one window measure inside another, since that exceeds the boundaries of how window measures are evaluated.

The supported path is to express the running/trailing/period-over-period logic with the declarative window: construct on the measure rather than an OVER() clause. You keep a plain aggregate in expr and describe the frame separately:

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

range accepts current, cumulative, trailing N day/month, leading N day/month, or all, and offset (for example offset: -12 month) handles year-over-year style comparisons. For the case where you genuinely need a window applied on top of another window result, the workaround is to materialize the inner window measure first (a base metric view or a precomputed table/column) and then define the outer window measure against that, rather than nesting the two in one definition.

Sources: