CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW monthly_sales AS
    SELECT
        customer_id,
        date_trunc('month', order_date) AS month,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id, date_trunc('month', order_date)

A materialized view runs its defining query once, at creation time, and persists the result set as a single Parquet file. Querying the view reads straight from the persisted Parquet instead of recomputing the original query — useful for expensive, repeated, or aggregation-heavy queries.

The defining query can read from any source Beacon knows about — registered tables, external tables, views, or table functions such as read_netcdf(), read_zarr(), or read_atlas().

Unlike a regular view (which recomputes on every reference), a materialized view only changes when you explicitly REFRESH it.

Syntax

CREATE MATERIALIZED VIEW <view_name> AS
    <select_statement>

When the statement runs, Beacon:

1. Stores the materialized view definition in the catalog as a table provider.
2. Executes the defining query immediately.
3. Writes the result as a single part.parquet file in a per-refresh versioned subdirectory under the reserved __beacon__/<view_name>/ prefix in the datasets object store.
4. Serves future reads from the persisted Parquet result.

The catalog records the view name, original SQL query, output schema, storage location, creation timestamp, and last-refresh timestamp.

Querying

SELECT * FROM monthly_sales

This scans the Parquet-backed result and benefits from columnar projection and predicate pushdown — it does not re-run the original query.

REFRESH

REFRESH monthly_sales

A refresh recomputes the original query and replaces the stored Parquet data with the new result (full refresh). The new data is written to a fresh directory and the catalog pointer is swapped atomically, so a failed refresh leaves the previous result intact and queryable.

INFO

Only full refresh is supported in this version. Incremental refresh, scheduled refresh, and dependency-based invalidation are planned for later releases.

Errors

Refreshing a name that is not a materialized view fails clearly:

Materialized view 'unknown_view' does not exist

Object 'orders' is not a materialized view

DROP

DROP TABLE monthly_sales

DROP TABLE IF EXISTS monthly_sales

Dropping a materialized view removes it from the catalog and deletes its persisted Parquet data.

Example

CREATE MATERIALIZED VIEW top_customers AS
    SELECT
        customer_id,
        SUM(total) AS lifetime_value
    FROM orders
    GROUP BY customer_id
    ORDER BY lifetime_value DESC
    LIMIT 100;

SELECT * FROM top_customers;

REFRESH top_customers;