Materialized View Syntax

The CREATE MATERIALIZED VIEW statement creates a stored dataset from a query result. Unlike regular queries that execute each time, materialized views persist their results for faster access.

Syntax

CREATE MATERIALIZED VIEW "view_name"
[REFRESH_SCHEDULE = <schedule>]
[DISPLAY_NAME = '<display_name>']
[DESCRIPTION = '<description>']
[EXPIRE = <retention_policy>]
[TAGS = ( '<tag1>', '<tag2>', ... )]
[WRITE_MODE = '<mode>']
[EXTENDED_STATS = '<level>']
[PARTITIONED_BY <field> <transform>[, <field2> <transform2>]]
AS
<select_query>
[MERGE ON <condition>
WHEN MATCHED THEN UPDATE SET <column> = source.<column>, ...
WHEN NOT MATCHED THEN INSERT (<columns>) VALUES (<values>)]

Options summary

Option	Type	Required	Description
`REFRESH_SCHEDULE`	enum or CRON	No	When to automatically refresh the view
`DISPLAY_NAME`	string	No	Human-readable name for the UI
`DESCRIPTION`	string	No	Documentation text
`EXPIRE`	ISO 8601 period or enum	No	Data retention policy
`TAGS`	array	No	Labels for organization and filtering
`WRITE_MODE`	enum	No	How refresh operations write data
`EXTENDED_STATS`	enum	No	Whether to compute additional statistics
`PARTITIONED_BY`	field + transform	No	How to partition stored data
`MERGE ON`	condition + clauses	No	Upsert logic for incremental updates

REFRESH_SCHEDULE

Defines when the materialized view automatically refreshes with new data. Syntax:

REFRESH_SCHEDULE = '@daily'
REFRESH_SCHEDULE = '0 2 * * *'

Values:

Value	Description
`@hourly`	Refresh every hour
`@daily`	Refresh once per day
`@weekly`	Refresh once per week
`@monthly`	Refresh once per month
`@once`	Refresh only once (no recurring schedule)
CRON expression	Custom schedule using standard CRON syntax

CRON format:

┌───────────── minute (0-59)
│ ┌───────────── hour (0-23)
│ │ ┌───────────── day of month (1-31)
│ │ │ ┌───────────── month (1-12)
│ │ │ │ ┌───────────── day of week (0-6, Sunday=0)
│ │ │ │ │
* * * * *

Example: Refresh every Monday at 3:15 PM UTC:

REFRESH_SCHEDULE = '15 15 * * 1'

For large datasets, consider using Incremental View Maintenance to refresh only changed data.

DISPLAY_NAME

A human-readable name shown in the Narrative I/O interface. Syntax:

DISPLAY_NAME = 'Monthly Sales Summary'

If not specified, the view name is used as the display name.

DESCRIPTION

Documentation text describing the view’s purpose and contents. Syntax:

DESCRIPTION = 'Aggregated sales metrics by month for the executive dashboard'

EXPIRE

Sets a data retention policy that determines how long data is kept. Syntax:

EXPIRE = 'P30D'
EXPIRE = 'retain_everything'
EXPIRE = 'expire_everything'

Values:

Value	Description
ISO 8601 duration	Data expires after the specified period (e.g., `P30D` for 30 days, `P1Y` for 1 year)
`retain_everything`	Keep all data indefinitely
`expire_everything`	Expire data immediately (effectively clears on each refresh)

ISO 8601 duration format:

P30D - 30 days
P6M - 6 months
P1Y - 1 year
P1Y6M - 1 year and 6 months

WRITE_MODE

Determines how data is written when the view refreshes. Syntax:

WRITE_MODE = 'overwrite'
WRITE_MODE = 'append'

Values:

Value	Description
`overwrite`	Replace all existing data with the new query result
`append`	Add new data to the existing dataset

When to use each:

overwrite: Use when you want a complete snapshot of the current state. Good for dashboards and reports where you always want the latest data.
append: Use when building a historical record. Good for event logs or time-series data where you want to accumulate data over time.

When using append mode, ensure your query includes appropriate filters to avoid inserting duplicate records.

EXTENDED_STATS

Controls whether additional statistics are computed for the dataset. Syntax:

EXTENDED_STATS = 'all'
EXTENDED_STATS = 'none'

Values:

Value	Description
`all`	Compute extended statistics including cardinality estimates, value distributions, and null counts
`none`	Skip extended statistics (faster refresh, less metadata)

Extended statistics help the query optimizer but increase refresh time. Use all for frequently queried views and none for large views where refresh speed is critical.

PARTITIONED_BY

Defines how the stored data is partitioned for improved query performance. Syntax:

PARTITIONED_BY <field> <transform>
PARTITIONED_BY <field1> <transform1>, <field2> <transform2>

Transforms:

Transform	Description	Example
`DAY`	Partition by day	`PARTITIONED_BY event_date DAY`
`MONTH`	Partition by month	`PARTITIONED_BY created_at MONTH`
`YEAR`	Partition by year	`PARTITIONED_BY order_date YEAR`

Partitioning improves query performance when filtering on the partitioned field. Choose a partition scheme that matches your most common query patterns.

MERGE ON

Defines upsert logic that matches incoming rows against existing data during refresh. Use MERGE ON to update existing records and insert new ones without creating duplicates. Syntax:

MERGE ON <condition>
WHEN MATCHED THEN UPDATE SET
  <column> = source.<column>,
  ...
WHEN NOT MATCHED THEN INSERT (<columns>) VALUES (<values>)

Auto-generated aliases:

Alias	Description
`source`	References columns from the incoming query result
`target`	References columns from the existing materialized view

Example:

MERGE ON target.user_id IS NOT DISTINCT FROM source.user_id
WHEN MATCHED THEN UPDATE SET
  email = source.email,
  last_seen = source.last_seen
WHEN NOT MATCHED THEN INSERT (user_id, email, last_seen)
VALUES (source.user_id, source.email, source.last_seen)

MERGE ON is only valid within CREATE MATERIALIZED VIEW statements. It cannot be used as a standalone command.

For step-by-step guidance and practical examples, see Incremental Upserts with MERGE ON.

Complete example

CREATE MATERIALIZED VIEW "female_audience"
REFRESH_SCHEDULE = '@daily'
DISPLAY_NAME = 'Female Audience'
DESCRIPTION = 'Audience containing demographic of female users.'
EXPIRE = 'retain_everything'
TAGS = ( '_nio_materialized_view', 'user_engagement', 'metrics' )
WRITE_MODE = 'overwrite'
EXTENDED_STATS = 'all'
PARTITIONED_BY event_date DAY
AS
SELECT
    "unique_identifier",
    "hl7_gender",
    "age"
FROM narrative.rosetta_stone
WHERE
    "hl7_gender"."gender" = 'female'
    AND "event_timestamp" > CURRENT_TIMESTAMP - INTERVAL 160 DAYS
    AND rosetta_stone._price_cpm_usd <= 2.00

This example creates a materialized view that:

Refreshes daily
Stores female user demographics from the last 160 days
Partitions data by day for efficient time-based queries
Retains all historical data indefinitely
Computes full statistics for query optimization

Creating Materialized Views

Step-by-step guide to creating your first materialized view

Incremental Upserts

Use MERGE ON for deduplication and incremental updates