Clickhouse Architecture Advisor

ClickHouse Architecture Advisor

This skill adds workload-aware architecture decisioning on top of clickhouse-best-practices.

Official docs remain the source of truth.

This skill must always prefer official ClickHouse documentation when available.

Required behavior

Before producing recommendations:

1. Identify the workload shape

• observability
• security / SIEM
• product analytics
• IoT / telemetry
• market data / financial services
• mixed OLAP with point-lookups

2. Read the relevant decision rule files in rules/ 3. Use mappings/doc_links.yaml to attach official documentation 4. Classify every recommendation as:

• official
• derived
• field

5. Never present field guidance as official guidance 6. If a recommendation is uncertain, say so explicitly

Provenance rules

official

Use this when the recommendation is directly backed by official docs.

derived

Use this when the recommendation is not stated verbatim in docs but follows logically from documented ClickHouse behavior.

field

Use this only for experience-based guidance that may be situational. When using field, include:

• a disclaimer that the advice is heuristic
• a relevant official doc if one partially applies
• the reason the advice depends on workload context

Read these rule files by scenario

Real-time ingestion design

1. rules/decision-ingestion-strategy.md 2. rules/decision-real-time-preaggregation.md 3. Relevant best-practices insert rules

Time-series and retention design

1. rules/decision-partitioning-timeseries.md 2. Relevant best-practices schema partition rules

Enrichment and dimension lookups

1. rules/decision-join-enrichment.md 2. Relevant best-practices query join rules

Mutable state / late-arriving events

1. rules/decision-late-arriving-upserts.md 2. Relevant best-practices mutation avoidance rules

Output format

Structure responses like this:

## Workload Summary
- workload:
- latency target:
- data shape:
- primary query patterns:
- operational constraints:

## Key Decisions
- ...
- ...

## Recommendations

### <Recommendation title>

**What**
...

**Why**
...

**How**
...

**Category**
official | derived | field

**Confidence**
high | medium | heuristic

**Source**
- doc link(s)

**Validation**
- concrete SQL, metric, or smoke test

Architecture-specific guidance

Prefer decision frameworks over generic advice. Good responses should:

• explain tradeoffs
• identify the likely operating bottleneck
• separate immediate actions from structural redesign
• provide target architecture patterns, not just isolated settings

Full reference

See AGENTS.md for the compiled version and examples/ for sample outputs.

ClickHouse Architecture Advisor

Version 0.1.0 ClickHouse Inc April 2026 ClickHouse 24.1+

Abstract

This skill complements clickhouse-best-practices by adding a workload-aware architecture layer for ClickHouse. It is optimized for advisory, workshop, and system design workflows where a user needs more than a rule check. It provides decision frameworks for ingestion strategy, time-series partitioning, enrichment paths, late-arriving data, and real-time pre-aggregation.

Core principle

Official documentation is the source of truth. Every recommendation must be labeled as:

• official
• derived
• field

Decision areas

1. Ingestion strategy

Use when deciding between:

• direct inserts
• async inserts
• Kafka engine + MV
• upstream buffering

2. Time-series partitioning

Use when deciding:

• whether to partition
• partition granularity
• how retention and TTL affect design
• how to avoid excessive partition counts

3. Enrichment path selection

Use when deciding between:

• runtime JOINs
• dictionaries
• denormalization
• materialized enrichment

4. Late-arriving data and mutable state

Use when reasoning about:

• immutable append-only events
• latest-state queries
• replacing or collapsing semantics
• whether frequent mutations should be avoided

5. Real-time pre-aggregation

Use when deciding:

• raw-only design
• incremental materialized views
• refreshable MVs
• rollup tables

Output standard

A valid architecture response should include:

• workload summary
• key decisions
• recommendations with provenance labels
• suggested target architecture
• example DDL or SQL
• validation approach

Required recommendation schema

See schemas/recommendation_schema.yaml.

Rule index

1. decision-ingestion-strategy 2. decision-partitioning-timeseries 3. decision-join-enrichment 4. decision-late-arriving-upserts 5. decision-real-time-preaggregation

Implementation notes

This skill is intentionally narrow:

• it does not replace low-level rule enforcement
• it does not make commercial recommendations
• it does not claim field heuristics are official policy

Its purpose is to translate documented ClickHouse capabilities into workload-specific architecture decisions.

Example: Financial Services — Real-time market surveillance

Scenario

• Workload: order and execution event stream
• Ingest rate: 80M events/day
• Query pattern:
• latest order state
• time-bounded compliance scans
• intraday anomaly and pattern detection
• Freshness target: sub-second to low-single-digit seconds
• Additional requirement: late-arriving corrections and cancels

Workload Summary

This is not classic OLTP. It is a high-throughput analytical event pipeline with mutable business state derived from ordered events. The architecture should preserve append-only facts and compute latest-state views rather than forcing row-by-row transactional mutations.

Key Decisions

1. Keep a raw append-only event table 2. Model current state separately 3. Avoid using ClickHouse like a row store 4. Use pre-aggregation only for repeated surveillance views

Recommendations

1. Raw event table plus latest-state projection

What Store all order lifecycle events immutably, then derive current order state.

Why This preserves auditability and handles late-arriving business events without relying on heavy mutations.

Category derived

Confidence medium

Source

• https://clickhouse.com/docs/en/guides/replacing-merge-tree

2. Use ReplacingMergeTree for current-state table if version semantics are clean

What Maintain a latest-state table keyed by order identifier and version timestamp.

Why If corrections naturally replace prior state, ReplacingMergeTree is often the cleanest documented pattern.

Category official

Confidence high

Source

• https://clickhouse.com/docs/en/guides/replacing-merge-tree

3. Use dictionaries for small reference data used in surveillance rules

What Use dictionaries for symbol metadata, venue mappings, or account-tier lookups if they are read constantly and update slowly.

Why Repeated runtime joins in hot surveillance logic are often more expensive than key-based dictionary lookup.

Category official

Confidence high

Source

• https://clickhouse.com/docs/en/sql-reference/dictionaries

Example raw events table

CREATE TABLE order_events
(
    event_time DateTime64(3),
    trade_date Date,
    order_id String,
    account_id String,
    symbol LowCardinality(String),
    venue LowCardinality(String),
    event_type LowCardinality(String),
    qty UInt64,
    px Decimal(18, 6),
    version_ts DateTime64(3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(trade_date)
ORDER BY (trade_date, symbol, order_id, event_time);

Example current-state table

CREATE TABLE order_state_latest
(
    order_id String,
    symbol LowCardinality(String),
    venue LowCardinality(String),
    status LowCardinality(String),
    qty UInt64,
    px Decimal(18, 6),
    version_ts DateTime64(3)
)
ENGINE = ReplacingMergeTree(version_ts)
ORDER BY (order_id);

Caveat

This pattern is architectural, not transactional. If the requirement is strict OLTP locking semantics with many point updates per key, ClickHouse should not be the system of record for that path.

Example: Observability — High-throughput event ingestion

Scenario

• Workload: observability / logs
• Ingest rate: 300K events/sec
• Producer shape: many agents, uneven bursts
• Query pattern: time-range scans, grouped aggregations, service-level dashboards
• Freshness target: under 5 seconds

Workload Summary

This is a high-ingest, append-friendly, time-series workload. The main architectural risks are:

• excessive small parts
• merge pressure
• slow tail queries if rollups are not used for dashboards

Key Decisions

1. Use a decoupled ingestion path 2. Partition conservatively 3. Preserve raw data while introducing focused rollups

Recommendations

1. Kafka engine + materialized view for ingestion

What Use Kafka as the decoupling layer and load ClickHouse through Kafka engine tables and downstream MVs.

Why The producer fleet is bursty and distributed. This pattern improves replayability and isolates producers from storage behavior.

How

• Kafka topic per stream family
• Kafka engine source table
• MV into MergeTree raw table

Category derived

Confidence medium

Source

• https://clickhouse.com/docs/engines/table-engines/integrations/kafka
• https://clickhouse.com/docs/materialized-view/incremental-materialized-view

2. Monthly partitions on event time

What Use PARTITION BY toYYYYMM(event_time) for the main raw table.

Why This workload is time-bounded and retention-based, but daily partitioning would likely create unnecessary operational overhead at scale.

Category derived

Confidence medium

Source

• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key

3. Incremental MVs for hot service dashboards

What Create rollup tables for repeated service-health queries.

Why Dashboards and alerts should not repeatedly scan the raw log corpus.

Category official

Confidence high

Source

• https://clickhouse.com/docs/materialized-view/incremental-materialized-view

Example raw table

CREATE TABLE logs_raw
(
    event_time DateTime64(3),
    service LowCardinality(String),
    level LowCardinality(String),
    host String,
    message String,
    attrs JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (service, event_time, host);

Example rollup table

CREATE TABLE logs_rollup_1m
(
    bucket DateTime,
    service LowCardinality(String),
    level LowCardinality(String),
    count_state AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(bucket)
ORDER BY (service, level, bucket);

Example workload pack

This folder demonstrates how the architecture advisor should respond for representative real-time and enterprise workloads.

Included

• observability-high-throughput.md
• finserv-market-surveillance.md
• siem-security-analytics.md

Why examples matter

The examples show how the skill should:

• classify the workload
• separate key architectural decisions from low-level rules
• provide official documentation links
• label derived and field guidance explicitly

Example: SIEM / security analytics

Scenario

• Workload: endpoint, identity, cloud, and network telemetry
• Query pattern:
• repeated detection logic
• time-bounded investigations
• lookup-heavy enrichments
• Freshness target: near real-time

Workload Summary

This workload is time-series heavy, multi-source, and often enrichment-bound. The two common failure modes are:

• expensive runtime JOINs on slow-changing dimension data
• micro-batched ingest that creates excessive parts

Key Decisions

1. Use append-friendly event storage 2. Replace repeated small-dimension JOINs where possible 3. Protect hot detections with precomputation or lookup structures

Recommendations

1. Enable async inserts for small-batch telemetry senders

What Use async inserts when many agents or producers write very small batches.

Why This reduces small-part pressure without rewriting every upstream sender.

Category official

Confidence high

Source

• https://clickhouse.com/docs/en/operations/settings/settings#async_insert
• https://clickhouse.com/docs/optimize/asynchronous-inserts

2. Use dictionaries for slow-changing asset and identity lookups

What Move repeated device-owner or asset-lookup enrichment out of runtime joins where appropriate.

Why Security detections often execute continuously; repeated joins on slow-changing dimensions waste CPU.

Category official

Confidence high

Source

• https://clickhouse.com/docs/en/sql-reference/dictionaries

3. Use incremental MVs for repeated aggregated detection views

What Precompute common counts, rates, and rollups that power dashboards or recurring detections.

Why Not every threat-hunting query should hit the same raw telemetry tables repeatedly.

Category official

Confidence high

Source

• https://clickhouse.com/docs/materialized-view/incremental-materialized-view

ClickHouse Architecture Advisor

Agent skill providing workload-aware architecture guidance for ClickHouse.

This skill is intended to complement clickhouse-best-practices, not replace it.

What it adds

The existing best-practices skill is rule-first and documentation-first. This skill adds:

• workload classification
• decision frameworks
• architecture tradeoff guidance
• broader system design suggestions
• explicit separation of doc-backed guidance from field heuristics

Recommendation categories

Every recommendation must be labeled as exactly one of:

• official — directly backed by official ClickHouse documentation
• derived — reasoned from official documentation and core ClickHouse behavior
• field — practice-based guidance from field experience, explicitly flagged as non-authoritative

When this skill should activate

Use this skill when the user is:

• designing a real-time architecture
• choosing between ingestion patterns
• deciding whether to use joins, dictionaries, denormalization, or MVs
• planning for late-arriving data or upserts
• reasoning about time-series modeling
• building a POC or workshop design
• asking for “what should the architecture look like?”

Relationship to clickhouse-best-practices

Use clickhouse-best-practices for:

• concrete schema and query rule checks
• low-level design validation
• docs-backed enforcement

Use this skill for:

• when / why / how decisioning
• architecture shape
• system-level tradeoffs
• converting best practices into a target design

Included decision frameworks

• ingestion strategy for throughput and latency
• time-series partitioning and retention design
• enrichment path selection: JOIN vs dictionary vs denormalization
• late-arriving data and mutable-state patterns
• real-time pre-aggregation with incremental MVs

Output contract

Responses should typically include: 1. workload summary 2. key decisions 3. recommendations with provenance labels 4. suggested target architecture 5. example DDL and query patterns 6. caveats and validation steps

Choose an ingestion strategy based on throughput, latency, and producer shape

Principle

Do not recommend a single ingestion pattern for every workload. The right approach depends on:

• events per second
• rows per insert
• acceptable buffering latency
• whether producers can batch
• whether decoupling is required

Decision framework

Guidance

Recommendation: direct batched inserts

Use when the application can naturally batch inserts into healthy sizes.

Why The existing best-practices guidance already favors appropriately sized insert batches.

Official sources

• insert-batch-size
• https://clickhouse.com/docs/best-practices/selecting-an-insert-strategy

Recommendation: async inserts

Use when producers emit many small writes and the application cannot easily batch.

Why Async inserts let ClickHouse buffer small writes server-side to reduce part pressure.

Official sources

• https://clickhouse.com/docs/en/operations/settings/settings#async_insert
• https://clickhouse.com/docs/optimize/asynchronous-inserts

Recommendation: Kafka engine + materialized view

Use when a queue-based, decoupled ingest path is needed.

Why This is typically the right design when multiple producers, burst handling, or replayability matter.

Category derived

Sources

• https://clickhouse.com/docs/engines/table-engines/integrations/kafka
• https://clickhouse.com/docs/materialized-view/incremental-materialized-view

Validation

• Check average rows per insert
• Check part creation rate
• Check whether insert latency spikes correlate with small batch behavior

Choose the right enrichment path: JOIN, dictionary, denormalization, or precomputed enrichment

Principle

Not every dimension lookup should remain a runtime JOIN. The right design depends on dimension volatility, cardinality, and the cost profile of repeated enrichment.

Decision framework

Guidance

Recommendation: dictionaries for repeated low-latency lookups

Why Dictionaries are often the best fit for repeated key-based enrichment when the lookup data is relatively static.

Official sources

• https://clickhouse.com/docs/en/sql-reference/dictionaries
• query-join-consider-alternatives

Recommendation: denormalize when operationally simple

Why If the dimension is stable and queried constantly, denormalization may outperform repeated joins.

Category derived

Official context

• https://clickhouse.com/docs/best-practices/minimize-optimize-joins

Recommendation: use refreshable or incremental MVs for structured enrichment

Why Precomputed enrichment is often better than expensive runtime joins for recurring production queries.

Official sources

• https://clickhouse.com/docs/materialized-view/incremental-materialized-view
• https://clickhouse.com/docs/materialized-view/refreshable-materialized-view

Validation

• Identify top CPU-consuming JOIN patterns
• Compare runtime JOIN cost vs dictionary lookup or precomputed enrichment
• Check dimension update frequency before choosing dictionary lifetime

Handle late-arriving data and mutable state without defaulting to heavy mutations

Principle

Frequent ALTER TABLE UPDATE and ALTER TABLE DELETE operations are usually the wrong first answer. Prefer append-friendly patterns and engines designed for state evolution.

Decision framework

Guidance

Recommendation: prefer append + latest-state logic for event streams

Why Many real-time systems do not need in-place updates if the application can compute current state from ordered events.

Category derived

Official context

• https://clickhouse.com/docs/en/guides/replacing-merge-tree

Recommendation: use ReplacingMergeTree for replacement semantics

Why ReplacingMergeTree is the standard documented pattern for row replacement based on version ordering.

Official sources

• https://clickhouse.com/docs/en/guides/replacing-merge-tree
• insert-mutation-avoid-update

Recommendation: avoid defaulting to mutations

Why Heavy mutation usage often becomes the bottleneck in otherwise append-friendly systems.

Official sources

• insert-mutation-avoid-update
• insert-mutation-avoid-delete

Validation

• Measure mutation volume per day
• Check whether the workload is actually latest-state, not true OLTP
• Confirm whether late-arriving records can be handled by version semantics

Choose time-series partitioning for retention, pruning, and operational hygiene

Principle

Partitioning should primarily support lifecycle management and bounded pruning. It should not be used casually or at excessively fine granularity.

Decision framework

Guidance

Recommendation: start without partitioning when unsure

Why The best-practices skill already notes that teams often over-partition too early.

Official sources

• schema-partition-start-without
• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key

Recommendation: monthly partitions for many real-time systems

Why For observability, SIEM, telemetry, and many financial workloads, monthly partitions often balance lifecycle management with manageable partition counts.

Category derived

Source

• https://clickhouse.com/docs/partitions

Recommendation: align partitioning with TTL boundaries

Why If retention deletes are a primary operational concern, partitioning should make those drops efficient.

Official sources

• schema-partition-lifecycle
• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key

Validation

• Count active partitions
• Verify common queries align to the partition key
• Confirm retention actions operate at partition granularity where possible

Choose raw-only vs incremental materialized views vs refreshable materialized views

Principle

Real-time workloads should not be forced into either “everything raw” or “precompute everything.” The correct choice depends on freshness, query repetition, and transformation complexity.

Decision framework

Guidance

Recommendation: incremental MVs for repeated real-time aggregation

Why Incremental MVs are the documented best fit for continuously maintained rollups over insert streams.

Official sources

• https://clickhouse.com/docs/materialized-view/incremental-materialized-view
• query-mv-incremental

Recommendation: refreshable MVs for heavier joins or scheduled transforms

Why Refreshable MVs better fit complex transformations that do not need per-row trigger semantics.

Official sources

• https://clickhouse.com/docs/materialized-view/refreshable-materialized-view
• query-mv-refreshable

Recommendation: dual-path design for hot dashboards

Why A raw table preserves flexibility while a rollup path protects latency-sensitive workloads.

Category derived

Official context

• https://clickhouse.com/docs/use-cases/time-series/basic-operations

Validation

• Identify repeated dashboard queries
• Compare raw scan cost against incremental aggregation maintenance
• Confirm whether the source is append-only enough for incremental MV semantics