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 runs a bounded query over one exact source snapshot vector. Refresh builds a complete new physical generation and publishes it atomically. Readers continue using the previous complete generation while this happens.

CREATE MATERIALIZED VIEW sales.daily_totals
WITH (
  refresh_interval = INTERVAL '15 minutes',
  max_staleness = INTERVAL '1 hour'
)
AS
SELECT sale_date, sum(amount) AS amount
FROM sales.orders
GROUP BY sale_date
WITH DATA;

WITH DATA is the default. Use WITH NO DATA to create only the definition; reads then fail with MATERIALIZED_VIEW_NOT_POPULATED until the first successful refresh.

REFRESH MATERIALIZED VIEW sales.daily_totals;

REFRESH MATERIALIZED VIEW sales.daily_totals
WITH (wait = false);

ALTER MATERIALIZED VIEW sales.daily_totals
SET (refresh_cron = '0 */15 * * *', refresh_timezone = 'UTC');

Create and refresh wait for their durable job by default. wait = false returns a job identity so the caller can observe it separately. A failed or cancelled refresh does not expose partial data and does not invalidate the last successful generation.

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.