ClickHouse to Snowflake: How to Move Your Data
Replicate ClickHouse into Snowflake with Airbyte. Cursor-based syncs, cost-efficient warehouse setup, and the number-type default that changes your values.
Moving data from ClickHouse to Snowflake usually means one of two things: you are consolidating a fast analytical store into a central warehouse where governance and BI already live, or you are migrating off ClickHouse entirely. Either way the mechanics are the same, and the details that bite are on the Snowflake side rather than the ClickHouse side.
This guide covers configuring the ClickHouse source, preparing Snowflake to receive data cheaply, and one numeric setting that will quietly change your values if you leave it alone.
ClickHouse to Snowflake at a glance:
Why move ClickHouse data into Snowflake?
ClickHouse is exceptional at what it does: columnar storage, fast scans, real-time analytical queries. What it is not is the place most organisations govern data centrally. If finance reports out of Snowflake, if your dbt project targets Snowflake, if row-level access policies live there, then ClickHouse data that stays in ClickHouse is data your governance model does not cover.
Replicating into Snowflake puts event or telemetry data next to the rest of the business, joinable in one query engine. The alternative case is a straight migration, where the pipeline runs until cutover and is then retired.
How does the ClickHouse source replicate?
The ClickHouse source is built on Airbyte's JDBC codebase and uses the ClickHouse JDBC driver. It supports full refresh and incremental syncs, where incremental means cursor-based: you nominate a column, and Airbyte reads rows whose cursor value is newer than the last sync.
There is no change data capture. That has one consequence worth stating plainly: deletions in ClickHouse are not reflected in Snowflake. A row removed from the source stays in the destination until a full refresh replaces the table. If your ClickHouse tables are append-only event data, this rarely matters. If rows get deleted, plan a periodic full refresh or a reconciliation step.
The source requires ClickHouse Server 21.3.10.1 or later, supports SSL, and can connect through an SSH tunnel via a bastion host if ClickHouse is not directly reachable. It does not alter the schema in your ClickHouse instance.
How do you prepare Snowflake?
Step 1: Create dedicated Snowflake entities
The Snowflake destination docs include a setup script that creates a role, user, warehouse, database, and schema specifically for Airbyte. Run it from a worksheet with the ACCOUNTADMIN role. Using dedicated entities is not ceremony. It isolates permissions and makes Airbyte's compute spend legible on your Snowflake bill.
The script provisions an X-Small warehouse with a 60-second auto-suspend, and that is deliberate. Snowflake bills compute per second and the warehouse resumes every time Airbyte loads. The smallest tier with a fast suspend keeps idle charges down. Airbyte also needs CREATE SCHEMA on the target database to create schemas itself; if you prefer to create them manually, the connection user needs OWNERSHIP on them.
Step 2: Handle network policy and authentication
Snowflake permits connections from any IP by default, but if a security administrator has set a network policy, add the Airbyte Cloud IP addresses to the allowlist. For authentication you can use a username and password, or key pair. For key pair, generate an unencrypted PKCS#8 key with openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt, derive the public key, and register it on the user with alter user <user_name> set rsa_public_key=<public_key_value>;.
Step 3: Configure the destination and run
Enter the host, role, warehouse, database, and default schema, then create a connection from the ClickHouse source. Select your tables, choose full refresh or incremental with a cursor column, set a frequency, and sync. Airbyte loads via Snowflake's Internal Stage; the database and schema need the USAGE privilege.
What is the number-type trap?
This is the setting most people miss. Snowflake's Decimal Data Type option controls how Airbyte maps the number type, and it defaults to FLOAT, an approximate binary floating-point type with roughly 15 digits of precision. The alternative, NUMBER(38,9), is an exact fixed-point type supporting up to 29 digits before the decimal point and 9 after.
ClickHouse Decimal columns holding money, rates, or measurements will land as approximate floats unless you change this. For financial data that is the wrong default. Switch it to NUMBER(38,9) before the first sync.
Changing it later is worse than setting it correctly up front. Airbyte will attempt to convert existing columns in place, and because the two types have different range and precision characteristics, that conversion can null out values or silently reduce precision. Run a full refresh after changing the setting on an existing connection.
Snowflake also enforces size limits that Airbyte respects by nulling out overruns and recording the change in the _airbyte_meta column: VARCHAR tops out at 16 MB and VARIANT at 128 MB. Values within range but with excessive precision are rounded rather than dropped, and flagged the same way.
Frequently asked questions
Does the ClickHouse source support CDC?
No. It offers full refresh and cursor-based incremental syncs. Deletions in ClickHouse will not be removed from Snowflake, so schedule periodic full refreshes if rows in your source get deleted.
Should I use FLOAT or NUMBER(38,9) in Snowflake?
NUMBER(38,9) for anything requiring exact decimals such as financial amounts, rates, and precise measurements. FLOAT suits scientific values, model scores, and probabilities where range matters more than exactness. FLOAT is the default, so change it deliberately.
How do I keep Snowflake costs down for Airbyte syncs?
Use a dedicated X-Small warehouse with a short auto-suspend, as in the setup script. Snowflake bills per second and the warehouse resumes on every load, so idle time between syncs is the thing to minimise.
What Snowflake version or edition do I need?
No specific edition is required, but you need an account where someone holds ACCOUNTADMIN to run the setup script, and the connection user needs schema creation or ownership rights on the target database.
Why is my sync failing with a target schema permission error?
Usually the connection is set to mirror the source structure and is trying to write to a schema the role lacks rights on, often PUBLIC. Switch the destination namespace to Destination Default, or grant the role permissions on the namespace you want.
Get your ClickHouse data into Snowflake
Set the number type correctly, size the warehouse small, and this pipeline runs quietly. Sending the same ClickHouse data to Google's warehouse instead? See our guide to ClickHouse to BigQuery.
Connector behaviour described here reflects the ClickHouse source and Snowflake destination v4.x, verified August 2026. Check the linked documentation for current versions.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.