ClickHouse vs Snowflake: how do we run an apples-to-apples POC for 500 concurrent dashboard queries and 5TB/day ingest?

Quick Answer: To get an apples-to-apples POC between ClickHouse and Snowflake for 500 concurrent dashboard queries and 5TB/day ingest, you need the same data model, query set, concurrency pattern, and ingestion profile on both systems—then tune each according to its own best practices, not a lowest-common-denominator setup. Treat it like a workload simulation: define SLAs, generate realistic traffic, and instrument both engines with their native observability to compare latency, cost, and operational risk.

Why This Matters

If you’re running 500+ concurrent dashboard queries on 5TB/day of fresh data, you’re already at the point where architectural mismatches surface quickly: slow dashboards, runaway costs, and brittle ingestion pipelines. A sloppy POC will hide those issues for a few demo queries and a tiny subset of data; a disciplined, apples-to-apples POC forces you to see how ClickHouse and Snowflake behave under the concurrency, freshness, and cardinality you’ll actually run in production.

Key Benefits:

• Realistic performance comparison: You’ll measure millisecond vs multi-second query latency on the exact dashboard patterns and cardinalities your users care about.
• Accurate cost modeling: Matching ingest and concurrency lets you compare storage, compute, and egress costs instead of vendor-specific “best case” benchmarks.
• Operational clarity: You’ll see how each system behaves under stress—ingest backpressure, throttling, merges, cache behavior—before committing to a platform.

Core Concepts & Key Points

Concept	Definition	Why it's important
Workload fidelity	How closely your POC traffic (ingest + queries) matches production patterns in volume, shape, and timing.	Synthetic “toy” workloads make anything look fast; fidelity ensures your 500-concurrent / 5TB/day test exposes real-world bottlenecks.
Engine-native tuning	Configuring each system according to its design: ClickHouse with MergeTree, batching, and system tables; Snowflake with warehouses, caching, and micro-partitions.	An apples-to-apples test doesn’t mean identical configs; it means fair use of each engine’s strengths.
End-to-end observability	Using built-in logs and system tables to track latency, resource usage, and errors.	You need evidence, not anecdotes—query logs, CPU usage, merge behavior, and ingest lag to explain every latency and cost number.

How It Works (Step-by-Step)

At this scale, your POC should feel much closer to a production cutover exercise than a quick benchmark. The outline below assumes: 500 concurrent dashboard queries, 5TB/day ingest, and BI/observability style workloads with high-cardinality dimensions.

1. Define the POC contract

Before you touch either system, lock in:

• Workload scope

  • Data domain (e.g., product analytics events, logs/metrics, customer journeys).
  • Time horizon to test (e.g., full 90 days of history, not just last 24 hours).
  • Required freshness (e.g., “dashboards must see events within 30–60 seconds of ingest”).

• SLOs / SLAs

  • P50 / P95 / P99 latency targets for key dashboard queries (e.g., P95 < 1s, P99 < 2s at 500 concurrent).
  • Ingest durability and lag (e.g., “no more than 1 minute behind real-time under steady load”).
  • Error budgets (e.g., <0.1% query failures/timeouts).

• Cost boundaries

  • Monthly target budget for the workload.
  • Constraints on bursty vs steady-state spend (e.g., warehouses scaled up for peaks vs always-on).

This “contract” becomes your scoring rubric for ClickHouse vs Snowflake.

2. Normalize the data model & queries

You want semantic equivalence, not feature equivalence.

1. Data schema

   • Start from your existing warehouse or log schema.
   • For each table, define:
     • Columns (types, nullability).
     • Primary business keys (user_id, session_id, trace_id, etc.).
     • Critical dimensions (e.g., country, device_type, plan_tier) and measures.
   • Target the same retention window (e.g., 90 days hot).

2. ClickHouse modeling

   • Use MergeTree (or ReplicatedMergeTree in distributed setups) as your base:

     CREATE TABLE events
     (
         event_time  DateTime64(3),
         user_id     UInt64,
         session_id  UUID,
         event_name  LowCardinality(String),
         properties  JSON,
         country     FixedString(2),
         device_type LowCardinality(String),
         revenue     Decimal(18, 4)
     )
     ENGINE = MergeTree
     PARTITION BY toDate(event_time)         -- retention & lifecycle, not a performance hack
     ORDER BY (event_time, user_id, event_name)
     SETTINGS index_granularity = 8192;
     

   • Keep partitions low-cardinality (e.g., daily or monthly, not per-user).
   • Use LowCardinality for medium-cardinality enums to improve compression and joins.

3. Snowflake modeling

   • Mirror the columns and types as closely as possible.
   • Accept that clustering and micro-partition behavior is Snowflake-native; don’t force ClickHouse-style ordering.

4. Query workload

   • Extract the top 20–30 dashboard queries from production logs:
     • Filter-heavy queries (e.g., by country, plan, feature flags).
     • Time-series aggregations (e.g., rolling 7-day metrics).
     • High-cardinality group-bys (e.g., by URL, campaign, error code).
   • Normalize:
     • Same filters/time ranges.
     • Same group-by dimensions.
   • Implement each query twice:
     • ClickHouse SQL that leverages column types and functions (e.g., timeSlot, approx_count_distinct).
     • Snowflake SQL with equivalent semantics, even if functions differ.

3. Reproduce 5TB/day ingest fairly

3.1. Define ingest shape

5TB/day alone is not enough; shape matters more:

• Average row size (e.g., 500 bytes vs 2KB).
• Source pattern:
  • Streaming (Kafka, Kinesis, Pub/Sub)?
  • Micro-batches (e.g., files dropped to S3/GCS every minute)?
• Peaks vs baseline (e.g., 2x traffic spikes during business hours).
• Upserts vs append-only.

Use this to build a synthetic generator or replay real data.

3.2. Ingest into ClickHouse

Your goal: large, batched inserts to keep MergeTree parts healthy.

• Batching guidance

  • Aim for 10,000–100,000 rows per insert.
  • Hard minimum: 1,000 rows per insert to avoid “Too many parts” from tiny fragments.

• Example ingest via Kafka engine:

  CREATE TABLE events_raw
  (
      event_time  DateTime64(3),
      user_id     UInt64,
      session_id  UUID,
      event_name  String,
      properties  String,
      country     String,
      device_type String,
      revenue     Float64
  )
  ENGINE = Kafka
  SETTINGS
      kafka_broker_list = 'kafka:9092',
      kafka_topic_list = 'events',
      kafka_group_name = 'clickhouse_events_group',
      kafka_format = 'JSONEachRow',
      kafka_num_consumers = 4;
  

  Then materialize into MergeTree:

  CREATE MATERIALIZED VIEW events_mv
  TO events AS
  SELECT
      parseDateTime64BestEffort(event_time) AS event_time,
      toUInt64(user_id)                     AS user_id,
      toUUID(session_id)                    AS session_id,
      event_name,
      properties,
      country,
      device_type,
      toDecimal64(revenue, 4)              AS revenue
  FROM events_raw;
  

• Monitor ingest health

  • system.parts:

    SELECT
        table,
        partition,
        count() AS parts,
        sum(rows) AS rows
    FROM system.parts
    WHERE active AND database = 'default'
    GROUP BY table, partition
    ORDER BY parts DESC
    LIMIT 10;
    

    • If parts per partition are exploding, your batches are too small or merges are stuck.
  • system.merges:

    SELECT table, partition_id, bytes_read, bytes_written, is_mutation
    FROM system.merges
    WHERE is_mutation = 0;
    

    • Confirms merges are keeping up; useful under 5TB/day stress.

3.3. Ingest into Snowflake

• Use Snowpipe or streaming ingest with a similar file/batch size to ClickHouse’s rows-per-insert (e.g., 10–200MB files).
• Keep ingest timing identical:
  • If ClickHouse receives micro-batches every 30s, Snowflake should see files or streams at the same cadence.

4. Simulate 500 concurrent dashboard queries

4.1. Concurrency pattern

Decide on:

• Duration: e.g., 60-minute test after warmup.
• Mix:
  • 70% “hot path” queries (top 10 dashboard views).
  • 30% exploratory or ad-hoc analytics.
• Request profile:
  • Users shifting filters quickly (common in dashboards).
  • Overlapping time ranges and dimensions.

Use a load tool that can fire queries against both engines with the same schedule.

4.2. ClickHouse configuration for concurrency

• Deploy options:
  • ClickHouse Cloud: managed autoscaling, suitable if you want “database as a service” and easier POC setup.
  • Open-source ClickHouse: if you want full control and are comfortable operating your own cluster.
• Key settings to understand (don’t blindly max them out):
  • max_concurrent_queries: prevent overload beyond what the CPUs can handle.
  • max_threads: per-query parallelism; adjust to avoid oversubscribing cores.
• Use system.query_log to measure:

  SELECT
      query_kind,
      count() AS queries,
      quantiles(0.5, 0.95, 0.99)(query_duration_ms) AS q_ms
  FROM system.query_log
  WHERE
      type = 'QueryFinish'
      AND event_date = today()
      AND query LIKE '%your_dashboard_signature%'
  GROUP BY query_kind;
  

  • This gives you P50 / P95 / P99 for your dashboard workload at 500 concurrent.

4.3. Snowflake configuration for concurrency

• Choose a warehouse size and scaling policy:
  • e.g., X-LARGE with auto-suspend disabled and auto-scaling to 2–3 clusters to handle 500 concurrent queries.
• Make sure:
  • Caching behavior is consistent (warmup runs before the main test).
  • Warehouse is not being shared with unrelated workloads.

5. Measure latency, freshness, and cost

5.1. Latency and throughput

For each system:

1. Collect per-query metrics:
   • P50, P95, P99 latency for each of the top 20–30 queries.
   • Query failure / timeout rate.
2. Capture system-level behavior:
   • ClickHouse: CPU, memory, number of parts in system.parts, merges in system.merges.
   • Snowflake: warehouse credit consumption, queueing, and cache hit ratios.

Chart them over time: you want to see if latency spikes when ingest peaks, or if concurrency increases tail latency.

5.2. Data freshness

• Track lag between event production and query visibility:
  • Ingest a marker event every X seconds with a timestamp.
  • Run a query every X seconds that checks the most recent event per cluster.
• Compare ClickHouse vs Snowflake:
  • ClickHouse (with Kafka + materialized views) can usually keep seconds-level freshness under load.
  • Snowflake’s freshness depends on Snowpipe/streaming configuration and micro-batch timing.

5.3. Cost modeling

For the exact test window:

• ClickHouse Cloud
  • Note the service size, autoscaling behavior, and storage usage.
  • Convert to estimated monthly cost for your 5TB/day + retention.
• Open-source ClickHouse
  • Compute instance costs + storage + network + operational overhead.
• Snowflake
  • Sum warehouse credits used during ingest and query phases.
  • Add storage cost per TB per month.

Normalize by answering:

“What does it cost per 1,000 dashboard queries and per TB ingested at our SLOs?”

Common Mistakes to Avoid

• Treating partitioning as a speed hack in ClickHouse:

  • Over-partitioning (e.g., PARTITION BY user_id or per-minute partitions) will create too many parts and slow merges, especially at 5TB/day.
  • Instead, use coarse partitions (daily/monthly) for lifecycle and retention, and rely on ORDER BY + columnar scans for performance.

• Ignoring MergeTree health (ClickHouse) or micro-partition behavior (Snowflake):

  • For ClickHouse, not checking system.parts and system.merges during the POC means you might “win” a benchmark while silently building up a parts explosion that will hurt later.
  • For Snowflake, not understanding micro-partition clustering and pruning can lead to over-scanning and hidden cost.

• Running unrealistic concurrency:

  • A test that serializes queries or uses 50 concurrent instead of 500 tells you nothing about your peak load behavior.
  • Use a load profile that matches your BI tool (Looker, Tableau, Superset, etc.) concurrency patterns.

• Comparing “default config vs tuned config”:

  • Don’t compare default ClickHouse with heavily tuned Snowflake, or vice versa.
  • Each platform should get one tuning pass based on vendor guidance and your observations.

Real-World Example

A team running a product analytics platform needed to support 400–600 concurrent dashboard queries on 4–6TB/day of event data. They had Snowflake as their main warehouse and were evaluating ClickHouse as a speed layer for their real-time product analytics and self-serve dashboards.

They built a POC with:

• Same schema and 90 days of data on both platforms.
• Top 25 dashboard queries, replayed at real concurrency, including high-cardinality breakdowns (by URL, feature flag, campaign).
• Ingest via Kafka → ClickHouse (MergeTree with batched materialized views) and S3 → Snowflake (Snowpipe).

During the test:

• ClickHouse Cloud handled 500 concurrent queries with P95 dashboard latency around 300–500ms over billions of rows, while preserving sub-minute freshness.
• Snowflake hit their latency SLOs only by scaling warehouses up, which pushed the monthly query cost above their budget.
• system.query_log and system.parts in ClickHouse exposed a few initial ingest issues (too many small parts due to under-batched inserts), which the team fixed by increasing batch size to ~50,000 rows per insert.
• Once tuned, ClickHouse became their real-time speed layer for interactive dashboards, with Snowflake staying as their central warehouse for ETL and long-running batch analytics.

Pro Tip: When you see a query that’s fast in one engine and slow in the other, don’t stop at “X is faster.” Inspect the query plan and system tables—e.g., in ClickHouse, check system.query_log for read_rows/read_bytes; in Snowflake, examine query profile and micro-partition pruning. You’ll learn whether it’s a data layout issue you can fix or a fundamental engine tradeoff.

Summary

An apples-to-apples POC between ClickHouse and Snowflake for 500 concurrent dashboard queries and 5TB/day ingest is less about synthetic benchmarks and more about faithfully recreating your real workload. Normalize schema and queries, mirror ingest patterns, give each engine a fair tuning pass, and measure not only latency but also freshness, cost, and operational behavior.

ClickHouse shines when you need millisecond results on billions of rows with high concurrency and tight freshness requirements—especially for real-time dashboards, logs/metrics, and agentic AI systems that need low-latency access to recent events. Snowflake remains a strong choice for general-purpose warehousing and batch analytics. A disciplined POC will show you where each fits, and whether a combined “warehouse + speed layer” architecture makes sense.

Next Step

Get Started