Why Snowflake can’t match ClickHouse Cloud for real-time analytics

SHARED HARNESS

Both systems used the same source files, schema, row decoder, workload generator, AWS EC2 m6i.8xlarge client host, rate controller, scheduler, timing, and result recorder. Only the destination-specific delivery adapter changed. Part 1 describes the shared methodology.

DATASET AND PREPARATION

The complete NBBO dataset contains 113.2 billion narrow, 12-column rows. Raw tables used (sym, t), stock symbol and event timestamp; daily summaries used (sym, day), stock symbol and day. The summaries maintained the counts, sums, and price minima and maxima used by the aggregate queries.

CONTINUOUS WORKLOAD

The client paced fresh rows toward 1 million rows per second while four interactive aggregate queries ran every ten minutes and two drill-downs every hour. Every query covered the history ingested so far, including the newest quotes. This simulates fresh data being generated continuously and becoming query-ready as it arrives. It measures the cost and performance of sustaining that workload, rather than the speed of bulk-loading or backfilling an existing dataset.

RECOMMENDED STACKS AND SIZING

Both systems used vendor-recommended low-latency components and native implementations of the same preparation requirements. ClickHouse Cloud used one 16-CPU, 64-GiB read node; Snowflake used a Small Interactive Warehouse and a Gen2 Small fallback, each treated as approximately 16 CPUs because Snowflake does not publish CPU counts. This sizing draws on SELECT.dev and an independent Gen2 inspection.

① PREPARATION COST

The complete 113.2-billion-row ingest includes all metered ingestion and preparation work. ClickHouse’s cost includes both 2-CPU nodes in its smallest calibrated high-availability ingest service. Snowflake’s separately metered Snowpipe Streaming and serverless MV refresh are both included.

② QUERY COST AND ③ RUNTIME

Query cost prices each accepted query’s full client-visible duration at the applicable read-side rate. Runtime sums those durations over the same active-ingestion workload, aligned by row count. These normalized query costs are separate from complete warehouse invoices. The query-comparison and pricing notes below give the accepted counts, rates, and exclusions.

TARGET VERSUS OBSERVED RATE

The target implies about 28 hours for 100 billion rows. Observed throughput averaged approximately 0.86 million rows/s for ClickHouse and 0.90 million for Snowflake. They reached 100 billion rows after roughly 32 and 31 hours, and completed 113.2 billion rows after roughly 37 and 35 hours, respectively. Client pacing, parallel streams, and batching targeted an exact rate but could not guarantee one.

REPORTING WINDOW

Per-query latency charts stop at 100 billion rows. Preparation costs cover the complete ingest; accumulated query runtime and cost use the full accepted active-ingestion observations aligned by row count. Displayed totals are rounded.

PRICING BASIS

The tested Enterprise pricing in AWS us-east was $3 per Snowflake credit and $0.3903 per ClickHouse Cloud compute unit (CU) per hour. Snowflake’s serverless charges use its metering history. Displayed values below are rounded; the accepted summary uses full-precision inputs.

① COMPLETE PREPARATION COST

This covers ingestion and preparation of all 113.2 billion rows.

ClickHouse Cloud: 2 CUs × 36.75 hours × $0.3903/CU-hour = $28.68705. This includes both nodes of the HA ingest service.

Snowflake ingestion: Snowpipe Streaming processed 4.6762 TiB, metered at 17.71783 credits under the 0.0037-credits-per-uncompressed-GB rate. Multiplying the unrounded credits by $3 gives $53.15348. Ingest-time clustering is included; no ingest warehouse was used.

Snowflake refresh: 8.75553 metered credits × $3/credit = $26.26659. Total preparation cost: $53.15348 + $26.26659 = $79.42007.

② NORMALIZED QUERY COST

Each accepted query is priced as its full client-visible duration × the applicable per-second read-side rate.

ClickHouse Cloud: 8 CUs × $0.3903/CU-hour × (11.361s aggregate + 50.466s drill-down) ÷ 3,600 = $0.05362.

Snowflake Interactive-priced work: (946.509s aggregate + 74.815s drill-down) × 1.2 credits/hour × $3/credit ÷ 3,600 = $1.02132.

Snowflake fallback-priced work: 6,653.660s aggregate × 2.7 credits/hour × $3/credit ÷ 3,600 = $14.97074. Total normalized query cost: $1.02132 + $14.97074 = $15.99207.

Fallback boundary: The elapsed-time proxy assigns jobs above five seconds to Gen2 Small for their full duration and all others to Interactive Small. It excludes standing fallback capacity, idle capacity, minimum billing, and an additional Interactive charge for fallback-priced jobs. The allocation is a cost model, not a reconstruction of which warehouse physically executed each job.

③ ACCUMULATED QUERY RUNTIME

ClickHouse Cloud: 11.361s aggregate + 50.466s drill-down = 61.827s.

Snowflake: 7,600.169s aggregate + 74.815s drill-down = 7,674.984s.

COMPARISON BOUNDARY

① covers the complete ingest. ② and ③ use the accepted active-ingestion sets aligned by row count: 209 × 4 aggregate queries and 35 × 2 drill-downs per system. Later post-ingestion observations and database storage are excluded. Query cost is normalized to accepted query work; it is not the complete warehouse invoice.

FINAL SCORE

ClickHouse Cloud: ($28.68705 + $0.05362) × 61.827 = 1,776.9494.

Snowflake: ($79.42007 + $15.99207) × 7,674.984 = 732,286.6479.

Relative score: 732,286.6479 ÷ 1,776.9494 = 412.103× using the full-precision inputs.

The writer-utilization results show the two-node service sustaining ingestion alongside background maintenance. CPU usage remained around 3.1 of its 4 available cores, tracked memory stayed within capacity, background merges processed several million rows per second, and the maximum active-part count per partition stayed around 100.

Both nodes were retained for high availability, so the preparation cost includes the complete two-node deployment.

THE SAME INGESTION PROBLEM

Applications produce small, frequent writes that must be combined into efficient storage writes. This benchmark measures direct application-to-table ingestion, using server-side buffering in both Snowpipe Streaming and ClickHouse asynchronous inserts.

SNOWFLAKE’S MANAGED SERVICE

Clients open channels against a PIPE object. The high-performance ingestion service handles buffering, validation, configured transformations, and optional pre-clustering before committing rows. Snowflake manages the ingestion compute and bills it separately by uncompressed volume.

CLICKHOUSE’S NATIVE PATH

Ordinary INSERT requests with async_insert enabled are buffered on each receiving node. An adaptive flush timeout responds to incoming traffic. Ordering, pre-aggregation, and insert-path transformations run on those nodes; a dedicated ClickHouse Cloud ingest service can scale independently from reads while sharing the same stored data.

SHARED PACING

Both destinations used the same Parquet source, row decoder, client compute, rate controller, and eight-worker parallelism. Destination-specific batching adapted delivery to each API.

CLICKHOUSE BATCHES

The client sent 3,000-row batches through asynchronous inserts. With the run’s default buffering settings, a node flushed at the first of three thresholds: 100 MiB buffered, an adaptive timeout between 50 milliseconds and 1 second, or 450 queued insert queries.

SNOWFLAKE BATCHES

Each worker called append_row through its own channel. Earlier stream-preview evidence for this setup shows the SDK choosing file mode and grouping calls into NDJSON batches averaging approximately 61,500 rows. Across eight channels, that produced about 14.6 file uploads per second before further server-side buffering.

BATCH-SIZE BOUNDARY

The 3,000-row and approximately 61,500-row figures describe client delivery batches. Both systems buffered them again server-side, so these figures do not specify final storage-write sizes.

RETRIES AND RECOVERY

Snowpipe Streaming’s offset tokens record committed channel progress. A custom application retrieves the last token, maps it to its source, and resumes from the next record; Snowflake’s Kafka connector handles that mapping for Kafka partitions. ClickHouse supports insert deduplication for retries, including dependent materialized views.

WHERE CLICKPIPES FITS

Direct application writes are compared here. For Kafka, the corresponding integrations are Snowflake’s Kafka connector and Kafka ClickPipes; for object-storage files, classic Snowpipe and Object Storage ClickPipes. Those paths include an external-source integration beyond the direct-write boundary measured here.

STORAGE CONTEXT

In the earlier configuration, at roughly 113 billion rows, Snowflake’s raw Interactive Table occupied 2.9 TiB, versus 701 GiB for its standard table and 362 GiB for ClickHouse’s raw table, roughly four and eight times as much. These are earlier-study footprints, and database storage costs are excluded from this score.

SNOWFLAKE SERIES

The MV-lag series uses 2,072 active one-minute behind_by samples. Poll timestamps were aligned to raw row count by linear interpolation between 208 active dashboard observations.

SMOOTHING AND READOUTS

The reported average and maximum come from a centered 61-sample rolling mean: 1.372 and 2.033 minutes, rounded to 1.4 and 2.0 minutes. The displayed trend adds a centered 11-sample rolling mean and shape-preserving interpolation. Its interactive readout follows that displayed trend at the selected row count.

CLICKHOUSE BASELINE

The zero line is a benchmark-semantic baseline rather than a separately polled provider metric. The raw table and incremental MV consume the same flushed insert block, with no independent refresh interval between them.

CURRENT-ANSWER CONTRACT

Queries against an Interactive MV include the rows added to the base table since its last refresh. The amount of newer data grows between refreshes and shrinks when a refresh catches up, explaining the rising-and-reset pattern in the aggregate curves.

FALLBACK CONFIGURATION

The tested Interactive Warehouse used a five-second limit and a separate Gen2 Small fallback warehouse for eligible queries. The benchmark’s normalized-cost model uses elapsed time as a pricing proxy; it does not establish which warehouse physically executed each query.

ATTRIBUTION RULE

Each accepted query with a full client-visible duration strictly greater than five seconds is priced for its entire duration at the Gen2 Small rate. Queries at or below five seconds use the Interactive Small rate. Exactly five seconds remains Interactive-priced. Each query belongs to one bucket, without an additional Interactive charge for fallback-priced work.

OBSERVED ALLOCATION

Of 836 aggregate executions, 477 (57.1%) were above five seconds; all 70 drill-downs were at or below it. A query at or below the threshold could still have used fallback in reality, so the proxy can undercount fallback.

COST EXCLUSIONS

Standing fallback capacity, minimum billing, idle capacity, and an additional primary-warehouse charge for fallback-priced queries are excluded. Full-duration timing is retained; no query phase is removed from latency or normalized cost. The pricing note below provides the rates and calculation.

QUERY DEFINITIONS

Part 1 explains the four aggregate queries A1–A4 and two drill-downs D1–D2, including how newly ingested quotes contribute to their answers. Both systems used the same query semantics and schedule.

CACHE POLICY

Query-result caching was disabled, so every measured query ran. Underlying data caches were allowed to remain warm. Both systems used the read-side sizing described in the methodology note.

TIMING

Latency and accumulated runtime use each query’s full client-visible duration. Per-query median, P99, and maximum statistics summarize unsmoothed observations through 100 billion rows.

LATENCY CHARTS

Each system is plotted against its own observed row counts, rather than equal iteration numbers or interpolation between systems. Aggregate trends use a centered seven-observation rolling median; drill-down trends use a centered five-observation rolling median. The aggregate chart retains its Snowflake-only Tukey upper-fence rule, with excluded points retained in the source and summary. Drill-downs have no outlier filter.

INTERACTIVE READOUTS

Values beneath the charts follow the displayed trends at the selected row count. Logarithmic scales keep milliseconds and seconds readable together; Linear shows absolute differences. Each chart has independent playback and row-position controls.

ACCEPTED ACCUMULATED WORKLOAD

Runtime and normalized query cost use 209 four-query aggregate batches and 35 two-query drill-down batches per system: 836 + 70 = 906 executions. Observations are aligned by ingestion progress. The first complete-dataset observation belongs to active ingestion; later post-ingestion observations are excluded.

ACCUMULATED CHART

Recorded durations are added at their aligned row positions, without smoothing or interpolation between observations. The total is the sum of query durations, not the elapsed ingestion time. Its aggregate component uses maintained pre-aggregations; the alternative raw-table test described later is outside the headline score.

EARLIER DIAGNOSTIC EVIDENCE

Snowflake documents cache warming as a non-blocking background process. Earlier query profiles for this workload reported zero or negligible remote reads during active ingestion and a median 100% of bytes served from cache. At roughly 75 MB/s of ingest, the queried working set fit within Small’s approximately 600 GB cache and warming kept pace. These diagnostics describe the earlier configuration, rather than separately measuring cache hits for this post’s headline run.

WHAT THAT SHOWS

The earlier diagnostic represented a favorable cache case. Having the relevant data local still leaves the work of reading and filtering the growing raw table.

WORKING-SET CAPACITY

Even the largest Interactive Warehouse, 4X-Large, has a finite documented 44 TB cache. A working set that exceeds cache capacity requires more remote reads. Warehouse sizing therefore affects both cache capacity and cost.

WARMING THROUGHPUT

Snowflake documents roughly 300–400 MB/s of warming throughput even for X-Small, with larger warehouses warming faster. That exceeds the earlier workload’s roughly 75 MB/s ingest rate. At higher source rates, recently arrived data may require remote reads even when the total working set still fits.

SIZING IMPLICATION

The Interactive Warehouse must be sized for both the data queries touch and the rate at which new data arrives. ClickHouse’s query path reads the ordered MergeTree table without a separately populated Interactive Warehouse serving cache.