Home › Learn › Materialized views
Databricks · tables and refresh
Databricks materialized views: refresh types, cost, limits and how to avoid full refresh
A complete guide to Databricks materialized views: where a refresh runs, incremental refresh vs full recompute, the refresh policies, what forces a full recompute, what it costs, tips, and how to find out which refresh really happened with EXPLAIN, the event log and system tables.
Read from the Databricks documentation on 21 November 2026 (AWS pages; the Azure pages match except for wording). Features change often, so check the "Not confirmed" list at the bottom. SQL marked "sketch" has not been run.
What it is
A saved answer that the engine keeps up to date
A materialized view (MV) is a query whose result is stored like a table. Readers query it fast, like any table, and Databricks refreshes it when you ask, on a schedule, or when the sources change. A normal view recomputes on every read. An MV pays once at refresh time instead.
| Standalone MV | Pipeline-defined MV | |
|---|---|---|
| Created with | CREATE MATERIALIZED VIEW in a SQL warehouse or notebook | The same keyword inside a Lakeflow pipeline's source code |
| Who runs the refresh | A backing pipeline Databricks creates for you (shown as type MV/ST under Jobs & Pipelines) | Your pipeline |
| Schedule | SCHEDULE or TRIGGER ON UPDATE in the statement | The pipeline's own schedule or trigger |
| Good for | One summary table owned by one team | A chain of bronze, silver and gold steps that refresh together |
The result sits in cloud storage and Unity Catalog holds its metadata and query. A few helper tables for incremental refresh live in an internal catalog.
Where it runs
Which compute does the refresh
The statement starts a serverless pipeline, and the pipeline does the work and the billing.
| Question | Answer from the docs |
|---|---|
| Which warehouse can create a standalone MV? | A Pro or Serverless SQL warehouse, with Unity Catalog. A classic warehouse works for streaming tables but not for MVs. |
| Which compute does the refresh run on? | Serverless. A refresh is run by a serverless pipeline, whether the MV is standalone or defined in a Lakeflow pipeline. For a standalone MV, your workspace does not need serverless pipelines turned on; one is used automatically. |
| Can it run on classic compute? | A pipeline-defined MV can be set up on classic compute, but then every update is a full recompute. Incremental refresh needs serverless. |
| Can I use a notebook? | A notebook on serverless compute, Databricks Runtime 18.1 or later, can create and refresh MVs, with limits: no per-table cost attribution, no async refresh, and a table must be refreshed on the compute type that created it. |
| Other requirements | Unity Catalog, a region that supports serverless SQL warehouses, and the serverless terms accepted. |
Refresh types
Incremental refresh, full recompute and the rest
The two kinds of update
| Update | What it does | How you start it |
|---|---|---|
| Refresh (the default) | Tries to read only what changed; falls back to a full recompute if it has to | REFRESH MATERIALIZED VIEW name, a schedule, a trigger, or a pipeline update |
| Full refresh | Always recalculates everything and resets all checkpoints | REFRESH MATERIALIZED VIEW name FULL, or a pipeline update with full refresh chosen |
Both give the same data. The difference is cost and method. Full refresh is also how you pick up a changed UDF library (see s5).
The techniques, as named in the event log
| Technique name | Meaning |
|---|---|
ROW_BASED, PARTITION_OVERWRITE, WINDOW_FUNCTION, APPEND_ONLY, GROUP_AGGREGATE, GENERIC_AGGREGATE | Incremental: only changed data was processed, by this method |
FULL_RECOMPUTE | Everything was recomputed |
NO_OP | Nothing in the sources changed, so nothing ran |
The docs list these names but do not say how each incremental technique works inside. The UI shows the same result as Incremental, Full recompute or No change.
How one refresh decides
One refresh, left to right. Red boxes are the full recomputes.
The refresh policy: who decides
| Policy | Behaviour | Use when |
|---|---|---|
AUTO (default) | The cost model picks the cheaper of incremental and full | Most cases |
INCREMENTAL | Incremental whenever possible; falls back to full if the plan no longer allows it. Create fails if the query could never be incremental. | You want incremental but can live with an occasional fallback |
INCREMENTAL STRICT | Incremental or fail. The refresh stops with an error instead of recomputing everything. | A surprise full recompute is not acceptable |
FULL | Never incremental, even when it could be | Small data, or a query you keep changing |
CREATE MATERIALIZED VIEW sales_summary
REFRESH POLICY INCREMENTAL STRICT
AS SELECT region, SUM(CAST(revenue AS DECIMAL(18,2))) AS total_revenue, COUNT(*) AS orders
FROM main.sales.orders GROUP BY region;Choosing
When to use which refresh setting
| Situation | Choose | Verdict | Why |
|---|---|---|---|
| Big source, small daily change, query is eligible | AUTO or INCREMENTAL on serverless | Pursue | You pay for the change, not the whole table |
| A full recompute would be a billing surprise | INCREMENTAL STRICT | Pursue | It fails loudly so you can fix the query |
| Small table, refreshed rarely | FULL or AUTO | Pursue | Incremental bookkeeping saves nothing |
| Source rewritten most nights | AUTO | Pursue | The cost model will pick full, which is cheaper then |
| Source has no history (Kafka, files you delete) | A streaming table, not an MV | Drop the MV | A full refresh would lose data you cannot read again |
| Pipeline is classic compute | MV | Drop | Every update is a full recompute |
| You need results in milliseconds or per-event latency | A streaming table or a normal table | Drop | MV updates take seconds or minutes |
Limitations
What stops incremental refresh, and other limits
What forces a full recompute
| Cause | Example | Fix |
|---|---|---|
| Not serverless | Classic pipeline compute | Use serverless |
LIMIT | ORDER BY total DESC LIMIT 100 | Store everything; apply the limit when reading |
| Recursive CTE | WITH RECURSIVE | Flatten the hierarchy into a table first |
| Non-deterministic function | RAND(), or CURRENT_DATE() in SELECT | Only current_date(), current_timestamp() and now() in a WHERE are allowed |
| Floating-point sums | SUM(price) on DOUBLE | SUM(CAST(price AS DECIMAL(18,2))) |
Window without PARTITION BY | ROW_NUMBER() OVER (ORDER BY day) | Add PARTITION BY |
| Source is not Delta or managed Iceberg | A volume, external location, foreign catalog, or foreign Iceberg table | Land it into a Delta table first |
| Row tracking is off on a source | A JOIN or UNION ALL over a table without it | Turn row tracking on for the source |
| UDF not marked deterministic, or its behaviour changed | Python UDF in the query | Mark it deterministic; after a library upgrade, run a full refresh yourself |
| Complex shape above the aggregate | A GROUP BY with calculations stacked on top | Put the aggregate last; do the extra maths in a view on top |
| Cost model says full is cheaper | Most of the source changed; deeply nested query | Nothing to fix. Or force with INCREMENTAL if you disagree |
What is supported for incremental refresh
SELECT with deterministic built-in functions and immutable UDFs, GROUP BY, non-recursive WITH, UNION ALL, inner, left, right and full outer joins, WHERE and HAVING, window functions with PARTITION BY, and QUALIFY. Sources can be Delta tables, managed Iceberg tables, other MVs and streaming tables.
Other limits of MVs
- Not low-latency. Updates take seconds or minutes.
- No CLONE. An MV cannot be the source or target of a clone.
- No OPTIMIZE or VACUUM by you; maintenance is automatic. No identity or surrogate-key columns. No renaming or changing the owner.
- Aliases required on every expression (
SUM(x) AS total, not bareSUM(x)). - Constraints are informational. Primary and foreign keys are not enforced, though the optimizer can use them (declare with
RELYonly if they are true). - A
SUMover a column that becomes all NULL returns 0, not NULL. - Expectations: the two doc pages disagree on whether an MV with expectations can refresh incrementally. Test with
EXPLAIN.
Cost
What a refresh costs
- What you are billed: the refresh runs "on serverless compute, billed as serverless Lakeflow pipelines DBUs", on a pipeline separate from your warehouse. Cost scales with the volume of data processed, not the warehouse size. You may see serverless charges even when the warehouse you used is a dedicated one.
- Price per DBU: the docs I read give no rate for this, so no dollar rate is quoted here. Look up the current Lakeflow pipelines serverless rate for your cloud and plan.
- Why incremental matters (illustration, not a bill): take a 1 TB source with 1% new data a day. An incremental refresh reads about 10 GB. A full recompute reads 1 TB, about 100 times more. If the work is roughly proportional to the data read, the full recompute costs on the order of 100 times more each day.
- Storage: the result is stored data, so there is a storage footprint. The docs I read do not say how it is billed.
See what an MV actually cost
-- DBUs per refresh pipeline of one MV (from the Databricks docs; fill in your names)
WITH p AS (
SELECT usage_metadata.dlt_pipeline_id AS pipeline_id
FROM system.billing.usage
WHERE usage_metadata.uc_table_catalog = 'main'
AND usage_metadata.uc_table_schema = 'sales'
AND usage_metadata.uc_table_name = 'sales_summary'
LIMIT 1)
SELECT u.sku_name, u.usage_date, SUM(u.usage_quantity) AS dbus
FROM system.billing.usage u JOIN p
ON u.usage_metadata.dlt_pipeline_id = p.pipeline_id
GROUP BY ALL ORDER BY usage_date;
Tags on the warehouse that created an MV are copied onto its usage rows (custom_tags), so a cost split by team can work. See dividing the bill.
Avoid full refresh
A checklist to keep refreshes incremental
| Do | Instead of |
|---|---|
| Run the MV on serverless | Classic compute |
ALTER TABLE src SET TBLPROPERTIES (delta.enableRowTracking = true, delta.enableDeletionVectors = true) on every source | Leaving row tracking off |
| Keep sources as Delta tables (or managed Iceberg) | Reading files, volumes or foreign catalogs directly |
Cast floats to DECIMAL before summing | SUM and AVG on DOUBLE |
Add PARTITION BY to every window function | Global ORDER BY windows |
Store the full aggregate, apply LIMIT in the reading query | LIMIT inside the MV |
Keep current_date() only in WHERE | Non-deterministic functions in SELECT |
| Mark UDFs deterministic, or use built-in functions | Opaque Python UDFs |
| Put the aggregate at the top of the query; build extra layers as a view on the MV | Stacking maths on top of GROUP BY |
| Put expectations on the source table | Expectations inside the MV (if your test shows it blocks incremental) |
Use INCREMENTAL STRICT on MVs that must stay cheap | Hoping AUTO keeps choosing incremental |
Test with EXPLAIN CREATE MATERIALIZED VIEW before deploy | Finding out from the bill |
How to know
Six ways to see whether a refresh was full
- Before you build:
EXPLAIN. RunEXPLAIN CREATE MATERIALIZED VIEW ...with your whole statement (Databricks Runtime 17.3 or later). It printsThe Materialized View can be incrementally refreshed.orcannot, then a list of blockers with codes such asOPERATOR_NOT_INCREMENTALIZABLEorINPUT_NOT_IN_DELTA. It checks the shape of the query only, not what the cost model will do later. - After each refresh: the event log.
SELECT timestamp, message FROM event_log(TABLE(main.sales.sales_summary)) WHERE event_type = 'planning_information' ORDER BY timestamp DESC; -- e.g. "Flow 'sales' has been planned to be executed as ROW_BASED."
For a standalone MV only the owner can query its event log directly; share it through a view. - Why it chose that: the detail. The same event holds a
technique_informationlist. Each entry hasmaintenance_type,is_chosen,is_applicableandincrementalization_issues, so you can read why incremental was not chosen. The docs show a parsing query on their monitoring page; I have not run one. - The cost model said no. When incremental was possible but rejected, look in the event log for
INCREMENTAL_PLAN_REJECTED_BY_COST_MODELevents. Reasons includeCHANGESET_SIZE_THRESHOLD_EXCEEDED,TABLE_SIZE_THRESHOLD_EXCEEDEDandEXCESSIVE_OPERATOR_NESTING. - In the UI. The pipeline or Catalog Explorer view has an Incrementalization column (Incremental, Full recompute, No change). A lightbulb opens the Issues panel. The query profile shows a
FLOW_FULL_RECOMPUTEinsight explaining why.DESCRIBE TABLE EXTENDED nameshows the latest refresh and its type, though the docs do not name the field. - Make it fail. With
REFRESH POLICY INCREMENTAL STRICT, a refresh that cannot be incremental stops with the error classMATERIALIZED_VIEW_NOT_INCREMENTALIZABLEand a reason such asROW_TRACKING_NOT_ENABLED,WINDOW_WITHOUT_PARTITION_BY,UDF_NOT_DETERMINISTICorEXPRESSION_NOT_DETERMINISTIC.
Also watch the bill: a sudden rise in DBUs for the MV's pipeline in system.billing.usage (s6) is often a quiet switch to full recompute.
Tips and tricks
Practical advice
- Run
EXPLAINin code review, for every new MV and every change to one. - Choose a schedule or trigger that matches the change.
SCHEDULE EVERY 1 DAYor aCRONfor batch.TRIGGER ON UPDATE [AT MOST EVERY interval]to refresh when sources change (minimum one minute, up to 10 upstream sources per MV, upstreams must be Delta tables, MVs, streaming tables or managed views over them). - Use
CLUSTER BYorCLUSTER BY AUTOrather thanPARTITIONED BY. The docs recommend liquid clustering for MVs. - Use
ASYNC(REFRESH MATERIALIZED VIEW name ASYNC) for long refreshes you do not want to wait on. The default isSYNC. - Declare keys with
RELYonly when they are true: the optimizer can drop needless joins and aggregations. - Use row filters and column masks in the
CREATEstatement, not added later. - Do not run
FULLon sources that lose history, such as Kafka. Existing data is truncated and may not come back. - After a UDF library upgrade, run
REFRESH MATERIALIZED VIEW name FULLon every dependent MV. Databricks cannot see that change. - Use
PRIVATEMVs inside a pipeline for steps you do not want published. - Permissions: to refresh you must own the MV or hold
REFRESHon it, and the owner needsSELECTon the base tables.
CREATE MATERIALIZED VIEW main.sales.daily_revenue
CLUSTER BY AUTO
REFRESH POLICY INCREMENTAL STRICT
SCHEDULE CRON '0 0 2 * * ?' AT TIME ZONE 'UTC'
AS SELECT order_date, SUM(CAST(amount AS DECIMAL(18,2))) AS revenue
FROM main.sales.orders GROUP BY order_date; -- sketch: not runChoosing the object
MV, streaming table, table or metric view
| Need | Use | Verdict | Why |
|---|---|---|---|
| A summary that must match its sources after every refresh, source keeps history | Materialized view | Pursue | The engine keeps it correct and cheap |
| Process each new record once; source loses history (Kafka, Auto Loader files) | Streaming table | Pursue | Checkpoints track what was read; a full refresh would truncate |
| Rows updated or deleted by your own merge logic | Physical table | Pursue | MVs cannot be written by you |
| Same number, many groupings and tools | Metric view (materialized if slow) | Pursue | See the comparison |
| Query too complex to refresh incrementally on a huge source | An MV on it | Drop | Each refresh is a full recompute; build a pre-aggregated table by hand instead |
Honest limits
Not confirmed
- What each incremental technique does inside. The docs name
ROW_BASED,GROUP_AGGREGATEand the others but do not define them. - Price per DBU, SKU name, pricing tier and storage billing for MV refresh. The docs say serverless Lakeflow pipelines DBUs and give no rate. They also do not say which pipeline edition (Core, Pro or Advanced) a standalone MV is billed under.
- Expectations. The pipeline
CREATEpage says an MV with expectations is fully refreshed on every update. The incremental refresh page says it is supported except in two cases. I have not tested which is right today. - The
DESCRIBE EXTENDEDfield that holds the refresh type, and the full event log schema: the schema page was only partly readable. Treat field names beyondplanning_informationandtechnique_informationas unconfirmed. - The
costfield insidetechnique_information: one docs page shows it, another summary did not. - Cost of the same refresh on a Pro warehouse vs a serverless one. Both hand the work to a serverless pipeline, but the docs do not spell out the handoff.
- The one example here that I have not run: the
CREATE ... CLUSTER BY AUTO ... SCHEDULE CRONstatement (marked sketch). The illustration of 1 TB and 1% change is arithmetic, not a measurement.
FAQ
Questions people ask
Does a materialized view refresh incrementally?
Only when the refresh runs on serverless compute, the query uses supported operations, the source tables allow it, and the cost model judges incremental cheaper than a full recompute. Otherwise Databricks recomputes everything.
How do I know whether a refresh was incremental or full?
Query the pipeline event log for event_type = 'planning_information' (the message names the technique, for example ROW_BASED or FULL_RECOMPUTE), or look at the Incrementalization column in the UI. EXPLAIN CREATE MATERIALIZED VIEW tells you before you build whether the query can be incremental at all.
Can I stop a materialized view from silently doing a full recompute?
Yes. Create it with REFRESH POLICY INCREMENTAL STRICT. If an incremental refresh is not possible, the refresh or create fails instead of recomputing everything.
What does a materialized view refresh cost?
The docs say a refresh runs on serverless compute billed as serverless Lakeflow pipelines DBUs, and that cost scales with the volume of data processed, not with the size of your SQL warehouse. The docs I read give no price per DBU for it.
Next
Where to go next
Related on this site: Table, view, materialized view or metric view · Declarative pipelines · Metric views: speed and materialization · Databricks compute and cost · Who maintains your tables · Medallion architecture