49.7k
TL;DR
Real-time ingestion only matters if the data becomes query-ready in real time.
-
Part 2 compares Snowflake’s recommended real-time stack with ClickHouse Cloud under the same continuous workload.
-
Both systems continuously ingested fresh data. But interactive aggregate queries hit a bottleneck in Snowflake that a larger warehouse could not fix.
-
ClickHouse Cloud delivered 412× better performance per dollar than Snowflake in the end-to-end CostBench comparison.
Where the 412× gap comes from
Part 1 showed that the fresh-data path affects performance per dollar twice: it carries the direct cost of making new data query-ready, and it determines how much work remains for the query engine.
Part 2 examines those two effects in ClickHouse Cloud and Snowflake. We ingested the same 113.2 billion stock-market quotes into both systems at 1 million rows per second, using their recommended real-time components. The same aggregate and drill-down queries ran throughout ingestion.
The diagram below shows the full measurement boundary: ingestion, ongoing query-readiness work, and query serving all run together. Ordering and pre-aggregation reduce how much data queries must read and how much calculation remains. When that preparation falls behind, additional work can move into queries, increasing their latency and cost.
That complete operating loop is the comparison boundary. Both systems received the same stream, schema, query workload, and schedule.
We configured matching sorting or clustering keys across systems for both raw data and daily pre-aggregations. These layouts support the workload’s stock-symbol filters. The pre-aggregations group quotes by stock symbol and day, maintaining the counts, sums, and price minima and maxima that the aggregate queries use. Each system therefore had to keep the same preparation current for the same query workload as new rows arrived.
Query-serving compute was matched at approximately 16 CPUs, while each system retained its recommended architecture and complete metered fresh-data path.
BENCHMARK METHODOLOGY AND CONFIGURATION DETAILS (click to expand)
SHARED HARNESS
The same source files, schema, row-decoding logic, workload-generation code, client host (AWS EC2 m6i.8xlarge), rate controller, scheduler, timing, and result-recording logic drove both systems; only the destination-specific delivery adapter changed. Full shared methodology is here.
REAL-TIME, NOT BULK LOAD
CostBench simulates a real-time analytics system in which fresh data is continuously generated at the source and must become query-ready as it arrives. The harness therefore models paced continuous ingestion rather than a bulk load or backfill of data that already exists.
DATASET AND LAYOUT
Both systems received the same complete 113.2-billion-row NBBO stock-market dataset: narrow, 12-column rows.
Both systems used the following sorting or clustering keys:
-
Event-level data:
(sym, t)— stock symbol and event timestamp. -
Daily pre-aggregations:
(sym, day)— stock symbol and day.
WORKLOAD AND QUERY SCHEDULE
The harness replayed the source as one continuous stream toward a target rate of 1 million rows per second. From the beginning of ingestion through the final row, the same four interactive aggregate queries ran every 10 minutes and the same two selective drill-down queries every hour.
RESOURCE-SIZING POLICY
We aligned query-serving compute as closely as the platforms allowed. ClickHouse Cloud used one read node with 16 CPUs and 64 GiB of memory; Snowflake used a Small Interactive Warehouse and a Gen2 Small fallback warehouse, each estimated at roughly 16 CPUs because Snowflake does not publish CPU counts. This estimate draws on SELECT.dev and an independent Gen2 inspection. For ingestion, ClickHouse used the smallest high-availability configuration that sustained the target during calibration-two nodes with 2 CPUs each-while Snowpipe Streaming and Interactive MV refresh ran as separately metered serverless services. Their complete metered work remains included in Snowflake’s fresh-data-path cost.
THE COMPLETE FRESH-DATA PATH ON FOUR CPU CORES
The writer-utilization results show how the two-node ingest service handled continuous ingestion and background maintenance. Background merges processed several million rows per second, while the maximum active-part count per partition stayed around 100. CPU usage remained around 3.1 of the service’s 4 available cores, showing sustained utilization with headroom. Tracked memory usage remained within the service’s capacity.
We retained two nodes for high availability, so the reported ingest cost includes both nodes of the HA deployment.
BEST-PRACTICE PLATFORM CONFIGURATION
We configured each platform’s fresh-data path using its vendor-recommended low-latency components and documented best practices for keeping incoming data query-ready. Differences between those paths are part of the benchmark.
RUN AND REPORTING SCOPE
For simplicity, most charts below show results through the 100-billion-row milestone. The benchmark itself continued until the complete 113.2-billion-row dataset had been ingested into both systems, and cost calculations cover that complete ingest.
TARGET AND OBSERVED RATE
At the target rate of 1 million rows per second, ingesting 100 billion rows would take approximately 28 hours. Client-side pacing, parallel ingest streams, and batch sizing targeted-but could not guarantee-an exact end-to-end rate, and fluctuations are normal during a run lasting more than a day. Observed throughput averaged approximately 0.86 million rows per second for ClickHouse and 0.90 million for Snowflake. ClickHouse therefore reached 100 billion rows after roughly 32 hours and completed the 113.2-billion-row dataset after roughly 37 hours; Snowflake reached 100 billion rows after roughly 31 hours and completed the dataset after roughly 35 hours.
With the workload around each system held constant, the comparison now moves inside the two architectures.
Fresh-data-path architecture
This round of CostBench used push-based ingestion: the shared client sent the same paced stream through each system’s recommended low-latency ingest path. For Snowflake, that was Snowpipe Streaming; for ClickHouse Cloud, native asynchronous inserts.
WHY COMPARE SNOWPIPE STREAMING WITH CLICKHOUSE ASYNCHRONOUS INSERTS? (click to expand)
THE SAME INGESTION PROBLEM
Real-time applications across observability, customer-facing applications, IoT, fraud detection, and analytical agents often produce small, frequent writes from many clients. The ingestion path must accept those writes continuously and combine them into efficient storage writes. Both Snowpipe Streaming and ClickHouse asynchronous inserts provide server-side buffering for this purpose.
SNOWFLAKE: A MANAGED INGESTION SERVICE
Snowpipe Streaming lets applications submit rows without managing staged files themselves. In the high-performance architecture used here, clients open channels against a PIPE object. Snowflake’s ingestion service handles buffering, schema validation, configured transformations, and optional pre-clustering before committing rows to the target table. Snowflake manages and scales the ingestion compute, with ingestion billed separately by uncompressed data volume.
CLICKHOUSE: INGESTION WITHIN THE ENGINE
Applications send ordinary INSERT requests with async_insert enabled. Each receiving node buffers compatible requests and combines them into larger writes, with an adaptive flush timeout that responds to incoming traffic. This functionality is native to both open-source ClickHouse and ClickHouse Cloud.
The engine also performs ordering and transformations during ingestion. Receiving nodes sort incoming rows by each destination MergeTree-family table’s sorting key as they write data parts. Incoming data can be filtered, routed, enriched (dictionary lookups, UDFs), and reshaped on the insert path, before anything is stored. These operations run on the node receiving the inserts.
In ClickHouse Cloud, ingestion can run on a dedicated service whose compute scales independently of the services serving queries, while sharing the same stored data.
RETRIES AND RECOVERY
Snowpipe Streaming’s offset tokens record committed source progress for each channel. After a restart, the application must retrieve the last committed token, map it back to its source, and resume from the next record. Snowflake’s Kafka connector performs this mapping for Kafka partitions. For a custom application, that recovery logic belongs to the application; the offset token itself does not make resending already committed rows safe.
ClickHouse supports idempotent retries through insert deduplication. Retried inserts carrying the same data are detected and discarded by the server (deduplicated for synchronous and asynchronous inserts, including dependent materialized views). After a crash or a lost acknowledgment, there's no offset bookkeeping: the client simply resends the recent batches, and ClickHouse ignores whatever already landed.
WHERE CLICKPIPES FITS
ClickPipes manages ingestion from external sources such as Kafka, object storage, and Postgres. The appropriate comparison therefore depends on where the data originates:
- Applications pushing rows directly: Snowpipe Streaming and native ClickHouse asynchronous inserts - the comparison measured here.
- Kafka topics: Snowpipe Streaming with Snowflake’s Kafka connector and Kafka ClickPipes.
- Files in object storage: classic Snowpipe and Object Storage ClickPipes.
The latter pairings compare complete integrations that consume an external source. This benchmark measures the direct application-to-table path.
We begin with ClickHouse Cloud and follow its fresh-data path from incoming rows to query-ready event-level and pre-aggregated data.
Ingesting the stream - ClickHouse Cloud
The benchmark used a dedicated ClickHouse Cloud ingest service with two 2-CPU nodes for high availability - the smallest HA configuration that sustained the target during calibration. The shared client sent rows through asynchronous inserts built into the engine.
Those same two nodes also handled sorting, incremental pre-aggregation, and background merges, running the complete fresh-data path with just four CPU cores in total.
Keeping data query-ready - ClickHouse Cloud
When one of these two ingest nodes flushes its asynchronous-insert buffer, it sorts the event-level rows by the target MergeTree table’s (sym, t) sorting key and writes an ordered data part.
In parallel, the same node executes the incremental MV’s query over that block in memory, computes aggregate states, sorts the resulting rows by the target AggregatingMergeTree table’s (sym, day) sorting key, and writes an ordered part containing the pre-aggregated data.
Background merges consolidate parts in both tables. Both write paths process the same flushed insert block, with ordering and pre-aggregation performed during ingestion, and no separate refresh cycle.
The result: raw data and pre-aggregations stay in sync. Both advance from the same flushed insert block; there is no separate refresh cycle between them.
We now follow the same two stages through Snowflake, where ingestion and materialized-view refresh are split across separate services.
Ingesting the stream - Snowflake
The benchmark used Snowpipe Streaming’s high-performance architecture, which places a managed server-side ingestion buffer between application writes and the Interactive Table.
Keeping data query-ready - Snowflake
Snowpipe Streaming writes rows directly into an Interactive Table. With CLUSTER_AT_INGEST_TIME=TRUE, it pre-clusters incoming rows on the shared (sym, t) key before writing them into micro-partitions - Snowflake’s columnar storage units.
Interactive Tables carry a storage trade-off. In our earlier study, at roughly 113 billion rows, Snowflake’s raw Interactive Table occupied 2.9 TiB, compared with 701 GiB for its standard table and 362 GiB for ClickHouse’s raw table - roughly four and eight times as much storage, respectively. These measurements come from the earlier configuration.
A separate Snowflake-managed serverless service refreshes the Interactive MV asynchronously from that table. We configured the view with a (sym, day) clustering key, matching the sorting key of ClickHouse’s pre-aggregation table. Snowflake controls when refreshes run and how much compute they use; users cannot configure the refresh schedule or size the refresh compute.
The result: raw data and pre-aggregations can fall out of sync. The Interactive Table advances through ingestion, while the Interactive MV advances through a separate asynchronous refresh cycle.
UNDER THE HOOD: PACING BOTH SYSTEMS TOWARD 1 MILLION ROWS PER SECOND (click to expand)
SHARED CLIENT AND RATE CONTROL
Both destinations used the same Parquet source, row decoder, client-side compute, pacing logic, and eight-worker parallelism. The shared rate controller paced the stream toward 1 million rows per second. Only the delivery adapter and destination-specific batching changed.
CLICKHOUSE CLOUD
The client sent 3,000-row batches through asynchronous inserts. Each receiving node accumulated these requests in its server-side buffer.
The benchmark retained the default buffering settings used for the run. A flush occurred when the first of three thresholds was reached: 100 MiB buffered, an adaptive timeout between 50 milliseconds and 1 second, or 450 queued insert queries.
SNOWFLAKE
The client called append_row through one Snowpipe Streaming channel per worker. The SDK selected file mode and grouped those row-wise calls into client-side NDJSON batches averaging approximately 61,500 rows.
Across eight channels, this produced about 14.6 file uploads per second. Snowpipe Streaming then buffered the uploaded batches again server-side before committing their rows.
HOW TO READ THESE BATCH SIZES
The 3,000-row and approximately 61,500-row figures describe client-side delivery batches. Both systems performed further server-side buffering, so neither figure directly specifies the size of a final storage write.
Query-serving architecture
Those different query-ready states are what the read side receives. ClickHouse Cloud serves both workloads from one 16-CPU read service; Snowflake uses an approximately matched Small Interactive Warehouse, with separate compilation and fallback behavior around it.
Read-side compute - ClickHouse Cloud
Sizing: The read service used one node with 16 CPUs and 64 GiB of memory, matching the estimated CPU count of Snowflake’s Small warehouse.
Serving model: That single service handled both workloads. Aggregate queries read the always-current AggregatingMergeTree table, which advances with the same flushed insert blocks as the raw MergeTree table; current answers therefore require no query-time reconciliation. Drill-down queries read the event-level MergeTree table.
The same paths run through Snowflake’s serving layer, which adds compilation and fallback behavior around the Interactive Warehouse.
Read-side compute - Snowflake
Sizing: Snowflake does not publish the warehouse’s CPU or memory. We estimate the Small Interactive Warehouse at approximately 16 CPUs - matching ClickHouse Cloud’s 16-CPU read service - using SELECT.dev and an independent Gen2 inspection; Snowflake documents an approximately 600 GB local SSD cache for this size.
Serving model: The Interactive Warehouse was the primary service for both workloads. Aggregate queries targeted the Interactive MV. As discussed above, Snowflake refreshed that view asynchronously, so it could lag the Interactive Table. To return current results, Snowflake reconciles the unrefreshed base-table delta during query compilation in its Cloud Services layer, before execution reaches the warehouse. Drill-down queries read the Interactive Table directly.
Timeout and fallback: Interactive Warehouses cancel queries that exceed five seconds, and that limit can only be lowered. We configured a separate Gen2 Small standard warehouse-also estimated at 16 CPUs-to receive eligible timed-out queries through Snowflake’s automatic fallback mechanism.
Did query readiness keep pace with ingestion?
Both systems kept their event-level tables advancing with the continuously paced stream. The difference appeared in pre-aggregation: ClickHouse kept its event-level and pre-aggregated data current together; Snowflake did not.
The chart tracks the gap between each system’s event-level and pre-aggregated data as the event-level table grows; lower is fresher.
ClickHouse Cloud: The incremental MV processed every flushed insert block as part of the insert, so the raw MergeTree and pre-aggregated AggregatingMergeTree advanced together with zero raw-to-pre-aggregated lag throughout.
Snowflake: Snowpipe Streaming kept the Interactive Table advancing, but the separate serverless refresh left the Interactive MV behind. Its active-ingestion trend averaged 1.4 minutes of lag and peaked at 2.0 minutes, so current aggregate answers required query-time reconciliation.
MEASUREMENT DETAILS: FRESHNESS (click to expand)
SNOWFLAKE LAG 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.
Legend statistics come from a centered 61-sample rolling mean; the displayed curve adds an 11-sample centered rolling mean and shape-preserving interpolation. The resulting 1.372-minute average and 2.033-minute maximum are shown above as 1.4 and 2.0 minutes.
CLICKHOUSE BASELINE
ClickHouse’s 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, so there is no independent refresh interval between them.
412× better real-time performance per dollar
That’s what ClickHouse Cloud delivered compared with Snowflake in CostBench under continuous load. See what it can do for your data - sign up today.
Try ClickHouse CloudWhat happened when query readiness fell behind?
Snowflake’s pre-aggregation lag moved unfinished fresh-data-path work onto the read path. Aggregate queries inherited that work through reconciliation; drill-down queries exposed a separate event-level scaling path.
All query-performance comparisons below use approximately matched 16-CPU read-side configurations: one 16-CPU ClickHouse Cloud read node and Snowflake Small warehouses estimated at approximately 16 CPUs.
MEASUREMENT DETAILS: HOW QUERY RESULTS WERE COMPARED (click to expand)
See Part 1 for the complete query definitions and how newly ingested data contributes to their results: Measuring real-time performance per dollar under continuous load: CostBench’s first end-to-end results.
CACHE POLICY
Query-result caching was disabled on both systems, so every measured query executed. Their underlying data caches were allowed to remain warm; a warm data cache still requires the query itself to run.
END-TO-END LATENCY
The measured latency is the full client-visible runtime. Snowflake’s compilation and execution phases are retained as supporting telemetry, but neither phase is subtracted from the end-to-end result.
VISIBLE CURVES
The charts show results through the 100-billion-row milestone against each system’s own observed row counts, without matching by iteration or interpolating between systems.
The aggregate chart uses a seven-observation centered rolling median and a Snowflake-only Tukey upper-fence rule; excluded points remain in its source data and summary. The drill-down chart uses a five-observation centered rolling median and no outlier filter.
ACCUMULATED TOTALS
Runtime and normalized query cost use the accepted row-count-matched active-ingestion sets: 209 four-query aggregate batches and 35 two-query drill-down batches per system. The first complete-dataset observation remains part of active ingestion; later post-ingestion observations are excluded.
Aggregate queries over pre-aggregated data
Every 10 minutes during active ingestion, the benchmark ran four aggregate queries (A1-A4) against the maintained daily pre-aggregations. These summaries already contain the counts, sums, and price minima and maxima needed to calculate stock-market activity across the history ingested so far. The queries combine those prepared results across days or stocks. Their answers must include newly arrived quotes, so any lag in the pre-aggregations leaves work for the query engine. A1 and A2 also filter by stock symbol - the leading sorting or clustering column - so they can skip summaries for other stocks.
The chart follows the query’s latency as the event-level table grows. The orange dashed line marks Snowflake’s five-second retry threshold. Blue shows the Interactive Warehouse path; purple shows executions that crossed the threshold and were attributed to the Gen2 Small fallback path. ClickHouse appears in yellow.
ClickHouse Cloud: Through the 100-billion-row chart window, aggregate-query latency was:
- 13 ms median - 801× faster than Snowflake
- 42 ms P99 - 533× faster than Snowflake
- 111 ms maximum - 218× faster than Snowflake
Its incremental MV kept the aggregate states current as part of the insert path, so queries required no reconciliation. Across 209 row-count-matched active-ingestion batches, accumulated runtime was 11.36 seconds.
Snowflake: Through the same chart window, aggregate-query latency was:
- 10.41 s median - 801× slower than ClickHouse Cloud
- 22.20 s P99 - 533× slower than ClickHouse Cloud
- 24.17 s maximum - 218× slower than ClickHouse Cloud
Queries reconciled the lagging Interactive MV with newer rows from the Interactive Table, producing the sawtooth pattern as the delta grew and refreshes reduced it. Across the same 209 matched batches, accumulated runtime was 2.11 hours; under the accepted five-second fallback proxy, 477 of 836 executions-57%, crossed the threshold and were attributed to the fallback path.
Across the interactive aggregate-query workload, ClickHouse Cloud was 669× faster than Snowflake and kept its pre-aggregated data current without query-time reconciliation.
Unfinished refresh work reappeared during query compilation
The phase breakdown below separates Snowflake’s end-to-end aggregate-query latency into compilation and execution.
Snowflake’s phase telemetry attributes 85% of aggregate-query time to compilation and 15% to warehouse execution. A larger warehouse can accelerate execution, but it cannot reduce the compilation time dominating this chart.
You can’t buy your way out of the orange compilation area - only the thin blue execution band.
The diagram below shows why.
① An aggregate query targets the Interactive MV using the Interactive Warehouse. Although the view is behind, the query must still return current results, including rows that arrived after its last refresh.
② During query compilation, Snowflake’s planner determines how to combine the materialized view with those newer rows. To do that, it loads metadata for every base-table micro-partition created since the last refresh. This compilation occurs in Snowflake’s shared Cloud Services layer before execution begins in the warehouse.
Snowflake manages the resources for this compilation work; users cannot resize them. Increasing warehouse size adds resources to the later execution phase.
As new micro-partitions accumulate between refreshes, each query has more metadata to process during compilation. When a refresh catches up, that work shrinks and latency drops, then begins climbing again as ingestion continues.
The paradox: faster Snowpipe Streaming makes current MV queries slower.
More new micro-partitions accumulate between asynchronous refreshes, so every query inherits more reconciliation work.
UNDER THE HOOD: RECONCILIATION, WAREHOUSE SIZE, AND FALLBACK (click to expand)
1. Two query phases
Every Snowflake aggregate query first compiles in shared Cloud Services, independently of the warehouse, and then executes on the selected warehouse.
2. How Snowflake assembles a current MV answer
When the Interactive MV is behind, Snowflake does not return stale results. During compilation, the planner reconciles the view with everything added to the Interactive Table since the last refresh, loading metadata for each new base-table micro-partition in that delta.
3. Why latency rises and then resets
Snowpipe Streaming keeps creating micro-partitions while the serverless MV refresh runs asynchronously. The unrefreshed delta grows between refreshes and shrinks when a refresh catches up, producing the same rising-and-reset pattern visible in the latency curves.
4. What we measured
Compilation accounted for 85% of Snowflake’s aggregate-query time; warehouse execution accounted for 15%. Under the accepted five-second fallback proxy, 477 of 836 executions-57%-crossed into the fallback-attributed path.
5. Why a larger or fallback warehouse does not remove the bottleneck
Warehouse size changes only execution compute. Reconciliation has already happened in shared Cloud Services before execution reaches either the Interactive Warehouse or the Gen2 Small fallback.
6. Earlier diagnostic evidence for the mechanism
In one profiled query, remote micro-partition metadata I/O consumed 4,908 ms of a 5,030 ms compilation phase-97.6%, or roughly 25 ms per partition. A parallel Small-versus-X-Large diagnostic over approximately 0.4–16.8 billion raw rows found no compilation-time improvement even though X-Large carried an 8× credit rate. These diagnostics explain the mechanism; Run14 supplies the headline measurements above.
7. Cost implication
The warehouse can remain active and billable while waiting for compilation, while Cloud Services usage can itself become billable when daily Cloud Services consumption exceeds 10% of daily warehouse usage. Faster ingestion therefore increases both the reconciliation latency and its potential cost.
8. How to read the fallback colors
The purple segment is an accepted elapsed-time proxy for normalized cost, not query-level proof that Snowflake physically executed on fallback. Jobs above five seconds are fallback-priced for their full end-to-end runtime; jobs at or below five seconds remain Interactive-priced. Each job enters exactly one bucket.
Because the threshold is a classifier, it can miss fallback that occurred at or below five seconds; the later score box documents the complete pricing boundary.
Drill-down queries over event-level data
Every hour during active ingestion, the benchmark ran two drill-down queries (D1-D2) directly against the event-level tables. They calculate hourly price summaries and a risk-and-liquidity profile for one stock across its growing history. Their answers must include newly arrived quotes, so the event-level tables must keep new rows queryable in a layout that supports efficient filtering. Both queries filter by stock symbol - the leading column of the tables’ sorting or clustering key.
The chart follows their end-to-end latency as each event-level table grows; Snowflake appears in blue and ClickHouse in yellow.
These queries bypassed the MV and read each event-level table directly, so this path isolates event-level layout and serving rather than MV reconciliation. Both systems used the same (sym, t) ordering/clustering and approximately 16 CPUs of read-side compute; Snowflake’s Small Interactive Warehouse additionally provided an approximately 600 GB local SSD cache.
ClickHouse Cloud: Through the 100-billion-row chart window, drill-down latency was:
- 722.5 ms median - 1.38× faster than Snowflake
- 1.15 s P99 - 2.06× faster than Snowflake
- 1.18 s maximum - 2.17× faster than Snowflake
Across 35 row-count-matched active-ingestion batches, accumulated runtime was 50.47 seconds. The ordered MergeTree path’s latency increased more gradually and remained lower at larger data volumes.
Snowflake: Through the same chart window, drill-down latency was:
- 993.5 ms median - 1.38× slower than ClickHouse Cloud
- 2.37 s P99 - 2.06× slower than ClickHouse Cloud
- 2.56 s maximum - 2.17× slower than ClickHouse Cloud
Across the same 35 matched batches, accumulated runtime was 74.81 seconds. Despite the Interactive Warehouse’s approximately 600 GB local SSD cache, latency rose more steeply as data accumulated; all 70 executions remained below the five-second fallback threshold.
Across the matched drill-down workload, ClickHouse Cloud was 1.48× faster overall, despite Snowflake’s approximately 600 GB local SSD cache.
A growing event-level table left more work for query execution
The phase breakdown below separates Snowflake’s drill-down latency into compilation and execution.
Snowflake’s phase telemetry shows a different bottleneck from its MV path. At the median, compilation took 225 ms for D1 and 191 ms for D2; execution took 643 ms and 830 ms, respectively-74–81% of provider-reported phase time. That execution work grew as the Interactive Warehouse read and filtered the expanding event-level table. Unlike MV reconciliation, it runs inside the warehouse and can be reduced with more warehouse compute.
The dominant drill-down layer is execution: buying a larger warehouse can reduce it. The separate compilation layer remains outside the warehouse.
DEEP DIVE: THE CACHE KEPT UP. HOW DOES THIS SCALE? (click to expand)
Measured case
Snowflake documents Interactive Warehouse cache warming as a non-blocking background process. In an earlier diagnostic run of this workload, query profiles 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 inside the Small warehouse’s approximately 600 GB cache, and warming kept the relevant data local. These diagnostics explain the cache behavior; Run14 supplies the headline latency measurements above.
What that establishes
This was the favorable cache case: the working set fit and warming kept pace. The remaining latency came primarily from executing scans and filters over the growing event-level table, not from repeatedly fetching the working set from remote storage.
Boundary 1: working-set capacity
Even the largest Interactive Warehouse-a 4X-Large-has a finite 44 TB cache. When a query’s working set outgrows the cache, more reads go remote. Undersize the warehouse and latency rises toward the five-second cap, sending queries that cross it to fallback; oversize it and you pay for cache you do not need.
Boundary 2: warming throughput
Snowflake documents cache-warming throughput of roughly 300–400 MB/s even for an X-Small warehouse, and says larger warehouses warm faster. That documented X-Small rate was comfortably above this benchmark’s roughly 75 MB/s ingest rate.
At higher source rates, however, query-relevant data can arrive faster than warming makes it local: the total working set may still fit while the newest rows require remote reads.
Sizing implication
Snowflake customers must size the Interactive Warehouse for both how much data their queries touch and how quickly new data arrives. ClickHouse queries run directly against the ordered MergeTree table, without a separately populated serving cache to size or keep warm.
Bypassing the MV moved aggregation onto the raw table
That event-level path also provides a useful control:
What happens if Snowflake’s aggregate queries bypass the lagging MV and run directly against the raw Interactive Table?
The chart compares that path with ClickHouse’s pre-aggregated path. As in the earlier aggregate chart, the orange dashed line marks Snowflake’s five-second retry threshold. Blue shows the Interactive Warehouse path; purple shows executions that crossed the threshold and were attributed to the Gen2 Small fallback path. ClickHouse appears in yellow. The same approximately matched 16-CPU read-side sizing applies.
ClickHouse Cloud: Same pre-aggregated path and results as above.
Snowflake: Bypassing the Interactive MV and querying the raw Interactive Table produced:
| Metric | Raw-table latency | vs. Interactive MV | vs. ClickHouse Cloud |
|---|---|---|---|
| Median | 2.47 s | 4.21× faster | 190× slower |
| P99 | 18.04 s | 1.23× faster | 433× slower |
| Maximum | 20.67 s | 1.17× faster | 186× slower |
Bypassing the lagging Interactive MV removed query-time reconciliation, but it left the aggregation work on Snowflake’s raw table. A1 and A2 filter on sym-the leading clustering-key column - yet both show a clear latency uptrend as the Interactive Table grows. Within the measured window, more ingested data increased query-time work even on these selective paths. ClickHouse Cloud’s pre-aggregated latency remained effectively flat across the same 100-billion-row window.
A3 and A4 are unfiltered full-table aggregates. Under the accepted five-second fallback proxy, 220 of 836 executions-26% crossed the threshold and were attributed to the fallback path: 103 A3 executions and 117 A4 executions. Across the same 209 matched batches, Snowflake’s raw-table path accumulated 1.10 hours of runtime.
Bypassing the lagging Interactive MV made Snowflake faster by removing query-time reconciliation-but it shifted aggregation onto the raw table. ClickHouse Cloud’s already-current pre-aggregated path was still 347× faster across the matched workload.
Across Snowflake’s two aggregate paths, unfinished fresh-data-path work reappeared at query time:
- Interactive MV: reconciliation during query compilation accounts for rows missing from the pre-aggregations.
- Raw Interactive Table: scans and aggregation during query execution calculate the answers directly from event-level rows.
ClickHouse Cloud performed the pre-aggregation during ingestion, keeping current summaries ready for queries.
That leaves one final architectural question: can Snowflake store current pre-aggregations in a form that is fast to query?
Why Snowflake’s query-ready path forces a freshness-latency trade-off
The two aggregate tests reveal a structural choice. Querying the Interactive MV returns current results, but Snowflake must reconcile its unrefreshed delta during compilation. Querying the raw Interactive Table avoids that reconciliation, but also removes pre-aggregation and shifts the work into scans and aggregation over a growing table.
The obvious composition is not supported
The obvious alternative would be to store the pre-aggregated result in an auto-refreshed Interactive Table, turning aggregate queries into ordinary table reads. That would serve asynchronously refreshed results, with a minimum supported target lag of one minute.
Even with that freshness trade-off, the direct composition is unsupported: Snowpipe Streaming’s high-performance path lands the raw data in an Interactive Table, and Snowflake does not support using one Interactive Table as the auto-refresh source for another.
The component that accepts the stream cannot feed the component that keeps pre-aggregations fast.
The supported route is a detour
The supported route lands the stream in a standard table first, then uses a refresh warehouse to maintain separate raw and pre-aggregated Interactive Tables. That adds another copy of the raw data and dedicated refresh compute. The same one-minute minimum target lag still applies.
The table below is an approximate scope bridge, not a literal three-way rerun: the first two rows reuse this post’s results, while the supported-detour row summarizes the earlier study through 100 billion rows. All three paths use approximately 16 CPUs of read-side compute; the supported detour additionally requires the separate 128-CPU Gen2 X-Large refresh warehouse shown above.
Aggregate-query latency and freshness during ingestion
| fresh-data path | Median query latency | P99 query latency | Maximum query latency | Result state / refresh overhead |
|---|---|---|---|---|
| ClickHouse Cloud · incremental MV | 13 ms | 42 ms | 111 ms | Current by design |
| Snowflake · Interactive MV | 10.41 s | 22.20 s | 24.17 s | Current via query-time reconciliation |
| Snowflake · supported detour (prior study) | 173 ms | 557 ms | 925 ms | Not continuously current: 1-minute minimum target lag; +$1,814.40 refresh warehouse through 100B rows |
At the median, the supported detour was roughly 60× faster than Snowflake’s Interactive MV, but still roughly 13× slower than ClickHouse Cloud. It also adds $1,814.40 of refresh-warehouse cost through 100 billion rows; by comparison, ClickHouse Cloud’s complete fresh-data path shown below cost $28.69 through the full 113.2-billion-row ingest.
Snowflake’s trade-off: up-to-date answers or consistently fast aggregate queries.
- Interactive MV: up-to-date answers, but reconciliation slowed query compilation.
- Raw Interactive Table: up-to-date answers, but each query had to scan and aggregate the growing raw data.
- Supported detour: fast queries, but over asynchronously refreshed pre-aggregations, with a separate refresh warehouse and a one-minute minimum target lag.
ClickHouse Cloud delivered both: current pre-aggregations and fast aggregate queries, with the preparation performed during ingestion.
WHAT DID THE SUPPORTED DETOUR REQUIRE? (click to expand)
RESULT CONTRACT
The supported detour serves an asynchronously refreshed pre-aggregated table instead of reconciling to a current result at query time. Its minimum supported target lag is one minute.
REFRESH CAPACITY AND COST
The modeled 100-billion-row setup ran a Gen2 X-Large refresh warehouse continuously for 28 hours, adding $1,814.40 at Enterprise list price (21.6 credits/hour × 28 hours × $3/credit).
SMALLER WAREHOUSES
Gen2 Small, Medium, and Large lost the one-minute target after roughly 3, 8, and 15 hours. The X-Large held it for the first 24 hours, but its lag was still trending upward.
One final scope note: the score below covers the architecture tested in this post-Snowpipe Streaming into an Interactive Table, with pre-aggregations maintained in an Interactive MV. The supported standard-table detour above is not part of this comparison; we benchmarked its cost and performance in our previous post.
The final result: real-time performance per dollar
Part 1 defines the CostBench scoring model in full. In short, CostBench adds the cost of keeping the dataset query-ready to the normalized cost of serving the query workload, then multiplies that combined cost by the workload’s accumulated end-to-end runtime. Lower is better.
Snowflake’s end-to-end score was 412× worse
The visual below shows the three score inputs and the final 412× result. Expand the pricing details for the source data, full formulas, matching boundary, and Snowflake fallback assumptions.
The score retains the same approximately matched 16-CPU query-serving basis: ClickHouse Cloud used one 16-CPU read node, while Snowflake’s Small query warehouses are estimated at approximately 16 CPUs. It separately includes each system’s complete fresh-data-path cost.
END-TO-END SCORE: PRICING, FORMULAS, AND CALCULATION BOUNDARY (click to expand)
PRICING BASIS
Enterprise pricing in AWS us-east is $3 per Snowflake credit and $0.3903 per ClickHouse Cloud compute unit (CU) per hour. Snowflake serverless charges come directly from its metering history.
① FRESH-DATA-PATH COST
This covers the complete 113.2-billion-row ingest.
ClickHouse Cloud
2 CUs × 36.75 hours × $0.3903/CU-hour = $28.68705.
Snowflake
Snowpipe Streaming processed 4.6762 TiB and was metered at 17.71783 credits using Snowflake’s 0.0037-credits-per-uncompressed-GB rate. 17.71783 displayed credits × $3/credit = $53.15348 using the unrounded metering values.
Serverless MV refresh was metered at 8.75553 credits. 8.75553 credits × $3/credit = $26.26659.
Total: $53.15348 + $26.26659 = $79.42007. Ingest-time clustering is included in Snowpipe Streaming; no ingest warehouse was used.
② NORMALIZED QUERY-SERVING COST
CostBench prices every matched active-ingestion query as full end-to-end runtime × the applicable per-second read-side rate. The matched schedule contains 209 × 4 = 836 aggregate executions and 35 × 2 = 70 drill-down executions per system.
ClickHouse Cloud
8 CUs × $0.3903/CU-hour × (11.361s aggregate + 50.466s drill-down) ÷ 3,600 = $0.05362.
Snowflake
Interactive-priced time: (946.509s aggregate + 74.815s drill-down) × 1.2 credits/hour × $3/credit ÷ 3,600 = $1.02132.
Fallback-priced time: 6,653.660s aggregate × 2.7 credits/hour × $3/credit ÷ 3,600 = $14.97074.
Total: $1.02132 + $14.97074 = $15.99207.
The accepted proxy prices jobs at or below five seconds on Interactive Small and jobs above five seconds for their full elapsed time on Gen2 Small. Of 836 aggregate jobs, 477 (57.1%) crossed that threshold; no drill-down job did. Compilation is included.
Standing fallback capacity, minimum billing, and an additional primary-warehouse charge for fallback-priced jobs are excluded.
③ TOTAL 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.
CALCULATION BOUNDARY
① covers the complete ingest. ② and ③ use row-count-matched observations from active ingestion only; post-ingestion observations are excluded.
Storage is excluded. Database storage costs are excluded from the score, so the 412× result does not include the storage-footprint difference discussed earlier. Displayed values may be rounded, but the score uses full-precision inputs.
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 result: 732,286.6479 ÷ 1,776.9494 = 412.103×.
The exact summary publishes every accepted input and the normalized-cost contract.
Snowflake’s end-to-end path cost 3.32× as much as ClickHouse Cloud’s, and its queries took 124× as long in total across the matched workload. CostBench multiplies cost by total query runtime, so these two factors combine into a 412× worse end-to-end score.
Snowflake’s fresh-data path affected every score input: Snowpipe Streaming and MV refresh added direct cost; reconciliation increased total query runtime; and that longer runtime increased normalized query-serving cost.
Fast ingestion alone does not make a real-time analytics system
The final view separates cost and runtime instead of collapsing them into one score. ClickHouse Cloud finishes in the faster, lower-cost region; Snowflake finishes in the slower, higher-cost region.
Snowflake kept its raw table advancing through Snowpipe Streaming, but its aggregate paths exposed a choice between up-to-date answers and consistently fast queries. Current answers required reconciliation against the lagging MV or scans and aggregation over the raw table. The supported detour offered fast reads from pre-aggregations, but added a separate refresh warehouse and delayed updates. Drill-down latency also rose as the event-level table grew.
ClickHouse Cloud delivered both current pre-aggregations and fast aggregate queries. It performed ordering and pre-aggregation during ingestion, keeping raw and pre-aggregated data synchronized as new rows arrived. Aggregate queries therefore avoided unfinished refresh work, while drill-down queries read the ordered event-level data.
Fast ingestion is one stage of real-time analytics. Query readiness has to keep pace with it.
Next in the CostBench series: ClickHouse Cloud vs. BigQuery.
