Databend Incremental Materialized Views - Change Tracking, Incremental Refresh, and Consistent Reads

A Refresh May Lag; Query Results Must Not

A materialized view (MV) precomputes and persists the result of a frequently executed query. The additional storage reduces query latency, repeated computation, and the amount of source data scanned.

Persisting a result is the easy part. A production implementation must also answer harder questions:

• How should the result be maintained while the source table continues to change?

• Can refresh work run without delaying source-table writes?

• What should a query return when the persisted result is behind the source?

• How should aggregate state, physical layout, and storage maintenance evolve independently?

Databend's new materialized view design stores the precomputed result in an independent, read-only FUSE table. Change Tracking identifies source-table changes since the last refresh, while a checkpoint records the source position already consumed by the MV. The refresh path and the consistency path are deliberately separated: background maintenance may lag, but the read path selects a plan that still produces the correct result.

That plan may read the persisted MV directly, merge an unmaterialized delta at query time, or bypass the MV and execute against the source table. The central design principle is straightforward: delayed maintenance may reduce performance, but it must not silently expose an outdated answer.

Materialized View Maintenance Models and Their Trade-offs

Materialized view implementations make two related decisions. The first is when to maintain the result: synchronously in the write transaction or asynchronously in the background. The second is how much to recompute: only the affected data or the complete defining query.

These choices produce several common maintenance models. A database may support more than one of them.

Databend uses asynchronous maintenance and chooses between incremental refresh and full rebuild according to the changes that actually occurred. At query time, it selects a Fresh, Hybrid, or Live Fallback plan according to the MV's materialization progress.

UPDATE

DELETE

An Independent Storage Model for Materialized Results

A Databend materialized view is not an index attached to source-table Blocks. It is a specialized, read-only FUSE table with

engine = MATERIALIZED_VIEW

TableMeta

This separation gives the MV an independent refresh position and allows its physical layout to follow its own query pattern rather than the ingestion pattern of the source table.

From Aggregating Indexes to Standalone Materialized Views

Databend's earlier Aggregating Index model behaves like a Block-level projection that moves with the source table. Both designs can avoid repeated computation, but they differ in storage ownership and lifecycle.

Metadata and Transaction Boundaries

The system keeps three categories of metadata to describe the physical result, the view definition, and the dependency on the source table.

TableMeta(mv_id)

MVDefinition(mv_id)

TableMeta

SourceTableMV(source_id, mv_id)

Creating, replacing, or dropping an MV updates the table metadata, MV definition, and source dependency in one Meta transaction. Creation also enables Change Tracking on the source table atomically. There is therefore no intermediate state in which the MV has been published but subsequent source changes cannot yet be tracked.

From Refresh to Read: Maintaining Correct Results

Refresh progress, persisted data, and query planning form one consistency mechanism. The refresh path consumes source changes through Change Tracking and advances a checkpoint. The read path compares the checkpointed source Snapshot with the source table's current Snapshot to decide whether the stored result can be used as-is.

Change Tracking and Checkpoints

Databend reuses the change-table semantics already provided by Streams instead of implementing a separate CDC mechanism for materialized views. A refresh reads the changes between two consumption endpoints. Each change includes internal columns such as:

change$action

INSERT

DELETE

change$is_update

change$row_id

After a successful refresh, the materialized result and its checkpoint are committed in the same transaction:

materialized_view_source_table_seq
materialized_view_source_snapshot_location

Atomic commit prevents two invalid states: persisted MV data without a corresponding checkpoint advance, and an advanced checkpoint without the data it claims has been materialized.

The checkpoint still advances when all rows in a change batch are filtered out by the MV's

WHERE

Refresh Strategy Follows the Actual Change Set

At the beginning of a refresh, Databend locks the MV, reloads its definition, and pins the source Snapshot for that run. Writes that arrive during the refresh continue committing to newer source Snapshots and are left for a later refresh.

The changes between the previous checkpoint and the pinned Snapshot determine the maintenance strategy.

INSERT OVERWRITE

INSERT

INSERT INTO

UPDATE

DELETE

MERGE

_mv_source_row_id

UPDATE

DELETE

INSERT OVERWRITE

MIN

MAX

For an aggregate MV, an append-only refresh writes aggregate states, not finalized scalar results. A Group Key may temporarily have states in multiple Blocks. Functions such as

sum_merge

count_merge

min_merge

Three Read Paths: Fresh, Hybrid, and Live Fallback

When a query accesses an MV, the Binder compares the source Snapshot recorded in the MV checkpoint with the current source Snapshot.

Read fix is query-plan compensation, not an automatic background refresh. This distinction matters operationally: a delayed refresh can increase query work, but it does not change the answer returned to the user.

Mergeable Aggregate State and Independent Storage Maintenance

Incremental materialization must do more than append each new result. It must preserve mergeable state across batches and prevent state rows and small files from accumulating without bound.

Logical Schema Versus Physical Schema

The columns visible to a user do not always match the columns stored physically. Consider

avg(amount)

avg(amount)

average_amount

sum_state(amount)

count_state(amount)

sum_merge(sum_state) / count_merge(count_state)

Initial support covers common aggregates including

sum

min

max

avg

count

approx_count_distinct

avg

sum_state + count_state

_mv_source_row_id

Reaggregate, Compact, and Recluster

Repeated append-only refreshes can create multiple state rows for the same Group Key. The query result remains correct because the read path merges those states, but the amount of merge input and the degree of file fragmentation increase over time.

Before compact or recluster writes a new Block, Databend performs reaggregation: states with the same Group Key inside that output Block are combined.

OPTIMIZE TABLE mv COMPACT

ALTER MATERIALIZED VIEW mv RECLUSTER [FINAL]

An MV that contains

GROUP BY

_mv_source_row_id

Query Rewrite and a Complete Usage Example

Applications may query an MV directly or continue querying the source table. For source queries, the optimizer compares output expressions, filters, Group Keys, aggregate functions, aggregation granularity, and required columns. When the query matches, the optimizer rewrites it to an MV read plan. That rewritten plan still chooses Fresh, Hybrid, or Live Fallback according to the current data state.

The following MV summarizes paid orders by customer:

CREATE MATERIALIZED VIEW paid_orders_by_customer
    (customer_id, total_amount, order_count, average_amount)
    CLUSTER BY (customer_id)
AS
SELECT
    customer_id,
    sum(amount),
    count(*),
    avg(amount)
FROM orders
WHERE paid
GROUP BY customer_id;

Creation publishes the definition but does not populate the MV immediately. Before the first refresh, a direct query uses Live Fallback. The answer is correct, but the persisted MV provides no acceleration yet.

After the initial refresh, append-only changes can be consumed incrementally. Storage maintenance can be scheduled as the number of incremental batches grows.

-- Build the initial materialized result.
REFRESH MATERIALIZED VIEW paid_orders_by_customer;

-- If subsequent changes are inserts only, consume the delta.
REFRESH MATERIALIZED VIEW paid_orders_by_customer;

-- Reorganize storage as incremental batches accumulate.
OPTIMIZE TABLE paid_orders_by_customer COMPACT;
ALTER MATERIALIZED VIEW paid_orders_by_customer RECLUSTER FINAL;

Where Incremental Materialized Views Fit

The value of an incremental MV depends on the source change pattern, the defining query, and the workload's refresh and consistency requirements. The current Databend implementation is strongest for single-table analytical workloads whose changes can usually be consumed as append-only deltas. It is not intended to replace every continuous data transformation pipeline.

Recommended Workloads

Logs, events, behavioral data, and append-only fact tables. Historical records in these datasets are rarely updated or deleted. Each refresh can process only the unconsumed interval instead of rerunning the full source query.

Frequent

GROUP BY

Write-throughput-sensitive systems that cannot accept stale answers. Asynchronous refresh keeps derived computation out of the source write transaction. If the MV falls behind and the delta is safe to merge, read fix supplies the missing rows during the query. If it is not safe, Databend reads from the source.

Workloads in which ingestion and analytics need different physical layouts. The source may be organized around ingestion time, while the MV uses a customer or business dimension as its Cluster Key. Compact and recluster operations can then maintain the two layouts independently.

What to Consider Before Adoption

Definitions currently focus on a single persistent FUSE table. An MV can currently be created from one persistent FUSE table in the

default

SELECT ... FROM ... [WHERE ...] [GROUP BY ...]

JOIN

Updates and deletes can make aggregate refresh expensive. A non-aggregate MV can project an

UPDATE

DELETE

_mv_source_row_id

Refresh scheduling must be configured explicitly. A Databend Task can run

REFRESH MATERIALIZED VIEW

Incremental processing depends on continuous Change Tracking history. If a historical Snapshot required for incremental refresh or read compensation has already been garbage-collected, Databend can no longer reconstruct that delta and uses Live Fallback. Snapshot retention, refresh frequency, ingestion rate, and query requirements therefore need to be planned together.

Materialized views are read-only objects. Ordinary

INSERT

UPDATE

DELETE

Correctness Determines the Value of Incremental Maintenance

Databend's new materialized view is not merely a query-result cache. It forms an end-to-end incremental computation path: Change Tracking identifies source changes, a checkpoint records the consumption position, the Physical Schema persists mergeable state, refresh advances data and position atomically, and read fix preserves consistent reads while maintenance is asynchronous.

This model is particularly effective for continuously ingested, append-heavy, single-table workloads that require stable analytical answers. Multi-table dependencies, frequent updates or deletes, and workloads that expect fully automatic scheduling require a different pipeline today—or a careful assessment of full-rebuild cost and future Dynamic Table capabilities.

The relevant engineering question is therefore not only whether an MV avoids computation. It is whether the refresh model, mutation pattern, and storage-maintenance cost remain favorable over the full lifecycle of the workload.

Incremental materialized views were introduced and updated in Databend Enterprise Edition after v1.2.934.