Implementing ClickHouse Materialized Views with Refresh Policies for Real‑Time Analytics

Desired Outcome

By the end of this guide you will be able to:

• Create a MergeTree‑based source table and a materialized view (MV) that aggregates its data.
• Attach a refresh policy (ON INSERT, ON UPDATE, or a scheduled cron‑style job) so the MV stays current.
• Verify that the MV contains the expected rows and that refresh events are logged.
• Identify and recover from common refresh failures.

Prerequisites

• ClickHouse 20.9 or newer (refresh policies were added in 20.9).
• Access to a ClickHouse client (e.g., clickhouse-client) with a user that has CREATE TABLE, ALTER TABLE, and SELECT privileges on the target database.
• At least one MergeTree‑based table that will serve as the source for the MV.
• Basic understanding of ClickHouse primary keys and how they affect pruning during refresh.

Creating a Materialized View

First, create a simple source table. The example uses MergeTree with a primary key on event_date:

# Run on the clickhouse client
CREATE TABLE IF NOT EXISTS analytics.events
(
    event_id UInt64,
    event_date Date,
    user_id UInt64,
    action String
)
ENGINE = MergeTree(event_date, (event_id), 8192);

Insert a few rows so the MV has data to materialize:

INSERT INTO analytics.events VALUES
(1, '2026-09-27', 1001, 'click'),
(2, '2026-09-27', 1002, 'view'),
(3, '2026-09-28', 1001, 'purchase');

Now create the MV that aggregates daily click counts:

CREATE MATERIALIZED VIEW IF NOT EXISTS analytics.daily_clicks
ENGINE = SummingMergeTree(event_date, (user_id), 8192)
AS
SELECT
    event_date,
    user_id,
    count(*) AS click_count
FROM analytics.events
WHERE action = 'click'
GROUP BY event_date, user_id;

Key points:

• The MV’s engine must be a MergeTree variant (e.g., SummingMergeTree, AggregatingMergeTree).
• The primary key of the MV (here event_date) must match the source’s primary key or at least be a prefix of it to allow efficient pruning.
• Because the MV is created without a refresh policy, it will initially populate from existing rows and then refresh only on new inserts.

Configuring a Refresh Policy

To keep the MV up to date automatically, attach a refresh policy. The syntax is:

ALTER TABLE <mv_name> SET
    MATERIALIZED VIEW <mv_name>
    WITH REFRESH POLICY =
        (ON INSERT | ON UPDATE)  -- or a cron expression
        [WHEN <condition>];

Example: refresh on every insert into the source table:

ALTER TABLE analytics.daily_clicks
SET MATERIALIZED VIEW analytics.daily_clicks
WITH REFRESH POLICY = (ON INSERT);

Or schedule a daily refresh at midnight:

ALTER TABLE analytics.daily_clicks
SET MATERIALIZED VIEW analytics.daily_clicks
WITH REFRESH POLICY = ('0 0 * * *');

When you set a cron‑style policy, ClickHouse will enqueue a background job that runs the MV’s SELECT query at the specified times.

Validating the Materialized View

After creation, confirm the MV exists and contains data:

# List tables in the database
SELECT name, engine FROM system.tables
WHERE database = 'analytics' AND name = 'daily_clicks';

Query the MV to compare with the source SELECT:

SELECT * FROM analytics.daily_clicks;

For manual refresh testing, run:

ALTER TABLE analytics.daily_clicks REFRESH;

Then check the refresh log:

SELECT * FROM system.mv_refresh_log
WHERE mv_name = 'analytics.daily_clicks'
ORDER BY event_time DESC
LIMIT 5;

Expected output includes a status of SUCCESS and the timestamp of the last refresh.

Monitoring Refreshes

Refresh events are recorded in system.mv_refresh_log. A useful snapshot table:

mv_name	event_time	status	message
analytics.daily_clicks	2026-09-27 12:00:00	SUCCESS	Manual refresh
analytics.daily_clicks	2026-09-27 00:00:00	SUCCESS	Scheduled refresh

Use system.mv_create_log to see MV creation history.

Common Pitfalls and Mitigations

• Schema Mismatch: If the source table’s schema changes (e.g., adding a column used in the MV’s SELECT), the refresh will fail. Verify the SELECT still compiles before applying changes.
• Primary Key Divergence: A different primary key between source and MV forces a full scan on each refresh, hurting performance. Align keys or add a PRIMARY KEY that covers the MV’s grouping columns.
• Write Load: Frequent refreshes can overwhelm the cluster. Test refresh intervals against cluster capacity; consider using ON INSERT only when new data arrives.
• Missing Permissions: The user executing ALTER TABLE must have CREATE TABLE and ALTER TABLE rights on the MV database.

Recovery Options

If a refresh fails or the MV becomes stale, you can:

• Drop and Recreate:

  DROP TABLE IF EXISTS analytics.daily_clicks;
  -- Recreate using the steps above.
  

• Force a Refresh:

  ALTER TABLE analytics.daily_clicks REFRESH;
  

• Check Logs:

  SELECT * FROM system.mv_refresh_log
  WHERE mv_name = 'analytics.daily_clicks'
  ORDER BY event_time DESC
  LIMIT 10;
  

  This shows error messages if the refresh failed.

Always validate after recovery by querying the MV and comparing with a fresh SELECT from the source table.

Limitations and Practical Checks

• Refresh policies are only supported on ClickHouse 20.9+. Verify your version with SELECT version();.
• The MV engine must be a MergeTree family; other engines will reject the creation.
• Refresh logs are retained for a limited time (configurable via mv_refresh_log_retention_period).
• When using cron expressions, ensure the server’s timezone matches your scheduling expectations.

Practical check: After setting a cron policy, run SELECT * FROM system.mv_refresh_log WHERE mv_name = 'analytics.daily_clicks'; to confirm that entries appear at the scheduled times.