How to Refresh a Materialized View in ClickHouse
Last updated August 20, 2026 · By the SaturnSQL team
Classic ClickHouse materialized views never refresh: they are insert triggers that only transform new rows. To recompute on a schedule, create a refreshable materialized view with REFRESH EVERY, or trigger one now with SYSTEM REFRESH VIEW. To fix history in a classic view, backfill the target table yourself.
CREATE MATERIALIZED VIEW daily_revenue
REFRESH EVERY 1 HOUR
ENGINE = MergeTree
ORDER BY day
AS
SELECT
toDate(event_time) AS day,
sum(amount) AS revenue
FROM payments
GROUP BY day;Refresh on demand
SYSTEM REFRESH VIEW starts a refresh immediately instead of waiting for the schedule. SYSTEM WAIT VIEW blocks until it finishes, which is useful in scripts that read the view right after.
SYSTEM REFRESH VIEW daily_revenue;
SYSTEM WAIT VIEW daily_revenue;Check refresh status
Every refreshable view reports its last and next refresh in system.view_refreshes, including progress and the exception if the last run failed.
SELECT view, status, last_success_time, next_refresh_time
FROM system.view_refreshes;Classic materialized views do not refresh
A regular materialized view runs its SELECT only against blocks being inserted into the source table. Changing the view's query, or updating old rows in the source, does not recompute anything. To correct history, insert the recomputed rows into the view's target table for the affected period yourself.
INSERT INTO daily_revenue_target
SELECT
toDate(event_time) AS day,
sum(amount) AS revenue
FROM payments
WHERE event_time >= '2026-08-01' AND event_time < '2026-09-01'
GROUP BY day;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free