From Google BigQuery to ClickHouse

Teams leave Google BigQuery for ClickHouse when the bill stops correlating with value. On-demand pricing charges by bytes scanned, so a few unoptimized dashboards hammering wide tables can cost more than a fleet of servers. Slot reservations smooth that out but lock you into committed spend. ClickHouse is a column-store built for high-volume aggregation, and on your own hardware (or ClickHouse Cloud) the unit economics for heavy analytical workloads are often an order of magnitude better. The cost is that you now own capacity planning and a different SQL dialect.

Start with the access pattern, not the schema

ClickHouse rewards a specific shape of query: large scans, heavy aggregation, predictable filters. If your BigQuery usage is mostly that, you are a strong candidate. If it is sparse point lookups and unpredictable ad-hoc joins across huge tables, temper expectations.

The single most important design decision is the ORDER BY key of your MergeTree tables. It is the primary index and it drives everything. BigQuery’s clustering columns are the closest analogue, so start there.

Pick the table engine to match how the data is written, not just how it is read. A plain MergeTree covers append-heavy event and fact tables, which is where most BigQuery analytical volume lives. Use ReplacingMergeTree when you need last-write-wins upserts, since ClickHouse does not cheaply update rows in place, and reach for SummingMergeTree or AggregatingMergeTree when you maintain pre-aggregated rollups that BigQuery would have recomputed on every scan. The engine choice, the ORDER BY, and the partitioning are one connected decision, so make them together rather than porting the BigQuery schema column-for-column.

Export: Parquet through GCS

Do not stream rows out of BigQuery one query at a time. Export to GCS as Parquet, which preserves types and compresses well. It also keeps the export cheap: EXPORT DATA avoids paying the per-TB-scanned charge repeatedly that streaming query-by-query would incur, and a single bounded export per large table or date range is restartable if it fails partway. The Parquet on GCS doubles as a reusable staging layer, so a Trino or DuckDB reader could later query the same files without re-exporting from BigQuery.

EXPORT DATA OPTIONS(
  uri='gs://my-bucket/events/*.parquet',
  format='PARQUET',
  overwrite=true
) AS SELECT * FROM `proj.dataset.events`;

Then pull it straight into ClickHouse with the gcs (or s3) table function. No intermediate loader required:

INSERT INTO events
SELECT * FROM gcs(
  'https://storage.googleapis.com/my-bucket/events/*.parquet',
  'ACCESS_KEY', 'SECRET',
  'Parquet'
);

Translate partitioning and clustering

BigQuery’s partitioning and clustering map to two different ClickHouse concepts, and conflating them is a classic mistake.

• A BigQuery date partition becomes a PARTITION BY expression, usually toYYYYMM(event_date). Keep partitions coarse; thousands of tiny partitions hurt.
• BigQuery clustering columns become the ORDER BY tuple, which controls data locality and sparse-index pruning.

CREATE TABLE events (
  event_date Date,
  user_id    UInt64,
  event_type LowCardinality(String),
  value      Float64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_date);

LowCardinality and tight integer types matter here in a way they never did in BigQuery, where storage was abstracted away.

Rewrite the SQL dialect

BigQuery Standard SQL and ClickHouse SQL diverge on functions, not just syntax.

• APPROX_COUNT_DISTINCT(x) becomes uniq(x) or uniqExact(x).
• SAFE_DIVIDE(a, b) has no direct twin; use a / nullIf(b, 0).
• Date math like DATE_ADD(d, INTERVAL 1 DAY) becomes d + INTERVAL 1 DAY or addDays(d, 1).
• STRUCT and nested fields become ClickHouse Nested columns or Tuples, which behave differently in GROUP BY.
• Window functions exist in both but edge-case semantics differ, so test them explicitly.

Validate parity, then dual-run

Migrations earn trust through numbers that match. For every critical report, run the BigQuery query and the ClickHouse query against the same date range and diff the aggregates. Watch for floating-point drift in SUM and for distinct-count gaps where you swapped exact for approximate.

Then dual-run: keep BigQuery as the system of record while ClickHouse ingests in parallel, point a few dashboards at ClickHouse, and watch cost and latency for a couple of weeks before flipping the rest.

Use the dual-run to prove the cost case, because BigQuery’s billing makes it measurable. On-demand pricing charges by bytes scanned, so the queries that hurt most on BigQuery, wide scans over large tables, are exactly the ones ClickHouse should run far cheaper on its own capacity. Put the scanned-bytes or slot spend for the migrated dashboards against the ClickHouse infrastructure (or Cloud) cost for the same queries, and treat any saving as illustrative until the parallel run has held under real concurrency. Model the comparison in the calculator above rather than trusting a single benchmark.

Where BigQuery still wins

Stay on BigQuery when:

• You want zero operations. ClickHouse self-hosted means you patch, scale, and back up. ClickHouse Cloud helps but is not free.
• You live deep in GCP. Native ties to GCS, Dataflow, Looker, and IAM are seamless and expensive to rebuild.
• Your workload is truly ad-hoc petabyte scanning by analysts who will not tune ORDER BY keys. BigQuery’s serverless scale absorbs that; ClickHouse punishes unindexed access patterns.
• Spend is already small and predictable. The migration effort will not pay back.

Making the call

Moving from BigQuery to ClickHouse is a cost play that only works when your workload is aggregation-heavy and your team can own infrastructure. Export to Parquet via GCS, ingest with the gcs function, translate clustering into ORDER BY and partitioning into PARTITION BY, and rewrite the dialect function by function. Validate result parity before you trust a single dashboard, and dual-run until the numbers and the latency hold. Model your scanned-bytes or slot spend against ClickHouse capacity in the calculator above to see whether the payback is real.