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

# Materialized view

> Create, open, list and drop a table whose contents are defined by a query.

**Applies to:** Embedded ❌, Enterprise ✅

A materialized view is a table whose contents are defined by a query over
another table. The query is recorded in the view's schema, and a refresh
brings its stored rows up to date with the source.

The result is an ordinary table: queryable, indexable, searchable, and present
in `table_names()`. Nothing about reading it is special.

This page covers creating and managing one. Keeping it current is
[Refresh a materialized view](https://docs.lancedb.com/feature-engineering/refresh-materialized-view.md).

> [!NOTE]
>
> In SQL, see [`REFRESH MATERIALIZED VIEW`](https://docs.lancedb.com/sql/dml/refresh-materialized-view) and [`SHOW MATERIALIZED VIEWS`](https://docs.lancedb.com/sql/ddl/materialized-view#show).

## Create a materialized view

A materialized view is defined by a SQL query:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
db.execute_query("""
    CREATE MATERIALIZED VIEW frames_captioned AS
    SELECT frame_id, video_id, ts, caption
    FROM frames
    WHERE selected = true
""").read_all()
view = db.open_materialized_view("frames_captioned")
```

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE MATERIALIZED VIEW frames_captioned AS
SELECT frame_id, video_id, ts, caption
FROM frames
WHERE selected = true
```

See [Create a materialized view](https://docs.lancedb.com/feature-engineering/materialized-views.md#create)
for the full walkthrough, including joins and Functions in the query.

## Open, list and drop views

| | Python | TypeScript | SQL |
| - | - | - | - |
| create | `create_materialized_view` | `createMaterializedView` | `CREATE MATERIALIZED VIEW` |
| open | `open_materialized_view` | `openMaterializedView` | -- |
| list | `list_materialized_views` | `listMaterializedViews` | `SHOW MATERIALIZED VIEWS` |
| refresh | `refresh` | `refresh` | `REFRESH MATERIALIZED VIEW` |
| drop | `drop_materialized_view` | `dropMaterializedView` | `DROP MATERIALIZED VIEW` |

Enterprise Creating, refreshing and dropping a
materialized view each have an async, job-returning counterpart --
`create_materialized_view_async`, `view.refresh_async()`,
`drop_materialized_view_async` -- the same pattern as
[`refresh_column_async`](https://docs.lancedb.com/feature-engineering/refresh-column#async): each
returns a [job](https://docs.lancedb.com/basics/jobs.md) you `wait()` on instead of blocking, with the
same arguments as its synchronous counterpart, including
`auto_refresh_enabled` and `auto_refresh_interval_seconds`; see
[Auto Refresh](https://docs.lancedb.com/enterprise/auto-refresh.md).

> [!NOTE]
>
> On a local database, `list_materialized_views` reads table schemas to find
> views. On Enterprise, it asks the server for the view listing, as
> `SHOW MATERIALIZED VIEWS` does.

A view is dropped with `drop_materialized_view`, a dedicated operation, even
though a materialized view **is** a table underneath. That also means
dropping one removes stored rows, unlike dropping a
[view](https://docs.lancedb.com/management/view.md), which removes only a saved query.

## Read the view and its definition

`view.table` is the underlying table, for querying, indexing, or search.
`view.definition` has a single field, `query` -- the defining query, in the
canonical spelling the server stores. Both are properties; on an async view, the definition is read with
`await view.definition()`.

## Enterprise compatibility

Enterprise supports creating and refreshing materialized views on remote
compute. Use the SDK version supported by your deployment, or the
[SQL interface](https://docs.lancedb.com/sql/ddl/materialized-view.md).

## Privileges

A materialized view **is** a table (see [Dropping one](https://docs.lancedb.com/sql/ddl/materialized-view.md#drop)),
granted through the REST ACL API or SQL `GRANT`/`REVOKE` with the same
privilege names as a table, but two of them mean something different here:
the server refuses a direct `INSERT`, `UPDATE` or `DELETE` on a materialized
view's rows -- only a refresh writes them. `UPDATE` is instead what
authorizes calling refresh, and `OWNERSHIP` is what authorizes `ALTER`
(auto-refresh settings):

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
GRANT SELECT, UPDATE ON MATERIALIZED VIEW daily_rollup TO ROLE analysts
```

That grants `analysts` read access and the ability to refresh `daily_rollup`,
not row-level write access. See [Privileges](https://docs.lancedb.com/governance/privileges.md#sql) for
the full statement syntax and every target it reaches.

## Related guides

> [card] [Refresh a materialized view](https://docs.lancedb.com/feature-engineering/refresh-materialized-view.md)
>
> How a refresh works, and what it writes.

> [card] [Views](https://docs.lancedb.com/management/view.md)
>
> The non-materialized counterpart, defined in SQL.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.