Materialized views

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.

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).

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.

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.

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},
    ],
)

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" } },
);

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.

view = db.create_materialized_view(
    "adults",
    "people",
    select=["name", ("shout", "upper(name)")],
    where="age >= 18",
)

const view = await db.createMaterializedView("adults", "people", {
  select: ["name", ["shout", "upper(name)"]],
  where: "age >= 18",
});

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).

result = view.refresh()
print(result.mode)          # "rebuild" on first refresh
print(result.rows_written)  # 2

const result = await view.refresh();
console.log(result.mode);          // "rebuild" on first refresh
console.log(result.rowsWritten);   // 2

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 }.

view.refresh(full=True)
view.refresh(source_version=7)

await view.refresh({ full: true });
await view.refresh({ sourceVersion: 7 });

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.

rows = view.table.search().to_list()
print(sorted(row["shout"] for row in rows))  # ["ADA", "GRACE"]

const rows = await view.table().query().toArray();
console.log(rows.map((r) => r.shout).sort()); // ["ADA", "GRACE"]

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.

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'])

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'],
// }

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.

db.list_materialized_views()  # ["adults"]

await db.listMaterializedViews(); // ["adults"]

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

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())

Get started

Model training

Guides

Feature Engineering (Geneva)

Support

Materialized views

Prerequisites

Create a view

Refresh the view

Query a view

Open an existing view

Async Python API

​Prerequisites

​Create a view

​Refresh the view

​Query a view

​Open an existing view

​Async Python API

Prerequisites

Create a view

Refresh the view

Query a view

Open an existing view

Async Python API