Claude Code with ClickHouse: OLAP Queries Without Footguns

Why ClickHouse without CLAUDE.md ships slow queries

ClickHouse is a column-oriented OLAP database that handles billions of rows on a single node when you respect its physics. The physics are simple. Data is sorted on disk by the ORDER BY key of the MergeTree engine. Queries that filter on a prefix of that key skim only the relevant granules. Queries that do not filter on the key prefix scan everything. The difference between the two is two to four orders of magnitude in latency.

Claude Code does not know the ORDER BY key of your table unless you tell it. Without a CLAUDE.md in place, Claude reaches for the OLTP patterns it has seen the most: SELECT with random WHERE clauses, secondary indexes treated as B-trees, JOINs assumed to be cheap, GROUP BY assumed to spill to disk gracefully. None of these assumptions hold in ClickHouse. The result is queries that work fine on a million rows in dev, then time out on a billion rows in production.

This guide gives you the CLAUDE.md configuration that anchors Claude Code to how ClickHouse actually works: MergeTree engines chosen for the right workload, ORDER BY keys that match the dominant query shape, materialized views that pre-aggregate without recursing, and SETTINGS overrides that prevent runaway scans. If you are comparing analytical backends, DuckDB and Claude Code with BigQuery cover the equivalent setups for those platforms.

The ClickHouse CLAUDE.md template

The CLAUDE.md at your project root is read at the start of every Claude Code session. For a ClickHouse project it needs to declare: the engines you use and what each one is for, the ORDER BY key conventions for every table family, the projection and materialized view patterns, the JOIN strategy, the partition design, and the SETTINGS that must be present on production queries.

# ClickHouse analytics rules

## Stack
- ClickHouse 24.x (Cloud or self-hosted)
- clickhouse-client for ad-hoc; HTTPS interface for app code
- dbt-clickhouse for managed transformations (optional)
- Materialized views for pre-aggregation, never for joins

## Engine selection (use this table, no exceptions)
- MergeTree              , default for fact tables and event streams
- ReplacingMergeTree     , when natural key dedup is required, version column declared
- AggregatingMergeTree   , target of materialized view aggregations
- SummingMergeTree       , numeric rollups by ORDER BY key
- Log family             , NEVER for tables larger than 1M rows
- Memory / Set / Join    , reference tables only, fits in RAM

## ORDER BY key rules (STRICT)
- ALWAYS lead with the column queried most often in WHERE
- ALWAYS include a low-cardinality column before a high-cardinality one
- For event tables: (tenant_id, event_type, event_time) is the default shape
- For daily fact tables: (date, dimension_id) is the default shape
- NEVER lead with a timestamp on a wide table , partition handles time

## Partition key rules
- PARTITION BY toYYYYMM(event_time) is the default
- Daily partitions only when retention is shorter than 60 days
- NEVER partition by high-cardinality columns , one part per value is fatal

## Query rules (STRICT)
- ALWAYS include a WHERE filter on the ORDER BY key prefix
- ALWAYS include a WHERE filter on the partition expression where possible
- PREWHERE before WHERE when filtering wide tables on selective predicates
- LIMIT is mandatory on every SELECT that returns to the client
- ARRAY JOIN before JOIN when exploding nested columns

## JOIN strategy
- Dimensional joins , dictionaries via dictGet, never JOIN to a small table
- Large , large joins , use the hash JOIN with SETTINGS join_algorithm = 'hash'
- NEVER cross JOIN without a LIMIT and a WHERE
- Right-side of JOIN is always the smaller table

## Materialized views
- Target table is an AggregatingMergeTree or SummingMergeTree
- AS SELECT must use the matching State aggregate functions (sumState, uniqState)
- NEVER reference a Materialized View from another Materialized View directly
- ALWAYS test by inserting into the source and SELECT FROM the target

## Required SETTINGS for production queries
- max_execution_time = 60 (or domain-appropriate)
- max_memory_usage = 10 * 1024 * 1024 * 1024
- max_rows_to_read = 10_000_000_000
- read_overflow_mode = 'throw' (never 'break')
- log_queries = 1

Drop this into CLAUDE.md and adapt the values to your tables. The point is not the exact numbers. The point is that Claude now has a non-negotiable structure to generate against.

ORDER BY key design is the entire game

Most ClickHouse performance problems are ORDER BY problems. A table with the wrong ORDER BY key cannot be saved by an index, by hardware, or by query tuning. Claude needs the right ORDER BY in the CREATE TABLE statement on day one.

The rule is: lead with the column that appears in WHERE on the hot query path, in ascending cardinality. If your dominant query is "events for tenant X between time A and time B," the ORDER BY is (tenant_id, event_time), not (event_time, tenant_id). The first form lets ClickHouse skip every granule that does not contain tenant X. The second form forces a time-range scan across all tenants.

Tell Claude this explicitly in CLAUDE.md. The above template does. Then, when Claude generates a CREATE TABLE, it picks the ORDER BY based on the workload comments you leave above the table, not based on a generic best guess pulled from training data.

Partition design that does not blow up parts

Partitioning in ClickHouse is not the same as partitioning in PostgreSQL. A partition is a physical directory of parts that merge in the background. Too many partitions and the merge thread cannot keep up. The result is the famous "Too many parts" error and a slow degradation of write throughput.

The CLAUDE.md template above forbids high-cardinality partition keys for this reason. PARTITION BY toYYYYMM(event_time) produces twelve partitions per year of data. PARTITION BY user_id could produce millions. Claude defaults to the latter without a rule that forbids it, because partitioning by the entity feels natural in OLTP-land. ClickHouse is not OLTP-land.

For tables with high churn and short retention, daily partitions are acceptable. The template above marks that as the exception. Most tables want monthly.

Get Claudify. Skills, hooks, and CLAUDE.md templates that make Claude Code generate ClickHouse-correct SQL by default.

Materialized views without recursion

Materialized views in ClickHouse are insert triggers. When you INSERT into the source table, the SELECT in the materialized view runs against the inserted block, and the result is written to the target table. They are not views in the PostgreSQL sense. They do not query the source on read.

This has two consequences Claude needs to know. First, the target table needs to be a MergeTree variant that handles the aggregation correctly. AggregatingMergeTree with State combinators is the default. Second, materialized views chained on top of materialized views become recursive insert triggers. A single source row can fire dozens of triggers. Performance degrades silently until inserts time out.

Pattern	Use when	Avoid when
Materialized view to AggregatingMergeTree	Pre-aggregating per-tenant counts	You need full event detail
Materialized view to SummingMergeTree	Numeric rollups by key	Aggregates beyond sum or count
Projection on source table	Reordering data for a secondary query shape	Source table has high write rate
Dictionary	Small, slow-changing lookup data	Data is hot-path mutable

The CLAUDE.md rule "NEVER reference a Materialized View from another Materialized View directly" is the line that prevents the recursion problem. Claude breaks this rule by default. Without the explicit constraint, it will chain views to express a pipeline, because chaining feels clean in DAG-land. In ClickHouse it is a write-amplification bomb.

PREWHERE, FINAL, and the SETTINGS that matter

ClickHouse has a few keywords that exist nowhere else and that Claude regularly misuses. PREWHERE filters before the columns referenced in SELECT are read off disk. It is faster than WHERE for selective predicates on wide tables. FINAL forces the merge to happen at query time for ReplacingMergeTree and similar engines. It is correct but slow, and Claude reaches for it when it is unsure about deduplication.

The CLAUDE.md template above codifies the safe defaults. PREWHERE is encouraged on wide tables. FINAL is not mentioned because using it should be a deliberate choice, not a generation default. If your workflow requires it, add a specific rule for the specific table.

The SETTINGS block at the bottom of the template is the safety net. max_execution_time prevents runaway queries. max_memory_usage caps the per-query allocation so one bad GROUP BY does not OOM the node. read_overflow_mode = 'throw' ensures that hitting max_rows_to_read raises an error instead of silently truncating results. The last one matters for analytics correctness. A truncated aggregate that returns a wrong number is worse than a query that fails loudly.

Verifying Claude's queries before they ship

A CLAUDE.md is necessary but not sufficient. The verification loop is the second half. For ClickHouse, the loop is straightforward:

1. Generate the query with Claude
2. Prepend EXPLAIN indexes = 1 to confirm the ORDER BY prefix is used
3. Prepend EXPLAIN PIPELINE to confirm the join algorithm and PREWHERE placement
4. Run with SETTINGS log_queries = 1 and inspect system.query_log for read_rows and memory_usage
5. Compare read_rows to total rows. If the ratio is above 0.1 the query is doing a near-scan

A hook that runs EXPLAIN indexes = 1 automatically on every generated query and surfaces the result back to Claude is a meaningful upgrade. Claude can then iterate against the actual plan rather than against its own prediction of the plan. This is the difference between "looks correct" and "is correct."

For broader patterns on hooks and skills, Claude Code skills covers how to package the verification loop, and Claude Code best practices covers the CLAUDE.md structure in detail.

Insert paths and ingestion patterns

ClickHouse is at its happiest with large, infrequent inserts. The merge engine batches small parts into larger ones in the background, and high-frequency single-row inserts produce too many parts faster than the merge can clear them. The standard recommendation is one insert per second per table per replica, with a batch size of 1,000 to 100,000 rows.

Claude needs this in the CLAUDE.md too, because the default Python or Node client patterns for "write a row" do not match ClickHouse's preferred shape. The correct pattern is buffer-and-flush, either in app code or via the Buffer engine, or via async_insert with a backing queue. The wrong pattern is one HTTP request per event. The template above implies this by forbidding the Log family for tables over a million rows, but a dedicated section on ingestion is worth adding to your project-specific CLAUDE.md.

For the dbt-driven case, the dbt-clickhouse adapter handles the batching for you, and the rules above still apply to the model SQL. For the streaming case, Kafka engine tables write into a Null engine table that fans out into materialized views for the actual storage layout. That pattern is worth a separate section if it applies to your workload.

When ClickHouse is the wrong answer

A CLAUDE.md is also a place to record the negative space. ClickHouse is not the right backend for transactional workloads, for workloads with high update frequency on individual rows, or for workloads that need strong consistency on read-after-write. If your project has those requirements alongside the analytical ones, the right shape is usually a transactional store like Postgres with a CDC pipeline into ClickHouse for the OLAP side.

Tell Claude this in CLAUDE.md. A simple "This is the analytics layer. Transactional writes go to Postgres via the CDC pipeline. Do not propose Postgres-style patterns here." is enough to stop Claude from suggesting UPDATE statements on a MergeTree table or row-level deletes that will not behave the way you expect.

Wrapping up

ClickHouse rewards precision. ORDER BY keys, partition design, materialized view targets, and ingest batching all need to be correct on day one because changing them later means rewriting tables. Claude Code without a CLAUDE.md will get most of these wrong by default, because the OLTP patterns dominate its training data.

With the template above, Claude Code becomes a reliable ClickHouse partner. It picks the right engine, designs ORDER BY keys against the actual query shape, refuses to chain materialized views, and includes the SETTINGS that prevent the worst classes of failure. The CLAUDE.md does not make Claude smart about your specific workload. It makes Claude default to the patterns that work, and surfaces the questions that need a human answer.

Get Claudify. Skills, hooks, and CLAUDE.md templates that make Claude Code generate ClickHouse-correct SQL by default.