How to Migrate from Snowflake to ClickHouse

Why migrate Snowflake data to ClickHouse?

Cut storage costs, achieve sub-second analytics, and keep full SQL support by off-loading historical or high-volume tables to ClickHouse.

What are the high-level steps?

1) Export Snowflake tables to external storage (S3/GCS). 2) Generate compatible DDL for ClickHouse. 3) Load files into ClickHouse. 4) Validate row counts and query results.

How do I export Snowflake tables?

Use COPY INTO @stage to write compressed Parquet files to cloud storage.Partition large tables by date to enable parallel loads.

Example

COPY INTO @mystage/orders FROM Orders FILE_FORMAT=(TYPE=PARQUET COMPRESSION=SNAPPY);

How do I create equivalent ClickHouse tables?

Translate Snowflake data types to ClickHouse types.Generate DDL with ORDER BY and PRIMARY KEY for query speed.

Example

CREATE TABLE Orders (id UInt64, customer_id UInt64, order_date Date, total_amount Decimal(12,2)) ENGINE = MergeTree() ORDER BY (order_date,id);

What’s the quickest way to load data?

Point ClickHouse at the export bucket with s3 table function or use clickhouse-client --query piping.

Example

INSERT INTO Orders SELECT * FROM s3('https://bucket/orders/*.parquet', 'aws_key', 'aws_secret', 'Parquet');

How do I validate the migration?

Compare counts and sample aggregates: SELECT count(*) FROM Orders.Cross-check totals with Snowflake to catch truncation or type mismatch.

Which tools automate the process?

Open-source clickhouse-migrator, Airbyte, and dbt can orchestrate export, DDL generation, and load retries.

Best practices for large tables?

• Compress Parquet files
• Use 64–128 MB file size for balanced parallelism
• Set max_partitions_per_insert_block in ClickHouse to avoid small parts

How do I keep data in sync?

Schedule hourly incremental exports using Snowflake streams and load them with ClickHouse Kafka engine or simple cron jobs.

How to revert quickly?

Retain Snowflake for critical workloads until ClickHouse has passed acceptance tests.Enable dual-write in ETL pipelines for a grace period.

What are common performance tweaks?

Use column compression codecs, increase max_threads, and align ORDER BY keys with common filters such as order_date.

When should I avoid migrating?

If you need ANSI compliant transactions, native time-travel, or automatic clustering without manual tuning, Snowflake may still fit better.

.