From Snowflake to ClickHouse

Snowflake is elastic and easy, and its credit-based pricing, separately metered compute and storage, plus egress and feature add-ons, is hard to forecast and expensive at sustained volume. ClickHouse is the most common open destination for high-volume analytics: a blazing columnar engine (open-source, with a managed Cloud option) that can cut cost dramatically for the right workloads. But it is not a drop-in Snowflake, pick it for the workloads it suits, not reflexively.

Where ClickHouse wins, and where it doesn’t

ClickHouse excels at real-time analytics, high ingest rates, and fast aggregation over large tables (MergeTree engines, materialized views). It wins hardest on dashboards, event/log analytics, and append-heavy fact tables. It is weaker than Snowflake at ad-hoc, heavy-JOIN data-warehouse workloads, complex multi-statement transactions, and the zero-management elasticity Snowflake gives you. If your warehouse is JOIN-heavy star-schema BI with unpredictable concurrency, evaluate Trino over a lakehouse instead. Know your query shape before committing.

Use open formats as the bridge

The clean migration path is export to Parquet on object storage, then load into ClickHouse, this avoids vendor-specific dump formats and gives you a reusable lakehouse layer. From Snowflake, COPY INTO @stage ... FILE_FORMAT = (TYPE = PARQUET) unloads tables to S3/GCS/Azure; ClickHouse ingests directly from object storage (s3() table function) into MergeTree tables. Inventory schemas, datasets, ETL/ELT jobs, BI connections, and SQL specifics first.

Because Snowflake bills compute, storage, and egress separately, the export itself has a cost: the unload consumes credits and the cross-cloud data transfer can add egress charges. Plan the export as a set of bounded jobs, one per large table or partition, rather than a single unbounded dump, so you can restart cleanly and keep the credit and egress cost visible rather than surprising. Keeping the Parquet files on object storage also gives you a reusable staging layer if you later want Trino or DuckDB to read the same data.

Getting the physical layout right

Snowflake hides physical layout behind micro-partitions and optional clustering keys, so most teams never think about it. ClickHouse makes that layout explicit and it is the single biggest lever on performance.

• Pick the engine first. A plain MergeTree covers append-heavy fact tables. Use ReplacingMergeTree when you need last-write-wins upserts, and SummingMergeTree or AggregatingMergeTree when you pre-aggregate.
• Choose ORDER BY for your predicates. The ORDER BY tuple is the primary key and the sparse index. Lead with the columns you filter and range-scan on most often. A well-chosen tuple lets ClickHouse skip most of the data; a poorly chosen one forces full scans and erases the cost advantage.
• Keep partitions coarse. PARTITION BY toYYYYMM(event_date) is a common choice. Partitioning is for retention and dropping old data, not for query pruning, so avoid thousands of tiny partitions.
• Use tight types. LowCardinality(String) for low-distinct columns and narrow integer types matter for compression in a way they never did on Snowflake, where storage was abstracted away.

Rewriting the SQL

This is the substance of the project. Differences to plan for:

• Functions and window syntax. Snowflake and ClickHouse diverge on function names, window semantics, and date/time helpers. Convert and test key queries on a sample before full load rather than assuming portability.
• Semi-structured data. Snowflake VARIANT, dot-path access, and FLATTEN map to ClickHouse JSON handling, Tuple, or Nested columns, which behave differently in GROUP BY. Rewrite these early.
• Concurrency model. ClickHouse favors fewer, heavier analytical queries; high-concurrency point lookups are an anti-pattern.
• Updates and deletes. ClickHouse mutations are asynchronous and costly, design for append-mostly, using ReplacingMergeTree or similar for upserts.

Re-point ETL/ELT pipelines and BI tools (Tableau, Power BI, Looker) to ClickHouse.

Cutover via reconciliation

1. Export full datasets to Parquet; load into ClickHouse MergeTree tables with the right ORDER BY/partitioning.
2. Convert and validate the SQL for your top queries and dashboards.
3. Run both systems in parallel, reconciling row counts and aggregate results, plus query-correctness and performance benchmarks.
4. Switch BI/consumers to ClickHouse only after sign-off; keep Snowflake authoritative until reconciled.

Proving the numbers and the savings

The acceptance bar is numbers that match and queries that are fast enough: reconcile aggregates against Snowflake, benchmark your real dashboards under realistic concurrency, and confirm ingestion keeps up with your event rate. Don’t cut over BI until the parallel run reconciles cleanly.

Do the parity and the cost check together, because Snowflake makes it easy to compare. On the parity side, run each critical query on both systems over the same date range and diff the aggregates; watch for floating-point drift in SUM and for gaps where a Snowflake exact-distinct was swapped for a ClickHouse approximate function. On the cost side, the parallel run is your evidence: measure the credits Snowflake still burns for the migrated workload against the ClickHouse infrastructure (or Cloud) cost for the same queries. Treat any savings as provisional until the parallel run has held under real concurrency, and model the comparison in the calculator above rather than trusting a single benchmark.

Cases where ClickHouse is the wrong call

Check the fit before you commit. If most of your Snowflake spend comes from JOIN-heavy star-schema BI with unpredictable concurrency, from multi-statement transactions, or from frequent row-level updates and slowly-changing-dimension patterns, ClickHouse will fight you rather than save you money. Its sweet spot is append-heavy, aggregation-first analytics: events, logs, metrics, and product analytics. For broad ad-hoc enterprise BI, evaluate Trino over a lakehouse, and consider moving only the heavy aggregation workloads to ClickHouse while leaving the rest where it already works.

So, is it worth it?

Snowflake → ClickHouse can slash cost for real-time and high-ingest analytics, but it’s a re-platform with real data-modeling and SQL-dialect work, not a lift-and-shift, and it’s the wrong move for JOIN-heavy, high-concurrency BI (consider Trino there). Bridge via Parquet, convert SQL, and cut over by reconciliation. Model your per-TB savings in the calculator above, and treat them as provisional until your parallel run proves both cost and performance.