Materialized Views

Materialized views store pre-computed query results as Iceberg tables, with support for incremental maintenance. Gnok tracks dependencies on base tables and can refresh only the changed data.

Architecture

Each materialized view is backed by a hidden Iceberg table named __mv_<view_name> in the same schema as the view. This backing table stores the pre-computed results and is managed entirely by the engine. Users interact with the materialized view by name; the backing table is not directly accessible.

When you create a materialized view, Gnok:

1. Creates the backing Iceberg table (__mv_<view_name>) with the schema derived from the view's SELECT clause.
2. Executes the view's defining query and writes the results to the backing table.
3. Records the current snapshot ID of each source table as a bookmark — same id you'd see in <source>$snapshots WHERE is_current (Snapshots, Time Travel, and Rollback).
4. Registers source table dependencies in catalog metadata.

When you query the materialized view, Gnok reads directly from the backing table. There is no recomputation at query time.

Create a Materialized View

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
    order_date,
    region,
    SUM(amount) AS total_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY order_date, region;

Refresh Strategies

Full Refresh

Recomputes the entire view from scratch:

REFRESH MATERIALIZED VIEW daily_revenue;

Incremental Refresh

Processes only the data that changed since the last refresh:

REFRESH MATERIALIZED VIEW daily_revenue INCREMENTAL;

Refresh Strategies at Creation

Configure the refresh strategy when creating the view:

Strategy	Description
OnDemand	Manual refresh only (default)
Scheduled	Automatic refresh on a configurable time interval
OnCommit	Refresh triggered when base tables are modified

-- Scheduled refresh every 15 minutes
CREATE MATERIALIZED VIEW hourly_stats
REFRESH EVERY '15 minutes'
AS SELECT ...;

-- Refresh on commit to source table
CREATE MATERIALIZED VIEW live_summary
REFRESH ON COMMIT
AS SELECT ...;

Incremental Refresh Pipeline

The incremental refresh pipeline avoids recomputing the entire view by processing only the data that has changed since the last refresh. The pipeline consists of five steps:

Step 1: Snapshot Comparison

The engine compares each source table's current snapshot ID with the bookmarked snapshot ID from the last refresh. If all snapshot IDs match, no refresh is needed (NoOp strategy).

Step 2: Delta File Enumeration

For each source table with a changed snapshot, the engine enumerates the delta data files -- the files added between the bookmarked snapshot and the current snapshot. This uses Iceberg's snapshot log to identify exactly which data files are new.

Step 3: Strategy Classification

Based on the nature of the view definition and the type of changes, the engine selects one of three incremental strategies:

Strategy	Condition	Description
AppendOnly	View is a simple projection or filter (SELECT ... FROM ... WHERE ...) and changes are insert-only	Executes the view's query against only the delta files and appends results to the backing table.
AggregateMerge	View contains decomposable aggregates (SUM, COUNT, AVG, MIN, MAX) and changes are insert-only	Computes partial aggregates over the delta files, then merges with existing aggregates in the backing table.
FullRefresh	View contains DISTINCT, HAVING with non-decomposable predicates, or source tables have deletes/updates	Falls back to full recomputation. Incremental maintenance is not possible for these cases.

Step 4: Delta Execution

The engine executes the incremental query on the delta files only. For AppendOnly, this produces new rows. For AggregateMerge, this produces partial aggregate results that are merged with the existing backing table using a key-based upsert.

Step 5: Commit and Bookmark Update

The result is committed to the backing Iceberg table as a new snapshot. The engine then updates the snapshot bookmarks for all source tables to their current snapshot IDs.

Event-Driven Refresh

For materialized views created with REFRESH ON COMMIT, Gnok uses Server-Sent Events (SSE) from the catalog service to detect source table commits in near-real-time.

When a source table commits a new snapshot, the catalog service emits an SSE event. The coordinator's refresh manager receives this event and schedules an incremental refresh for all materialized views that depend on the modified table.

This approach avoids polling and provides low-latency refresh without consuming resources during idle periods.

Staleness Monitoring

A background thread monitors the freshness of all materialized views. Each view transitions through staleness states based on the time since its last refresh relative to its configured SLO tier:

State	Meaning
Fresh	The view is within its target lag budget.
Approaching	The view is approaching its maximum lag threshold. A refresh is recommended.
Stale	The view has exceeded its maximum lag. Data may be out of date.
Critical	The view is significantly past its maximum lag. Urgent refresh needed.
Expired	The view has not been refreshed for an extended period. Results should not be trusted.

The staleness monitor tracks SLO compliance and can trigger alerts with configurable cooldown periods (default: 300 seconds) to prevent alert storms.

Source Dependency Tracking

When a materialized view is created, Gnok registers the dependency relationship between the view and its source tables in catalog metadata. This dependency graph is used for:

• Cascade refresh: When a source table is modified, all dependent views are identified for refresh.
• Drop protection: Dropping a source table warns about dependent materialized views.
• Schema change detection: If a source table's schema changes in a way that is incompatible with the view definition, the view is marked as stale and must be recreated.

Dependency metadata is stored in the catalog service and survives coordinator restarts.

Persistence

Materialized view definitions, refresh strategies, snapshot bookmarks, and staleness state are all stored in the catalog service. This means:

• MV definitions survive coordinator restarts.
• Snapshot bookmarks are preserved, so incremental refresh resumes from the correct point after a restart.
• Scheduled refresh timers are re-established on coordinator startup.

Performance

Incremental refresh reads only the delta files from source tables, not the entire source table. For append-heavy workloads, this can reduce refresh time by orders of magnitude compared to full refresh.

• AppendOnly: Reads delta files, executes the view query, appends to backing table. Cost is proportional to the volume of new data.
• AggregateMerge: Reads delta files, computes partial aggregates, merges with existing results via key-based upsert. Cost is proportional to the volume of new data plus the number of affected groups.
• FullRefresh: Reads the entire source table and rewrites the backing table. Cost is proportional to the total source data size.

For large tables with small incremental changes, AppendOnly and AggregateMerge provide refresh times that are a small fraction of full refresh time.

Query

Materialized views are queried like regular tables:

SELECT * FROM daily_revenue
WHERE order_date >= '2025-01-01'
ORDER BY total_revenue DESC;

The optimizer can also use materialized views for automatic query rewriting. If a query matches the structure of an existing materialized view, the optimizer may substitute a scan of the backing table instead of computing the query from the base tables.

Listing Materialized Views

SHOW MATERIALIZED VIEWS lists the MVs in a schema. All three qualifier forms are accepted — IN, FROM, and bare — and produce identical results:

-- Session default schema
SHOW MATERIALIZED VIEWS;

-- Specific schema (three interchangeable forms)
SHOW MATERIALIZED VIEWS analytics.sales;
SHOW MATERIALIZED VIEWS IN analytics.sales;
SHOW MATERIALIZED VIEWS FROM analytics.sales;

-- Nested Iceberg namespaces (catalog.ns1.ns2…)
SHOW MATERIALIZED VIEWS IN analytics.sales.us_west;

Output columns: name, catalog, schema.

Identifiers are case-normalized to lowercase following Postgres convention. Double-quote to preserve case: SHOW MATERIALIZED VIEWS IN "Analytics"."Sales".

A qualifier pointing at a missing namespace returns an empty result (not an error), matching SHOW VIEWS.

Direct DML is Rejected

Direct INSERT, UPDATE, DELETE, MERGE, TRUNCATE, and INSERT OVERWRITE against a materialized view's backing table are rejected. Manual writes would desynchronize the backing data from the view's defining query — the next refresh would silently overwrite them.

-- All of these fail with a typed error:
INSERT INTO daily_revenue VALUES (...);       -- rejected
INSERT INTO daily_revenue SELECT ... FROM t;  -- rejected
UPDATE daily_revenue SET total_revenue = 0;   -- rejected
DELETE FROM daily_revenue WHERE region = 'X'; -- rejected
MERGE INTO daily_revenue ...;                 -- rejected
TRUNCATE TABLE daily_revenue;                 -- rejected

The error surfaces consistently across all wire protocols:

• HTTP / Web UI — HTTP 400, JSON error field carries the full message.
• Flight SQL — gRPC status FAILED_PRECONDITION (9) with the full message.
• PostgreSQL wire — SQLSTATE 55000 (object_not_in_prerequisite_state).

Materialized view 'analytics.sales.daily_revenue' cannot be modified
directly. Use ALTER MATERIALIZED VIEW ... REFRESH or redefine the view.

To change the contents of an MV, use one of:

• REFRESH MATERIALIZED VIEW <name> — recompute from source data.
• DROP MATERIALIZED VIEW <name>; CREATE MATERIALIZED VIEW <name> AS <new query>; — redefine the view.

This mirrors the standard warehouse-engine convention: direct DML on materialized views is rejected to keep view contents in lockstep with the defining query.

Dropping

DROP MATERIALIZED VIEW daily_revenue;

Dropping a materialized view removes the view definition, the backing Iceberg table (__mv_daily_revenue), and all associated metadata (bookmarks, dependency records, staleness state).

Limitations

• No circular dependencies: A materialized view cannot depend on another materialized view. All source tables must be base tables.
• No MVs on MVs: Chaining materialized views (creating a view that selects from another materialized view) is not supported.
• DISTINCT forces full refresh: Views containing DISTINCT cannot be incrementally refreshed because deduplication requires access to the full dataset.
• Deletes and updates force full refresh: If source tables have DELETE or UPDATE operations since the last refresh, incremental refresh falls back to full recomputation. Append-only source tables benefit most from incremental refresh.
• Schema evolution: If a source table adds or removes columns referenced by the view, the view must be dropped and recreated.