ClickHouse to BigQuery: How to Move Your Data

Moving data from ClickHouse to BigQuery is a common step when analytics consolidates onto Google Cloud. ClickHouse handles the fast operational analytics; BigQuery becomes the place where that data meets everything else, governed by IAM and queryable by the whole organisation.

The pipeline itself is straightforward. What deserves attention is one BigQuery decision you cannot undo, one loading-method choice, and the fact that the ClickHouse source cannot tell BigQuery when rows disappear.

ClickHouse to BigQuery at a glance:

Why replicate ClickHouse into BigQuery?

ClickHouse and BigQuery are both columnar analytical stores, so the motivation is rarely raw query capability. It is consolidation. Teams end up with ClickHouse serving a product analytics or observability workload while the rest of the business runs on BigQuery, and the two datasets cannot be joined without moving one of them.

Replicating into BigQuery also brings ClickHouse data under Google Cloud IAM, into the same billing model, and within reach of Looker, Dataform, and whatever else already points at your warehouse. The trade-off is a copy to maintain.

How does the ClickHouse source work?

The ClickHouse source connects over JDBC and supports full refresh and incremental syncs. Incremental is cursor-based: you nominate a column, typically a timestamp or an incrementing ID, and each sync reads rows newer than the last recorded value. ClickHouse Server 21.3.10.1 or later is required, SSL is supported, and an SSH tunnel is available if the instance is not directly reachable from Airbyte.

There is no CDC option, which means deletes do not propagate. Rows removed in ClickHouse persist in BigQuery until a full refresh rebuilds the table. For append-only event data this is a non-issue; for anything mutable, schedule a periodic full refresh.

Choose your cursor carefully. A column that is not reliably monotonic will silently skip rows, and no error will tell you it happened.

How do you set up the BigQuery destination?

Step 1: Create a service account

The BigQuery destination needs a Google Cloud service account holding the BigQuery User and BigQuery Data Editor roles, with its key in JSON format. This is required on Airbyte Cloud and optional on open source. Most permission errors during setup trace back to one of those two roles being missing.

Step 2: Choose the dataset location carefully

When you configure the destination you set a Project ID, a Dataset Location, and a Default Dataset ID. The location is the one to think about, because it cannot be changed afterwards. Getting it wrong means recreating the dataset and resyncing.

If you plan to join Airbyte-synced data with existing BigQuery datasets, create them in the same location. Cross-location joins are not something you can retrofit.

Step 3: Pick a loading method

Two options. Batched Standard Inserts converts inserts into file uploads that BigQuery loads in batches, with staging files managed and cleaned up by BigQuery automatically. It is the simplest performant choice and needs no extra infrastructure.

GCS Staging gives you control over where staging files live, at the cost of provisioning and permissioning a bucket. One hard constraint applies: the bucket must use Google-managed encryption, which is the default for new buckets. Customer-managed encryption keys are not supported. Check the Encryption type row under your bucket's Configuration tab before wiring it up. The service account also needs write permissions on the bucket and path.

Step 4: Connect and sync

Create the connection, select tables, pick full refresh or incremental with a cursor, and set a frequency. If your organisation restricts access by IP, add the Airbyte Cloud IP addresses to your allowlist first.

What should you know about naming in BigQuery?

BigQuery dataset naming conventions apply, and Airbyte adapts to them rather than failing. Invalid characters are converted to underscores. Because datasets beginning with an underscore are hidden in the BigQuery Explorer panel, Airbyte prepends converted namespaces with n so they remain visible.

The practical effect is that a ClickHouse database or table name with characters BigQuery dislikes will not arrive under the name you expect. Check what actually got created after the first sync rather than writing queries against assumed names.

Frequently asked questions

Can I change the BigQuery dataset location later?

No. The location is fixed when the dataset is created. Changing it means creating a new dataset in the correct location and resyncing, so decide before the first run, especially if you will join against existing datasets.

Which loading method should I use?

Batched Standard Inserts is the simpler default and BigQuery manages the staging files for you. Choose GCS Staging when you need control over where staging files are written, and be prepared to manage bucket permissions.

Does GCS staging work with customer-managed encryption keys?

No. The staging bucket must use Google-managed encryption, which is the default for new buckets. CMEK buckets are not supported by the connector.

Will deletes in ClickHouse be reflected in BigQuery?

No. The ClickHouse source has no CDC, so deletions are invisible to Airbyte. A periodic full refresh is the practical way to bring the destination back in line.

What permissions does the service account need?

BigQuery User and BigQuery Data Editor, or equivalent. If you use GCS staging, it also needs permission to write to the bucket and path.

Get your ClickHouse data into BigQuery

Set the dataset location deliberately, pick a loading method, and the rest is a normal Airbyte connection. Consolidating onto Snowflake instead? See our guide to ClickHouse to Snowflake.

Connector behaviour described here reflects the ClickHouse source and BigQuery destination, verified August 2026. Check the linked documentation for current versions.

Start syncing now →