---
title: "Materialized view"
description: "A materialized view stores the physical, precomputed result of a query as if it were a table, so reads are fast, at the cost of needing an explicit refresh when the underlying data changes."
canonical: "https://motherduck.com/glossary/materialized-view/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "STORAGE_INFO views | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/storage_info/"
  - title: "Results | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/results/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# Materialized view

> A materialized view stores the physical, precomputed result of a query as if it were a table, so reads are fast, at the cost of needing an explicit refresh when the underlying data changes.

## Overview

A regular view is just a saved query: every time you select from it, the database re-runs the underlying SQL from scratch. A **materialized** view instead stores the query's result set physically, like a table, so reading from it is as fast as reading any other table — the cost is that the stored result goes stale as soon as the underlying data changes, and needs to be refreshed (fully or incrementally) to stay current. Many warehouse engines (Postgres, Snowflake, BigQuery) support `CREATE MATERIALIZED VIEW` with either manual or automatic refresh.

<glossary-callout guide="duckdb-cheatsheet-full" />

### DuckDB doesn't have native materialized views

DuckDB does not currently support a `CREATE MATERIALIZED VIEW` statement or automatic view refresh. `CREATE VIEW` in DuckDB always creates a virtual view — its query re-runs on every read, exactly like a standard SQL view. To get materialized-view behavior in DuckDB (or MotherDuck), you emulate it directly with a real table, rebuilt on your own schedule:

```sql
-- Emulate a materialized view with CREATE OR REPLACE TABLE ... AS SELECT
CREATE OR REPLACE TABLE daily_revenue_mv AS
SELECT date_key, region, SUM(revenue) AS revenue
FROM fact_sales
JOIN dim_customer USING (customer_key)
GROUP BY ALL;

-- Reads are as fast as any table
SELECT * FROM daily_revenue_mv WHERE region = 'EMEA';

-- "Refresh" is just re-running the same statement, e.g. on a schedule
CREATE OR REPLACE TABLE daily_revenue_mv AS
SELECT date_key, region, SUM(revenue) AS revenue
FROM fact_sales
JOIN dim_customer USING (customer_key)
GROUP BY ALL;
```

This `CREATE OR REPLACE TABLE ... AS SELECT` (CTAS) pattern is the standard way to get materialized-view semantics in DuckDB: fast, table-like reads, with the refresh made explicit and controllable rather than automatic. It's typically driven by a scheduler or orchestration tool (a cron job, a dbt run, a MotherDuck scheduled query) rather than the database itself.

### When to reach for this pattern

The trade-off is the same as any materialized view: rebuild frequency versus freshness. A dashboard aggregation that changes hourly doesn't need to be recomputed on every single query — refreshing the underlying table every 15 minutes with CTAS, rather than joining and aggregating raw fact and dimension tables on every read, can turn a multi-second dashboard query into an instant one.