Floor helpers

You do not need to have done this workshop to help run it. This page is everything you need on the floor: modules 04 through 06 in one line each, the five failures you will actually be asked about, and one rule about what not to do.

The three modules, one line each

Every module is a guided walkthrough, not a challenge -- every participant runs the same steps in order and sees the same DDL, so there is no hidden answer to protect anywhere in this workshop.

• Module 04 (shrinking the table). The lever is type sizing, LowCardinality, codecs, and sort order, walked through one step at a time against the naive BigQuery-inferred schema. The real lesson underneath all of that is to extract what you actually query into typed columns, keep everything else in one residual map, and never store the same bytes twice.
• Module 05 (the sort key). A fixed single-user lookup, measured on read_rows from system.query_log, not on the console's clock. The lever is which column leads the ORDER BY -- a sort key only prunes on its leading column (or a leading prefix held equal), nothing further back in the list.
• Module 06 (the impossible dashboard). A per-minute funnel that groups every row in its window, so no sort key rescues it. The lever is an incrementally-maintained materialized view on AggregatingMergeTree, written with -State functions and read with the matching -Merge functions, plus a one-time backfill for every row that existed before the view did.

The five failures you will actually be asked about

1. "My count is way too big -- like 10x." They put arrayJoin on event_params (or a similar array column) in the same SELECT as a count(). arrayJoin expands every row into one row per array element for the whole query, so count() counts parameter-entries, not events. Fix: use an -Array combinator instead -- uniqExactArray(event_params.key), not uniqExact(arrayJoin(event_params).key).

2. "My CREATE TABLE fails: 'Sorting key contains nullable columns.'" Every column in the BigQuery export infers as Nullable, and a MergeTree sort key cannot be built on a nullable column by default. Fix: either add SETTINGS allow_nullable_key = 1 to get past the error, or -- the approach modules 04 and 05 both walk through -- give the sort key's leading columns a real non-nullable type with a sensible default instead of carrying Nullable through.

3. "My materialized view / dashboard is empty." A materialized view only sees rows inserted after it was created -- it is a trigger on new inserts, not a saved query re-run against existing data. Anything already sitting in the source table before the view existed never passes through it. Fix: a one-time INSERT ... SELECT into the view's backing table, running the identical aggregation, covering everything that already existed. This is the single most common way module 06's view goes wrong.

4. "I tuned this and the console shows no difference." The SQL console's wall clock includes a full network round trip -- tens to hundreds of milliseconds -- on top of whatever the query did, and that alone can be bigger than the entire improvement being measured. Fix: copy the query's query_id and read read_rows (or read_bytes, query_duration_ms) off system.query_log instead of trusting the console's stopwatch. Two more things can lie to this same measurement: the query condition cache rewards repeating the exact same predicate (SETTINGS use_query_condition_cache = 0 turns it off), and a table that was just bulk-loaded has several un-merged parts that each get scanned separately -- that settles on its own within a few minutes as ClickHouse's background merges catch up, so re-run the measurement a little later rather than trusting the first number off a fresh load. Point them at whichever module covers this -- both modules 05 and 06 walk through it in full.

5. "My timezone check found a million wrong rows." Comparing event_date (a string) against a conversion of event_timestamp without passing 'UTC' explicitly to both fromUnixTimestamp64Micro and formatDateTime converts through the host or server's local timezone instead, which produces a large false mismatch on data that was correct all along -- worse the further the service's region is from UTC. Fix: pass 'UTC' explicitly to every timestamp-formatting function on both sides of any such comparison.

More failures than these five exist -- learner/troubleshooting.mdx is the full reference, including a schema-import path issue with ClickPipes and an alias-shadowing hazard when flattening nested columns. Point a participant there directly if their problem is not one of the five above.

What not to do

Do not type the answer into a participant's console for them, however stuck they look. Every module already gives the exact DDL and the exact queries -- what a participant is usually missing is not the lever itself but why the previous step didn't work, and that is exactly what the five failures above are for. Walk them through diagnosing a broken CREATE TABLE, an empty view, or a bad measurement, and let them type the fix themselves. A participant who watches you fix it learns nothing they can repeat on their own migration; a participant who fixes it once, guided, usually does not need help with the same failure again.

Who to escalate to

Anything outside the five failures above, and anything that looks like a real service or ClickPipes outage rather than a participant's SQL, goes to whoever is running the session, not to a judgment call on the floor. If the instructor is mid-beat and cannot break away, note the participant's handle and module number and follow up at the next break rather than guessing at a ruling in the moment.