Materialized views

VegaDB has two materialization models. They share a name because both expose a query result as a table, but their execution and failure behavior are intentionally different.

Traditional materialized views

A traditional materialized view stores the result of its query in a table and refreshes it as its sources change. A refresh recomputes only the view partitions whose source partitions changed, reads them at one source snapshot, and publishes every replaced partition in one commit. Readers keep seeing the previous complete result while a refresh runs, and a failed or cancelled refresh exposes no partial data.

CREATE MATERIALIZED VIEW sales.daily_totals
PARTITIONED BY (sale_date)
REFRESH EVERY INTERVAL '15 minutes'
AS
SELECT sale_date, store_id, sum(amount) AS amount
FROM sales.orders
GROUP BY sale_date, store_id;

REFRESH EVERY schedules refresh as a background job; REFRESH MANUAL refreshes only on request:

REFRESH MATERIALIZED VIEW sales.daily_totals;

A refresh can also be limited to named partitions of the view.

Sources and partitions

The sources can be managed native or DuckLake tables. Federated tables are refreshed in full on each refresh.

When the view's partition expression derives from a source's partition column, such as sale_date above or year(ts), month(ts), day(ts), each view partition maps to the source partitions it reads, and a change to one source partition refreshes only the view partitions that depend on it. Other sources, such as dimension tables, are tracked as a whole: a change to them refreshes every view partition. Compaction and other maintenance never make a view stale.

Storage

By default the result is stored as a native table owned by the view. With WITH (storage = 'ducklake') it is stored as a DuckLake table instead, so other engines can read it; such a view is read by name and is not used for query rewrite.

Query rewrite

The optimizer answers matching queries from natively stored views. A view can answer a select-project-join-group-by query when its joins match the query's (or the missing joins are lossless), the query's predicates imply the view's, and the grouping rolls up with re-aggregatable functions. A query can group more coarsely than the view; VegaDB re-aggregates the view's rows.

Rewrite is exact by default. Fresh view partitions are read from the view; partitions whose sources changed since the last refresh are computed from the base tables at the query's snapshot, and the two are combined. Results are therefore the same as without the view. To accept bounded staleness instead, set it on the view or in the session:

CREATE MATERIALIZED VIEW sales.store_totals
REFRESH EVERY INTERVAL '1 hour' WITH (staleness = '5 minutes')
AS
SELECT store_id, sum(amount) AS amount
FROM sales.orders
GROUP BY store_id;

EXPLAIN shows the views and partitions a query uses and their staleness.

Streaming materialized views — preview

A streaming materialized view is a long-lived incremental job. It consumes a VegaDB-managed DuckLake change feed or Kafka, maintains operator state and publishes exactly-once-visible changes into a read-only VegaDB table.

It is not a timer that reruns the original query.

CREATE STREAMING MATERIALIZED VIEW analytics.live_sales
WITH (
  initial_mode = 'backfill',
  source_batch_rows = 512,
  source_batch_bytes = '1 MiB',
  source_batch_max_linger = INTERVAL '10 milliseconds',
  publish_batch_bytes = '1 MiB',
  publish_max_linger = INTERVAL '10 milliseconds',
  checkpoint_interval = INTERVAL '10 seconds',
  on_error = 'fail',
  parallelism = 4,
  max_staleness = INTERVAL '1 minute',
  wait_for_ready = false
)
AS
SELECT store_id, sum(amount) AS amount
FROM sales.orders
GROUP BY store_id;

Creation parses, binds and checks whether every operator can be maintained incrementally. Unsupported plans fail synchronously. Unless wait_for_ready = true, valid creation returns while the view enters BACKFILLING or STARTING. Reads fail with MATERIALIZED_VIEW_NOT_READY until the first complete snapshot is published.

Initial modes

• backfill reads an exact initial source snapshot, catches up changes after its boundary, and publishes the first result when it is complete.
• future captures a new boundary, publishes an empty initial result and processes only later changes.

Lifecycle commands

ALTER STREAMING MATERIALIZED VIEW analytics.live_sales PAUSE;
ALTER STREAMING MATERIALIZED VIEW analytics.live_sales RESUME;
ALTER STREAMING MATERIALIZED VIEW analytics.live_sales RESTART;
ALTER STREAMING MATERIALIZED VIEW analytics.live_sales SET (parallelism = 8);
ALTER STREAMING MATERIALIZED VIEW analytics.live_sales REBUILD
WITH (initial_mode = 'backfill');

SHOW STREAMING MATERIALIZED VIEWS;
SHOW STREAMING MATERIALIZED VIEW analytics.live_sales;

Pause is checkpointed. A parallelism change also follows checkpoint, stop, reschedule and restore; it does not move live state by guesswork. RESTART keeps the generation and restores its last complete checkpoint. REBUILD creates a new generation.

Read behavior when work stops

During PAUSED, RECOVERING, SCHEMA_BLOCKED, PERMISSION_BLOCKED, RESOURCE_BLOCKED or FAILED, authorized readers get the last committed result. A configured max_staleness changes this: once exceeded, reads fail unless an authorized session explicitly accepts stale results.

Event time and state

Windowed queries must state event-time and watermark behavior. Late rows behind the effective watermark are dropped with metrics and rate-limited audit signals; they are never silently ignored. There is no hidden TTL for non-windowed DuckLake state. Independently changing Kafka inputs require a time bound or semantic TTL when state could grow forever.

Inspect admission before creating

EXPLAIN STREAMING
SELECT store_id, sum(amount)
FROM sales.orders
GROUP BY store_id;

The planned explanation includes each node's input and output changelog mode, key or row identity, state kind, state bounds, watermark, window or TTL, partitioning and the reason an operator is rejected.