Interactive Warehouse Benchmark

Executive Summary

Benchmark Setup

Connection

PM

Database / schema (interactive)

DM_TESTTPCH_BENCH_DB.TPCH_SF100

Interactive warehouse

IWB_202609091200_BENCH_WH_INT · Small · MIN=7 / MAX=10 clusters · STANDARD scaling · fallback IWB_202609091200_BENCH_WH_STD (X-Small)

Clusters used

8 of 10 clusters served queries during the load test

Users

50 concurrent (locust --users 50 --spawn-rate 5)

Duration

3-minute run (180 s)

Load driver

SPCS BENCHMARK_LOCUST (Locust 2.45.0, auto-start / auto-quit)

API layer

SPCS BENCHMARK_API (FastAPI + Snowflake connector) · 4 uvicorn workers · POOL_SIZE=50 · CPU_X64_M · 3 instances

Result caching

USE_CACHED_RESULT=FALSE (measures Snowflake, not the result cache)

Query tag

QUERY_TAG='IWB_202609091200' (used for server-side isolation)

Query and Filter Variants

Locust rotates through the query variants uniformly at random per request, simulating a dashboard where the user changes the date range or applies filters.

SELECT
  N_NAME,
  COUNT(*) AS ORDERS
FROM ORDERS AS O
INNER JOIN CUSTOMER AS C ON O_CUSTKEY = C_CUSTKEY
INNER JOIN NATION   AS N ON C_NATIONKEY = N_NATIONKEY
WHERE O_ORDERDATE BETWEEN '1996-01-01' AND '1996-12-31'
GROUP BY ROLLUP (N_NAME)
ORDER BY N_NAME NULLS LAST;

Table Details (Interactive)

Total working set ≈ 4.6 GB. This is 1% of an X-Small warehouse's ~350 GB cache and fits comfortably in a Small warehouse's ~600 GB cache. All tables are already clustered on the columns used by the workload, so zero-copy is the right path — no interactive-table copies were needed.

Performance Results — Client-side (Locust HTTP)

End-user-observed latency: HTTP round-trip through the SPCS API + Snowflake execution.

All values in milliseconds.

Performance Results — Server-side (Snowflake)

What Snowflake alone spent — compile + execute + queue — measured from INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE over the run window, filtered to QUERY_TAG='IWB_202609091200'.

All values in milliseconds unless labelled. N is the count of SUCCESS queries observed.

Client vs Server — Bottleneck Diagnosis

The gap between the client-observed latency (Locust) and the server-side latency (Snowflake) tells us where to optimize.

Analysis

Client-vs-server delta is small and constant across percentiles (24–34 ms). This lines up with the baseline test where the no-op endpoint returned P99=10 ms — so ~15–25 ms per request is round-trip HTTP + connection-pool acquire + response framing, and the rest is Snowflake time. The delta is flat, not growing with the percentile, so there is no worker/GIL saturation and no connection-pool contention on the API tier. Snowflake is doing effectively all of the observed work.

Client-Side Percentile Comparison

Server-Side Percentile Comparison (Snowflake alone)

Query Profile Health (Interactive, top-slowest)

Verdict

• Compile is well under control — 80–124 ms across the tail. Plan cache is doing its job; no compile-driven spikes.
• No queueing at any percentile — QUEUED_OVERLOAD_TIME=0 for every query. MCW at 7-10 is sized right for 50 users.
• Execute time dominates the tail — the slowest queries are 960–1116 ms of pure execute. This is fundamental per-query cost on Small for a 12-month rollup over 150M orders; reducing further requires either a larger SKU or a pre-aggregated table.
• Scan volume is uniform (~1.5 GB per query) — pruning on O_ORDERDATE is working; each query reads only the partitions in its date window.
• Zero fallbacks — no query exceeded 5 s (max observed: 1,204 ms), the interactive-cancel threshold. All work stayed on the interactive warehouse.

Escalation Path

Each row is one benchmark iteration. If only one row is present, the target latency was met at the initial configuration and no scale-up or scale-out was required. If multiple rows are present, they show the sequence of Step 13c decisions that led to the reported numbers.

Optimization Recommendations

To meet the latency goal

1. Already met at Small × MCW 7-10 with zero-copy on the source tables. No further tuning is required for the current workload shape.

Already correct — keep these

• Clustering keys are correct on all three tables. ORDERS.O_ORDERDATE, CUSTOMER.C_CUSTKEY, NATION.N_NATIONKEY: each matches the query predicates and join keys, giving strong pruning and cheap joins.
• Zero-copy path is the right choice. All tables are standard and already well-clustered; no interactive-table copies are needed. Any future refresh of the base tables is instantly visible to the interactive warehouse.
• Fallback warehouse is set. IWB_202609091200_BENCH_WH_STD (X-Small) will catch any accidental query that exceeds the 5-s interactive cancel — during this run none was needed, but the safety net is in place for future ad-hoc queries.
• MIN=7 clusters is warm at the start of a spike. New users pay ~30–50 ms of cold-cluster warm-up on a fresh cluster; keeping seven clusters up front avoids the cold-start tax during peak dashboard hours.
• MAX=10 gives headroom. Only 8 of the 10 configured clusters were used at 50 concurrent users, so you have ~25% burst headroom before more clusters spin up.

General guidance for dashboards on Interactive

• Keep the dashboard's date-range default aligned with hot cache. The Small warehouse can hold the full 4.6 GB working set. If users routinely pick multi-year ranges, keep the tables attached to the WH (already done) so proactive caching keeps them warm across clusters.
• Consider a pre-aggregated Dynamic Table for the "top of the funnel" tile (nation-level rollup by month, no other filter). It would reduce that tile to a few hundred rows of scan and drop its latency into double-digit ms — freeing capacity for the detail tiles.
• Watch queue time as concurrency grows. If AVG_QUEUE_MS > 0 starts appearing in QUERY_HISTORY_BY_WAREHOUSE, bump MAX_CLUSTER_COUNT (scale out). If queue stays at 0 but P95 drifts up, bump the SKU (scale up). The current run is 0 on both — plenty of margin.
• Guard against runaway ad-hoc queries. The fallback warehouse (X-Small) will catch anything exceeding 5 s; monitor its usage in ACCOUNT_USAGE to spot dashboard cards that grew beyond the interactive envelope.
• Cache warm-up matters after any WH change. CREATE OR REPLACE INTERACTIVE WAREHOUSE (used by resize) resets the data cache. After a resize, run a handful of primer queries before letting real users in — an X-Small warms at ~300–400 MB/s, so 4.6 GB takes ~15 s.
• Result cache is disabled in this benchmark by design (USE_CACHED_RESULT=FALSE). In production, leave it on: identical dashboard queries within 24 h return in single-digit ms.

Configuration Used