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

How these scores are arrived at — the rings, the 0–5 scale, and the evidence rules — is written up in the radar methodology. The numbers on this page — this table and the interactive chart — come from one file: scorecards.json. It is the source of truth, it is machine-readable, and keeping it separate is what stops three copies of the same scores drifting apart between editions. Last reviewed 2026-Q3.

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

Ten-axis fit for the high-volume real-time-analytics use case. Higher is lower-risk for this workload, not "better in the abstract." Click any name in the legend to toggle it on or off — comparing two shapes at a time is far more legible than comparing four.

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.

graph LR
  A["Apps / Services"] --> K["Kafka"]
  B["Logs / Metrics
/ Events"] --> K
  K --> P["ClickPipes /
Kafka engine"]
  P --> CH[("ClickHouse
MergeTree /
SharedMergeTree")]
  D["Dimension data"] --> DI["Dictionaries"]
  DI -->|"no hot-path join"| CH
  L[("Object storage
+ Iceberg")] --> C["REST / Glue /
Unity catalog"]
  C -->|"read in place"| CH
  CH --> MV["Materialized views
/ projections"]
  MV --> DA["Customer-facing
dashboards"]
  CH --> AP["Serving APIs"]
  CH --> OB["Observability
/ ClickStack"]
  G["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

Adopt ClickHouse for real-time analytics at volume; treat it as narrow-use if you're eyeing it as a general warehouse. That split is the whole recommendation, and the rest of this section is why.

The case for it is not subtle. Nothing open-source scans and aggregates faster or cheaper — it took the ClickBench top spot in March 2026, and the cost-per-query lead is structural rather than a benchmark artifact, because it comes from compression and CPU efficiency rather than from throwing hardware at the problem. The streaming-first ingest path and materialized views fit observability, events, and customer-facing serving the way those workloads actually behave: append-mostly, enormous, and read back as aggregates. SharedMergeTree and lightweight updates have also quietly closed the two gaps people still cite from three years ago — cloud elasticity and mutability — which means a lot of the received wisdom about ClickHouse is now out of date.

Where I would not point it: a governed enterprise system of record, where the lineage and dynamic-masking depth simply isn't there yet; join-heavy normalized star schemas, where StarRocks or Snowflake will do less damage to your query plans and your patience; and anything where CDC upserts are the core pattern rather than an occasional correction — that's StarRocks primary-key or Doris merge-on-write territory.

Three risks are worth naming with their mitigations, because each is manageable but none is free. Join ergonomics are the real weak spot: denormalize, lean on dictionaries, and pre-aggregate with materialized views instead of fighting the optimizer. Governance depth needs an external policy layer, and I'd keep PII off the hot tier entirely rather than try to reproduce Snowflake's masking here. And the open-source operational burden is genuine — Keeper, merges, sharding, schema discipline all want an owner, so either buy ClickHouse Cloud or fund a platform team and stop pretending it's free.

The decision conditions I'd write into the ADR: adopt if the POC holds P95 under 300 ms at target concurrency, ingest freshness stays under 5 seconds, and cost-per-query beats the incumbent by more than 50%. Choose narrow use if you only need it for one serving surface alongside an existing warehouse — that's a perfectly good outcome and the most common one. Either way, re-examine joins and governance at each major release; both are improving fast enough that this page will need revisiting.

References

Every score traces back to a source. Vendor engineering blogs are treated as evidence about mechanism and as marketing about benefit — I've used them for the former. Where the claim is about performance, prefer the reproducible benchmark over the announcement.

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.