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.
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:
Copy code
-- 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.
Related terms
A view is a virtual table based on the result of a SQL query.
CREATE TABLE AS SELECT (CTAS) →CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.
Denormalized table →A denormalized table is a table that intentionally stores redundant, pre-joined data from multiple normalized sources, trading storage and update simplicity for faster, simpler reads.
table →A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.
CREATE TABLE statement →The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…
Temporary table →A temporary table is a table scoped to a single session (or transaction) that is automatically dropped when the session ends, used for intermediate results that don't need to persist.
FAQS
No. DuckDB's CREATE VIEW always creates a virtual view that re-runs its query on every read. To get materialized-view behavior, you emulate it with CREATE OR REPLACE TABLE ... AS SELECT, refreshed on your own schedule.
A view stores only the query definition and recomputes results on every read. A materialized view stores the actual result set physically, so reads are fast, but the stored data can go stale and needs an explicit or automatic refresh.
