ClickHouse: an architecture fit assessment

1. Executive summary

ClickHouse is not a general-purpose warehouse and doesn't try to be. It's a columnar OLAP engine built to scan billions of rows a second on commodity hardware, and for that niche nothing open-source is faster or cheaper — in a March 2026 ClickBench run, ClickHouse Cloud on Google Axion took the #1 spot with 30–55% faster queries and load times roughly halved (ClickHouse, Google Next '26). The trade-offs are equally clear: joins and mutable data are the relative weak spots, governance is thinner than a Snowflake, and the open-source deployment asks real operational maturity.

Verdict: Adopt (for real-time analytics at volume) / Narrow Use (as a warehouse)
Best fit: observability, logs/metrics, events & clickstream, customer-facing
          analytics; massive append-mostly ingest; sub-second scans and
          aggregations at scale, at the lowest $/query in class.
Main risks: joins and frequent-update ergonomics; governance / lineage depth;
          OSS operational load (Keeper, merges, sharding, schema discipline).
Required mitigations: ClickHouse Cloud (SharedMergeTree) or a funded platform
          team; denormalize and use dictionaries instead of hot-path joins;
          external governance; reserve lightweight updates for low-rate changes.
Recommended next step: 4-week POC + a ClickBench-style benchmark on YOUR data
          and query shapes, not the public numbers.

Best-fit workloads: observability & log analytics, product/event analytics, clickstream, real-time customer-facing dashboards, time-series and metrics at scale. Poor-fit workloads: a governed enterprise system-of-record; heavily-normalized star schemas that lean on large fact-to-fact joins; workloads with constant row-level mutation/CDC as the core pattern (StarRocks/Doris fit those better). Top strengths: raw scan/aggregation speed, ingest throughput, and cost efficiency. Top risks: join ergonomics, governance depth, and OSS ops burden.

2. Platform positioning

ClickHouse is a columnar, vectorized MPP database whose whole design compounds around one goal: read the fewest bytes and process them fast. Sparse primary-key indexes on sorted MergeTree parts, per-column compression, vectorized execution, and aggressive use of projections and materialized views are the levers. Ingestion is append-optimized — parts are written and merged in the background (LSM-like), which is why it ingests so fast and why updates were historically awkward. Two developments reshape the 2026 story:

• SharedMergeTree (ClickHouse Cloud) replaces ReplicatedMergeTree with a cloud-native engine that separates compute from data on object storage, adding stronger consistency, point-in-time restore, and time-travel (ClickHouse blog).
• Lightweight updates (GA, with a synergy with SharedMergeTree) plus lightweight deletes soften the mutable-data weakness — good for low-rate corrections, not a substitute for a real upsert model.
• Lakehouse reads matured fast: REST catalogs + Apache Polaris since 24.12, AWS Glue and Databricks Unity since 25.3, Hive Metastore since 25.5, Microsoft OneLake since 25.11, and Google's Lakehouse Runtime Catalog in 26.2 — plus a DataLakeCatalog engine and system.iceberg_files for querying Iceberg in place (ClickHouse is data-lake ready).

Against the comparators: Snowflake is the governed, elastic warehouse of record — richer SQL, governance, and ops, but far costlier for always-on serving and not built for sub-second event queries. StarRocks matches ClickHouse on raw speed and beats it on joins and real-time upserts, at the cost of a heavier operational surface and a smaller community. Druid/Pinot are the closest niche rivals for streaming serving — arguably better at real-time ingest and pre-modeled high-concurrency lookups, but weaker on ad-hoc SQL and far more components to run.

3. Radar criteria — the reasoning

Query performance

This is ClickHouse's headline. Sparse indexes, column pruning, vectorized execution, projections, and materialized views make single-table scans and aggregations extraordinarily fast, and it topped ClickBench in March 2026 (source). The honest caveat: multi-table joins are the relative weakness — the optimizer and distributed join strategies trail StarRocks. 4.7, docked from a 5 by the join story.

Real-time ingestion

Append ingest throughput is among the best anywhere — the Kafka table engine and ClickPipes stream millions of rows/sec, and freshness is seconds. Lightweight updates/deletes now handle low-rate mutation. But upsert/CDC as a primary pattern is still second-class vs StarRocks PK / Doris MoW. 4.3.

Lakehouse fit

Materially better than a year ago: broad catalog support (Polaris/Glue/Unity/Hive/OneLake/Google Lakehouse Runtime), a DataLakeCatalog engine, and per-file Iceberg metadata via system.iceberg_files (ClickHouse blog). It's read-first, though — StarRocks still leads on write-back and MV rewrite over Iceberg. 3.8 (up from the 3.5 I gave it in the Doris/StarRocks piece, on this fresh evidence).

SQL & data modeling

An enormous function library and powerful array/aggregate features, but an idiosyncratic dialect, weaker join ergonomics, and no rich dimensional-modeling constructs (SCDs, first-class upsert tables). dbt works well. 3.9.

Cloud elasticity & scalability

SharedMergeTree gives Cloud genuine compute-storage separation and autoscaling, with concurrency raised to ~1,000 queries/server × replicas (docs). OSS scaling is shared-nothing and more manual (sharding, rebalancing). Blended 4.1.

Reliability / HA / DR

ReplicatedMergeTree + Keeper in OSS; Cloud adds automated backups, point-in-time restore, and time-travel via SharedMergeTree. Mature and battle-tested at large scale. 3.9.

Governance & security

SQL-based RBAC, row policies, column restriction via views/read-only roles, quotas, and settings profiles; Cloud adds SSO and managed RBAC (ClickStack). Thinner than Snowflake on dynamic masking, lineage, and catalog-integrated policy. 3.4.

Operations & observability

Rich introspection (system.* tables, query profiling). But OSS operations are demanding — Keeper, merge/compaction tuning, schema discipline, sharding. ClickHouse Cloud removes most of that. Blended 3.6.

Ecosystem integration

Huge community, drivers in every language, dbt, Kafka/ClickPipes, most BI tools, and a growing observability stack (ClickStack). 4.1.

TCO & cost efficiency

The strongest axis after performance: exceptional compression and CPU efficiency make it the cost-per-query leader for analytical scans, OSS or Cloud (TCO guide). Budget real headcount for OSS ops, or pay for Cloud. 4.5.

4. Radar scorecard

Comparator scores (same axis order) — Snowflake: 4.2 / 3.6 / 4.1 / 4.7 / 4.6 / 4.8 / 4.8 / 4.6 / 4.6 / 3.2. StarRocks: 4.7 / 4.3 / 4.4 / 4.1 / 3.9 / 3.8 / 3.7 / 3.6 / 3.8 / 4.3. Druid/Pinot: 4.4 / 4.6 / 3.0 / 3.4 / 3.8 / 3.9 / 3.2 / 3.2 / 3.6 / 3.8.

5. The radar

radar-beta
  title Real-Time Analytics Platform Radar
  axis Perf["Query Perf"], RT["Real-Time"], Lake["Lakehouse"], SQL["SQL & Modeling"], Cloud["Elasticity"], HA["HA / DR"], Gov["Governance"], Ops["Ops"], Eco["Ecosystem"], Cost["Cost Eff."]
  curve ClickHouse["ClickHouse"]{ Perf: 4.7, RT: 4.3, Lake: 3.8, SQL: 3.9, Cloud: 4.1, HA: 3.9, Gov: 3.4, Ops: 3.6, Eco: 4.1, Cost: 4.5 }
  curve StarRocks["StarRocks"]{ Perf: 4.7, RT: 4.3, Lake: 4.4, SQL: 4.1, Cloud: 3.9, HA: 3.8, Gov: 3.7, Ops: 3.6, Eco: 3.8, Cost: 4.3 }
  curve Snowflake["Snowflake"]{ Perf: 4.2, RT: 3.6, Lake: 4.1, SQL: 4.7, Cloud: 4.6, HA: 4.8, Gov: 4.8, Ops: 4.6, Eco: 4.6, Cost: 3.2 }
  curve DruidPinot["Druid / Pinot"]{ Perf: 4.4, RT: 4.6, Lake: 3.0, SQL: 3.4, Cloud: 3.8, HA: 3.9, Gov: 3.2, Ops: 3.2, Eco: 3.6, Cost: 3.8 }
  max 5
  min 0
  ticks 5
  graticule polygon
          

Interactive version — click a platform in the legend to toggle it on or off:

6. Workload-specific analysis

7. Reference architecture

The canonical ClickHouse serving platform is streaming-first: events and logs land via Kafka/ClickPipes into wide, denormalized, sorted tables; materialized views pre-aggregate on write; dimension data rides along as dictionaries to avoid hot-path joins; the lake stays the system of record and ClickHouse reads it in place when needed.

flowchart LR
  Apps[Apps / Services] --> Kafka[Kafka]
  Logs[Logs / Metrics / Events] --> Kafka
  Kafka --> Pipes[ClickPipes / Kafka engine]
  Pipes --> CH[(ClickHouse
MergeTree / SharedMergeTree)]
  Dims[Dimension data] --> Dict[Dictionaries]
  Dict --> CH
  Lake[Object Storage + Iceberg] --> Cat[REST / Glue / Unity Catalog]
  Cat --> CH
  CH --> MV[Materialized Views / Projections]
  MV --> Dash[Customer-facing Dashboards]
  CH --> API[Serving APIs]
  CH --> Obs[Observability / ClickStack]
  IAM[SSO / RBAC / Row policies] -.governs.-> CH
          

The pattern that makes ClickHouse fly — a materialized view that pre-aggregates on ingest so dashboards read a tiny rollup, not the raw event stream:

-- Pre-aggregate events on write into a compact rollup
CREATE MATERIALIZED VIEW events_5m_mv
TO events_5m AS
SELECT
  toStartOfFiveMinutes(ts) AS bucket,
  app_id,
  countState()            AS events,
  uniqState(user_id)      AS users,
  sumState(amount)        AS revenue
FROM events
GROUP BY bucket, app_id;
-- Dashboards query events_5m (merge the -State aggregates), never raw events.

8. POC & benchmark plan (4 weeks)

The public ClickBench numbers are real but they're not your workload. Benchmark your own event shapes, your own concurrency, your own freshness target.

• Week 1 — setup: stand up ClickHouse Cloud (SharedMergeTree) and/or an OSS cluster with Keeper; model the two hottest tables (a wide event table + a rollup MV); wire SSO + RBAC + row policies; enable system.* dashboards.
• Week 2 — load & query: stream production-like volume via Kafka/ClickPipes; build MVs/projections; run the real dashboard query set plus a concurrency ramp to the target ceiling; test a lakehouse read via a catalog.
• Week 3 — enterprise readiness: replica failover; backup + point-in-time restore; row/column policy enforcement; quota isolation between tenants; lightweight-update/delete behavior under load; cost tracking.
• Week 4 — evaluate: update this scorecard with measured numbers, write the ADR, and commit Adopt / Narrow-Use / Defer.

9. Final recommendation

Recommendation: Adopt for real-time analytics at volume; Narrow Use as a warehouse

Rationale
  - Fastest, cheapest analytical scans in class (ClickBench #1, Mar 2026)
  - Streaming-first ingest + MVs fit observability/events/serving natively
  - SharedMergeTree + lightweight updates close old cloud/mutability gaps

Best-fit workloads
  - Observability, logs, metrics, traces
  - Event / clickstream / product analytics
  - Customer-facing real-time dashboards at high concurrency

Not recommended for
  - Governed enterprise system-of-record (lineage/masking depth)
  - Join-heavy normalized star-schema BI  -> StarRocks / Snowflake
  - CDC-upsert-as-core workloads           -> StarRocks PK / Doris MoW

Key risks -> mitigations
  - Join ergonomics  -> denormalize, dictionaries, pre-agg MVs
  - Governance depth -> external policy layer; keep PII off the hot tier
  - OSS ops burden   -> ClickHouse Cloud, or a funded platform team

Decision conditions
  - Adopt if the POC holds P95 < 300 ms at target concurrency AND
    ingest freshness < 5 s AND $/query beats the incumbent by >50%.
  - Narrow-Use if you need it only for one serving surface beside a warehouse.
  - Reassess joins + governance each major release — both are improving.

Scores are my own architect-level judgment for a specific use case, calibrated to mid-2026 releases — not a vendor ranking. Re-score against your own POC numbers before you commit. Sources are linked inline throughout.