Query metric views

You can query metric views like standard views from any SQL editor attached to a SQL warehouse or compute resource running a supported runtime. Metric views support flexible grouping and filtering, so you can analyze measures across any combination of dimensions at runtime without pre-computing every aggregation. The queries on this page demonstrate common query patterns.

Query measures and dimensions

All measure evaluations in a metric view query must use the MEASURE aggregate function. For complete details and syntax, see measure aggregate function.

note

To select all dimensions and measures from a metric view, explicitly list each dimension and wrap each measure with the MEASURE() aggregate function. Because measures require the MEASURE() function to evaluate properly, you must specify individual columns rather than using SELECT *.

Query with grouping

SQL
SELECT
  `Order Month`,
  `Order Status`,
  MEASURE(`Order Count`),
  MEASURE(`Total Revenue`)
FROM orders_metric_view
GROUP BY ALL
ORDER BY `Order Month`;

Query with filtering

SQL
SELECT
  o_orderpriority,
  MEASURE(`Total Revenue`),
  MEASURE(`Total Revenue per Customer`)
FROM orders_metric_view
WHERE `Order Status` = 'Fulfilled'
GROUP BY o_orderpriority;

Query with other tables

Metric views cannot be directly joined with other tables at query time. To join a metric view with another table, wrap the metric view query in a CTE, then join the CTE result:

SQL
WITH orders AS (
  SELECT
    o_custkey,
    MEASURE(`Order Count`) AS order_count,
    MEASURE(`Total Revenue`) AS total_revenue
  FROM orders_metric_view
  GROUP BY o_custkey
)
SELECT
  c.c_mktsegment,
  orders.order_count,
  orders.total_revenue
FROM orders
JOIN samples.tpch.customer c ON orders.o_custkey = c.c_custkey
ORDER BY c.c_mktsegment;

View metric view definition and metadata

The following query returns the full YAML definition for a metric view, including measures, dimensions, joins, and agent metadata. The AS JSON parameter is optional. For complete syntax details, see JSON formatted output.

SQL
DESCRIBE TABLE EXTENDED <catalog.schema.metric_view_name> AS JSON

The complete YAML definition is shown in the View Text field in the results. Each column contains a metadata field that holds agent metadata.

Consume metric views in tools

You can also use metric views throughout the Databricks workspace and in external tools:

Tool	Description
AI/BI dashboards	Use metric views to deliver accurate, explainable analytics. The `MEASURE()` function is applied automatically, and agent metadata such as synonyms, display names, and formatting is surfaced in the UI.
Genie	Use metric views in Genie spaces to enable accurate, explainable AI-powered analytics.
Alerts	Monitor metric view measures and trigger notifications when thresholds are crossed.
Query analysis	Use the query profile to troubleshoot and optimize metric view queries.
JDBC and ODBC	Access metric views through JDBC and ODBC connections. Metadata APIs expose the metric view structure to external BI and analytics tools.
Power BI	Query metric views directly from Power BI using BI compatibility mode.
Excel Add-in	Query metric views from Microsoft Excel using the Databricks Excel Add-in.
Google Sheets	Query metric views from Google Sheets using the Databricks connector.

Query measures and dimensions​

Query with grouping​

Query with filtering​

Query with other tables​

View metric view definition and metadata​

Consume metric views in tools​

Next steps​