This is a central design decision in a pipeline. It turns on how the source behaves and what “correct” has to mean for the result.
A streaming table trades correctness over time for low cost and low latency: each row is seen once and never revisited. A materialized view does the opposite: it guarantees the same answer as a batch query at the moment of refresh, recomputing whatever it must to get there.
| Requirement | Streaming table | Materialized view |
|---|---|---|
| Source shape | Append-only or continuously growing | Any; updates and deletes in the source are fine |
| Refresh behaviour | Evaluates only records since the last update | Incremental when supported and cheaper, otherwise full recompute |
| Latency | Low; real-time mode gives sub-second end-to-end for operational workloads | Seconds or minutes, not milliseconds |
| Cost as data grows | Scales with new data | Incremental is cheap, but some changes force an expensive recompute |
| Late or out-of-order data | Each row is handled once on arrival; state needs a watermark or a naturally bounded stream | All required data is processed; correct at update time |
| Dimension changes in joins | Joins do not recompute; old rows keep old values | Joins recompute when dimensions change |
| Changing the query | Only new rows get the new logic unless fully refreshed | Results always reflect the current query |
| Source that drops history (Kafka) | Correct choice: each record processed once | Do not use: a full refresh cannot rebuild it |
| Delta time travel | Supported | Not supported |
Decision rules that follow from the table:
- For ingestion from Auto Loader or a message bus, use a streaming table. A source that cannot replay history cannot survive materialized view semantics.
- If a full refresh would be cost-prohibitive on a very large table, use a streaming table to get exactly-once processing.
- For aggregations and joins whose inputs can change, and which must stay right, use a materialized view.
- For small or rarely changing reference data, load it into a materialized view with a batch read; there is no benefit in streaming something that barely changes.
- If source rows will be deleted or archived after processing but must stay downstream, use a streaming table, because a materialized view only reflects what the source still holds.
An unbounded stream with no watermark can fail a pipeline through memory pressure. Pipeline mode is a separate decision: both dataset types run in either mode. Figure 5.1 sets the decision rules side by side.
What makes a materialized view incremental
Incremental refresh needs serverless. On classic compute a materialized view is always fully recomputed, whatever the query looks like. On serverless, a cost model picks the cheaper of incremental and full. Both produce the same output.
Whether the query qualifies depends on its shape and its sources:
- Deterministic built-in functions and immutable UDFs in
SELECT,GROUP BY, CTEs,UNION ALL, andWHEREandHAVINGfilters are supported.WITH RECURSIVEis not, and falls back to a full recompute. - Non-deterministic functions are allowed only as time functions in
WHERE, such ascurrent_date(). - A window specification without
PARTITION BYis not incrementalisable. - Summing or averaging
FLOATorDOUBLEcan force a full refresh. Cast toDECIMALin the expression to avoid it. - Base tables can be Delta tables, Unity Catalog managed Iceberg tables, materialized views and streaming tables. Volumes, external locations and foreign catalogs are not supported, and foreign Iceberg tables can be read but not incrementally.
Databricks recommends enabling row tracking and deletion vectors on source tables, since some operations require row tracking. To test a query before committing to it, use EXPLAIN CREATE MATERIALIZED VIEW. To see what actually happened, the Incrementalization column in the pipeline’s Tables panel shows Incremental, Full recompute or No change.
A refresh policy overrides the cost model:
CREATE OR REFRESH MATERIALIZED VIEW my_mv
REFRESH POLICY INCREMENTAL
AS SELECT a, sum(b) FROM my_catalog.example.my_table GROUP BY a;| Policy | Incremental available | Incremental not available on a refresh | Create or re-initialise, query not incrementalisable |
|---|---|---|---|
AUTO (default) |
Cost model chooses | Full refresh | Full refresh |
INCREMENTAL |
Incremental | Full refresh | Fails |
INCREMENTAL STRICT |
Incremental | Refresh fails | Fails |
FULL |
Full refresh | Full refresh | Full refresh |
Failures raise MATERIALIZED_VIEW_NOT_INCREMENTALIZABLE with a reason such as ROW_TRACKING_NOT_ENABLED.
Common trap: Treating a materialized view as incremental because the query is simple - on classic compute every refresh is a full recompute, so a pipeline that needs cheap refreshes must run on serverless.
Common trap: Choosing a materialized view over Auto Loader because it is “always correct” - ingest queries belong in streaming tables, since records should be processed once and the source may later lose files that a full recompute would need.
Common trap: Trusting
REFRESH POLICY INCREMENTALto prevent a costly recompute - it still falls back to a full refresh when incremental is unavailable; onlyINCREMENTAL STRICTmakes the refresh fail instead.