ClickHouse is very fast and very literal. It does what you asked, and several of the things you can ask for return an answer that is wrong in a way nothing warns you about: no error, no null, no empty result. Just a number that is a bit too small, or a bit too large, or the text "NaN" where a percentage should be.

These are three we hit building analytics on it. Each was live before anybody noticed, which is the part worth writing down.

1. An aggregate state is not a number

The daily rollup stores unique visitors and sessions as AggregateFunction(uniq, UUID), a serialized sketch rather than a count. That is the whole point: you can merge sketches across days without going back to the raw rows.

The trap is that the column still looks like a column. Two things follow, and the second is the one that bites.

Rows are not collapsed until a background merge runs. The same key can exist several times over, so reading the column and summing what you get under-reports, quietly and plausibly. There is no error and no obvious symptom; the number is simply lower than the truth by an amount that changes depending on when the last merge happened. You need uniqMerge and a GROUP BY, always.

And a SQL client cannot render the raw state at all. The ClickHouse JDBC driver can only deserialize uniq states over native integer types, so any SELECT * over a uniq state on a UUID fails outright:

Only native integer types are supported but we got: UUID

Which means clicking the table in DataGrip or DBeaver, the most ordinary thing anybody does while debugging, throws and looks like a broken connection rather than a data-modelling decision you made months ago.

The fix is a view that merges the states and exposes plain UInt64, so browsing works and the numbers are right:

CREATE VIEW stats_daily_v AS
SELECT
    site_id, date, path,
    sum(page_views)            AS page_views,
    uniqMerge(unique_visitors) AS unique_visitors,
    uniqMerge(sessions)        AS sessions
FROM stats_daily
GROUP BY site_id, date, path;

Application code queried the base table with uniqMerge directly. The view existed so that a human opening the database saw the truth instead of an exception.

We dropped the rollup in September 2026. Nothing had ever read it: the dashboard queries the raw tables, and the aggregate was being rebuilt on every insert for no reader at all.

2. avgIf over no rows returns NaN, not NULL

This one reached production and stayed there.

avgIf(duration_ms, is_bounce = 0)

Over a period with no matching rows, ClickHouse returns Float64 nan. Not NULL, which you would have handled, because handling nulls is a thing everybody remembers to do. nan.

Two consequences, and they land on different people:

  • The dashboard rendered the literal text "NaN" where a bounce rate and an average duration should be. Ugly, obviously wrong, reported quickly.
  • The query API returned HTTP 500. JSON cannot encode a non-finite double at all, so serialization threw, for exactly the periods a customer is most likely to poll: today, before the first visitor of the morning, and any range over a site that is quiet.

The second is much worse than the first and was found much later. A dashboard saying "NaN" gets a bug report in an hour. An API that 500s only when the answer would have been "nothing happened" looks like a flaky endpoint, and gets a retry loop rather than a bug report.

The fix is one line at the read boundary: flatten anything non-finite to zero, which is the same answer the zero-denominator path already gives, so the two agree:

var value = Convert.ToDouble(reader.GetValue(ordinal));
return double.IsFinite(value) ? value : 0;

Zero is honest here because the visit count beside it also reads zero. Context is what makes a zero truthful rather than a guess.

3. A ReplacingMergeTree that is a lie without FINAL

Time on page is not the gap between two page views. A visitor who leaves a tab open in the background is not reading, and the last page of a visit has no next page to subtract from. So the tracker reports visible time, and it reports it more than once: somebody who switches tabs away and back and away again has genuinely hidden the page twice, and each report carries the running cumulative total.

Summing those would count the same minute several times over and report a wildly overstated time on page. So the table is a ReplacingMergeTree keyed per visit and page, versioned by the engaged milliseconds, so the longest report wins and the earlier partials collapse into it.

Which means every read needs FINAL, and a read that forgets it sees the partial rows as well as the final one. The number is not garbage; it is plausible, just too big. That is the kind of wrong that survives review.

The honest cost of that key, since a design like this always has one: a visitor who returns to the same page later in the same visit produces one row rather than two, and the shorter reading is discarded rather than added. It under-counts in an uncommon case, which is the right direction to be wrong in for a metric whose entire purpose is to stop overstating attention.

What the three have in common

None of them threw an error at the moment the mistake was made. Two of them produced numbers that looked entirely reasonable, and the third produced a crash somewhere else entirely, hours or weeks later.

The lesson we actually took: for every aggregate, write down what a wrong answer would look like, and check for that shape rather than for an exception. An analytics product that is 8% low is worse than one that is down, because nobody files a bug against 8%.

If you want to see what the numbers look like when they are right, there is a live demo running on a real shop, no account needed, and the glossary says exactly what each one counts.

All three of those bugs sat behind the Analytics product, and none of them announced itself, which is the argument for checking a number against a second method rather than trusting that it looks plausible.