06 The impossible dashboard

A dashboard that groups every row in its window, not one user's history. No sort key rescues it -- the fix is an incremental materialized view with no BigQuery equivalent, built here with real DDL.

Outcome

In about 25 minutes you will build a per-minute conversion funnel that answers in milliseconds, regardless of how much history it covers.

The dashboard

The dashboard this module builds is a conversion funnel: view_item -> add_to_cart -> begin_checkout -> purchase, broken down by minute, device category and country. Over the full export:

386,068view_item events

58,543add_to_cart events

38,757begin_checkout events

5,692purchase events

Spread across 109 countries. Those four counts, sliced by minute, device category and country, are the whole shape of the query a live ops screen would fire on every page load.

This module measures those four conversion counts, plus a distinct-user estimate, never revenue. Revenue is present in this dataset -- 5,692 purchase events all carry a populated ecommerce.purchase_revenue_in_usd, totalling 362,165 USD -- but it is sparse: those 5,692 rows are about 0.13% of the 4,295,584-row export, so a per-minute revenue panel would sit empty for almost every bucket. Conversion counts are dense across every minute, device and country, which is why they are what this module's funnel tracks.

Why no sort key rescues this

Module 05 was a point lookup: one user_pseudo_id, and a sort key leading with that column let ClickHouse skip almost the entire table. This dashboard's query is shaped differently. It groups every row inside a calendar-day window by minute, device category and country, so there is no single equality predicate for a sort key to lead on. The nearest thing to a filter is the window bound itself (event_time >= ... AND event_time < ...), and bq.events_tuned, the table module 04 had you build, is sorted by (event_date, event_name, user_pseudo_id), which helps a single-user lookup, not a per-minute range scan across every user.

Measure the raw cost

Run the raw aggregate for one calendar day against that table, with the condition cache off, the same way module 05 had you measure:

SELECT
  toStartOfMinute(event_time) AS minute,
  device_category, geo_country,
  sum(toUInt64(event_name = 'view_item'))      AS views,
  sum(toUInt64(event_name = 'add_to_cart'))    AS carts,
  sum(toUInt64(event_name = 'begin_checkout')) AS checkouts,
  sum(toUInt64(event_name = 'purchase'))       AS purchases,
  uniq(user_pseudo_id)                          AS users
FROM bq.events_tuned
WHERE event_time >= '2025-12-01 00:00:00' AND event_time < '2025-12-02 00:00:00'
GROUP BY minute, device_category, geo_country
SETTINGS use_query_condition_cache = 0;

Read read_rows straight off the console's results bar, the same way module 05 taught you to.

Measured against bq.events_tuned: 4,295,584 rows read, every row the table holds, for a single day's window. That has two causes:

A per-minute-across-everyone aggregate has no single value to seek to the way a user_pseudo_id filter does. This is structural to the question itself.
This table carries no date partitioning at all, so a range filter on event_time cannot prune by day either.

The partition BigQuery already has

A careful reader will notice the second cause above and should not have to go looking for it: BigQuery's own _TABLE_SUFFIX pruning is exactly what lets its version of this query scan only 4,455,256 bytes instead of the whole dataset. ClickHouse would prune the same way with one line added to the table definition, PARTITION BY toYYYYMMDD(event_date).

Worth naming plainly: ClickHouse is not even using an advantage it could have here. Without that partition, the unpartitioned raw query above still answers in 52 ms against BigQuery's already-pruned 0.98 s. Adding the partition would make the raw query faster still. Neither move is what this module is actually building toward. The materialized view below is a different, stronger lever again.

Step 1: Build the materialized view

Build an AggregatingMergeTree table to hold the aggregated result, and a materialized view that keeps it current on every insert:

CREATE TABLE bq.funnel_agg
(
  minute          DateTime,
  device_category LowCardinality(String),
  geo_country     LowCardinality(String),
  views           AggregateFunction(sum, UInt64),
  carts           AggregateFunction(sum, UInt64),
  checkouts       AggregateFunction(sum, UInt64),
  purchases       AggregateFunction(sum, UInt64),
  users           AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree
ORDER BY (minute, device_category, geo_country);

CREATE MATERIALIZED VIEW bq.funnel_mv TO bq.funnel_agg AS
SELECT
  toStartOfMinute(event_time)                          AS minute,
  device_category,
  geo_country,
  sumState(toUInt64(event_name = 'view_item'))         AS views,
  sumState(toUInt64(event_name = 'add_to_cart'))       AS carts,
  sumState(toUInt64(event_name = 'begin_checkout'))    AS checkouts,
  sumState(toUInt64(event_name = 'purchase'))          AS purchases,
  uniqState(user_pseudo_id)                            AS users
FROM bq.events_tuned
GROUP BY minute, device_category, geo_country;

AggregatingMergeTree exists to hold the result of a grouping instead of recomputing it. Its AggregateFunction(...)-typed columns do not store a finished sum or a finished distinct count -- they store a partial, mergeable state, which is what lets many small incremental updates combine into the same right answer a single bulk aggregation would have produced. The materialized view is what keeps that state current: it is not a saved query re-run on demand, it is attached to bq.events_tuned and fires automatically on every batch inserted there, writing new partial states for whichever (minute, device_category, geo_country) buckets that batch touched.

Write with the -State combinator matching each aggregate (sumState, uniqState) and read with the matching -Merge combinator (sumMerge, uniqMerge) -- AggregatingMergeTree requires the combinator that produced a partial state to be undone by its matching merge combinator, not any aggregate that happens to sound similar.

An empty result does not mean the view is broken

A materialized view only sees rows inserted after it is created. Nothing already sitting in bq.events_tuned before that moment appears in it automatically. You just built the view against a table that already holds the full 4,295,584-row export, so bq.funnel_agg comes back completely empty on every query right now: no error, just zero rows. This is the single most common way this kind of view goes wrong, and from the query side it looks exactly like a broken view.

The fix is a one-time backfill: run the identical -State aggregation the view's own SELECT performs, once, as a plain INSERT ... SELECT covering every row that already existed before the view did.

INSERT INTO bq.funnel_agg
SELECT
  toStartOfMinute(event_time)                          AS minute,
  device_category,
  geo_country,
  sumState(toUInt64(event_name = 'view_item'))         AS views,
  sumState(toUInt64(event_name = 'add_to_cart'))       AS carts,
  sumState(toUInt64(event_name = 'begin_checkout'))    AS checkouts,
  sumState(toUInt64(event_name = 'purchase'))          AS purchases,
  uniqState(user_pseudo_id)                            AS users
FROM bq.events_tuned
GROUP BY minute, device_category, geo_country;

Run this before you trust a single row your dashboard shows you.

Step 2: Confirm the win

Run the dashboard query against bq.funnel_agg, for the same one-day window you measured in "Measure the raw cost":

SELECT
  minute, device_category, geo_country,
  sumMerge(a.views)     AS views,
  sumMerge(a.carts)     AS carts,
  sumMerge(a.checkouts) AS checkouts,
  sumMerge(a.purchases) AS purchases,
  uniqMerge(a.users)    AS users,
  round(sumMerge(a.carts) / nullIf(sumMerge(a.views), 0), 4) AS cart_rate
FROM bq.funnel_agg AS a
WHERE minute >= '2025-12-01 00:00:00' AND minute < '2025-12-02 00:00:00'
GROUP BY minute, device_category, geo_country
ORDER BY minute DESC, device_category, geo_country
SETTINGS use_query_condition_cache = 0;

Read read_rows straight off the console's results bar, the same way you did for the raw baseline:

4,295,584 rowsbq.events_tuned, raw scan

16,384 rowsbq.funnel_agg, via the view

262.2xfewer rows read

This is not a tuning win the way module 05 was. No sort key, no partition, no codec choice turns a whole-table scan into anything cheaper for a query that has to group every row in the window. What turns 4,295,584 rows read into 16,384 is a different kind of object entirely: a table that already holds the answer, kept current by a view instead of recomputed by a query. BigQuery has nothing that fills this role. A scheduled query or a summary table gets you partway there, but neither one updates itself, incrementally, row by row, as new events land. That gap is this workshop's whole argument in one number.

Verify the view didn't change the data

Aggregating into a materialized view should never change what the underlying events say -- only how fast you can ask about them. Before trusting the read_rows win from Step 2, prove that bq.funnel_agg produces exactly the same four conversion counts for this window as computing them directly from bq.events_tuned. A fingerprint does that as a single number: groupBitXor folds a cityHash64 of every row into one value, so two full result sets can be compared by comparing one number instead of scrolling through rows.

Why order does not matter

groupBitXor over cityHash64 is commutative and associative, so the fingerprint depends only on the set of rows fed into it, never the order. This is the same property module 05's check already relied on.

Why `users` is left out of the check

This check deliberately leaves users (the uniq/uniqMerge distinct-visitor estimate) out.

uniq is approximate, and its merged internal state is not guaranteed to come out bit-identical between a bulk backfill and an incremental per-insert path, even when both converge to the same visible estimate. Including it would risk flagging a genuinely correct answer over a sketch-representation difference, not a real difference in the underlying data. views, carts, checkouts and purchases are exact sums with no such risk, and they are what this check actually compares.

Why every field is coalesced first

Every field on both sides is wrapped in ifNull, and that is not decoration.

cityHash64 returns NULL if any argument is NULL, and groupBitXor silently skips NULL inputs rather than erroring. So a nullable column with a NULL in it drops that row out of the fingerprint while count() still counts it, and a column that is NULL throughout collapses the fingerprint to \N. Both sides of this check read tables you built, so a NULL would poison both, and the two \N values would read as equal: a check that passes while proving nothing, which is worse than one that fails.

NULL is a sentinel here, not a value: both sides map it to '' for strings and 0 for numbers before hashing, and the two grouping keys are coalesced inside the subquery so a NULL and an '' land in the same group on both sides.

Run the check against your view

SELECT
  count() AS row_count,
  groupBitXor(cityHash64(ifNull(toString(minute, 'UTC'), ''),
                         device_category, geo_country,
                         ifNull(views, 0), ifNull(carts, 0),
                         ifNull(checkouts, 0), ifNull(purchases, 0))) AS fingerprint
FROM (
  SELECT
    f.minute                      AS minute,
    ifNull(f.device_category, '') AS device_category,
    ifNull(f.geo_country, '')     AS geo_country,
    sumMerge(f.views) AS views, sumMerge(f.carts) AS carts,
    sumMerge(f.checkouts) AS checkouts, sumMerge(f.purchases) AS purchases
  FROM bq.funnel_agg AS f
  WHERE f.minute >= '2025-12-01 00:00:00' AND f.minute < '2025-12-02 00:00:00'
  GROUP BY minute, device_category, geo_country
);

Run the same check against the raw baseline

Then compute the same shape directly from bq.events_tuned, reused here as the raw baseline. The two fingerprints should match:

SELECT
  count() AS row_count,
  groupBitXor(cityHash64(ifNull(toString(minute, 'UTC'), ''),
                         device_category, geo_country,
                         ifNull(views, 0), ifNull(carts, 0),
                         ifNull(checkouts, 0), ifNull(purchases, 0))) AS fingerprint
FROM (
  SELECT
    toStartOfMinute(t.event_time) AS minute,
    ifNull(t.device_category, '') AS device_category,
    ifNull(t.geo_country, '')     AS geo_country,
    sum(ifNull(t.event_name, '') = 'view_item')      AS views,
    sum(ifNull(t.event_name, '') = 'add_to_cart')    AS carts,
    sum(ifNull(t.event_name, '') = 'begin_checkout') AS checkouts,
    sum(ifNull(t.event_name, '') = 'purchase')       AS purchases
  FROM bq.events_tuned AS t
  WHERE t.event_time >= '2025-12-01 00:00:00' AND t.event_time < '2025-12-02 00:00:00'
  GROUP BY minute, device_category, geo_country
);

A mismatch means bq.funnel_agg is answering a different question, not just a faster one -- almost always the missing backfill from Step 1, not the aggregate functions themselves.

Done when

bq.funnel_agg holds a backfilled history, your dashboard query and raw-baseline check produce matching fingerprints, and you can say in one sentence why an incremental materialized view is the lever here when a sort key was the lever in module 05. Continue to 07 Ask your data a question when you are ready.