When Everyone Asks at Once: Designing Snowflake for Interactive Analytics

William McKnight and Jake Dolezal

Picture stock trading activity at a brokerage on a busy morning. Every trade the firm handles is landing in one big table, continuously, all day with: 

• Hundreds of analysts with dashboards open, each one refreshing on its own schedule.
• A compliance check running every thirty seconds, looking for suspicious trading patterns.
• A risk model looping over the same data, totaling up how much each customer account traded and how volatile those trades were.
• Three people investigating one particular stock at once, because something about it looked strange an hour ago.
• A customer-facing website showing a few hundred outside users their own account history.
• An AI agent, increasingly the fastest-growing consumer of all, firing off dozens of exploratory queries a minute on behalf of one person who asked one question in plain English.

None of the queries are complex. Most of them are basic – count the flagged trades, total the volume per account, rank the busiest stocks, look up a single record by its ID. Run any one of them on an idle system and it comes back in well under a second.

The interesting question is not “How fast is this query?” but “What happens when everyone asks at once?”

Why this scenario

We built a test around this kind of trading-activity data which are similar to the shape of most interactive analytics workloads:

• High concurrency is normal, not exceptional. The load comes from many clients, not from one giant query or nightly batch.
• The consumer is a human waiting on a screen. Latency expectations are measured in hundreds of milliseconds, not minutes. A dashboard that takes eight seconds is a dashboard people stop using.
• The query mix is heterogeneous. Point lookups sit next to window functions and self-joins in the same workload on the same table competing for the same compute.
• Failure is visible. In an overnight batch update, a query that dies gets retried at 3 a.m. and nobody notices. In an interactive workload, a failed query is a broken dashboard tile and a support ticket.

Swap trade executions for clickstream events, ad impressions, IoT telemetry, security logs, or application observability data, and the shape is almost the same. If you are building anything user-facing on top of analytical data, this is your workload.

Some published database benchmarks measure single-query latency on an idle system. However, it is practically useless for capacity planning, because it answers a question nobody operationally has. Your production system is never idle, never single-user, and never only a specific single query that is being run over and over again. This benchmark was inspired by and follows in the tradition of the synthetic performance tests created in 1983 by Dina Bitton, David J. DeWitt, and Carolyn Turbyfill at the University of Wisconsin-Madison Computer Sciences Department to systematically evaluate database systems. We layered a logical naming view and added new data types such as JSON to modernize it to match today’s workloads.

The three ways high-concurrency workloads fails

When you push concurrency up on a fixed amount of compute, systems degrade in one of three characteristic ways, and which one tells you a great deal about the underlying architecture.

1. Queueing. Work arrives faster than the engine can process it, so it is placed in a queue. Throughput flattens and latency climbs roughly in proportion to the queue depth. Nothing fails, but everything just gets slower.
2. Memory overrun. An engine that executes primarily in memory has a hard ceiling per node. Aggregations, sorts, window functions, and hash joins all hold shares of the memory footprint. When the available memory is gone, the engine has exactly two options: kill the query (an OOM error) or spill that state to disk and continue much more slowly.
3. Long tail-latency. Median latency looks fine, but p95 and p99 fly off the chart. This is what happens when a small number of heavy queries monopolize resources and starve the light ones behind them. Your dashboard “works” until you have one user who waits ninety seconds and files the ticket. If you only chart averages, you will never see this.

Our useful benchmark makes all three visible. That means reporting error rate as a key metric alongside throughput and latency, and reporting percentiles, not averages. A system serving 40 queries per second while silently failing a third of them is not serving 40 queries per second.

Architectural choices for high-concurrency workloads

The following are questions to ask when making architectural decisions for your interactive workloads.

1. Do your queries live in memory? Engines that keep working state resident in memory are extremely fast right up to the memory ceiling, then hit a wall. Engines that can spill to local storage degrade more gently but pay for it in latency when they do. Once you hit the wall, your only option is to go larger.
2. Do you scale up or scale out? Adding a bigger node gives each query more room. Adding a second node gives you more simultaneous queries with the same ceiling. Can your platform route work across the units without you rewriting anything?
3. Do you need an interactive query serving path? Snowflake now offers Interactive Tables and Interactive Warehouses, a serving path purpose-built for high-concurrency, low-latency access, as distinct from a general-purpose warehouse. It carries a different physical layout, a much larger cache, and a different scheduling model, plus a hard latency limit with a fallback path for anything heavier. 

Our Central Focus

We framed the following set of questions to test Snowflake and ClickHouse’s capabilities and performance:

1. Where does each configuration saturate? At what concurrency does adding more clients stop producing more completed work?
2. How does latency tail? p95 and p99, not just the average.
3. What is the failure mode at the ceiling? Queueing, out of memory, or long tail latency and at what error rate?
4. How well do they scale?

The Test

We built a test around trading activity data. One row per trade, with the stock, the customer account, where it was executed, the price, the quantity, and a measure of volatility.

• 500 million trades. Roughly 1 TB uncompressed, three years of history, 500,000 distinct stocks, both loaded from the same parquet in the same S3 bucket. Neither engine got a private data path.
• Realistic skew. Stock popularity follows a Zipf distribution. The most heavily traded stock alone is 15.8% of all rows, the top five are a third of the table, and a long tail of thousands of stocks have a few thousand trades each. This skew is what makes join and aggregation memory unpredictable.
• Thirteen queries. Tiered by cost, run round-robin. An interactive workload is not uniform, and a mix of only low-end queries measures connection handling rather than the engine.

• SQL equivalence. All thirteen queries are identical in rows returned, with only platform SQL syntax differences where necessary.

Compute Cost

We designed the test for an equivalent compute cost across the two platforms.

The Harness

A closed-loop, rising-concurrency sweep:

• Concurrency levels 1, 2, 4, 8, 16, 32. At level C, exactly C workers run the mix round-robin with no think time. Each issues its next query the instant the previous returns. We ran the queries as a deterministic round-robin to ensure equal distribution of the queries.
• 600 seconds per level. But in-flight queries going past the deadline were allowed to finish. 
• Metrics captured. Attempts, errors, error rate, QPS, and p50 / p95 / p99. 

How to Read the Results

Three numbers per configuration:

• Throughput or QPS (completed queries per second). Rises with concurrency until the engine saturates, then flattens. Where it flattens is that configuration’s capacity.
• P50 / p95 / p99 latency. P50 tells you what a typical user experiences. P99 tells you whether your dashboard has a sporadic performance problem.
• Error rate. Queries that returned no result, split by cause. A throughput number needs an error rate to be honest.

Saturation throughput is fixed, so every additional concurrent client converts directly into latency. Past the bend of the curve, more load does not produce more work, only more waiting.

Results

Throughput (in Queries Per Second)

Latency (in seconds)

Error Rates (in percentage of queries executed)

Figure 1: P95 Latency vs. Throughput (QPS) as concurrency increases

• ClickHouse hits the memory wall. Its errors are not timeouts. They are Out of Memory (OOM) errors. The two account-level aggregations (a GROUP BY over 15.8 million distinct accounts) and the window/self-join queries build hash and window state nearly exceeds the 16 GiB per replica. Once the query is run in parallel with other queries, the engine killed it, even with spilling on. The failure rate climbs with concurrency—0.7% at one user, 59.4% at 32 users. Concurrent heavy queries share the same fixed pool, and once it is exhausted even light queries fail to allocate and die. Note the median (p50) query reaches 18.7 seconds at 32 clients. The successful queries are not fast either because the whole system is congested.
• ClickHouse spilling actually made performance worse with higher concurrency. Counterintuitively, ClickHouse’s fallback mechanism, disk spilling, caused active queries to live long and retain their hold on memory. With spilling on, the same query ran 10-90 seconds (p95 hit 89 seconds even at c=1), spilling partial state while still holding large resident buffers and merge state. In our sweeps, longer queries meant many more heavy queries alive simultaneously. Their combined memory footprint climbs until it exhausted the 16 GiB server pool, and then queries started failing across the board. The OOM error line crossed at 8 concurrent users.
• Snowflake never errors—it falls back. Every query that exceeds the interactive warehouse’s ~5-second limit is transparently rerouted to a fallback warehouse and completed there. The error rate is 0% at every concurrency level. This causes some tail latency but not failures. P95 queries rise from 5 seconds to 68 seconds as the heaviest queries queue on the fallback path. But the light queries—the point lookups and selective filters that make up most of an interactive workload—keep returning in sub-second p50 the whole way up.

The architectural lesson is in the shape of the two failures. ClickHouse has a hard ceiling, a killed query, a broken dashboard tile, and a failure rate that worsens exactly when you need the system most. Snowflake is more resilient. No query is lost, the interactive path stays fast for the queries that belong on it, and only the genuinely heavy work degrades onto separate compute.

Lessons Learned

• Size the system to your concurrency, but diagnose whether you need to scale up or out. A single-user run tells you almost nothing about production, because peak pressure is driven by the number of concurrent heavy queries, not by any one query. Scaling up (a bigger warehouse or replica) raises the memory-and-compute ceiling each query runs against. This might fix out-of-memory failures. Scaling out (multi-cluster warehouses, or more replicas) adds parallel capacity for more simultaneous queries, but every added unit keeps the same per-query ceiling, so it relieves queueing. Estimate your worst-case concurrent heavy-query footprint, then scale up to survive the heavy query and scale out to absorb the concurrency.
• Understand how your engine fails, not just whether it does. These are architectural properties you inherit. ClickHouse in-memory engine with spilling off fails on OOM, and it’s worse under load. Snowflake interactive fails as a graceful slope with a ~5-second limit with a fallback warehouse that completes heavy work on separate compute instead of dropping it. The difference is whether an overloaded moment produces a slow dashboard or a broken one.
• Disk spilling sometimes has unintended consequences. Turning on spilling (or giving a replica more RAM) helps a single heavy query but can make things worse under concurrency, because the engine then admits more concurrent heavy queries that collectively exhaust the pool. This trades a fast failure for a slow one. 
• Know what the Interactive path gives you. Interactive Tables and Interactive Warehouses buy a purpose-built serving layer with a larger cache, a scheduling model tuned for many small queries, a bounded latency budget, and a fallback method. Use it for the high-concurrency serving tier, not as a general-purpose warehouse.

Conclusion

Go back to the brokerage on a busy morning: forty analysts, a compliance scan every thirty seconds, a risk model, three people chasing one stock, a public account-history page, and an AI agent firing off a dozen questions a minute. None of these queries is complex. The challenge was all of them arriving at once, on the same table, on a fixed amount of compute.

That is the real design problem in interactive analytics, and it is an architectural one, not a tuning one. Our test made the three failure modes visible—queueing, memory overrun, and long-tail latency—and the two engines reacted differently by design. ClickHouse is fast right up to its memory ceiling, then it fails hard. With spilling on, it dropped up to 59% of queries due to out-of-memory errors. This is exactly the concurrency an interactive system should be able to handle. Snowflake’s interactive path took the same unbounded pressure with zero dropped queries, keeping the light queries sub-second-to-second and letting only the heavy work spill onto a fallback warehouse.

If you are building anything user-facing on analytical data—clickstream, ad impressions, IoT, security logs, or trades—this is your workload. Choose a serving path for the workload that degrades gracefully when a heavy query slips through. Snowflake’s Interactive Tables and Interactive Warehouses are built to make this choice an easy one.