Clickhouse reference architecture

ClickHouse Reference Architecture

Overview

Production-grade architecture for ClickHouse analytics platforms covering project layout, data flow, multi-tenancy, and operational patterns. Work through the five steps below to get the high-level shape, then drill into the linked reference files for the full DDL, client code, and tenancy trade-offs.

Prerequisites

• Understanding of ClickHouse fundamentals — table engines, ORDER BY sort keys, and partitioning.
• A TypeScript/Node.js project (the client examples use @clickhouse/client).
• When reviewing an existing codebase, Grep for createClient( to locate the current client module and Read the SQL files under clickhouse/schemas/.

Instructions

Step 1: Project Structure

Keep SQL DDL as the source of truth under clickhouse/schemas/, named query functions under clickhouse/queries/, and ingestion/API/jobs in sibling modules.

my-analytics-platform/
├── src/
│   ├── clickhouse/
│   │   ├── client.ts           # Singleton client with health checks
│   │   ├── schemas/            # SQL DDL files (source of truth)
│   │   │   ├── 001-events.sql
│   │   │   ├── 002-users.sql
│   │   │   └── 003-materialized-views.sql
│   │   ├── queries/            # Named query functions
│   │   └── migrations/         # Schema migrations (runner.ts + *.sql)
│   ├── ingestion/              # webhook-receiver, kafka-consumer, buffer
│   ├── api/                    # routes.ts, middleware.ts (auth, rate limit)
│   └── jobs/                   # daily-rollup.ts, cleanup.ts (TTL enforcement)
├── tests/                      # unit/ + integration/
├── docker-compose.yml          # Local ClickHouse
├── init-db/                    # Docker init scripts
└── config/                     # development / staging / production .env

Step 2: Data Flow Architecture

Data moves in one direction: sources → a batching ingestion layer → ClickHouse (raw MergeTree → materialized views → aggregate tables) → an API that reads only the aggregate tables → dashboards.

Data Sources (Webhooks, API, Kafka, S3)
        │
Ingestion Layer (Buffer + batch, 10K+ rows/insert)
        │
ClickHouse Server
   Raw Event Tables (MergeTree, append-only)
        │  auto-aggregate on INSERT
   Materialized Views (hourly, daily, tenant-level)
        │
   Aggregate Tables (AggregatingMergeTree)
        │
API Layer (queries aggregate tables, never raw events)
        │
Dashboards / Client Apps

Step 3: Schema Design (3-Layer Pattern)

Three layers — raw append-only events, hourly aggregation, and a daily rollup for dashboards — with materialized views auto-populating each aggregate on INSERT. The essential raw-table skeleton:

CREATE TABLE analytics.events_raw (
    event_id    UUID DEFAULT generateUUIDv4(),
    tenant_id   UInt32,
    event_type  LowCardinality(String),
    user_id     UInt64,
    properties  String CODEC(ZSTD(3)),
    created_at  DateTime64(3) DEFAULT now64(3)
)
ENGINE = MergeTree()
ORDER BY (tenant_id, event_type, toDate(created_at), user_id)
PARTITION BY toYYYYMM(created_at)
TTL created_at + INTERVAL 90 DAY;

Full three-layer DDL, materialized views, and the rationale: see references/schema-design.md.

Step 4: Multi-Tenant Patterns

Choose an isolation strategy. Default to Approach A (shared table, tenant_id first in ORDER BY) — it scales to 10K+ tenants:

ORDER BY (tenant_id, event_type, created_at)
SELECT count() FROM events_raw WHERE tenant_id = 42;  -- scans only tenant 42

Database-per-tenant (strict isolation) and row-level security (RBAC) alternatives with trade-offs: see references/multi-tenant-patterns.md.

Step 5: Client Module

Use a singleton @clickhouse/client instance and parameterized queries that read from the aggregate tables. Full client + query-function code: references/client-module.md.

Architecture Decision Records

Decision	Choice	Why
Engine	MergeTree (raw) + AggregatingMergeTree (rollups)	Best for append + pre-agg
Multi-tenant	Shared table + tenant_id in ORDER BY	Scales to 10K+ tenants
Ingestion	Buffer + batch INSERT	Avoids "too many parts"
Aggregation	Materialized views (not cron)	Real-time, zero-lag
Format	JSONEachRow	Client support, debugging
Compression	ZSTD(3) for strings, Delta for ints	10-20x compression

Output

Applying this skill produces a concrete architecture plan for a ClickHouse system:

• A project directory layout (Step 1) with DDL as the source of truth.
• A 3-layer schema — raw MergeTree table, hourly and daily AggregatingMergeTree tables, each fed by a materialized view.
• A chosen multi-tenant isolation strategy (shared table / database-per-tenant / row policy) with the reasoning recorded.
• A singleton client module plus named, parameterized query functions that read only from aggregate tables.
• A filled-in Architecture Decision Record table capturing engine, tenancy, ingestion, aggregation, format, and compression choices.

Error Handling

Issue	Cause	Solution
Cross-tenant data leak	Missing WHERE tenant_id	Use row policies or middleware
Stale dashboard data	MV not created	Verify MV exists and is attached
Schema drift	Manual DDL changes	Use migration runner
Slow dashboard queries	Querying raw table	Query aggregate tables instead

Examples

Design a new multi-tenant analytics platform. Start from the Step 1 layout and the Step 3 raw-table skeleton, then open references/schema-design.md for the full three-layer DDL and references/multi-tenant-patterns.md to pick an isolation strategy.

Query a tenant dashboard from Node.js. Read from the daily rollup, not the raw table — the pattern the client module in references/client-module.md implements:

SELECT date, sum(total) AS events, uniqMerge(users) AS unique_users
FROM analytics.events_daily
WHERE tenant_id = {tid:UInt32} AND date >= today() - {days:UInt32}
GROUP BY date ORDER BY date;

Review an existing ClickHouse integration. Grep for createClient( and any raw-table SELECTs in the API layer; flag queries hitting events_raw instead of an aggregate table against the Error Handling table above.

Resources

• ClickHouse Architecture
• SharedMergeTree (Cloud)
• Materialized Views
• references/schema-design.md — full 3-layer DDL
• references/multi-tenant-patterns.md — tenancy trade-offs
• references/client-module.md — client + query code

Next Steps

For multi-environment configuration, layer on the clickhouse-multi-env-setup skill, which covers per-environment .env files, staging/production connection settings, and migration promotion between environments.