ClickHouse Materialized Views: How to Keep Dashboard Aggregates Up to Date

Dashboards often need to display aggregated information such as daily revenue, total orders, active users, and product performance. As the amount of data grows, calculating these metrics directly from millions or billions of raw records can become expensive.

ClickHouse is designed for analytical workloads, but even a column-oriented database benefits from carefully designed data models. Repeatedly scanning a large events table to calculate the same dashboard metrics can increase query latency and consume unnecessary resources.

ClickHouse materialized views provide a way to process incoming data and write derived results into another table. Instead of calculating every aggregate from the original dataset whenever a dashboard loads, an application can maintain pre-aggregated data as new records arrive.

However, ClickHouse materialized views do not behave exactly like traditional database views. In particular, an incremental materialized view normally processes newly inserted blocks of data rather than automatically recalculating its results whenever the source table changes.

Understanding this behavior is essential when designing dashboards that must remain accurate as data arrives, late events are processed, and historical records are corrected.

This article explains how ClickHouse materialized views work, how to build an incremental aggregation pipeline, how to query aggregated results correctly, and how to troubleshoot common consistency and performance problems.

Understanding ClickHouse Materialized Views

A standard database view represents a query that can be executed when the view is accessed. Unless the database provides a separate materialization mechanism, the view does not maintain a stored copy of its results.

A ClickHouse incremental materialized view works differently. It processes inserted data from a source table and writes the query results into a target table.

Consider an application that records completed orders:

CREATE TABLE orders
(
    order_id UInt64,
    customer_id UInt64,
    order_time DateTime,
    amount Decimal(12, 2),
    status LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (order_time, order_id);

As the application inserts orders, the source table accumulates individual records.

A dashboard might repeatedly execute a query similar to this:

SELECT
    toDate(order_time) AS order_date,
    sum(amount) AS total_revenue,
    count() AS total_orders
FROM orders
WHERE status = 'Completed'
GROUP BY order_date
ORDER BY order_date;

For a small dataset, this query may be sufficient. As the table grows, however, the database must repeatedly process the records needed to calculate the daily totals.

A materialized view can calculate partial aggregates when data is inserted and store those results in a separate table.

The dashboard can then query the smaller aggregate dataset instead of repeatedly scanning the raw orders table.

How Incremental Materialized Views Work

The basic process consists of four steps:

The application inserts new records into the source table.
ClickHouse executes the materialized view's query against the newly inserted block.
The materialized view writes the resulting rows into the target table.
Dashboard queries read the target table and combine partial aggregates where necessary.

This approach can reduce the amount of data processed by recurring analytical queries.

However, it is important to understand what the materialized view does not do automatically. An incremental materialized view does not continuously monitor arbitrary changes to every existing source row and recompute the complete result.

If historical records are updated, deleted, or corrected, the application must account for those changes through an appropriate data-modeling and reconciliation strategy.

Designing the Aggregation Table

The target table should match the dashboard's reporting requirements.

For daily revenue reporting, the aggregation dimensions might include:

Order date.
Product category.
Sales region.
Payment method.

The more dimensions included in the aggregation key, the more detailed the resulting data becomes. However, additional dimensions can also increase the number of aggregate groups and the storage required.

For this example, the dashboard needs daily totals for completed orders.

ClickHouse provides the SummingMergeTree engine, which can combine rows containing the same sorting key by summing designated numeric columns during background merges.

Create the aggregation table as follows:

CREATE TABLE daily_order_summary
(
    order_date Date,
    total_revenue Decimal(18, 2),
    total_orders UInt64
)
ENGINE = SummingMergeTree
ORDER BY order_date;

The sorting key identifies the grouping dimension for this summary.

Rows with the same order_date can be combined during background merges. Those merges happen asynchronously, so duplicate dates may exist in the table at any given time.

This is an important implementation detail: applications should not assume that background merging has already combined every row for a given date.

When querying a SummingMergeTree table, use sum() and GROUP BY when you need correct totals regardless of whether background merges have completed.

Creating the Incremental Materialized View

Once the source and target tables exist, create the materialized view that processes newly inserted orders.

CREATE MATERIALIZED VIEW mv_daily_order_summary
TO daily_order_summary
AS
SELECT
    toDate(order_time) AS order_date,
    sum(amount) AS total_revenue,
    count() AS total_orders
FROM orders
WHERE status = 'Completed'
GROUP BY order_date;

This view processes inserted data from orders and writes daily partial aggregates into daily_order_summary.

For example, suppose the application inserts these records:

order_id	order_time	amount	status
101	2026-10-28 09:15:00	250.00	Completed
102	2026-10-28 10:30:00	400.00	Completed
103	2026-10-28 11:45:00	150.00	Pending

The materialized view processes the inserted block. The two completed orders contribute 650.00 to revenue and two to the order count. The pending order does not contribute to this summary.

The target table may receive a partial aggregate row like this:

order_date	total_revenue	total_orders
2026-10-28	650.00	2

If more completed orders arrive later that day, the view inserts another partial aggregate for the same date.

The background merge process may eventually combine the rows, but correct dashboard queries should not depend on that happening immediately.

Important Consideration: Initial Data

Creating an incremental materialized view does not automatically process all the historical rows already present in its source table.

It processes new inserts that occur after the view is created.

If orders already contains millions of records, the aggregation table will not automatically contain their totals simply because the view was created.

Historical data must be backfilled separately.

This distinction is important when deploying materialized views to existing production databases.

Querying Aggregated Data Correctly

A common mistake is selecting aggregate values directly from a SummingMergeTree table without combining rows that have the same key.

For example, this query is not reliable when multiple partial aggregate rows exist for a date:

SELECT
    order_date,
    total_revenue,
    total_orders
FROM daily_order_summary
ORDER BY order_date;

It can return multiple rows for the same date, each representing a partial aggregate.

Instead, aggregate the stored values at query time:

SELECT
    order_date,
    sum(total_revenue) AS total_revenue,
    sum(total_orders) AS total_orders
FROM daily_order_summary
GROUP BY order_date
ORDER BY order_date;

This query returns the combined totals for each date, regardless of whether background merges have already consolidated the underlying rows.

For a dashboard that needs the most recent 30 days, add a date filter:

SELECT
    order_date,
    sum(total_revenue) AS total_revenue,
    sum(total_orders) AS total_orders
FROM daily_order_summary
WHERE order_date >= today() - 29
  AND order_date <= today()
GROUP BY order_date
ORDER BY order_date;

The exact date range should match the dashboard's reporting requirements and time-zone policy.

For applications that report business days in a particular time zone, calculate the reporting date consistently. Converting timestamps to dates without accounting for the intended time zone can place events into the wrong reporting day.

Backfilling Historical Data Safely

Existing data must be processed separately when introducing a materialized view.

A straightforward backfill inserts historical aggregates into the target table:

INSERT INTO daily_order_summary
SELECT
    toDate(order_time) AS order_date,
    sum(amount) AS total_revenue,
    count() AS total_orders
FROM orders
WHERE status = 'Completed'
GROUP BY order_date;

This calculates aggregates from the existing source records and inserts the results into daily_order_summary.

However, running this operation without a deployment plan can create missing or duplicated totals.

Why Backfill Coordination Matters

Suppose the materialized view begins processing new inserts while a backfill query is scanning historical data.

Depending on the deployment sequence, the same source records could be represented by both the backfill and the incremental materialized view. Alternatively, records inserted during the backfill might be missed by the historical scan and not yet represented in the target table.

The exact risk depends on the deployment process, table behavior, and how the backfill range is selected.

For production deployments, establish a clear boundary between historical data and data handled by the incremental view.

One practical strategy is to:

Define a historical cutoff using a stable event identifier or ingestion boundary.
Backfill only records on one side of that boundary.
Configure the incremental pipeline to process records on the other side.
Validate that the two ranges do not overlap and leave no gaps.
Reconcile the resulting aggregates against the source data.

For example, a system might backfill records whose ingestion timestamp is earlier than a cutoff and use a separate ingestion process for records at or after that cutoff.

A timestamp-based boundary must be designed carefully if timestamps can be delayed, duplicated, or assigned by clocks that are not synchronized. A reliable ingestion sequence or another stable boundary can be easier to reason about.

For large tables, test the backfill process on a representative dataset before running it against the entire production history.

Handling Late-Arriving Events and Corrections

Real-world event pipelines do not always deliver records in chronological order.

An order might be created on Monday but arrive in ClickHouse on Tuesday because of a delayed message, an offline client, or a retry.

If the materialized view groups by toDate(order_time), the late-arriving order contributes to Monday's aggregate when it is inserted.

This is usually desirable because the reporting date is based on the event's business timestamp rather than its arrival time.

However, the summary changes only when the relevant data is inserted and processed. If an existing record is corrected in place, an incremental materialized view does not automatically subtract the old contribution and add the new contribution.

Why Updates Require Special Handling

Suppose an order was initially recorded with an amount of 100.00 and later corrected to 120.00.

If the source table is updated without a corresponding correction to the aggregate pipeline, the summary may still reflect the original amount. Simply inserting the corrected amount as another record could also overcount revenue.

Several approaches can address this problem.

Approach	How it works	Considerations
Append-only event model	Store corrections as new events rather than silently replacing existing records.	Requires explicit business rules for interpreting corrections.
Compensating entries	Insert a negative adjustment for the previous contribution and a positive adjustment for the corrected value.	Requires reliable tracking of which contribution is being reversed.
Periodic reconciliation	Recalculate aggregates from authoritative source records and compare them with the summary.	Uses additional compute resources and requires a controlled correction process.
Rebuild the summary	Recreate the aggregate table from the source data.	Can be expensive for large datasets and requires a safe cutover strategy.

The correct choice depends on the application's event semantics, correction frequency, data volume, and consistency requirements.

For financial reporting, do not assume that append-only ingestion automatically provides correct accounting totals. Define how refunds, cancellations, reversals, and corrected transactions should affect the aggregates.

Choosing the Right ClickHouse Table Engine

The target table engine determines how partial aggregates are stored and combined.

SummingMergeTree is useful when the stored values are additive. It can combine numeric values for rows with the same sorting key during background merges.

For example, daily order counts and total revenue can be summed across partial aggregate rows.

However, not every metric can be calculated correctly by summing precomputed values.

When SummingMergeTree Is Appropriate

Consider these metrics:

Total sales amount.
Total number of events.
Total units sold.
Total bytes processed.

These values can generally be combined by addition, provided the underlying event model does not double-count records.

When SummingMergeTree Is Not Enough

Metrics such as averages, distinct counts, and percentiles require more careful handling.

Suppose one insert block contains ten orders with an average value of 100, while another contains two orders with an average value of 200.

Adding the two averages gives 300, which is not the overall average.

The correct calculation is based on the total amount and total number of orders:

SELECT
    sum(total_revenue) / nullIf(sum(total_orders), 0)
        AS average_order_value
FROM daily_order_summary;

This works because the target table stores additive values that can be combined before calculating the average.

For distinct counts and quantiles, ClickHouse provides aggregate-function states that can be stored and merged using AggregatingMergeTree and functions such as uniqState, uniqMerge, quantileState, and quantileMerge.

For example, a summary that tracks unique customers may store a distinct-count aggregate state rather than summing independent counts from separate insert blocks.

The important principle is to store a representation that can be combined correctly for the metric being calculated.

Common Mistakes With ClickHouse Materialized Views

Assuming Materialized Views Recalculate the Entire Source Table

Incremental materialized views normally process newly inserted blocks. They do not automatically recompute every existing result after arbitrary updates or deletes to the source table.

Design a separate backfill, correction, or reconciliation process when historical data can change.

Forgetting to Backfill Existing Records

Creating a view over an existing source table does not automatically populate the target with all previous records.

Plan historical backfilling as part of the deployment rather than assuming that the target table will immediately contain complete results.

Reading SummingMergeTree Rows Without Aggregation

Background merges are asynchronous. Multiple rows with the same sorting key may exist at the same time.

Use appropriate aggregation in dashboard queries rather than depending on physical row consolidation.

Summing Non-Additive Metrics

Averages, distinct counts, and percentiles cannot generally be combined by simply adding their precomputed values.

Store sums and counts for averages, or use suitable aggregate-function states for distinct counts and quantiles.

Ignoring Duplicate Events

If an event is inserted more than once, an incremental materialized view can process both insertions. This may increase counts and totals incorrectly.

Use reliable event identifiers, deduplication strategies appropriate to the ingestion pipeline, and reconciliation procedures. Do not assume that a materialized view automatically makes ingestion idempotent.

Treating Event Time and Ingestion Time as Interchangeable

An event may arrive long after it occurred. Grouping by event time and grouping by ingestion time answer different business questions.

Choose the correct timestamp for the dashboard and apply a consistent time-zone policy.

Best Practices for Production Dashboards

A production aggregation pipeline should be designed around correctness as well as query speed.

Define the dimensions and metrics required by the dashboard before designing the target table.
Use additive values or mergeable aggregate states for metrics that combine data across insert blocks.
Backfill historical data using a clearly defined boundary.
Coordinate backfills with ongoing ingestion to avoid missing or duplicate contributions.
Keep ingestion event identifiers and correction rules explicit.
Monitor source-table inserts, target-table growth, query latency, and background merge activity.
Use representative data to validate aggregate results against the raw source.
Reconcile historical periods periodically when the business requires strong reporting accuracy.
Account for late-arriving events and corrected records.
Review the target table's sorting key and partitioning strategy against actual query patterns.

Materialized views can significantly simplify recurring dashboard queries, but they do not remove the need for data-quality controls or a well-defined ingestion model.

Advantages and Disadvantages of ClickHouse Materialized Views

Advantages

Reduce repeated aggregation work for frequently accessed dashboards.
Allow new data to be summarized during ingestion.
Separate raw event storage from dashboard-oriented data models.
Support efficient queries against smaller aggregate datasets.
Work with ClickHouse table engines designed for merging partial aggregates.

Disadvantages

Incremental materialized views do not automatically recalculate all historical results after arbitrary source-table changes.
Historical data requires a separate backfill process.
Duplicate or corrected events can produce inaccurate aggregates without an explicit handling strategy.
The target table may contain multiple partial aggregate rows before background merges complete.
Incorrect aggregation design can produce misleading averages, distinct counts, or other non-additive metrics.
Additional tables and ingestion logic increase operational complexity.

Conclusion

ClickHouse materialized views provide an effective way to maintain dashboard aggregates as new data arrives. By processing inserted blocks and writing partial results into a dedicated target table, they can reduce the amount of work required by recurring analytical queries.

The design must account for how ClickHouse handles incremental inserts, asynchronous merges, historical backfills, late-arriving events, and corrections. For additive metrics, SummingMergeTree can be a practical choice, provided queries combine partial rows correctly. For distinct counts, quantiles, and other complex metrics, mergeable aggregate states may be more appropriate.

The most reliable implementations validate summaries against the source data, define a clear backfill boundary, and make duplicate and correction handling explicit.

With these practices in place, developers can build ClickHouse dashboards that remain responsive while keeping aggregate calculations understandable and maintainable.