Troubleshooting

Reference material, not a module to work through in order. Come here when something behaves differently from what an earlier module said to expect.

A count that is roughly ten times too large

Symptom: count() alongside anything touching event_params returns a number far larger than the row count you expect -- roughly ten times too large is the specific shape this dataset produces.

Cause: arrayJoin in a SELECT list does not transform only the column it is called on. It expands every row in the whole query into one row per array element, and every other aggregate in that same query -- including count() -- sees the expanded rows, not the original ones. A BigQuery UNNEST-in-FROM instinct reaches for arrayJoin here and gets this by surprise.

-- WRONG: count() here counts event_params entries, not events
SELECT
  count()                                AS n,
  uniqExact(arrayJoin(event_params).key) AS distinct_keys
FROM bq.events_naive;
-- n: 46,095,652 -- not the event count (4,295,584), but the total number of
-- (key, value) pairs across every event's event_params array: 10.73 per event

Fix: reach for an -Array combinator instead. uniqExactArray, sumArray and the rest apply element-wise, without multiplying rows:

-- RIGHT: pull the key array out first, no arrayJoin needed
SELECT
  count()                          AS n,
  uniqExactArray(event_params.key) AS distinct_keys
FROM bq.events_naive;
-- n: 4,295,584 -- matches the real event count

If a count ever looks larger than you expect, check whether an arrayJoin snuck into the same SELECT list first, before you suspect the data.

A materialized view that returns nothing

Symptom: you created a materialized view over an existing table, queried it, and got zero rows back -- even though the source table is clearly not empty.

Cause: a MATERIALIZED VIEW only sees rows inserted after it was created. It is a trigger on new inserts, not a query re-run against existing data. Rows already sitting in the source table when the view was created never pass through it.

Fix: backfill separately, as its own explicit step, once the view exists:

INSERT INTO bq.funnel_agg
SELECT /* the same SELECT the view's definition uses */
FROM bq.events_tuned;

Do this once, right after creating the view, before you trust any aggregate read from it.

CREATE TABLE fails with "Sorting key contains nullable columns"

Symptom: CREATE TABLE refuses outright with an error naming a nullable column in the sort key.

Cause: BigQuery's export makes every column Nullable, and a MergeTree sort key cannot be built on a nullable column by default -- NULL has no defined position in a sort order.

Fix: either add the setting that allows it:

CREATE TABLE bq.events_naive (...)
ENGINE = MergeTree
ORDER BY event_timestamp
SETTINGS allow_nullable_key = 1;

or, better, do not carry the nullability through at all: give the sort key's leading columns a real non-nullable type with a sensible default instead of Nullable. The setting gets you past the error; a non-nullable type is the actual fix, and it is what the compression and sort-key challenges both reward.

event_date sorts correctly, but only by luck

Symptom: date range filters and min()/max() over event_date give the right answer, even though event_date is a String, not a Date.

Cause: event_date is a string in %Y%m%d form -- "20260115". Lexicographic string order and calendar order happen to agree for exactly that format, so comparisons come out right by coincidence, not because ClickHouse understands it as a date. Any other layout of the same information -- "1/15/2026", or a locale that writes the day first -- would break every one of these comparisons silently, with no error to flag it.

Fix: nothing is broken to fix in this dataset, but do not build on that string as if it were a real date type. Parse it with toDate(parseDateTime64BestEffort(event_date)) or similar before relying on it for anything beyond string equality, and never assume a BigQuery export's string-encoded date will sort correctly in general.

A query that looks no faster in the console

Symptom: you rebuilt a table specifically to make a query faster, ran both versions in the SQL console, and the wall-clock time barely changed.

Cause: what the console's clock measures includes a full network round trip between your browser and the service -- tens to hundreds of milliseconds, every run -- on top of whatever the query itself did. That round trip does not shrink no matter how well you tuned the query, and it can be larger than the entire improvement you are trying to measure.

Fix: do not trust the console's clock for anything smaller than that round trip. Read read_rows directly off the console's results bar instead -- unlike a wall clock, it does not move between runs of the same query, because it counts something the query actually did, not how long the round trip happened to take. Modules 05 and 06 walk through this in full.

ClickPipes shows no rows

Symptom: you created a ClickPipe, it shows as running or completed, and the destination table is empty or short of the expected count.

Cause: almost always one of two things. The source path glob does not actually match the objects in the bucket -- a typo in the prefix, a missing *, or a path that points one directory too high or too low. Or the pipe has not actually finished; "started" and "completed" are different states, and a pipe that is still ingesting will under-report until it finishes.

Fix: re-check the exact path against what module 03 used --

https://storage.googleapis.com/ch-workshop-bq-migration/ga4-events/*.parquet

-- and confirm the pipe's status reads Completed, not merely Running, before you count rows. Once it is, SELECT count() FROM bq.events_naive should return 4,295,584.

An alias that shadows its own source column

Symptom: a query compiles and returns rows, but a value comes out wrong in a way that looks like it is reading the wrong column -- most often when flattening a nested field.

Cause: an output alias that reuses the name of a source column resolves to the alias, not the underlying column, anywhere else it appears in the same query. This bites hardest when the natural, obvious name for a flattened field is identical to its parent struct's own name -- traffic_source.source naturally wants to be aliased traffic_source, which is also bq.events_naive's own top-level traffic_source tuple column. Reference traffic_source again anywhere later in the same query and it now means the alias, not the struct.

Fix: qualify every source-table reference with a table alias, so there is no name left for an output alias to shadow:

-- WRONG: bare traffic_source is ambiguous once aliased below
SELECT traffic_source.source AS traffic_source, ...
FROM bq.events_naive;

-- RIGHT: qualify the source reference with a table alias
SELECT n.traffic_source.source AS traffic_source, ...
FROM bq.events_naive AS n;

This hit three separate times while this workshop's own reference queries were built, always while flattening a nested schema. Qualify source references on any query that both reads a nested struct and names its output column after the struct.

Measuring a sort key before the parts have merged

Symptom: you changed a table's sort key specifically to reduce rows read, measured it right after loading data, and the improvement looks much smaller than expected -- or absent.

Cause: a single bulk INSERT lands as several parts, each sorted independently. The same value -- one user's rows, one day's rows, whatever your sort key groups on -- sits in every one of those parts until a background merge combines them, so a query touches one granule per part instead of one granule total, no matter how good the sort key is. This is a transient artifact of a fresh bulk load, not the steady state the table settles into.

Measured in this exact workshop: a table read 57,344 rows across 5 scattered granules right after loading, and 16,384 rows across 2 contiguous granules a few minutes later, once ClickHouse's background merges caught up on their own -- the same table, the same query, the same sort key.

Fix: give the table a few minutes after loading, then re-run the measurement -- trust the later number over whatever you measured right after the insert.

Module 05 covers this in full, alongside the query condition cache, which produces a related but separate illusion -- a naive baseline that looks artificially fast because it has already seen the exact literal predicate you are timing. Do both -- wait for the parts to merge, and disable the condition cache with SETTINGS use_query_condition_cache = 0 -- before trusting any read_rows figure this workshop asks you to report.