Home › Data engineering › Metric views › 7 · Speed

Data engineering · BI and analytics · free guide · 7 · Speed

Speed: materialization, pre-aggregation and layout

A metric view stores no data. Every query is turned into SQL over the gold tables and run on the warehouse. So speed comes, in order, from the tables underneath, the warehouse, and only then from pre-computed answers. Reach for materialization last, not first.

SELECT region,MEASURE(margin_pct)... GROUP BY ALLUnity Catalogexpands the measureand the joinsplain SQL on gold:SUM(...)/SUM(...)fact JOIN dealerSQL warehouse(Photon) runs itas the usermaterialized view(if one matches)rewrite if it fitsNo match: it reads the gold tables live. Match: it reads the pre-computed answer.

Two roads: live SQL on gold, or a rewrite to a materialized answer.

In order

Six levers, cheapest first

LeverWhat it isWhen
1 Gold layoutLiquid clustering on the columns queries filter by (date, region); predictive optimization for OPTIMIZE and statistics; no small files.Always. Cheapest and helps every reader, not just metric views.
2 Keys and relyPrimary and foreign keys with RELY on gold; rely.at_most_one_match on joins that are truly many-to-one, so the optimizer can skip joins a query doesn't need.When joins are proven unique by a test.
3 Right warehouseServerless SQL warehouse sized for the dashboard's concurrency; the result cache answers repeated identical queries.Before any pre-aggregation.
4 Fewer, bigger questionsDashboards that ask one query per tile at a sensible grain, instead of pulling line-level rows into the tool.Always.
5 MaterializationAggregated materialized views for the hot slices; one unaggregated one for heavy joins. Built and refreshed by a managed pipeline.When the same slices are asked often, data is not secured by row filters, and steps 1–4 are not enough.
6 Own aggregate tablesA day × region aggregate built in the gold pipeline, with its own row filter, and a metric view over it.When secured data still needs speed.

Materialization

How materialization works

Add a materialization block and Databricks creates a managed Lakeflow pipeline that builds and refreshes materialized views (serverless compute must be on; runtime 17.3+). At query time the optimizer tries, in order, an exact match, a rollup match, the unaggregated match, and finally the live source tables.

materialization:
  schedule: every 1 hour              # or a CRON; no schedule = manual refresh only
  mode: relaxed                       # the only mode: rewrite checks fields, not freshness
  materialized_views:
    - name: day_region_segment
      type: aggregated
      dimensions: [invoice_date, region, segment]
      measures: [net_revenue, gross_margin, units]
      cluster_by:
        cols: [invoice_date, region]
    - name: month_region_dealers       # distinct counts need their own exact grain
      type: aggregated
      dimensions: [invoice_month, region]
      measures: [active_dealers]
    - name: baseline                   # the joined, filtered rows; at most one
      type: unaggregated
      refresh_policy: incremental      # fails at create time if incremental isn't possible
MatchWhen it happensWhich measures
Exact matchThe query's GROUP BY equals the materialization's dimensions, and its measures are a subset.Any measure, distinct counts included.
Rollup matchThe query asks for a coarser grain than was materialized (month from day).Only additive measures: SUM, COUNT, MIN, MAX. Not with one-to-many joins.
Unaggregated matchFalls back to the unaggregated materialization (the joined, filtered rows).Anything, but less saving.
No matchReads the source tables live.Always correct, slowest.

The catch

Relaxed mode and freshness

In relaxed mode the rewrite only checks that a materialization has the right fields and measures. It does not check freshness, session settings (time zone, ANSI mode) or determinism. So a query that matches reads the last refresh, while a query that doesn't match reads live data, and the two can disagree for a while. Schedule refreshes right after the gold load (or trigger them from it) so both roads give the same answer.

-- Did my query use a materialization? The leaf node names it if so.
EXPLAIN EXTENDED
SELECT invoice_month, region, MEASURE(net_revenue) FROM main.sales_gold.sales_mv GROUP BY ALL;

-- Refresh by hand (for example right after the nightly gold load)
REFRESH MATERIALIZED VIEW main.sales_gold.sales_mv;

Pre-aggregation

Pick the grain of each pre-aggregate

invoice linemillions of rowsday × dealer × productpre-aggregate Aday × region × segmentpre-aggregate Bmonth × regionwhat the dashboard showsroll up:SUM, COUNT, MIN, MAXworkCOUNT(DISTINCT dealer)cannot roll up:the same dealer sitsin many days

Pre-aggregate at the finest grain the hot dashboards need; coarser questions roll up from it.

SituationWhat to doVerdict
Daily sales by region and segment, shown on the CEO dashboard 500 times a day, no RLSAggregated materialization at day × region × segment; month queries roll upPursue
Distinct dealers by monthIts own aggregated entry at exactly month × region; it can't roll up from daysPursue
Materialize every dimension combination 'just in case'Pipeline cost and refresh time for slices nobody asksDrop
Secured sales data (row filter on the fact)Not allowed; use layout or an own aggregate table with the same filterUse the alternative
Parameterized metric viewCan't be materializedDrop the parameter or skip materialization
Data refreshed once a night, dashboards read by daySchedule the materialization right after the gold load so live and materialized answers agreePursue

Limits

What can't be materialized

  • Metric views with parameters.
  • Metric views whose view or source tables use row filters, column masks or ABAC policies; invoker functions like current_user() are refused too.
  • Group-owned metric views (materialized ones need a single owner, and the owner can't change after materialization exists).
  • TRIGGER ON UPDATE schedules.
  • Rollup across one-to-many joins (exact match only).

Cost: refreshes are billed as Lakeflow pipeline usage and inherit the custom tags of the SQL warehouse that ran the statement; tag warehouses so the cost lands on the right team. See the Databricks cost calculator.

← 6 · Row-level security · Next: 8 · Production →

FAQ

Questions people ask

Do metric views store data?

No. They store the definition. Each query is compiled into SQL on the source tables, unless a materialization matches, in which case it reads the pre-computed result.

How do I know if a query used a materialization?

Run EXPLAIN EXTENDED on the query: the leaf node names the materialization if one was used. The query profile also shows a metric view materialization match insight.

Can a COUNT(DISTINCT) measure use a materialization?

Only with an exact match: the query must group by exactly the materialization's dimensions. Distinct counts can't roll up from a finer pre-aggregate.

Sources

Checked against (11 Nov 2026)

Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.