Unity Catalog Metric Views: Governed Metrics and Materialization

1.Why metric views: define once, group by anything

A standard view fixes its aggregations and groupings when you create it. To see revenue by month and by region, you need two views, and each repeats the revenue formula. A Unity Catalog metric view keeps the measure definitions separate from the fields (dimensions) used to group and filter them. You define a metric such as revenue per distinct customer once. Users can then group it by any available field, and the query engine generates the correct computation at runtime.

This is the governance benefit. Every KPI has one registered definition in Unity Catalog, so everyone in the organisation reports the same value for the same metric.

A source can be any table-like Unity Catalog asset, including tables, views, materialized views, streaming tables and other metric views, or it can be a SQL query. A gold star schema therefore plugs in directly. Use the fact table as the source and the dimensions as joins. If the source is a SQL query containing a JOIN, declare primary and foreign key constraints with the RELY option for the best performance.

Sources12

2.Fields, measures and the MEASURE() function

Fields behave like ordinary columns: you can use them in SELECT, WHERE and GROUP BY. A field can be a categorical column such as region, or an unaggregated numeric column such as price. Measures must be aggregate expressions, and they have no fixed level of aggregation; the grouping in each query decides it. A measure can combine aggregations, as in a ratio, or apply a FILTER (WHERE ...) clause.

measures:
  # Simple count measure
  - name: Order Count
    expr: COUNT(1)

  # Sum aggregation measure
  - name: Total Revenue
    expr: SUM(o_totalprice)

  # Distinct count measure
  - name: Unique Customers
    expr: COUNT(DISTINCT o_custkey)

  # Calculated measure combining multiple aggregations
  - name: Average Order Value
    expr: SUM(o_totalprice) / COUNT(DISTINCT o_orderkey)

You can define a metric view in Catalog Explorer, with built-in YAML validation, or in SQL by wrapping the YAML in a CREATE VIEW statement that has a WITH METRICS clause and placing the YAML between $$ delimiters.

CREATE OR REPLACE VIEW catalog.schema.orders_materialized WITH  ?  LANGUAGE YAML AS

No. Every measure in a query must be wrapped in the MEASURE() aggregate function, so you list fields and measures explicitly instead of using SELECT *. You also can't join a metric view directly to other tables at query time. To combine it with another table, wrap the metric view query in a CTE and join the CTE result.

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

Sources234

3.Materializing a metric view: aggregated vs unaggregated

Metric views compute at runtime, but you can still pre-compute them. Add a top-level materialization block to the YAML, and Databricks creates a managed Lakeflow pipeline that builds and maintains the materialized views on the schedule you set. You keep querying the metric view itself. The optimizer uses aggregate-aware query rewriting: it takes the fast path and reads a suitable materialization when one exists, and otherwise falls back to the source data. The metric view remains the single source of truth for what the metric means. Materialization only changes how it is stored. It requires serverless compute for the pipelines and Databricks Runtime 17.3 or above.

materialization:
  schedule: every 6 hours
  mode: relaxed

  materialized_views:
    - name: baseline
      type: unaggregated

    - name: revenue_breakdown
      type: aggregated
      dimensions:
        - category
        - color
      measures:
        - total_revenue
      cluster_by:
        cols:
          - category
          - color

To get good aggregated coverage, materialize at the finest grain your queries need, and include the columns that users filter on. A materialization at (region, sku, event_day) can serve GROUP BY region, GROUP BY region, event_month, and GROUP BY sku with WHERE region = 'US'. Don't go so fine that most groups contain a single row, such as millisecond timestamps, which only adds storage. Non-additive measures like COUNT(DISTINCT), MEDIAN and percentiles can't be rebuilt from partial results, so they need an exact match. If both types exist, Databricks computes the aggregated materializations from the unaggregated one, which gives a consistent snapshot.

Sources5

4.Relaxed mode, freshness, and what blocks materialization

The materialization mode is always relaxed. In relaxed mode, query rewrite checks only whether a candidate materialization contains the fields and measures the query needs. It does not check freshness, SQL settings such as TIMEZONE or ANSI_MODE, or determinism. So two queries against the same metric view can see different data: a query that matches a materialization reads its last refresh, while a query that doesn't match reads live source data. To limit the gap, schedule materialization refreshes to run after your source pipeline completes, or use an unaggregated materialization so that every query reads the same snapshot.

The same reasoning rules out invoker-dependent expressions, whose results depend on who runs the query, such as current_user() or is_member(). Databricks validates this when you create, alter or refresh a materialization. These operations fail with METRIC_VIEW_MATERIALIZATION_WITH_INVOKER_DEPENDENT_EXPRESSIONS_NOT_SUPPORTED.

Sources5

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.One aggregated materialization at a coarse grain can serve any grouping of its measures through rollup.Why is that wrong?

   An aggregation serves only queries on exactly its dimensions or on a subset of them, and non-additive measures need an exact match. Databricks recommends several aggregated materializations for different query shapes.

   Covered in Materializing a metric view: aggregated vs unaggregated

2. 2.Query rewrite to a materialization guarantees the result is as fresh as the source data.Why is that wrong?

   Relaxed mode checks only that the fields and measures are present. It skips the freshness check, so matched queries return the last refresh while unmatched queries return live data.

   Covered in Relaxed mode, freshness, and what blocks materialization

3. 3.You can materialize a metric view over tables protected by row filters or column masks, and the security policies will still apply per user.Why is that wrong?

   A materialization is computed once under the owner's identity, so it would bypass per-user controls. Materialization is therefore blocked when RLS, column masks or ABAC policies are present.

   Covered in Relaxed mode, freshness, and what blocks materialization