Refreshable materialized views in ClickHouse: when a scheduled rebuild beats incremental views
A refreshable materialized view stores the result of a query and rebuilds it on a schedule, set with REFRESH EVERY, and it can add rows to the target table with APPEND instead of replacing it. Teams reach for this feature when their self-managed ClickHouse cluster needs a result that incremental materialized views cannot express, such as a query with joins across tables that change at different times, window logic that depends on rows arriving later, or a full recomputation over a historical window. An *incremental* materialized view only reacts to the blocks inserted into its source table, so any logic that needs the complete source set falls outside what it can do.
Before you create one, decide three things. First, whether the target should be fully replaced on each run or appended to, which depends on whether the result is a snapshot of current state or a growing history. Second, whether the query depends on other refreshable views, because the order in which they rebuild determines whether a dependent view reads fresh or stale data. Third, when the rebuild runs. A heavy refresh competes with ingestion and dashboards for CPU, memory and disk, so schedule it for a window with low load and confirm, on a copy of your data, how long the rebuild takes at your volume. Check the exact syntax, refresh options and dependency behavior against the version you run, since the refreshable view documentation describes them in detail.
Which refresh mode fits which query
This table compares full replace, the default behavior where each refresh rebuilds the target, with APPEND, which adds new rows to the target. Read each row as a question about your query: whether its result can be rebuilt in full, whether old rows stay valid, and what each scheduling choice costs in load and freshness. Confirm exact clauses and defaults against the documentation for your ClickHouse version before you rely on them.
| Decision point | Full replace | APPEND |
|---|---|---|
| What the target holds after a refresh | Only the latest result of the query, so earlier rows disappear | Earlier rows stay and the new result rows are added to them |
| Fits queries that | Compute a complete answer from the source tables, such as a top-N ranking or a current-state summary | Produce new rows for a time window or batch that never changes once written, such as a per-hour rollup |
| Risk of duplicate rows | Low, because each refresh replaces the whole target | High if the query re-selects rows already appended, so the window must be bounded |
| Cost of each refresh | Scales with the full query result on every run | Scales with the new window, if the query filters to it |
| Dependence on source history | Needs the source to contain everything the result should show | Needs the window logic to match how the source receives late or corrected rows |
| Effect of a failed refresh | The previous result usually remains until a successful run replaces it, so confirm this for your version | Partial appends can be left behind, so confirm how failures interact with the window before relying on it |
| Scheduling choice that matters most | Refresh interval sets how stale the result can be and how often the full cost is paid | Refresh interval sets the append cadence, and a longer window reduces overlap risk but delays rows |
| Heavy refreshes | Schedule off-peak when the full rebuild competes with ingestion and dashboards | Keep appended batches small enough that off-peak scheduling still leaves room for normal load |
Chaining views and the risks of dependent refreshes
A refreshable view that reads from another view or from a base table inherits that source's timing, so the order of rebuilds becomes a design decision. The cards below cover the choices that determine whether a chain returns correct, current results on a cluster that is also serving queries and inserts.
Map the dependency order
Write down which view reads from which table or view before setting any schedule. A downstream view that rebuilds before its upstream source has finished will publish results built from the previous state. Confirm in your own environment which dependencies the scheduler respects and which it does not, and test the order on a copy of the chain with representative data.
Staleness compounds along the chain
Each link in a chain adds the age of its input to its own refresh interval, so a final view can be older than any single schedule suggests. Record the expected freshness of every view in the chain, then decide whether downstream consumers can accept that lag. Dashboards that imply real-time figures need an explicit note or a shorter chain.
Avoid overlapping refresh runs
A refresh that runs longer than its interval can overlap with the next one, competing for CPU, memory and disk with inserts and interactive queries. Compare each view's measured run time with its schedule, and confirm how your ClickHouse version handles a refresh that is still running when the next one is due. Overlap is most likely on a busy shared cluster.
Choose full replace or append per view
Each view in the chain needs a decision on whether a refresh replaces the target table or appends rows. Full replacement keeps the target consistent with the source query, while APPEND adds rows and suits data that only grows. Mixing the two in one chain makes it harder to reason about what a downstream view sees after each run.
Schedule heavy links off-peak
Place the most expensive refresh in the chain in a quiet window, and keep lighter links close to their sources. Staggering start times across related views reduces contention, but it does not replace measuring run times under real load. Review the schedule whenever data volume grows or a query changes.
Verify results after each chain run
Add a check that compares the final view with its source after a chain completes, such as row counts or totals for a known period. Without it, a stale or partial upstream result can pass silently into every downstream view. Keep the check simple enough to run on every schedule and alert when it fails.
How to set up a refreshable view safely on a test cluster first
This sequence moves a refreshable materialized view from a copy of production data to a scheduled job in production, with a check at each step so that a heavy or incorrect rebuild is caught before it runs on the live cluster. It follows the ClickHouse production FAQ recommendation to validate changes in a pre-production environment with representative data and queries.
Confirm the query on representative data
Load a copy of the source tables into a pre-production ClickHouse environment, using data that matches production in volume, column types and skew as closely as you can. Run the SELECT that the view will use by hand and confirm it returns the rows and aggregates your consumers expect. Check that the result is stable across two runs on the same data. Done means the query output has been reviewed by the team that owns the dashboard or report, not only by the engineer who wrote it.
Define the target schema and refresh mode
Write the target table definition, including engine, ordering key and column types, and decide whether each refresh replaces the target contents or appends new rows with APPEND. Replacement suits results that are fully recomputed each time, while APPEND suits results where earlier rows must stay. Record the choice and the reason in the change ticket. Done means the target DDL and the refresh mode are written down and reviewed by someone other than the author.
Create the view on the test cluster
Create the refreshable materialized view with a REFRESH EVERY clause on the pre-production cluster only, and do not start its schedule yet. Confirm the view appears in the system tables for views and that its target table exists with the expected schema. Done means the object definitions on the test cluster match the written design with no leftover objects from earlier attempts.
Run the refresh manually
Trigger one refresh by hand and watch it finish. Confirm the target table contains the expected row count and that a sample of rows matches the result of the query you validated in the first step. If you use APPEND, run the refresh a second time and confirm rows are added once per run and nothing is duplicated unexpectedly. Done means one manual refresh completes without error and the output matches the reference query.
Check refresh status and resource use
Read the system tables that track refreshable views to see the last refresh status, its start and finish times, and any exception text. Then check query log entries for the refresh to see rows read, memory used and elapsed time. Compare these figures with the capacity of the production nodes, not only the test nodes. Done means you have recorded the duration, peak memory and rows read for one refresh, and the team agrees whether those figures fit the planned off-peak window.
Test dependencies and failure behavior
If other views read from this target or this view reads from another refreshable view, trigger the upstream refresh and confirm the downstream result updates in the order you intend. Then force a failure, such as a query that exceeds a memory limit on the test cluster, and confirm the status shows the error and the previous target contents remain usable. Done means you have seen a failed refresh report clearly and know how the team will detect it in production.
Enable the schedule and monitor the first runs
Apply the schedule in production only after the test results are accepted, and choose a window that matches the refresh cost you recorded, scheduling heavy refreshes off-peak. Watch the first scheduled runs in the status tables and the query log, and confirm they match the manual run. Done means several scheduled refreshes complete with the expected results and resource use, and an alert or review routine for failed refreshes is in place.
Scheduling heavy refreshes off-peak without starving dashboards
The quiet window for a refresh has to come from your own cluster's history, not from a generic assumption that nights are idle. Start by querying system.query_log over several weeks and grouping query volume, read rows and peak memory by hour and by user or query kind. Separate ingestion (inserts and merges), dashboard traffic (usually a small number of recurring read users) and ad hoc analysis. Repeat the check for each environment, because a staging cluster with synthetic load says little about when production dashboards are busiest. Month-end, batch jobs in upstream systems and regional business hours can all move the window, so look for patterns across more than one cycle before you set a REFRESH EVERY schedule.
Heavy refreshes compete with the other workloads for the same CPU, disk I/O and memory, and a full replace of a large target table can also create a burst of parts and merges that slows inserts. A refresh that runs long enough to overlap the next scheduled run or the start of the business day will hurt dashboards even if it starts in a quiet hour. Confirm how your ClickHouse version handles a refresh that is still running at its next scheduled time and whether overlapping refreshes are allowed, since this determines the worst case you need to plan for. Measure the refresh's own duration and resource use in system.query_log on a copy of production data first.
If refreshes must run during shared hours, workload scheduling can limit their share of resources so dashboards and ingestion keep priority. Confirm which resource controls your version supports and whether the refresh's queries can be assigned to a dedicated workload before you rely on them. Limits are a safety net, not a substitute for the measured window: a refresh that is throttled to finish at 3 a.m. still needs a schedule that avoids the morning peak. Record the chosen window, the measured peak, and the expected refresh duration in your runbook so that a schedule change is a reviewed decision.
Access, replication and recovery for refreshed tables
A refreshable view is a query that writes to a target table on a schedule, so it inherits the access, replication and backup questions of any table plus a few of its own. The cards below cover who may create and alter these views, how a refresh behaves across replicas, and what a backup does and does not protect.
Who may create and alter views
Creating a refreshable view requires rights on the source tables, the target table and the view itself, and altering the schedule or query is a separate privilege from reading the results. Map these grants to named roles rather than the default user, which starts with all permissions. Confirm the exact privileges for your ClickHouse version in the access control documentation before you hand refresh ownership to a team.
Restrict the default user
Refresh jobs run under whichever user created them, so a view built by an administrator keeps administrator rights after the author leaves. Create a dedicated service user with only the source read and target write grants the refresh needs. Remove or lock down the default user before any scheduled job is defined.
Refresh behavior in replicated clusters
On a cluster, the target table is typically a ReplicatedMergeTree table, and replicas converge through ClickHouse Keeper. Confirm in a test cluster which replica runs a given refresh and how a full replace or append reaches the other replicas. Check that a refresh started during a replica outage completes or retries as you expect.
Replication does not protect against mistakes
A bad query, an accidental full replace or a corrupted source is copied to every replica within the same refresh cycle. Replication keeps copies alive when a node fails, but it does not undo a wrong result. Keep the previous generation of the target, or plan a backup point before each heavy refresh, so a faulty rebuild can be reversed.
Whether backups capture refreshed results
BACKUP copies the stored target table, so the last refreshed result is captured, but the view definition, schedule and grants must be recorded separately in your configuration management. A restore brings back the data without guaranteeing that the refresh resumes on schedule. Document both the table and the view definition as part of the recovery set.
Restore-test every recovery path
The backup documentation warns that a backup never test-restored will probably not restore properly when needed. Restore a refreshed target onto a spare cluster, re-create the view, run one refresh and compare row counts and checksums with production. Repeat this on a schedule and after every ClickHouse upgrade.
Refreshable materialized views questions buyers ask
What drives the cost of a refreshable materialized view design?
Cost follows the number of views, how heavy each query is, and how much design work surrounds them. Each refresh is a full query over source data, so the expensive part is usually the compute and storage the scheduled rebuilds consume on your cluster, plus the engineering time to design dependencies, targets and monitoring. One-time costs include modeling the queries, choosing full replace or APPEND for each view, and testing on a copy of production data. Recurring costs are cluster capacity for refreshes and ongoing review as data volumes grow. Ask any partner for a quote that lists the views, their source tables, the refresh interval per view, the expected run window and who maintains the schedule after handover.
How long does a refreshable view build take?
It depends on the environment and must be measured, so no reliable duration can be given before testing. The main inputs are the row count and width of the source tables, the complexity of each query, the hardware and replica layout of your cluster, and how much other workload runs at the same time. The practical method is to build one representative view on a copy of production data, time a full refresh, and extrapolate across views and schedules. Ask for that measurement step to be written into the scope so the timeline rests on your own numbers rather than an estimate.
What does our team need to provide for the engagement?
The team needs to provide read access to a representative copy of the source data, a list of the queries and the business question each view answers, the freshness each consumer actually needs, and a window or limit for heavy refreshes on your cluster. You also need to name an owner for the schema and the refresh schedule, and access to the monitoring used for ClickHouse. Without a staging environment with realistic volumes, the refresh timing and resource use cannot be validated, so confirm early that one exists and that its data is representative.
Can our in-house team build refreshable materialized views themselves?
Often yes, if your engineers already run ClickHouse and understand SQL, table engines and scheduling. The syntax for a refreshable view is compact, and a single view with a clear target is a manageable first step. The harder parts are deciding between full replace and APPEND, ordering dependencies between views, avoiding refreshes that overlap peak hours, and proving that a restore works. A useful self-assessment is whether your team already runs incremental materialized views in production and has a tested backup routine. If not, a partner can build the first set and train your staff to extend it.
How do full replace and APPEND change the result a consumer sees?
A full replace rebuilds the target from scratch on every refresh, so consumers see a consistent snapshot as of the last run, but the cost grows with the entire source history. APPEND adds new rows to the target instead of replacing it, which suits data that never changes after it is written, but it can duplicate rows if the query is not designed for repeated runs. Choose full replace for aggregates that depend on late-arriving or corrected data, and APPEND only when each refresh produces rows that are new and final. Document the choice per view, because a wrong choice shows up as double counts or stale totals rather than as an error.
When is a refreshable view the wrong choice?
A refreshable view is the wrong choice when consumers need data that is current within seconds or minutes, because it only updates on its schedule. Incremental materialized views, which compute results as rows are inserted, fit that need better and avoid full rebuilds. Projections suit cases where ClickHouse should pick a different sort order or precomputed aggregate on its own for queries over the same table, without a separate target to maintain. Use a refreshable view when the query cannot be expressed incrementally, such as ranking across the whole dataset or joins whose inputs change after insert. If you are unsure, test the query shape against incremental and projection options on the same copy of data before deciding.
Where should I confirm the refresh behavior before production?
Confirm it against the refreshable materialized view documentation for your ClickHouse version, and against the upgrade notes for any version you run, since refresh behavior and syntax can change between releases. Check the release you plan to use against the production FAQ guidance, which recommends a pre-production environment with representative data and queries and a changelog review before upgrading. Test failure modes too: a refresh that fails, a dependency that runs late, and a restore of the target table. Ask the implementation partner to document which versions were tested and how the schedule behaves under load.
How DeployOpen works on this
DeployOpen treats each refreshable materialized view as a designed rebuild job with a defined source, target, schedule and recovery path. The work is planned and delivered inside your own infrastructure, with the view definitions, scheduling and checks documented so your team can run them after handover.
How we scope
- Inventory each query that cannot be incremental and record its source tables, target table and consumers.
- Decide per view between full replace and APPEND, with the reason written into the scope.
- Agree refresh cadence and off-peak windows against your ingestion and dashboard load before build starts.
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before work begins.
How we build
- Write each view with REFRESH EVERY and an explicit target table, testing the query on a copy of representative data first.
- Map dependencies between refreshable views so downstream rebuilds run only after upstream results are complete.
- Schedule heavy refreshes away from peak ingestion and dashboard hours, and confirm resource limits on the test cluster.
- Check row counts and key results against the source after each test refresh before enabling the schedule.
How we hand over
- Deliver the view definitions, dependency map and schedule as versioned files your team can review and redeploy.
- Document how to pause, rerun and recover a failed refresh, including what a partial APPEND run leaves behind.
- Run a walkthrough with your engineers covering monitoring of refresh status and the checks used to accept results.
- Record which settings and resource controls were chosen, and why, for later changes and ClickHouse upgrades.
How we support
- Ongoing support, upgrades and continuous testing are available as retainers after handover.
- Review refresh timings and failures when data volumes grow or new consumers depend on a view.
- Revisit schedules and full-replace versus APPEND choices when ClickHouse versions change behavior.
- Confirm support boundaries, response expectations and fees with DeployOpen in the written scope.
