> ## Documentation Index
> Fetch the complete documentation index at: https://lancedb-bcbb4faf-mintlify-4386f3f8.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Materialized views

> Define derived tables over a source table in LanceDB and keep them fresh with incremental refresh in Python and TypeScript.

A materialized view is a table defined by a query over a source table. LanceDB
records the query in the view's schema at creation time, computes the rows when
you call `refresh()`, and updates them incrementally as the source changes.

Use a materialized view when you want a persisted, queryable projection of a
source table, for example a filtered subset, a set of derived columns, or a
capped sample. Once refreshed, the view is a normal table, so you can search,
index, and scan it like any other.

<Note>
  Materialized views are available in the LanceDB Python and TypeScript clients
  on local databases. Remote (`db://`) connections raise the core's not-supported
  error up front (Python: `NotImplementedError`).
</Note>

<Info>
  This page covers the OSS materialized-view API on a plain LanceDB connection.
  If you are looking for Geneva's UDF-driven materialized views used to backfill
  expensive columns, see [Materialized views with UDFs](/geneva/jobs/materialized-views).
</Info>

## Prerequisites

The source table must have stable row IDs. LanceDB uses them to track which
source rows a view has already materialized, so incremental refresh can survive
source compactions.

Enable stable row IDs when the source table is created. Stable row IDs cannot
be enabled on a table that already exists.

<CodeGroup>
  ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  import lancedb

  db = lancedb.connect(
      "./.lancedb",
      storage_options={"new_table_enable_stable_row_ids": "true"},
  )
  db.create_table(
      "people",
      [
          {"name": "ada", "age": 36},
          {"name": "kid", "age": 7},
          {"name": "grace", "age": 85},
      ],
  )
  ```

  ```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  import { connect } from "@lancedb/lancedb";

  const db = await connect("./.lancedb");
  await db.createTable(
    "people",
    [
      { name: "ada", age: 36 },
      { name: "kid", age: 7 },
      { name: "grace", age: 85 },
    ],
    { storageOptions: { newTableEnableStableRowIds: "true" } },
  );
  ```
</CodeGroup>

## Create a view

Call `create_materialized_view` (Python) or `createMaterializedView` (TypeScript)
on the connection.

* `select` accepts column names, `(alias, SQL expression)` pairs, or a dict /
  record of the same. A bare column name is quoted as an identifier, so column
  names with spaces or reserved words work. Omit `select` to project every
  source column.
* `where` is a SQL predicate. Only matching source rows appear in the view.
* `limit` caps the view at that many rows.

The view is created empty. Its query is recorded in the view's schema metadata,
so reopening the view later does not require any side channel.

<CodeGroup>
  ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  view = db.create_materialized_view(
      "adults",
      "people",
      select=["name", ("shout", "upper(name)")],
      where="age >= 18",
  )
  ```

  ```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  const view = await db.createMaterializedView("adults", "people", {
    select: ["name", ["shout", "upper(name)"]],
    where: "age >= 18",
  });
  ```
</CodeGroup>

## Refresh the view

`refresh()` computes the view from its source. LanceDB picks between two modes:

* **Incremental**: apply only the source rows added, changed, or removed since
  the last refresh. Chosen when the source's changes can be reconciled into the
  view.
* **Rebuild**: recompute the view from scratch. Chosen on the first refresh or
  when the source has changed in ways incremental refresh cannot reconcile
  (for example, an update on legacy-storage data).

<CodeGroup>
  ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  result = view.refresh()
  print(result.mode)          # "rebuild" on first refresh
  print(result.rows_written)  # 2
  ```

  ```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  const result = await view.refresh();
  console.log(result.mode);          // "rebuild" on first refresh
  console.log(result.rowsWritten);   // 2
  ```
</CodeGroup>

`refresh()` returns a `RefreshMaterializedViewResult` with:

| Python field     | TypeScript field | Description                                                      |
| ---------------- | ---------------- | ---------------------------------------------------------------- |
| `mode`           | `mode`           | `"rebuild"`, `"incremental"`, or `"no_op"` when nothing changed. |
| `rows_written`   | `rowsWritten`    | Rows written by this refresh.                                    |
| `source_version` | `sourceVersion`  | Version of the source table this refresh reflects.               |
| `version`        | `version`        | New version of the view.                                         |

Force a full rebuild by passing `full=True` (Python) or `{ full: true }`
(TypeScript). Refresh against a specific source version with `source_version=`
or `{ sourceVersion: N }`.

<CodeGroup>
  ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  view.refresh(full=True)
  view.refresh(source_version=7)
  ```

  ```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  await view.refresh({ full: true });
  await view.refresh({ sourceVersion: 7 });
  ```
</CodeGroup>

Concurrent refreshes of the same view do not duplicate rows. If two refreshes
plan the same source rows, the second one to commit conflicts and raises
instead of writing the rows again.

## Query a view

The view's underlying table is available as `view.table` in Python (a
`LanceTable`) and `view.table()` in TypeScript (a `Table`). Query, index, and
search it like any other table.

<CodeGroup>
  ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  rows = view.table.search().to_list()
  print(sorted(row["shout"] for row in rows))  # ["ADA", "GRACE"]
  ```

  ```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  const rows = await view.table().query().toArray();
  console.log(rows.map((r) => r.shout).sort()); // ["ADA", "GRACE"]
  ```
</CodeGroup>

Writes to the view's underlying table are not blocked, but a rebuild replaces
them. Treat the view as read-only outside of `refresh()`.

## Open an existing view

`open_materialized_view` / `openMaterializedView` returns a handle whose
definition is read back from the stored schema. Opening a table that is not a
materialized view raises an error.

<CodeGroup>
  ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  view = db.open_materialized_view("adults")
  print(view.definition)
  # MaterializedViewDefinition(source_table='people',
  #     projections=[('name', '`name`'), ('shout', 'upper(name)')],
  #     filter='age >= 18', limit=None, inputs=['age', 'name'])
  ```

  ```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  const view = await db.openMaterializedView("adults");
  const definition = await view.definition();
  console.log(definition);
  // {
  //   sourceTable: 'people',
  //   projections: [['name', '`name`'], ['shout', 'upper(name)']],
  //   filter: 'age >= 18',
  //   limit: undefined,
  //   inputs: ['age', 'name'],
  // }
  ```
</CodeGroup>

`list_materialized_views` / `listMaterializedViews` returns the names of every
materialized view in the database. It reads every table's schema, so it costs
one open per table.

<CodeGroup>
  ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  db.list_materialized_views()  # ["adults"]
  ```

  ```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  await db.listMaterializedViews(); // ["adults"]
  ```
</CodeGroup>

## Async Python API

The same operations are available on Python's `AsyncConnection`, and return
`AsyncMaterializedView`. `definition()` and `refresh()` are coroutines. The
TypeScript API is already async and matches the examples above.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
import lancedb

db = await lancedb.connect_async(
    "./.lancedb",
    storage_options={"new_table_enable_stable_row_ids": "true"},
)
view = await db.create_materialized_view(
    "shouts",
    "people",
    select=[("shout", "upper(name)")],
)
result = await view.refresh()
print(result.mode, result.rows_written)

reopened = await db.open_materialized_view("shouts")
definition = await reopened.definition()
print(await db.list_materialized_views())
```