> ## Documentation Index
> Fetch the complete documentation index at: https://docs.narrative.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Materialized View Syntax

> Complete reference for CREATE MATERIALIZED VIEW statement

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

```sql theme={null}
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:**

```sql theme={null}
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:

```sql theme={null}
REFRESH_SCHEDULE = '15 15 * * 1'
```

<Note>
  For large datasets, consider using [Incremental View Maintenance](/concepts/nql/incremental-view-maintenance) to refresh only changed data.
</Note>

***

## DISPLAY\_NAME

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

**Syntax:**

```sql theme={null}
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:**

```sql theme={null}
DESCRIPTION = 'Aggregated sales metrics by month for the executive dashboard'
```

***

## EXPIRE

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

**Syntax:**

```sql theme={null}
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

***

## TAGS

Labels for organizing and filtering materialized views.

**Syntax:**

```sql theme={null}
TAGS = ( '_nio_materialized_view', 'sales', 'monthly' )
```

Tags help categorize views and can be used to filter views in the UI or via API.

***

## WRITE\_MODE

Determines how data is written when the view refreshes.

**Syntax:**

```sql theme={null}
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.

<Warning>
  When using `append` mode, ensure your query includes appropriate filters to avoid inserting duplicate records.
</Warning>

***

## EXTENDED\_STATS

Controls whether additional statistics are computed for the dataset.

**Syntax:**

```sql theme={null}
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:**

```sql theme={null}
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:**

```sql theme={null}
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:**

```sql theme={null}
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)
```

<Warning>
  MERGE ON is only valid within `CREATE MATERIALIZED VIEW` statements. It cannot be used as a standalone command.
</Warning>

<Tip>
  For step-by-step guidance and practical examples, see [Incremental Upserts with MERGE ON](/guides/nql/incremental-upserts).
</Tip>

***

## Complete example

```sql theme={null}
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

***

## Output column types

Every column produced by the `SELECT` must resolve to a type the materialized view store can persist: `STRING`, `BOOLEAN`, `LONG`, `DOUBLE`, `DATE`, `TIMESTAMP`, and nested `ARRAY`/`STRUCT` of those types. Types such as `DECIMAL`, `BINARY`, `INTERVAL`, and `MAP` — as well as intermediate expression results that resolve to them — are not supported as stored output.

Validation rejects unsupported output types before the view is created. `/nql/validate`, `/nql/compile`, and `/nql/run` return a `422 Unsupported Type Error` naming the offending column and its type, and nested types report the innermost unsupported type (for example, `ARRAY<DECIMAL>` points at `DECIMAL`). `CAST` the column to a supported type in the `SELECT` to resolve the error — for example, `CAST(price AS DOUBLE) AS price`.

<Note>
  Plain `SELECT` queries run through `/nql/run` are not subject to this restriction — the output-type check only applies when the result is being materialized into a stored view.
</Note>

***

<Note>
  If you need a lightweight query abstraction that re-evaluates at query time instead of storing results, consider using a [view dataset](/concepts/nql/materialized-views#view-datasets) instead. View datasets are created through the SDK or API with the `create_as_view` option rather than through NQL syntax.
</Note>

## Related content

<CardGroup cols={2}>
  <Card title="Creating Materialized Views" icon="layer-plus" href="/guides/nql/creating-materialized-views">
    Step-by-step guide to creating your first materialized view
  </Card>

  <Card title="Incremental Upserts" icon="code-merge" href="/guides/nql/incremental-upserts">
    Use MERGE ON for deduplication and incremental updates
  </Card>

  <Card title="Materialized Views" icon="layer-group" href="/concepts/nql/materialized-views">
    Understand when and why to use materialized views
  </Card>

  <Card title="Incremental View Maintenance" icon="arrows-rotate" href="/concepts/nql/incremental-view-maintenance">
    Learn how NQL optimizes view refreshes
  </Card>
</CardGroup>