Insert Summaries in Batches and Match the Raw Data with Sums and States

Goal

You put the (site, day) summary from the source events into a SummingMergeTree and an AggregatingMergeTree in batches, and write queries that give exactly the same answer as the original even before merging. You confirm that values that cannot be reduced by summing must be stored as states, and how to merge those states again into a larger unit.

Why it matters

A summary table makes a dashboard cheap, but before merging finishes, the same key is scattered over several rows. If you read with SELECT * in that state, or store distinct user counts as numbers and add them, wrong answers come out silently. This lab stops merges to fix that wrong state, and the grader compares the results of your queries cell by cell with values it aggregates directly from the source agg.hits. How many batches you inserted in is confirmed from the records of system.part_log.

Steps

1. Create the database agg and the table agg.hits — columns ts DateTime, site LowCardinality(String), user_id UInt64, dur_ms UInt32, is_bot UInt8 (in this order), engine MergeTree, ORDER BY (site, ts).
2. Run /opt/lab/fixtures/aggregating/hits.sql once to insert 1 million rows.
3. Create agg.daily (site LowCardinality(String), day Date, hits UInt64, dur_total UInt64) with ENGINE = SummingMergeTree ORDER BY (site, day), stop merges with SYSTEM STOP MERGES agg.daily, and then insert site, toDate(ts), count(), sum(dur_ms) from agg.hits in three INSERTs, split by time of day (toHour(ts) < 8, 8 이상 16 미만 (8 or more and less than 16), 16 이상 (16 or more)).
4. Create /root/ch/aggregating/q_daily.sql, which gives the per-(site, day) views and dwell total exactly as the original even now, before merging (read only agg.daily, with columns in the order site, day, hits, dur_total). And write the current row count of agg.daily and the number of distinct (site, day) to /root/ch/aggregating/raw.json as raw_rows and keys.
5. After SYSTEM START MERGES agg.daily, merge with OPTIMIZE TABLE agg.daily FINAL so that there is one row per key.
6. Create agg.daily_state (site LowCardinality(String), day Date, users AggregateFunction(uniqExact, UInt64), avg_dur AggregateFunction(avg, UInt32), human_hits AggregateFunction(countIf, UInt8)) with ENGINE = AggregatingMergeTree ORDER BY (site, day), stop merges, and then insert uniqExactState(user_id), avgState(dur_ms) and countIfState(is_bot = 0) in the same three time-of-day batches as step 3.
7. Create /root/ch/aggregating/q_state.sql, which reads only agg.daily_state and gives users, avg_dur and human_hits per (site, day) (columns in the order site, day, users, avg_dur, human_hits). And write the value of simply adding the finalizeAggregation(users) of each row before merging, and the sum of all the users of q_state.sql, to /root/ch/aggregating/naive.json as naive_users_sum and true_users_sum.
8. Create agg.day_total (day Date, users AggregateFunction(uniqExact, UInt64)) with ENGINE = AggregatingMergeTree ORDER BY day, merge the states of agg.daily_state into it per date with uniqExactMergeState(users), and create /root/ch/aggregating/q_day.sql, which reads only agg.day_total and gives the distinct user count per date (without distinguishing sites) (columns day, users).

Notes

• The server is already running when the Pod starts. Just typing clickhouse-client connects. If it has stopped, run ch-up (if the server is restarted, STOP MERGES is released).
• INSERT records are left in system.part_log (event_type = 'NewPart'). The grader filters by the table's UUID. To redo it, TRUNCATE TABLE and insert three times.
• A state column is not a value people read. To check, finish it with finalizeAggregation(열) (the placeholder stands for the state column) or a -Merge function and look.
• Common mistakes: inserting the whole source with one INSERT — the same key is combined at the moment of insertion and you cannot see "before merging". In queries, adding again values that were already finished, like sum(uniqExactMerge(...)) — it counts overlapping people several times.
• Official docs: SummingMergeTree · AggregatingMergeTree · Aggregate Function Combinators · system.part_log

The source event table

Create the database agg and the table agg.hits. The columns are, in order, ts DateTime, site LowCardinality(String), user_id UInt64, dur_ms UInt32, is_bot UInt8, the engine is MergeTree, and the sorting key is ORDER BY (site, ts).

A summary table is always built from the source. If you leave the source as a plain MergeTree, you can rebuild it even if you make the summary wrongly — this is why the documentation recommends using SummingMergeTree together with MergeTree.

The 1 million source rows

Run /opt/lab/fixtures/aggregating/hits.sql once to insert 1,000,000 rows into agg.hits.

Just pass it to clickhouse-client with --queries-file. It is 5 sites × the 30 days of September 2026, so there are 150 (site, day) combinations. If you inserted twice, TRUNCATE and insert again.

Insert into the SummingMergeTree in three batches

Create agg.daily (site LowCardinality(String), day Date, hits UInt64, dur_total UInt64) with ENGINE = SummingMergeTree ORDER BY (site, day), stop merges with SYSTEM STOP MERGES agg.daily, and then insert site, toDate(ts), count(), sum(dur_ms) from agg.hits, grouped by (site, day), in three INSERTs split by time of day (toHour(ts) < 8 · 8 이상 16 미만 (8 or more and less than 16) · 16 이상 (16 or more)).

This is a situation where one day's summary arrives in three batches. Each INSERT does GROUP BY only for its time of day, so before merging the same (site, day) exists as three rows. You must stop merges before inserting.

A query that is right even before merging

Create /root/ch/aggregating/q_daily.sql, which reads only agg.daily and gives the per-(site, day) views and dwell total exactly as the original. The columns are in the order site, day, hits, dur_total. And write the current row count of agg.daily and the number of distinct (site, day) to /root/ch/aggregating/raw.json as raw_rows and keys.

You cannot know whether merging has finished, so you group and add once more in the query. If you read with SELECT *, you now get three rows per key. You must not read the source agg.hits — the aim is to produce the answer from the summary table alone.

After merging, one row per key

After SYSTEM START MERGES agg.daily, merge with OPTIMIZE TABLE agg.daily FINAL so that agg.daily has one part and one row per key.

The merge adds the numeric columns of the same key and folds them into one row. See whether SELECT * after merging equals the source aggregation. In production you do not know when merging happens, so a query like that of step 4 is still needed.

Values that cannot be reduced by summing go in as states

Create agg.daily_state (site LowCardinality(String), day Date, users AggregateFunction(uniqExact, UInt64), avg_dur AggregateFunction(avg, UInt32), human_hits AggregateFunction(countIf, UInt8)) with ENGINE = AggregatingMergeTree ORDER BY (site, day), and after SYSTEM STOP MERGES agg.daily_state, insert uniqExactState(user_id), avgState(dur_ms) and countIfState(is_bot = 0), grouped by (site, day), in the same three time-of-day batches as step 3.

The column type AggregateFunction(function, argument type) means it holds the intermediate state of that function, and when inserting you make it by attaching -State to the same function. The -If combinator takes the condition as the last argument. Since it is before merging, the states of the three batches must exist separately.

Finish a state after merging it

Create /root/ch/aggregating/q_state.sql, which reads only agg.daily_state and gives users (distinct user count), avg_dur (average dwell) and human_hits (views that are not bots) per (site, day) exactly as the original (column order site, day, users, avg_dur, human_hits). And write the value of simply adding the finalizeAggregation(users) of each row before merging, and the sum of all the users of q_state.sql, to /root/ch/aggregating/naive.json as naive_users_sum and true_users_sum.

A function with -Merge attached produces the result after merging the states of the same key. finalizeAggregation finishes one state on the spot, so if you add numbers finished per row, you count people who came in several time slots several times. See how different the two sums are.

Merge states again into a larger unit

Create agg.day_total (day Date, users AggregateFunction(uniqExact, UInt64)) with ENGINE = AggregatingMergeTree ORDER BY day, merge the users states of agg.daily_state into it per date with uniqExactMergeState(users), and create /root/ch/aggregating/q_day.sql, which reads only agg.day_total and gives the distinct user count per date (without distinguishing sites) (columns day, users).

-MergeState merges states and returns a state again, not a result, so you can put that result into another AggregatingMergeTree. Build it from the per-site states without reading the source again. If you add up the per-site user counts (numbers), you count the same person several times.