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.
Two roads: live SQL on gold, or a rewrite to a materialized answer.
In order
Six levers, cheapest first
| Lever | What it is | When |
|---|---|---|
| 1 Gold layout | Liquid 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 rely | Primary 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 warehouse | Serverless SQL warehouse sized for the dashboard's concurrency; the result cache answers repeated identical queries. | Before any pre-aggregation. |
| 4 Fewer, bigger questions | Dashboards that ask one query per tile at a sensible grain, instead of pulling line-level rows into the tool. | Always. |
| 5 Materialization | Aggregated 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 tables | A 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| Match | When it happens | Which measures |
|---|---|---|
| Exact match | The query's GROUP BY equals the materialization's dimensions, and its measures are a subset. | Any measure, distinct counts included. |
| Rollup match | The 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 match | Falls back to the unaggregated materialization (the joined, filtered rows). | Anything, but less saving. |
| No match | Reads 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
Pre-aggregate at the finest grain the hot dashboards need; coarser questions roll up from it.
| Situation | What to do | Verdict |
|---|---|---|
| Daily sales by region and segment, shown on the CEO dashboard 500 times a day, no RLS | Aggregated materialization at day × region × segment; month queries roll up | Pursue |
| Distinct dealers by month | Its own aggregated entry at exactly month × region; it can't roll up from days | Pursue |
| Materialize every dimension combination 'just in case' | Pipeline cost and refresh time for slices nobody asks | Drop |
| Secured sales data (row filter on the fact) | Not allowed; use layout or an own aggregate table with the same filter | Use the alternative |
| Parameterized metric view | Can't be materialized | Drop the parameter or skip materialization |
| Data refreshed once a night, dashboards read by day | Schedule the materialization right after the gold load so live and materialized answers agree | Pursue |
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)
- Databricks: Unity Catalog metric views and the YAML reference
- Materialization · Advanced techniques · Relationships · BI tools · Feature availability
- Row filters and column masks · ABAC evaluation through views (KB)
- dbt semantic models · MetricFlow open-sourced · Apache Ossie
Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.