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.
