08 Your real migration

Outcome

Everything so far has moved one fixed export, once, into one table. A real migration runs continuously, evolves, and has to make decisions this workshop's dataset never forced. This module opens with why that migration is worth doing at all, then names those decisions, then makes the case this whole workshop has been building toward: BigQuery does not go away, and it should not.

Why migrate at all?

ClickHouse's own published cost-performance benchmark (December 2025) is a better answer to this than anything this workshop's own 4.3-million-row sample can support -- that dataset is intentionally too small to show a cost gap, and this workshop has said so plainly rather than force one. The benchmark below runs ClickBench's 43 unmodified queries, no hand-tuning on either side, against real compute billing at each vendor's actual rates, not list price:

Concede the same way this workshop has all along: at 1 billion rows, ClickHouse Cloud and BigQuery Enterprise are genuinely close -- 38 seconds and 80 cents against 23 seconds and 67 cents. The gap that motivates a migration shows up as the data grows, not at this scale. By 10 billion rows, BigQuery Enterprise takes 5.2x longer and costs 2.7x more for the identical 43 queries. By 100 billion rows it takes 14x longer and costs 7.2x more. BigQuery on-demand, which bills per byte scanned rather than per provisioned node, falls behind faster still -- its bill grows with how often a query runs, not with how much compute was reserved ahead of time, which is exactly the per-request, customer-facing shape every challenge in this workshop modeled.

Continuous ingestion, not a one-off load

The ClickPipe you set up in module 03 read a fixed set of Parquet objects once and stopped. A production pipeline reads continuously -- new export files landing on a schedule, or a streaming source, arriving for as long as the business runs. The mechanics are the same ClickPipes concept, pointed at a source that keeps producing rather than a bucket that stopped changing the day you read it. What is different in production is everything downstream of that pipe having to assume rows keep arriving indefinitely: a materialized view built once and left alone, the way module 06's dashboard MV works, rather than a table you load, tune and walk away from.

Schema evolution

This workshop's export schema never changed underneath you. A real GA4 export schema does -- BigQuery and its export format add fields over time, and a events-shaped table built for this workshop's 2026 export will not be byte-for-byte identical to next year's. Plan for it: a residual event_params Map absorbs a genuinely new key without a schema migration, at the cost of that key not getting its own type or codec until you notice it is worth extracting. A new top-level column BigQuery adds to its export, by contrast, does need a real ALTER TABLE on the ClickHouse side -- nothing about a Map absorbs a new sibling of device_category or traffic_source. Decide which kind of change you can absorb silently and which kind needs a person to notice and act.

event_params as a Map, or flattened: when each wins

The compression challenge's real lesson was not "apply a codec" -- it was extract what you actually query into typed, named columns, and keep everything else in a residual Map rather than storing the same bytes twice. That lesson generalizes past this one dataset.

Flatten a key into its own column when you know today that queries will filter or group on it -- page_location, ga_session_id, engagement_time_msec in this workshop's case. A typed column gets its own codec, can lead a sort key, and a query planner can prune on it directly. The cost is committing to that key's shape ahead of time, and losing the distinction between "this key was absent" and "this key held an empty or zero value" once it is pulled into a non-nullable typed column -- values are always recoverable from the dedicated column, but presence or absence generally is not, once merged into a typed default.

Leave a key in the Map when you do not yet know it is worth a dedicated column, or when event_params genuinely is schema-on-read for your workload -- an analyst exploring what keys even exist, or a key so rarely queried that a typed column would be pure overhead. The Map costs more per byte at rest and cannot lead a sort key or be pruned on directly, but it commits you to nothing. Most real event schemas end up with both: a handful of typed columns for what every dashboard query touches, and a residual Map for the long tail nobody has built a query against yet.

Retention

Nothing in this workshop deleted a row. A production pipeline usually needs to: raw event detail is often only useful for a bounded window -- weeks to a few months, depending on the business -- after which storage cost outweighs the value of keeping row-level detail around. ClickHouse's TTL clause expresses that directly on a table (drop rows past an age) or roll them into a coarser aggregate instead of dropping them outright, which is the same idea module 06's materialized view already applies to a dashboard: keep the granularity a live query needs close to it, and let anything coarser absorb the rows that would otherwise sit there indefinitely at full detail.

Coexistence, not replacement

This is the point the whole workshop has been aimed at, and it is not "move everything off BigQuery."

BigQuery is built for a question nobody anticipated, over data nobody has modeled yet, at a scale where provisioning a cluster ahead of time makes no sense. Module 01 already conceded this and meant it: zero operational surface, no schema commitment, point it at petabytes and ask. That is a real advantage for ad-hoc, exploratory, schema-on-read analytics, and nothing in this workshop changes that.

ClickHouse is built for the question a real user asks by loading a page: the same shape of query, over and over, from many people at once, where the answer has to come back in milliseconds and the pricing has to survive being asked that often. That is the shape every challenge in this workshop modeled -- a profile lookup, a live funnel dashboard -- and it is a shape BigQuery's per-byte, per-job pricing and job-dispatch latency were never built to serve cheaply at that request rate, as the benchmark above just showed at real production scale.

The right architecture keeps both, each doing the job it is actually good at: BigQuery holds the ad-hoc, schema-on-read, petabyte-scale exploration; ClickHouse holds the customer-facing, concurrent, latency-bound serving layer in front of it. Neither replaces the other, and a migration that tries to make ClickHouse do BigQuery's job -- or the reverse -- is solving the wrong problem.

Send us your own schema

If what you saw in this workshop looks like your own workload -- a events table, a dashboard your users hit repeatedly, a BigQuery bill that scales with how often people look at something -- send your schema and your data volumes to your ClickHouse contact. What comes back is a real sizing and cost comparison against your own numbers, not an extrapolation from a public sample dataset. That is a genuinely useful next step, not a sales gesture: the published benchmark above is still someone else's workload, run on someone else's data. The same offer, done properly, replaces it with your own production schema and your own numbers.

Your own slowest query

Go back to module 01 and the puzzle it opened with -- BigQuery pruned its scan down to 4,455,256 bytes and still took 0.98 seconds, while the same question against ClickHouse answered in 52 milliseconds with no pruning at all, and 14 with a materialized view. Now name the query in your own environment that has that same shape: the one your team already knows is slow, that a customer or a colleague waits on, that gets asked the same way over and over by different people. That query, not this dataset, is the one worth the sizing conversation above.

Done when

You can name, in one sentence each, what changes about ingestion, schema and retention between this workshop and a real pipeline; you can say which workload BigQuery should keep and which one moves to ClickHouse, and why; and you have a specific slow query from your own environment in mind, not just this workshop's dataset. Continue to Troubleshooting if something along the way did not behave the way this workshop said it would.