CREATE MATERIALIZED VIEW defines a view that is persisted in durable storage and incrementally updated as new data arrives.

A materialized view specifies a cluster that is tasked with keeping its results up-to-date, but can be referenced in any cluster. This allows you to effectively decouple the computational resources used for view maintenance from the resources used for query serving.


CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS view_name ( col_ident , ) IN CLUSTER cluster_name WITH with_options AS select_stmt
Field Use
OR REPLACE If a materialized view exists with the same name, replace it with the view defined in this statement. You cannot replace views that other views or sinks depend on, nor can you replace a non-view object with a view.
IF NOT EXISTS If specified, do not generate an error if a materialized view of the same name already exists.

If not specified, throw an error if a view of the same name already exists. (Default)
view_name A name for the materialized view.
( col_ident) Rename the SELECT statement’s columns to the list of identifiers, both of which must be the same length. Note that this is required for statements that return multiple columns with the same identifier.
cluster_name The cluster to maintain this materialized view. If not specified, defaults to the active cluster.
select_stmt The SELECT statement whose results you want to maintain incrementally updated.


WITH ( field = val , )
Field Value Description
ASSERT NOT NULL col_ident text The column identifier for which to create a non-null assertion. To specify multiple columns, repeat the option multiple times.
RETAIN HISTORY FOR retention_period interval Private preview. This option has known performance or stability issues and is under active development. Duration for which Materialize retains historical data for performing time travel queries. Accepts positive interval values (e.g. '1hr'). Default: 1s.


Usage patterns

Maintaining a materialized view in durable storage has resource and latency costs that should be carefully considered depending on the main usage of the view. It’s a good idea to create a materialized view if:

  • The results need to be available across clusters;
  • View maintenance and query serving would benefit from being scaled independently;
  • The final consumer of the view is a sink or a SUBSCRIBE operation.

On the other hand, if you only need to access a view from a single cluster, you should consider creating a non-materialized view and building an index on it instead. The index will incrementally maintain the results of the view updated in memory within that cluster, allowing you to avoid the costs and latency overhead of materialization.


Although you can query a materialized view directly, these queries will be issued against Materialize’s storage layer. This is expected to be fast, but still slower than reading from memory. To improve the speed of queries on materialized views, we recommend creating indexes based on common query patterns.

It’s important to keep in mind that indexes are local to a cluster, and maintained in memory. As an example, if you create a materialized view and build an index on it in the quickstart cluster, querying the view from a different cluster will not use the index; you should create the appropriate indexes in each cluster you are referencing the materialized view in.

Non-null assertions

Because materialized views may be created on arbitrary queries, it may not in all cases be possible for Materialize to automatically infer non-nullability of some columns that can in fact never be null. In such a case, ASSERT NOT NULL clauses may be used as described in the syntax section above. Specifying ASSERT NOT NULL for a column forces that column’s type in the materialized view to include NOT NULL. If this clause is used erroneously, and a NULL value is in fact produced in a column for which ASSERT NOT NULL was specified, querying the materialized view will produce an error until the offending row is deleted.


Creating a materialized view

SELECT auction_id,
FROM highest_bid_per_auction
WHERE end_time < mz_now();

Using non-null assertions

  -- The semantics of a FULL OUTER JOIN guarantee that user_id is not null,
  -- because one of `` or `orders.user_id` must be not null, but
  -- Materialize cannot yet automatically infer that fact.
  coalesce(, orders.user_id) AS user_id,
FROM users FULL OUTER JOIN orders ON = orders.user_id


The privileges required to execute this statement are:

  • Ownership of existing view_name if OR REPLACE is specified.
  • CREATE privileges on the containing schema.
  • CREATE privileges on the containing cluster.
  • USAGE privileges on all types used in the materialized view definition.
  • USAGE privileges on the schemas that all types in the statement are contained in.
Back to top ↑