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?

Physical tablestored dataa pipeline writes itreads are fastNormal viewa saved questionruns again on every readgrouping is fixedMaterialized viewa saved answerstored, refreshed latergrouping is fixedMetric viewa saved formulanothing stored by defaultgrouping chosen per questionTable and materialized view store a result. View and metric view store a definition.Only the metric view lets each question choose how to group and still gets ratios right.

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 tableNormal viewMaterialized viewMetric view
What it holdsThe rows themselvesA saved SELECTA saved SELECT plus its stored resultA saved list of measures, dimensions and joins (YAML)
When it computesWhen a pipeline writes itEvery time someone reads itAt refresh time; reads use the stored resultEvery time someone asks, for the grouping they ask for
Who chooses the groupingWhoever writes the queryThe author, when creating itThe author, when creating itThe person asking, with GROUP BY
Can a pipeline update or delete rowsYesNo, nothing is storedNo, the engine refreshes itNo, nothing is stored (unless you switch on materialization)
Read speedFastAs slow as the queryFastAs slow as the query, unless materialized
Costs while idleStorageNothingStorage, plus the refresh computeNothing

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:

  1. the sales dashboard
  2. the finance report
  3. the weekly email
  4. a notebook for the data team
  5. the Power BI model
  6. a Genie space
  7. an AI agent's queries
  8. an alert on a drop
  9. a customer-facing report
  10. the board pack extract
Before: the formula is copiedAfter: written onceSales dashboardSUM(orders)/SUM(visits)shows 8.0%Finance reportAVG(orders/visits)shows 10.0%Agent queryforgot other channelsshows 6.3%Metric viewconversion_rate =SUM(orders) / SUM(visits)Sales dashboardshows 8.0%Finance reportshows 8.0%Agent queryshows 8.0%...and 7 more copies elsewhere: 10 places to keep in stepTen copies drift and give several numbers. One definition gives one.

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:

ChangeFormula in ten placesMetric 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 ownOne edit, and the old name can stay as a synonym
"Which formula did the board see last month?"Search ten placesRead 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.

NeedWhy a metric view is not the answerUse
A pipeline updates, deletes or merges rows (for example slowly changing history)It stores no rowsPhysical table
Heavy cleaning, deduplication or joins of raw dataIt defines numbers, not transformationsTables in the pipeline (bronze, silver, gold)
One fixed, expensive question read by many people, such as a daily executive summaryIt would recompute it on every readMaterialized view
A metric view is correct but too slow on big dataCorrect is not fastMetric view with materialization, or a pre-aggregated gold table
Row filters and column masks that must always applyPut security on the gold tables, so every route is coveredTables with row filters and masks
Shaping one table for one reader (rename columns, hide a few)OverkillNormal 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.

CaseExampleWhat happensWhat to do
Classic computeThe pipeline runs on a classic clusterFull recompute each time. Incremental refresh needs serverless.Use serverless pipelines
LIMIT in the querySELECT product, SUM(revenue) r FROM sales GROUP BY product ORDER BY r DESC LIMIT 10The limit operators cannot be incrementalised, so a "top 10" view is recomputed wholeStore the full aggregate as the view and take the top 10 when reading
Recursive CTEA parent-child hierarchy walked with WITH RECURSIVENot eligible, so full recomputeFlatten the hierarchy in a table first
Expectations in the definitionCONSTRAINT valid_amount EXPECT (amount > 0) on the viewOne 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 tableReading files in a volume, an external location, a foreign catalog, or a foreign Iceberg tableNot supported for incremental refreshLand the data into a Delta table first
Floating-point sumsSUM(price) where price is DOUBLEFloating-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 changedA nightly job rewrites 80% of the source tableEven when eligible, a cost check can decide a full recompute is cheaperNothing to fix: this is the right choice
A function you wrote changedYou edit a Python UDF used in the viewOld rows keep the old logic until a full refreshRun a full refresh yourself after changing it
Window function without a partitionROW_NUMBER() OVER (ORDER BY day)The docs say window functions need PARTITION BY columns to refresh incrementallyAdd 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 INCREMENTAL and INCREMENTAL 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

Does a pipeline need to write,update or delete this data?yesPhysical tablenoDo people choose their own grouping,or do many tools need one formula?yesMetric view (on gold tables)noOne fixed question, read oftenand slow to compute?yesMaterialized viewnoNormal view

Follow the questions from the top. The first yes wins.

Scenarios

Common situations, with a verdict

SituationUseVerdictWhy
Silver table updated by a nightly mergePhysical tablePursueNeeds writes and deletes
One dashboard, one fixed grouping, small dataNormal viewPursueSimplest thing that works
One dashboard, one fixed grouping, slow query read all dayMaterialized viewPursueCompute once, read many times
Ratio or distinct count that people slice many waysMetric viewPursueStays correct under any grouping
Same KPI in dashboard, Power BI, Genie, alerts and agentsMetric viewPursueOne definition instead of ten copies
Header-and-line data where totals must not doubleMetric view with one_to_manyPursueStops fan-out
A metric view to replace the gold tablesMetric view onlyDropDirty or non-atomic data gives wrong numbers faster
A metric view for a number only one person uses onceMetric viewDropA normal query is less work
A materialized view for a number people regroupMaterialized viewDropIts stored grouping cannot be re-summed correctly
A normal view per grouping: by region, by month, by region and month, and so onMany viewsDropViews multiply; a metric view needs one
Security rules only inside a metric view's filterMetric viewDropThat is scope, not security; put row filters on the tables

Put together

How the four fit as layers

  1. Tables at the bottom: clean, atomic gold tables written by pipelines, with row filters and masks.
  2. Materialized views (optional) beside them for fixed, expensive summaries.
  3. Metric views on top: the one place each business number is defined, over the gold tables and joins.
  4. 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

All data engineering · Databricks on this site