SQL Materialized Views and Snapshots

A materialized view is a query whose result the database stores, together with the query itself, so the result can be refreshed later. A view stores only the query and runs it every time; a table stores only rows and forgets where they came from. A materialized view sits between the two: reads are as fast as a table, and the definition stays in the database.

The trade is freshness. Between refreshes, a materialized view shows the data as it was when it was last built.

When to use one

• A summary that is expensive to compute and read often: a dashboard's daily revenue, built from millions of order lines.
• A result that should stay fixed while people look at it, such as the numbers a monthly report was signed off on.
• A slow query over another system's data, copied in once instead of fetched on every read.

If the underlying query is fast, a plain view is simpler and is never out of date.

How other databases write it

In PostgreSQL, you create it once and refresh it on command:

create materialized view category_revenue as
select c.category_name,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items as oi
join products as p on p.product_id = oi.product_id
join categories as c on c.category_id = p.category_id
group by c.category_name;

refresh materialized view category_revenue;

The other engines differ mainly in how the refresh happens:

DuckDB: a snapshot table

DuckDB does not parse CREATE MATERIALIZED VIEW at all:

create materialized view category_revenue as
select 1

It fails with Parser Error: syntax error at or near "materialized". The working pattern has two parts: a view that holds the definition, and a table built from it with CREATE TABLE AS SELECT that holds the stored result.

create view category_revenue_live as
select c.category_name,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items as oi
join products as p on p.product_id = oi.product_id
join categories as c on c.category_id = p.category_id
group by c.category_name

create or replace table category_revenue_snapshot as
select *,
       strftime(now(), '%Y-%m-%d %H:%M') as refreshed_at
from category_revenue_live

The snapshot has eight rows, one per category, and a refreshed_at column that says how old they are. Now record a sale: two pairs of Pruning Shears added to order 24, at $18 each.

insert into order_items (order_item_id, order_id, product_id, quantity, unit_price)
values (49, 24, 9, 2, 18.0)

select l.category_name,
       l.revenue as live,
       s.revenue as snapshot
from category_revenue_live as l
join category_revenue_snapshot as s using (category_name)
where l.revenue <> s.revenue

One row: Tools, 115 live and 79 in the snapshot. The view already counts the new line; the snapshot will not until it is rebuilt. Refreshing it is the same statement that built it:

create or replace table category_revenue_snapshot as
select *,
       strftime(now(), '%Y-%m-%d %H:%M') as refreshed_at
from category_revenue_live

Run the comparison again and it returns no rows. Because the rebuild is one CREATE OR REPLACE statement, there is no moment when the snapshot is missing. A DROP TABLE followed by a CREATE TABLE leaves a gap in which anything that reads it fails with Table with name ... does not exist!

Refreshing part of a snapshot

Rebuilding everything is simple and always correct. When the snapshot is large and only recent data changes, a partial refresh replaces just the affected rows. Oracle's fast refresh does this for you; in DuckDB you write it. Start with a monthly snapshot:

create view monthly_revenue_live as
select date_trunc('month', o.order_date) as month,
       sum(oi.quantity * oi.unit_price) as revenue
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by all;

create or replace table monthly_revenue as
select * from monthly_revenue_live

Six months, January to June 2024. To refresh only June, delete its row and insert it again, inside a transaction so nobody sees the month missing:

begin;

delete from monthly_revenue
where month = date '2024-06-01';

insert into monthly_revenue
select *
from monthly_revenue_live
where month = date '2024-06-01';

commit

That is correct only if nothing outside June changed. Order 24 was placed in January, so a correction to it lands in a month the refresh never looks at:

insert into order_items (order_item_id, order_id, product_id, quantity, unit_price)
values (50, 24, 10, 1, 9.5);

begin;
delete from monthly_revenue where month = date '2024-06-01';
insert into monthly_revenue
select * from monthly_revenue_live where month = date '2024-06-01';
commit

select l.month,
       l.revenue as live,
       s.revenue as snapshot
from monthly_revenue_live as l
join monthly_revenue as s using (month)
where l.revenue <> s.revenue

January shows 186 live and 176.5 in the snapshot. A partial refresh needs a rule for which rows can change (for example, "orders can be corrected for 35 days"), and refreshes that whole window. A comparison like this one, run after each refresh, is cheap insurance.

What can go wrong

• Nobody knows how old it is. A snapshot without a refreshed_at column looks exactly like live data. Store the time, and show it wherever the numbers appear.
• Nothing remembers to refresh it. A materialized view in PostgreSQL does not refresh itself, and a DuckDB snapshot is only as current as the last run of your script. Put the refresh in the same script or job that loads the data.
• A guarded create keeps the old result. CREATE TABLE IF NOT EXISTS ... AS SELECT does nothing when the table exists, without an error, so a refresh written that way never refreshes. Use CREATE OR REPLACE TABLE.
• The snapshot has no rules. CREATE TABLE AS SELECT copies rows and column types, not primary keys or NOT NULL. See table constraints if the snapshot needs them.
• A refresh that blocks readers. PostgreSQL's plain refresh materialized view locks out queries until it finishes; concurrently avoids that but needs a unique index.