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 MVPipeline-defined MV
Created withCREATE MATERIALIZED VIEW in a SQL warehouse or notebookThe same keyword inside a Lakeflow pipeline's source code
Who runs the refreshA backing pipeline Databricks creates for you (shown as type MV/ST under Jobs & Pipelines)Your pipeline
ScheduleSCHEDULE or TRIGGER ON UPDATE in the statementThe pipeline's own schedule or trigger
Good forOne summary table owned by one teamA 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

You, in a SQL warehouse(Pro or Serverless)CREATE / REFRESH statementOr: a Lakeflow pipelineyou wrote (must be setto serverless)Backing pipelinetype MV/ST, runs onserverless computethis is what you pay forThe stored resulta table in cloud storagethat readers query fastwritesCost follows the data the pipeline processes, not the size of the warehouse that sent the statement.If the pipeline is not serverless, every update is a full recompute.

The statement starts a serverless pipeline, and the pipeline does the work and the billing.

QuestionAnswer 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 requirementsUnity 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

UpdateWhat it doesHow you start it
Refresh (the default)Tries to read only what changed; falls back to a full recompute if it has toREFRESH MATERIALIZED VIEW name, a schedule, a trigger, or a pipeline update
Full refreshAlways recalculates everything and resets all checkpointsREFRESH 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 nameMeaning
ROW_BASED, PARTITION_OVERWRITE, WINDOW_FUNCTION, APPEND_ONLY, GROUP_AGGREGATE, GENERIC_AGGREGATEIncremental: only changed data was processed, by this method
FULL_RECOMPUTEEverything was recomputed
NO_OPNothing 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

What happens on each refresh (AUTO policy)Serverlesscompute?yesSource datachanged?yesQuery can beincremental?yesCost model saysincremental is cheaper?Full recomputealwaysnoNO_OPnothing to donoFull recomputenoIncrementalyesFull recomputenoPolicy INCREMENTAL STRICT turns every red box into a failed refresh instead.

One refresh, left to right. Red boxes are the full recomputes.

The refresh policy: who decides

PolicyBehaviourUse when
AUTO (default)The cost model picks the cheaper of incremental and fullMost cases
INCREMENTALIncremental 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 STRICTIncremental or fail. The refresh stops with an error instead of recomputing everything.A surprise full recompute is not acceptable
FULLNever incremental, even when it could beSmall 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

SituationChooseVerdictWhy
Big source, small daily change, query is eligibleAUTO or INCREMENTAL on serverlessPursueYou pay for the change, not the whole table
A full recompute would be a billing surpriseINCREMENTAL STRICTPursueIt fails loudly so you can fix the query
Small table, refreshed rarelyFULL or AUTOPursueIncremental bookkeeping saves nothing
Source rewritten most nightsAUTOPursueThe cost model will pick full, which is cheaper then
Source has no history (Kafka, files you delete)A streaming table, not an MVDrop the MVA full refresh would lose data you cannot read again
Pipeline is classic computeMVDropEvery update is a full recompute
You need results in milliseconds or per-event latencyA streaming table or a normal tableDropMV updates take seconds or minutes

Limitations

What stops incremental refresh, and other limits

What forces a full recompute

CauseExampleFix
Not serverlessClassic pipeline computeUse serverless
LIMITORDER BY total DESC LIMIT 100Store everything; apply the limit when reading
Recursive CTEWITH RECURSIVEFlatten the hierarchy into a table first
Non-deterministic functionRAND(), or CURRENT_DATE() in SELECTOnly current_date(), current_timestamp() and now() in a WHERE are allowed
Floating-point sumsSUM(price) on DOUBLESUM(CAST(price AS DECIMAL(18,2)))
Window without PARTITION BYROW_NUMBER() OVER (ORDER BY day)Add PARTITION BY
Source is not Delta or managed IcebergA volume, external location, foreign catalog, or foreign Iceberg tableLand it into a Delta table first
Row tracking is off on a sourceA JOIN or UNION ALL over a table without itTurn row tracking on for the source
UDF not marked deterministic, or its behaviour changedPython UDF in the queryMark it deterministic; after a library upgrade, run a full refresh yourself
Complex shape above the aggregateA GROUP BY with calculations stacked on topPut the aggregate last; do the extra maths in a view on top
Cost model says full is cheaperMost of the source changed; deeply nested queryNothing 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 bare SUM(x)).
  • Constraints are informational. Primary and foreign keys are not enforced, though the optimizer can use them (declare with RELY only if they are true).
  • A SUM over 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

DoInstead of
Run the MV on serverlessClassic compute
ALTER TABLE src SET TBLPROPERTIES (delta.enableRowTracking = true, delta.enableDeletionVectors = true) on every sourceLeaving row tracking off
Keep sources as Delta tables (or managed Iceberg)Reading files, volumes or foreign catalogs directly
Cast floats to DECIMAL before summingSUM and AVG on DOUBLE
Add PARTITION BY to every window functionGlobal ORDER BY windows
Store the full aggregate, apply LIMIT in the reading queryLIMIT inside the MV
Keep current_date() only in WHERENon-deterministic functions in SELECT
Mark UDFs deterministic, or use built-in functionsOpaque Python UDFs
Put the aggregate at the top of the query; build extra layers as a view on the MVStacking maths on top of GROUP BY
Put expectations on the source tableExpectations inside the MV (if your test shows it blocks incremental)
Use INCREMENTAL STRICT on MVs that must stay cheapHoping AUTO keeps choosing incremental
Test with EXPLAIN CREATE MATERIALIZED VIEW before deployFinding out from the bill

How to know

Six ways to see whether a refresh was full

  1. Before you build: EXPLAIN. Run EXPLAIN CREATE MATERIALIZED VIEW ... with your whole statement (Databricks Runtime 17.3 or later). It prints The Materialized View can be incrementally refreshed. or cannot, then a list of blockers with codes such as OPERATOR_NOT_INCREMENTALIZABLE or INPUT_NOT_IN_DELTA. It checks the shape of the query only, not what the cost model will do later.
  2. 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.
  3. Why it chose that: the detail. The same event holds a technique_information list. Each entry has maintenance_type, is_chosen, is_applicable and incrementalization_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.
  4. The cost model said no. When incremental was possible but rejected, look in the event log for INCREMENTAL_PLAN_REJECTED_BY_COST_MODEL events. Reasons include CHANGESET_SIZE_THRESHOLD_EXCEEDED, TABLE_SIZE_THRESHOLD_EXCEEDED and EXCESSIVE_OPERATOR_NESTING.
  5. 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_RECOMPUTE insight explaining why. DESCRIBE TABLE EXTENDED name shows the latest refresh and its type, though the docs do not name the field.
  6. Make it fail. With REFRESH POLICY INCREMENTAL STRICT, a refresh that cannot be incremental stops with the error class MATERIALIZED_VIEW_NOT_INCREMENTALIZABLE and a reason such as ROW_TRACKING_NOT_ENABLED, WINDOW_WITHOUT_PARTITION_BY, UDF_NOT_DETERMINISTIC or EXPRESSION_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 EXPLAIN in code review, for every new MV and every change to one.
  • Choose a schedule or trigger that matches the change. SCHEDULE EVERY 1 DAY or a CRON for 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 BY or CLUSTER BY AUTO rather than PARTITIONED 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 is SYNC.
  • Declare keys with RELY only when they are true: the optimizer can drop needless joins and aggregations.
  • Use row filters and column masks in the CREATE statement, not added later.
  • Do not run FULL on 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 FULL on every dependent MV. Databricks cannot see that change.
  • Use PRIVATE MVs inside a pipeline for steps you do not want published.
  • Permissions: to refresh you must own the MV or hold REFRESH on it, and the owner needs SELECT on 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 run

Choosing the object

MV, streaming table, table or metric view

NeedUseVerdictWhy
A summary that must match its sources after every refresh, source keeps historyMaterialized viewPursueThe engine keeps it correct and cheap
Process each new record once; source loses history (Kafka, Auto Loader files)Streaming tablePursueCheckpoints track what was read; a full refresh would truncate
Rows updated or deleted by your own merge logicPhysical tablePursueMVs cannot be written by you
Same number, many groupings and toolsMetric view (materialized if slow)PursueSee the comparison
Query too complex to refresh incrementally on a huge sourceAn MV on itDropEach 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_AGGREGATE and 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 CREATE page 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 EXTENDED field that holds the refresh type, and the full event log schema: the schema page was only partly readable. Treat field names beyond planning_information and technique_information as unconfirmed.
  • The cost field inside technique_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 CRON statement (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

All data engineering · Databricks on this site