Analytics PipelineClickHouse & Materialized Views
Guide ClickHouse Core Architecture & Analytics Fundamentals

Pre-Aggregating Rollups with AggregatingMergeTree

AggregatingMergeTree maintains rolling aggregates — distinct counts, quantiles, averages, sums — by storing partial aggregate state per key and combining that state whenever a background merge runs, so a dashboard reads a pre-computed rollup instead of scanning raw events. This walkthrough builds an aggregate target table with AggregateFunction columns, feeds it on the insert path with a materialized view that emits -State values, finalizes reads with the matching -Merge combinators, and verifies the state pipeline against system.parts and the raw source.

Prerequisites

The -State / -Merge pipeline

The whole design rests on one idea: an aggregate can be split into a partial state that is cheap to store and mergeable with other states of the same aggregate. A -State combinator (uniqState, sumState, quantileState) produces that intermediate state instead of a final number; the AggregatingMergeTree combines states with the same key on every merge; and a -Merge combinator (uniqMerge, sumMerge, quantileMerge) finalizes the state into a value at read time. The raw events are aggregated once, on the insert path, and never scanned again.

AggregatingMergeTree pipeline: raw events to -State view to state target to -Merge read Four stages left to right. Raw events land in a source MergeTree. A materialized view emits -State partial aggregates grouped by the rollup dimensions on every insert. The AggregatingMergeTree target merges states per key. A query finalizes with -Merge combinators to return values without rescanning raw events. Raw events events_raw MergeTree source Materialized view insert-path trigger uniqState, sumState GROUP BY dimensions Aggregating target AggregateFunction cols merges state per key on background merge Dashboard query uniqMerge, sumMerge final values, no rescan insert -State rows -Merge The rollup grain — the GROUP BY in the view — must equal the ORDER BY of the target, or states never collapse per key. Raw events are aggregated once on write; reads finalize stored state and never touch events_raw again.

Step 1 — Create the raw source table

The source is an ordinary MergeTree of individual events. It carries the raw dimensions and measures the rollup will aggregate.

sql
CREATE TABLE analytics.events_raw
(
    `event_ts`   DateTime64(3),
    `country`    LowCardinality(String),
    `user_id`    UInt64,
    `latency_ms` Float64,
    `amount`     Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_ts)
ORDER BY (country, event_ts);

Expected: a plain source table. Nothing aggregates yet — this is only the write target for producers.

Step 2 — Create the AggregatingMergeTree target

The target’s measure columns are typed AggregateFunction(fn, argtypes) — they hold state, not values. Key the target on the rollup dimensions; that ORDER BY is what the engine uses to merge states per group.

sql
CREATE TABLE analytics.events_rollup
(
    `day`      Date,
    `country`  LowCardinality(String),
    `visitors` AggregateFunction(uniq, UInt64),
    `p95_ms`   AggregateFunction(quantile(0.95), Float64),
    `revenue`  AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, country);

Expected: the target registers with engine = AggregatingMergeTree, and visitors reports type AggregateFunction(uniq, UInt64). It is intentionally unreadable as-is; reads must finalize it.

Step 3 — Attach a materialized view that emits state

The view runs on every insert into events_raw, transforms rows into partial states with -State combinators, and writes them to the target with TO. Its GROUP BY must match the target’s ORDER BY exactly.

sql
CREATE MATERIALIZED VIEW analytics.events_rollup_mv
TO analytics.events_rollup
AS
SELECT
    toDate(event_ts)               AS day,
    country,
    uniqState(user_id)             AS visitors,
    quantileState(0.95)(latency_ms) AS p95_ms,
    sumState(amount)               AS revenue
FROM analytics.events_raw
GROUP BY day, country;

Expected: the view is created and armed. From this point every insert into the source produces one state row per (day, country) group in that insert block. The view-authoring conventions — column alignment, POPULATE cautions, backfill order — are covered in materialized view creation patterns.

Step 4 — Insert raw events and let the view fire

Insert a batch into the source. The view intercepts the insert path; the target receives state automatically.

sql
INSERT INTO analytics.events_raw VALUES
    (now64(3), 'US', 1, 120.0, 9.99),
    (now64(3), 'US', 2, 240.0, 4.50),
    (now64(3), 'US', 1, 90.0,  9.99),   -- same user again; uniq must not double-count
    (now64(3), 'DE', 3, 310.0, 19.00);

Expected: events_rollup now holds state rows for (today, 'US') and (today, 'DE'). Do not SELECT * the target expecting numbers — the columns are binary state.

Step 5 — Query with -Merge combinators

Finalize the stored state with the -Merge function matching each column’s aggregate, grouped by the same dimensions. This collapses any un-merged state rows and returns final values.

sql
SELECT
    day,
    country,
    uniqMerge(visitors)            AS visitors,
    quantileMerge(0.95)(p95_ms)    AS p95_ms,
    sumMerge(revenue)              AS revenue
FROM analytics.events_rollup
GROUP BY day, country
ORDER BY day, country;

Expected: US shows visitors = 2 (user 1 counted once despite two events), a finalized p95 latency, and summed revenue; DE shows visitors = 1. The GROUP BY is mandatory even when the target looks fully merged, because fresh inserts may have added state rows that a background merge has not yet combined.

Verification

Cross-check the rollup against the raw source to prove the aggregation is faithful. The two distinct-user counts must agree:

sql
SELECT
    (SELECT uniqMerge(visitors) FROM analytics.events_rollup)      AS rollup_visitors,
    (SELECT uniq(user_id)       FROM analytics.events_raw)         AS raw_visitors;

Confirm the target is accumulating state rows and that merges are combining them — a growing part count with stable row counts per key is healthy:

sql
SELECT partition, count() AS parts, sum(rows) AS state_rows
FROM system.parts
WHERE database = 'analytics' AND table = 'events_rollup' AND active
GROUP BY partition
ORDER BY partition;

Check that the view is registered against the intended source and target, a common place for a silent misconfiguration:

sql
SELECT name, engine, as_select
FROM system.tables
WHERE database = 'analytics' AND name = 'events_rollup_mv';

Gotchas and edge cases

  • A -State / -Merge mismatch returns wrong or unreadable results. Each column must be read with the exact combinator of its aggregate, including parameters: a quantileState(0.95) column is finalized with quantileMerge(0.95), not quantileMerge(0.5). Mismatched parameters silently return a different quantile.
  • The view GROUP BY must equal the target ORDER BY. If the view groups by (day, country) but the target orders by (country, day) — or omits a column — states for the “same” logical key land under different keys and never collapse, inflating storage and row counts.
  • Reading the target without -Merge returns binary state. SELECT visitors FROM events_rollup yields the internal AggregateFunction blob, not a number. Always finalize, and always GROUP BY the key even on a freshly optimized table.
  • POPULATE can miss rows. Creating the view with POPULATE backfills at creation time but drops rows inserted during the backfill. Prefer creating the view first, then backfilling history with a separate INSERT INTO events_rollup SELECT … -State … FROM events_raw.
  • SimpleAggregateFunction is cheaper when the aggregate allows it. For aggregates whose partial and final state are the same type (sum, min, max, any), SimpleAggregateFunction(sum, …) stores a plain value and reads without -Merge, saving space and query complexity — reserve full AggregateFunction for uniq, quantile, and other stateful aggregates that genuinely need it.

Up: MergeTree Engine Variants