Home › Learn › Table, view, materialized view, metric view
Data modelling · Databricks
Table, view, materialized view or metric view: which one, and why
A plain guide to the four ways to keep a business number on Databricks: a physical table, a normal view, a materialized view and a metric view. Worked examples for the grain problem, the same code copied into many places, fan-out joins, filtered variants, speed and security, with a decision chart and a verdict for each scenario.
Written from the author's knowledge of Databricks docs, not re-checked live. Example numbers were computed by running the SQL on a small local engine with the tiny tables shown. Check the "Not confirmed" list at the bottom before you design.
The short answer
Four ways to keep a business number
A business number such as "conversion rate" or "net revenue" has to live somewhere. You have four choices, and they differ on two questions: is the result stored, or computed when asked? and is the grouping fixed, or chosen by the person asking?
Two of these store a result. Two store only a definition.
The one-line rule. Use tables for data that pipelines write. Use views and materialized views for one fixed question. Use a metric view when the same number must be right under many groupings and in many places. Without one, the formula ends up written in ten places.
What each one is
The four objects in plain words
| Physical table | Normal view | Materialized view | Metric view | |
|---|---|---|---|---|
| What it holds | The rows themselves | A saved SELECT | A saved SELECT plus its stored result | A saved list of measures, dimensions and joins (YAML) |
| When it computes | When a pipeline writes it | Every time someone reads it | At refresh time; reads use the stored result | Every time someone asks, for the grouping they ask for |
| Who chooses the grouping | Whoever writes the query | The author, when creating it | The author, when creating it | The person asking, with GROUP BY |
| Can a pipeline update or delete rows | Yes | No, nothing is stored | No, the engine refreshes it | No, nothing is stored (unless you switch on materialization) |
| Read speed | Fast | As slow as the query | Fast | As slow as the query, unless materialized |
| Costs while idle | Storage | Nothing | Storage, plus the refresh compute | Nothing |
Problem 1
The grain problem, with real numbers
Grain means what one row stands for and what you group by. Take a tiny table of daily results:
daily_facts day region channel visits orders 2026-01-01 North web 1000 50 2026-01-01 South web 100 20 2026-01-02 North app 1000 100 2026-01-02 South web 100 5
Conversion rate means orders divided by visits. Compute it two ways for the whole table:
SUM(orders) / SUM(visits) = 175 / 2200 = 8.0% (right) AVG(orders / visits) = 10.0% (wrong)
The second one gives each row the same weight, so the 100-visit South rows count as much as the 1,000-visit North rows. Now suppose you saved a normal view that groups by region:
CREATE VIEW conv_by_region AS SELECT region, SUM(orders) / SUM(visits) AS conversion_rate FROM daily_facts GROUP BY region; -- North 7.5% South 12.5%
Someone now wants the overall figure, or the figure by day. The view has already thrown away the orders and visits, so the only thing left to do is average the two rates: (7.5% + 12.5%) / 2 = 10.0%, the wrong number again. By day the right answers are 6.4% and 9.5%, and the view cannot produce them at all.
A metric view stores the formula and not the result:
measures:
- name: conversion_rate
expr: SUM(orders) / SUM(visits)
SELECT day, MEASURE(conversion_rate) FROM daily_metrics GROUP BY ALL; -- 6.4%, 9.5%
SELECT MEASURE(conversion_rate) FROM daily_metrics; -- 8.0%
Same formula, any grouping, right every time. A materialized view has the same weakness as the normal view, because what it stores was grouped when it was built.
Problem 2
Without a metric view, the SQL lives in ten places
The grain problem is the sharpest case of a wider one: repeated code. A normal view or table can hold the formula once, but only for one grouping. Everyone who needs another grouping copies the formula into their own query. Count the places in a normal company:
- the sales dashboard
- the finance report
- the weekly email
- a notebook for the data team
- the Power BI model
- a Genie space
- an AI agent's queries
- an alert on a drop
- a customer-facing report
- the board pack extract
The same formula written in three places drifts. Written once, it cannot.
Ten copies do not stay equal. One author forgets to remove cancelled orders. One averages instead of summing. One filters to a single channel. Each query looks reasonable on its own, and the meeting starts with "which number is right?" The cost shows up when the rule changes:
| Change | Formula in ten places | Metric view |
|---|---|---|
| "Exclude test orders from now on" | Find and edit ten queries. Miss one, and it quietly disagrees. | Edit one line. Every dashboard, tool and agent picks it up. |
| "Rename the measure" | Ten edits, some of them in tools you do not own | One edit, and the old name can stay as a synonym |
| "Which formula did the board see last month?" | Search ten places | Read one file in git |
Problem 3
Fan-out: a join that quietly repeats rows
orders_h (headers) lines
order_id freight order_id line_id amt
1 100 1 1 50
2 40 1 2 30
1 3 20
2 1 70
Total freight is 100 + 40 = 140. Join headers to lines and sum freight, and order 1 appears three times:
SELECT SUM(o.freight) FROM orders_h o JOIN lines l USING (order_id); -- 340 (wrong)
A normal view that includes this join builds the mistake in. Every dashboard on it inherits 340. In a metric view the join is declared once, with its cardinality (one_to_many), and the engine keeps each measure at its own grain, so freight stays 140 while line amounts still sum by line. More on this: grain and fan-out.
Problem 4
Many flavours of the same number
Teams ask for "revenue", "revenue from web only", "revenue last 7 days" and "revenue from premium customers". In SQL each one is a copy of the query with an extra WHERE or CASE. On our tiny table, web revenue is North 5,000 and South 2,400. As views that means four views to keep in step; as tables, four tables to refresh. In a metric view they are four named measures on one source:
measures:
- name: revenue
expr: SUM(revenue)
- name: web_revenue
expr: SUM(revenue) FILTER (WHERE channel = 'web')
Each one can then be cut by region, day or anything else the fields allow.
Where table and materialized view win
Speed, writes and security
A metric view is not a faster or cheaper way to hold data. It stores nothing by default, so a query still scans the gold tables beneath it. That is where the other three earn their place.
| Need | Why a metric view is not the answer | Use |
|---|---|---|
| A pipeline updates, deletes or merges rows (for example slowly changing history) | It stores no rows | Physical table |
| Heavy cleaning, deduplication or joins of raw data | It defines numbers, not transformations | Tables in the pipeline (bronze, silver, gold) |
| One fixed, expensive question read by many people, such as a daily executive summary | It would recompute it on every read | Materialized view |
| A metric view is correct but too slow on big data | Correct is not fast | Metric view with materialization, or a pre-aggregated gold table |
| Row filters and column masks that must always apply | Put security on the gold tables, so every route is covered | Tables with row filters and masks |
| Shaping one table for one reader (rename columns, hide a few) | Overkill | Normal view |
See speed: materialization and pre-aggregation and row-level security.
When a materialized view cannot refresh incrementally
Examples where it recomputes everything
An incremental refresh reads only the source rows that changed. A full recompute reads everything again. Databricks tries the first and falls back to the second, and you pay for the difference. These are the cases the docs list, each with an example. Checked against the Databricks docs on 21 November 2026; the docs change often, so treat the list as a guide. The full story, with refresh types, cost and how to check, is on its own page: Databricks materialized views.
| Case | Example | What happens | What to do |
|---|---|---|---|
| Classic compute | The pipeline runs on a classic cluster | Full recompute each time. Incremental refresh needs serverless. | Use serverless pipelines |
| LIMIT in the query | SELECT product, SUM(revenue) r FROM sales GROUP BY product ORDER BY r DESC LIMIT 10 | The limit operators cannot be incrementalised, so a "top 10" view is recomputed whole | Store the full aggregate as the view and take the top 10 when reading |
| Recursive CTE | A parent-child hierarchy walked with WITH RECURSIVE | Not eligible, so full recompute | Flatten the hierarchy in a table first |
| Expectations in the definition | CONSTRAINT valid_amount EXPECT (amount > 0) on the view | One docs page says fully refreshed on each update; another says incremental is supported except in two cases. Test it. | Put the quality checks on the table feeding it, to be safe |
| Source is not a Delta table | Reading files in a volume, an external location, a foreign catalog, or a foreign Iceberg table | Not supported for incremental refresh | Land the data into a Delta table first |
| Floating-point sums | SUM(price) where price is DOUBLE | Floating-point addition can give slightly different totals depending on order, so the engine may not trust it (the docs say to cast to DECIMAL) | Cast to DECIMAL |
| Most of the source changed | A nightly job rewrites 80% of the source table | Even when eligible, a cost check can decide a full recompute is cheaper | Nothing to fix: this is the right choice |
| A function you wrote changed | You edit a Python UDF used in the view | Old rows keep the old logic until a full refresh | Run a full refresh yourself after changing it |
| Window function without a partition | ROW_NUMBER() OVER (ORDER BY day) | The docs say window functions need PARTITION BY columns to refresh incrementally | Add PARTITION BY, for example by region |
What is supported
The docs list GROUP BY, non-recursive CTEs, UNION ALL, inner, left, right and full joins, and QUALIFY as supported. Some refresh types also need row tracking on the source tables.
How to stop a silent full recompute
- See which method will be used:
EXPLAIN CREATE MATERIALIZED VIEW ...reports whether the query can refresh incrementally. - Refuse to fall back: the refresh policy can be set so the statement fails when an incremental refresh is not possible. The docs name
INCREMENTALandINCREMENTAL STRICT. Check the current syntax before using them.
Why this is on a page about metric views. Metric view materialization is built on materialized views, so a rollup behind a metric view can fall into the same traps. A fast read is not proof of a cheap refresh. Check both.
Decision chart
Which one, step by step
Follow the questions from the top. The first yes wins.
Scenarios
Common situations, with a verdict
| Situation | Use | Verdict | Why |
|---|---|---|---|
| Silver table updated by a nightly merge | Physical table | Pursue | Needs writes and deletes |
| One dashboard, one fixed grouping, small data | Normal view | Pursue | Simplest thing that works |
| One dashboard, one fixed grouping, slow query read all day | Materialized view | Pursue | Compute once, read many times |
| Ratio or distinct count that people slice many ways | Metric view | Pursue | Stays correct under any grouping |
| Same KPI in dashboard, Power BI, Genie, alerts and agents | Metric view | Pursue | One definition instead of ten copies |
| Header-and-line data where totals must not double | Metric view with one_to_many | Pursue | Stops fan-out |
| A metric view to replace the gold tables | Metric view only | Drop | Dirty or non-atomic data gives wrong numbers faster |
| A metric view for a number only one person uses once | Metric view | Drop | A normal query is less work |
| A materialized view for a number people regroup | Materialized view | Drop | Its stored grouping cannot be re-summed correctly |
| A normal view per grouping: by region, by month, by region and month, and so on | Many views | Drop | Views multiply; a metric view needs one |
Security rules only inside a metric view's filter | Metric view | Drop | That is scope, not security; put row filters on the tables |
Put together
How the four fit as layers
- Tables at the bottom: clean, atomic gold tables written by pipelines, with row filters and masks.
- Materialized views (optional) beside them for fixed, expensive summaries.
- Metric views on top: the one place each business number is defined, over the gold tables and joins.
- Everything else (dashboards, Power BI, Genie, agents, alerts) reads the metric view, so none of them writes the formula again.
Normal views fill the gaps: a renamed or trimmed copy of one table for one reader.
Honest limits
What to check before you build
Not confirmed from this session: current YAML keys and minimum runtime for metric views; whether your BI tool reads metric views natively or needs custom SQL; the cost of a materialized view refresh on your compute (the list of non-incremental cases above was checked in the docs, but it changes); the exact materialization options for metric views. The earlier page Limits of each lists the limits as of its own check date. The sample tables and numbers above are small and made up to show the idea, not benchmarks.
FAQ
Questions people ask
Can a normal view do what a metric view does?
Only for the grouping you wrote into it. A normal view fixes its GROUP BY when you create it, so a different grouping needs another view or a query on the base table. A metric view keeps the formula and lets each question choose the grouping.
Is a materialized view the same as a metric view?
No. A materialized view stores the result of one query so it reads fast. A metric view stores no data (unless you switch on materialization); it stores the definition of measures and dimensions so any grouping is computed correctly.
Should I replace my gold tables with metric views?
No. Metric views sit on top of clean gold tables. The tables hold the data; the metric view holds the meaning.
When is a plain table the right answer?
When the data must be written, updated or deleted by a pipeline, when many readers need it fast, or when you need row filters and column masks applied directly to the data.
Next
Where to go next
Related on this site: Metric views, taught from zero · Grain, fan-out and kinds of measure · Speed: materialization and pre-aggregation · Row-level security with metric views · Medallion architecture · Declarative pipelines