ClickHouse vs Snowflake: Serving Latency, Concurrency, and Two Very Different Bills

I have watched this comparison start from the wrong premise more than once: a team benchmarks a ClickHouse query against a Snowflake query, sees a large difference, and concludes they should migrate the warehouse. Then they discover what a warehouse was doing for them — governance, elastic concurrency, joins across dozens of tables, and the ability to let anyone run bad SQL without breaking production.

The two systems are built for different jobs. Here is how I decide which one owns a given workload, and why the answer is usually “both, with a clear boundary”.

The architectural split

flowchart TD
  subgraph Snowflake
    s1[(Cloud object storage)] --> s2["Virtual warehouse XS"]
    s1 --> s3["Virtual warehouse L"]
    s1 --> s4["Virtual warehouse for BI"]
  end
  subgraph ClickHouse
    c1["Node with local NVMe"] --> c2[("MergeTree parts, sorted")]
    c3["Node with local NVMe"] --> c4[("MergeTree parts, sorted")]
  end

Snowflake scales compute against shared storage. Classic ClickHouse keeps data close to the CPU.

Snowflake separates storage from compute. Data lives once in object storage as immutable micro-partitions; any number of independent virtual warehouses read it. Adding a concurrent workload means starting another warehouse, and it neither competes for CPU with existing queries nor requires moving data.

ClickHouse (in its classic shared-nothing deployment) stores sorted MergeTree parts on local disk next to the CPU that reads them. There is no per-query provisioning step, which is a large part of why point queries return in milliseconds. Concurrency is bounded by the machines you are running right now. ClickHouse Cloud introduces separation of storage and compute and narrows this difference, but the data model and its consequences are unchanged.

Latency, and where it actually comes from

ClickHouse’s speed on serving queries is not magic and it is not solely the C++ implementation. It is that ORDER BY on a MergeTree table is the primary index. Rows are physically sorted, granules carry marks, and a query filtering on a sort key prefix reads a handful of granules instead of the table.

Get that key wrong and the advantage disappears entirely. A filter on a column that is not a sort key prefix is a full scan — fast, but a scan. I went through this in ClickHouse MergeTree: ORDER BY is the index, and it is the single most common reason a ClickHouse proof of concept underdelivers.

Snowflake prunes with micro-partition min/max metadata, which is automatic and requires no key design, but is coarser. Clustering keys recover some of it at a cost — the mechanics are in micro-partitions and pruning. And every Snowflake query pays warehouse scheduling overhead, which is fine for a dashboard and disqualifying for an API endpoint with a 100 ms budget.

So: for a filtered aggregation on a well-sorted table, ClickHouse answers in milliseconds and Snowflake in hundreds of milliseconds to seconds. If a human is looking at a dashboard, that difference is invisible. If an application is calling it per page load, it is the entire product.

Ingest patterns invert

This surprises people migrating in either direction.

Snowflake wants fewer, larger loads. Frequent tiny inserts create small micro-partitions and burn credits on warehouse uptime. Snowpipe or Snowpipe Streaming exists for continuous ingestion, with the cost profile covered in Snowpipe vs Snowpipe Streaming.

ClickHouse actively punishes small frequent inserts. Every insert creates a part; parts merge in the background; too many parts and you hit too many parts and the table stops accepting writes. The rule is to batch — thousands of rows or a time window per insert, via a buffer in your producer or asynchronous inserts.

-- ClickHouse: sort key first, everything else follows from it.
CREATE TABLE events
(
    tenant_id   UInt32,
    event_date  Date,
    user_id     UInt64,
    event_type  LowCardinality(String),
    amount      Decimal(18, 2),
    occurred_at DateTime64(3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, user_id)
TTL event_date + INTERVAL 13 MONTH;

-- Fast: filters match the ORDER BY prefix.
SELECT event_type, sum(amount)
FROM events
WHERE tenant_id = 42
  AND event_date >= today() - 30
GROUP BY event_type;

-- Slow: user_id alone is not a prefix, so this scans.
SELECT sum(amount) FROM events WHERE user_id = 991234;

That last query is the trap. In Snowflake it would be an unremarkable pruned scan. In ClickHouse it needs a skip index, a projection, or a second table sorted differently.

Joins, updates, and the things a warehouse does quietly

ClickHouse joins have improved substantially, and they are still not the reason you choose it. Large distributed joins across several big tables are where Snowflake’s optimizer and elastic compute earn their price. The ClickHouse-native answer is to denormalize into a wide table or use dictionaries for lookups — which is a modeling commitment, not a tuning flag.

Updates and deletes are the other gap. ClickHouse mutations rewrite parts asynchronously; they are not transactional row updates and they are not something to build a GDPR deletion workflow around casually. ReplacingMergeTree gives eventual deduplication, not a MERGE. If your model needs SCD Type 2 with real merges, that belongs in the warehouse.

Then the unglamorous list: time travel, zero-copy clones, secure data sharing, masking and row access policies, and role hierarchies that survive an audit. Snowflake has all of it — RBAC design, masking and row access policies — and ClickHouse’s equivalents are thinner. For a regulated environment this is often the whole decision.

The cost models are not comparable

Snowflake bills credits per second of warehouse uptime, so an idle warehouse costs nothing and a badly written query costs real money. Cost control is a discipline: auto-suspend, right-sized warehouses, and resource monitors, per the cost optimization playbook.

Self-hosted ClickHouse bills you for machines that run whether queries arrive or not. For a high-QPS serving workload that is excellent value — you saturate hardware you are paying for anyway. For a workload with three queries an hour, you are paying for idle NVMe.

The shape that matters: Snowflake is cheap when usage is spiky and expensive when it is constant. ClickHouse is cheap when usage is constant and expensive when it is spiky.

The architecture I usually end up recommending

flowchart LR
  src[Sources] --> wh["Snowflake: bronze → silver → gold"]
  wh --> dbt["dbt models, governance, history"]
  dbt --> serve["ClickHouse serving table<br/>(wide, sorted, TTL'd)"]
  serve --> app["Customer-facing app / API"]
  dbt --> bi[BI dashboards]

The warehouse stays the source of truth. ClickHouse serves one shape, fast.

The warehouse owns modeling, history, governance, and everything BI. One denormalized, well-sorted table is pushed into ClickHouse to serve the customer-facing analytics that need single-digit millisecond responses at high concurrency. The serving table is rebuildable from the warehouse, which means a ClickHouse incident is a performance event rather than a data-loss event.

That boundary keeps each system doing what it is good at, and it avoids the migration that eats a quarter.

Pitfalls

Choosing ClickHouse without designing the sort key. The ORDER BY is the architecture. Design it around your dominant filter before you load anything.

Row-at-a-time inserts. Part explosion, then a table that refuses writes. Batch, or use asynchronous inserts deliberately.

Expecting warehouse-style ad-hoc SQL. ClickHouse rewards known query shapes. Analysts writing arbitrary joins will have a bad time.

Treating ClickHouse as the system of record. No time travel, weak transactional updates. Keep the rebuildable copy upstream.

Running Snowflake as a serving layer for an application. Per-query overhead plus per-second billing on a high-QPS endpoint is a bad combination in both latency and cost.

Ignoring ClickHouse operations. Replication via ClickHouse Keeper, merges, disk pressure, and part counts are real work. “Faster” is not “simpler”.

FAQ

Can ClickHouse replace my warehouse entirely? For a narrow, well-understood analytics product with a small team — sometimes, and some companies run exactly that. For a general enterprise warehouse with dozens of sources, ad-hoc analysts, and compliance requirements, you will spend the savings rebuilding governance.

Is ClickHouse Cloud the same trade-off? It separates storage and compute and removes most of the cluster operations, which narrows the operational gap. The data model — sort keys, batched inserts, join limitations — is unchanged, and that is the part that decides workload fit.

What about Druid, Pinot, or StarRocks? Same category as ClickHouse: real-time OLAP for serving. Pinot and Druid have stronger stories for streaming ingestion with very low freshness targets; ClickHouse has the better SQL surface and the simpler mental model. Compare within that category rather than against a warehouse.

How do I get data from Snowflake into ClickHouse? Export gold tables to Parquet in object storage and read them with the S3 table function, or stream from Kafka into ClickHouse with the Kafka engine and materialized views. Keep the load idempotent so a rebuild is safe.

We only need faster dashboards. Do we need any of this? Probably not. Start with reading the query profile and warehouse sizing. Most slow dashboards are a modeling or pruning problem, and a second database is an expensive way to avoid fixing one.

What this means for data engineers

Do not frame this as a warehouse migration. Frame it as: which queries have a latency and concurrency requirement that Snowflake structurally cannot meet? Usually that set is small and specific — an in-product analytics page, a customer-facing API, an operational monitor.

Serve that set from ClickHouse with a purpose-built sorted table, keep the warehouse as the modeled source of truth, and make the serving table rebuildable from it. You get the millisecond latency where it earns money and keep the governance, history, and flexibility everywhere else. The ClickHouse interview questions cover the MergeTree details worth knowing before you commit to that sort key.