Forem: RAKESH THERANI

Four LLM Engines, One ClickHouse Cluster: An Agentic AI Architecture

RAKESH THERANI — Thu, 14 May 2026 06:19:43 +0000

We are building an agentic AI analytics platform for a crypto exchange where internal teams — Trading Ops, Risk, Compliance, Finance, Treasury, Product, Engineering — ask questions in plain English and get audited, citation-enforced answers.

It's built on five open-source components:

ClickHouse — data + vector + observability storage
Qwen 2.5 72B — self-hosted LLM via vLLM
Anthropic MCP — zero-trust tool layer
LibreChat — chat UI (acquired by ClickHouse, November 2025)
Langfuse — LLM observability (acquired by ClickHouse, January 2026)

The defining property is four execution engines on shared infrastructure, each tuned for a different question shape:

Engine	Typical signals (semantic, not keyword)	What it does	Latency
Analytics	Data retrieval, aggregations, single-domain "what is" patterns	NL → SQL on pre-joined marts	<2s
Numeric Research	Match to one of 20 catalogued fitted-model patterns (regimes, probabilities, elasticities, etc.)	Templated lookup against cached model output	<1s
Optimization	Decision-making with constraints + objective + trade-off ("what should we do")	NL → structured math model → solver → reviewable plan	30s–5min
Deep Research	Multi-source explanation requiring decomposition + reasoning ("why did this happen")	Planner → workers → critic → synthesizer loop	60–90s

Plus four continuous self-learning loops, a four-layer trust defense, and a 24-week build timeline. The rest of this post walks through each piece with concrete examples.

Why This Exists

The problem in current operations

Analyst bottleneck. Risk, Compliance, Finance teams wait days for ad-hoc SQL. Analytics is a ticket queue, not a self-service capability.
Investigations are slow. "Why did revenue drop 12%?" takes 2 days of analyst time and ships a 30-slide deck — often wrong on a key dimension that emerges weeks later.
Optimization is spreadsheet-based. Insurance fund allocation, fee tier choices, wallet rebalancing — done in Excel because actual OR tools require OR specialists.
Compliance is reactive. SAR drafts written from scratch each time. Wash trading detection runs only when someone happens to look. Regulator inquiries take a week.
No audit trail for ad-hoc analysis. When numbers ship to the CFO or regulator, you can't reproduce them six months later.

The fix

Self-service NL for non-engineers. Data team stops being a ticket queue.
Deep Research engine that produces cited investigations in 60–90 seconds vs analyst-hours.
Optimization layer that takes business problems in English, formulates math models, runs solvers, returns reviewable plans for human approval.
Citation enforcement so every claim traces back to specific SQL queries with parameter bindings — auditable, reproducible.
Self-learning loops so the platform gets better weekly without engineering intervention.

End-to-End Flow

┌─────────────────────────────────────────────────────────────────┐
│  USER (1 of 7 teams)                                            │
│  Types: "Why did Q2 fee revenue drop 12% vs Q1?"                │
└──────────────────────┬──────────────────────────────────────────┘
                       ▼
┌─────────────────────────────────────────────────────────────────┐
│  LIBRECHAT                                                      │
│  • SSO authentication; team role attached to session            │
│  • Session memory in MongoDB                                    │
│  • 128K context window for conversation history                 │
│  • Streams response back as it's generated                      │
└──────────────────────┬──────────────────────────────────────────┘
                       ▼
┌─────────────────────────────────────────────────────────────────┐
│  TRIAGE AGENT (Qwen 72B)                                        │
│  Semantic classification + structured output:                   │
│    {engine, confidence, case_id, extracted_params, reasoning}   │
│  Not keyword matching — full LLM intent inference               │
└──────────────────────┬──────────────────────────────────────────┘
                       ▼
┌──────────────────────┴──────────────────────────────────────────┐
│            ROUTED TO ONE OF FOUR EXECUTION ENGINES              │
└─────────────────────────────────────────────────────────────────┘
       │              │              │             │
       ▼              ▼              ▼             ▼
   ANALYTICS    NR LOOKUP    OPTIMIZATION   DEEP RESEARCH
       │            │              │             │
       ▼            ▼              ▼             ▼
  LLM writes   Templated     LLM emits      Planner →
  SQL grounded SELECT        structured     Workers →
  by schema +  from cache    JSON model     Critic →
  glossary     marts.numeric → template    Synthesizer
               _research_    generates      (5 rounds max)
               runs          CVXPY/CP-SAT
                             → solver
       │            │              │             │
       └────────────┴──────────────┴─────────────┘
                       │
                       ▼
       ┌─────────────────────────────────────────────┐
       │  MCP LAYER                                  │
       │  • ClickHouse MCP — read-only SELECT only   │
       │  • Solver MCP — solve_lp/mip/cp/convex      │
       │  • Numeric Research MCP — run_nr            │
       │  • Vector search MCP — similarity lookups   │
       │  • All tools allowlisted per agent          │
       │  • Bearer-token auth                        │
       └────────────────┬────────────────────────────┘
                        ▼
       ┌─────────────────────────────────────────────┐
       │  CLICKHOUSE                                 │
       │  • Raw → Staging → Marts (medallion)        │
       │  • HNSW vector indexes (25.8+ GA)           │
       │  • Refreshable MVs auto-update marts        │
       │  • Persistence tables:                      │
       │    – marts.research_runs (Deep Research)    │
       │    – marts.research_evidence (citations)    │
       │    – marts.optimization_runs (plans)        │
       │    – marts.numeric_research_runs (NR cache) │
       │    – marts.langfuse_flat (agent traces)     │
       └────────────────┬────────────────────────────┘
                        ▼
       ┌─────────────────────────────────────────────┐
       │  SYNTHESIZER (LLM)                          │
       │  • Renders prose answer from evidence       │
       │  • Citation regex enforcement:              │
       │    every claim must have [ev-N]             │
       │    → 3 retries; fallback to LOW confidence  │
       │  • Confidence label (HIGH/MEDIUM/LOW)       │
       │  • Cross-engine handoffs (DR → Opt for fix) │
       └────────────────┬────────────────────────────┘
                        ▼
       ┌─────────────────────────────────────────────┐
       │  LIBRECHAT renders answer to user           │
       │  Every step traced in Langfuse              │
       │  Trace ID returned for audit replay         │
       └─────────────────────────────────────────────┘
                        ▼
       ┌─────────────────────────────────────────────┐
       │  SELF-LEARNING LOOPS fire continuously      │
       │  • Glossary expansion (real-time + nightly) │
       │  • Mart recommendations (nightly)           │
       │  • LoRA fine-tuning (weekly)                │
       │  • Eval-set regression (weekly)             │
       └─────────────────────────────────────────────┘

Triage — How the Agent Chooses an Engine

This is the most important decision in the platform. Get it wrong and everything downstream is wrong.

It's semantic classification, not keyword matching

The triage agent is Qwen 72B doing intent inference — the LLM reads the full question and reasons about what the user is actually trying to do. The verb cues are hints in the system prompt, not the routing rule. A user asking "show me which markets to consolidate" routes to Optimization (not Analytics, despite the "show" verb) because the LLM infers that "which markets to consolidate" is a selection-with-objective decision. The classifier emits a structured JSON output with engine choice, confidence score, extracted parameters, and a one-sentence reasoning string.

What the triage prompt actually contains

You classify user questions into one of four engines.

ENGINES:

1. ANALYTICS — answers "what is happening" questions with a single SQL
   query against pre-joined marts. Best for: aggregations, counts,
   listings, filters over single domains.
   Sample questions:
     - "Top 10 markets by 24h volume"
     - "All withdrawals over $50K last week"
     - "Average fill time on BTC-PERP today"

2. NUMERIC_RESEARCH — looks up cached statistical models. Use when the
   question matches one of 20 catalogued NR cases (regimes, elasticity,
   probabilities, transition matrices, etc.). Lookup is sub-second;
   models are fitted weekly offline.
   Sample questions matched to NR cases:
     - "What market regime is BTC in?"          → NR-01
     - "Probability IF covers a 15% shock?"     → NR-04
     - "Forecast VIP tier distribution"         → NR-08
     - "Maker fee elasticity for tier 2"        → NR-07
     - "Almgren-Chriss impact on BTC-PERP"      → NR-19
     [...20 cases total...]

3. OPTIMIZATION — formulates a math program and runs a solver.
   Best for: allocation, scheduling, sequencing, pricing optimization,
   trade-off problems where the user wants the *best* answer.
   Sample questions:
     - "Allocate $50M insurance fund across markets"
     - "Sequence today's liquidations to minimize impact"
     - "Choose maker fees to maximize revenue with 5% retention cap"

4. DEEP_RESEARCH — multi-step planner → workers → critic → synthesizer
   loop. Best for: "why" investigations, root-cause analyses,
   multi-source evidence gathering. Takes 60-90 seconds.
   Sample questions:
     - "Why did Q2 fee revenue drop 12%?"
     - "Investigate user X for wash trading"
     - "Liquidation cascade post-mortem"

RETURN STRUCTURED JSON:
{
  "engine": "ANALYTICS | NUMERIC_RESEARCH | OPTIMIZATION | DEEP_RESEARCH",
  "confidence": 0.0-1.0,
  "case_id": "<NR-XX if Numeric Research, else null>",
  "extracted_params": { ... },
  "fallback_engine": "<second-best engine>",
  "reasoning": "one-sentence justification"
}

The LLM does semantic matching against the engine descriptions and sample questions, then emits a confidence score. Keywords are mnemonics — not the routing logic.

Real triage output for ambiguous cases

User: "Show me which 5 markets to consolidate this quarter"

{
  "engine": "OPTIMIZATION",
  "confidence": 0.84,
  "case_id": null,
  "extracted_params": { "n_markets": 5, "time_window": "quarter" },
  "fallback_engine": "ANALYTICS",
  "reasoning": "Despite 'show me' verb, 'which markets to consolidate' is a selection-with-objective decision — handled by optimization solver, not a SQL query."
}

User: "Why is marts.mart_user_trading_activity slow today?"

{
  "engine": "ANALYTICS",
  "confidence": 0.91,
  "case_id": null,
  "extracted_params": { "mart": "mart_user_trading_activity" },
  "fallback_engine": "DEEP_RESEARCH",
  "reasoning": "Despite 'why' verb, this is a single-target query latency question answerable from system.query_log — engineering analytics, not investigation."
}

When confidence is low

If confidence < 0.7, triage asks a one-sentence clarifier:

"I can interpret this two ways. Would you like a quick aggregation against current data (Analytics) or a deeper investigation tracing root causes (Deep Research)?"

User picks → routed. The question + chosen engine are logged → a self-learning loop may add a sample question to the triage prompt for better future disambiguation.

The Four Engines (In Detail)

ENGINE 1 — ANALYTICS (~80% of all questions)

The 80%-case engine. Most user questions are "show me X" or "count Y across Z" — those get answered here.

How the agent decides:

Triage classifies as Analytics based on semantic intent (not just verbs)
Schema injection — system prompt includes live list_tables() output + the top-5 marts retrieved by semantic similarity to the user question
Glossary injection — relevant glossary terms loaded (liquidation = order_type IN (1003, 1004), whale = users WHERE 30d_volume > $10M, etc.)
LLM writes SQL grounded by the schema + glossary
Pre-execution validator (code, not LLM) checks SQL is SELECT-only, references existing tables, has LIMIT
MCP executes against llm_role (read-only, mart-only access)
Synthesizer renders as formatted table or summary

Why this is reliable: Pre-joined marts mean the LLM doesn't write JOINs — it picks a mart and filters. The #1 NL→SQL failure mode (bad JOINs) is eliminated by design.

Worked example 1.1 — Trading volume + liquidation roll-up

User (Risk Manager): "Top 10 markets by 24-hour volume with liquidation count and average liquidation size."

LLM generates:

SELECT v.market_name,
       v.volume_usd_24h,
       l.liq_count_24h,
       round(l.total_liq_volume_usd / nullIf(l.liq_count_24h, 0), 0) AS avg_liq_size_usd,
       round(100.0 * l.liq_count_24h / v.trade_count_24h, 2)         AS liq_rate_pct
FROM marts.mart_volume_daily v
LEFT JOIN marts.mart_liquidations_summary l ON v.market_name = l.market_name
WHERE v.trade_date = today()
ORDER BY v.volume_usd_24h DESC
LIMIT 10;

Output: 10-row table sorted by volume desc. Latency: ~500ms. Cost: ~$0.005.

Note the agent retrieved liquidation = order_type IN (1003, 1004) from the glossary deterministically — never guessed.

Worked example 1.2 — Compliance cross-table screening

User (Compliance Officer): "All withdrawals over $50K in the last 7 days where the user has a KYC level below 2."

LLM generates:

SELECT b.username,
       b.coin_type,
       b.amount,
       b.amount_usdt,
       b.address,
       u.kyc_level,
       u.fregistertime,
       b.create_date_time
FROM staging.balance_audit b
JOIN staging.f_user u USING (username)
WHERE b.in_out_type = 2          -- withdrawal direction (from glossary)
  AND b.amount_usdt > 50000
  AND b.create_date_time >= today() - 7
  AND u.kyc_level < 2
ORDER BY b.amount_usdt DESC;

Output: flagged-user list with KYC level. Latency: ~1s.

Worked example 1.3 — Trading bot performance breakdown

User (Product Manager): "How many trading bots are running on BTC-PERP by bot type, and what's the median ROI for each?"

LLM generates:

SELECT bot_type,
       count() AS running_bot_count,
       quantile(0.5)(roi) AS median_roi,
       round(quantile(0.5)(roi) * 100, 2) AS median_roi_pct,
       round(sum(total_investment_usdt), 0) AS total_capital_deployed
FROM marts.mart_trading_bot_performance
WHERE state = 'RUNNING' AND symbol = 'BTC-PERP'
GROUP BY bot_type
ORDER BY running_bot_count DESC;

Output: grouped table with quantile aggregation. Latency: ~800ms.

What happens when Analytics misses: If LLM-generated SQL references a non-existent column, the pre-execution validator rejects it. The agent retries with corrected schema. Repeated failures log to Langfuse → a learning loop may draft a glossary entry to handle the term differently next time.

ENGINE 2 — NUMERIC RESEARCH LOOKUP (cached fitted models)

This engine serves cached output from 20 statistical models that run offline (weekly or monthly). The lookup itself is a deterministic templated SELECT — sub-second. The actual model fitting is heavy Python (scikit-learn, statsmodels, scipy, lifelines, PyMC) done in batch jobs.

How the agent decides:

Triage classifies as Numeric Research → matches one of 20 NR cases by semantic similarity to the user question (not by keyword)
Templated lookup runs against marts.numeric_research_runs — the SQL skeleton is fixed; only case_id and filter params are extracted by LLM
Branch on freshness:
- Fresh (within case-specific window — varies from 24h for IF coverage to 30 days for VIP transitions) → parse predictions_json, return cached
- Stale → call run_nr(case_id=...) MCP tool to trigger synchronous refit (~5–30s depending on case)
- Missing (new market, new case) → honest "no model for this market yet" + log for catalogue recommendation
Synthesizer renders with confidence label + last-fit timestamp

Worked example 2.1 — Volatility regime (NR-01)

User (Trader): "What market regime is BTC-PERP in right now?"

Agent decision flow:

Triage matches NR-01 (Volatility Regime Classification) by semantic similarity to NR-01's catalogued sample question
Extracted params: {symbol: "BTC-PERP"}
Templated SELECT:

SELECT predictions_json, created_at, next_run_at
FROM marts.numeric_research_runs
WHERE nr_case_id = 'NR-01'
  AND JSONExtractString(inputs_json, 'symbol') = 'BTC-PERP'
ORDER BY created_at DESC LIMIT 1;

Returned predictions_json:

{
  "current_regime": 2,
  "regime_label": "high-vol bullish skew",
  "days_in_current_regime": 5,
  "avg_persistence_days": 8.2,
  "transition_probs": {
    "to_regime_0_low_vol":          0.08,
    "to_regime_1_neutral":          0.42,
    "to_regime_2_high_vol_bullish": 0.28,
    "to_regime_3_high_vol_bearish": 0.22
  },
  "silhouette_score": 0.61
}

Synthesizer renders: > "BTC-PERP is in regime 2 (high-vol bullish skew). Regimes persist ~8 days on average; this is day 5. Next-week transition probabilities: stay 28%, neutral 42%, bearish 22%, low-vol 8%. Last fit: 14h ago."

Latency: <1s. Cost: ~$0.001.

Worked example 2.2 — Insurance fund coverage (NR-04)

User (CRO): "What's our insurance fund coverage probability at a 15% shock?"

Agent decision flow:

Triage matches NR-04 (Insurance Fund Coverage Probability — Monte Carlo)
LLM extracts shock_pct = 0.15
Templated lookup returns Monte Carlo output (excerpt):

{
  "method": "monte_carlo_100k_trials",
  "current_if_usdt": 50000000,
  "results_by_shock": {
    "0.05": { "p_breach": 0.0024, "wilson_ci_95": [0.0021, 0.0027] },
    "0.10": { "p_breach": 0.0158, "wilson_ci_95": [0.0150, 0.0166] },
    "0.15": { "p_breach": 0.0792, "wilson_ci_95": [0.0775, 0.0809] },
    "0.20": { "p_breach": 0.1834, "wilson_ci_95": [0.1810, 0.1858] }
  },
  "top_tail_risk_contributors": [
    {"market": "BTC-PERP", "share_pct": 38},
    {"market": "ETH-PERP", "share_pct": 24},
    {"market": "SOL-PERP", "share_pct": 21}
  ]
}

Synthesizer renders: > "Insurance fund coverage probability at 15% shock: 92.1% (CI: 91.9–92.3%). At 20% shock: 81.7%. Top tail-risk contributors: BTC-PERP (38%), ETH-PERP (24%), SOL-PERP (21%). Last refit: 4h ago."

Latency: <1s. Cost: ~$0.001.

Worked example 2.3 — VIP tier transitions (NR-08)

User (CFO): "Forecast next month's VIP tier distribution. Are we losing VIP-3 users?"

Agent decision flow:

Triage matches NR-08 (VIP Tier Transition Probabilities — Markov chain)
Templated lookup returns full 7×7 transition matrix
Orchestrator (code, not LLM) does matrix multiplication: current distribution × transition matrix = next-month forecast
Synthesizer renders: > "Next-month forecast: VIP-3 will drop from 480 → 403 (-16%). Demotion rate (T3→T2) has spiked from 5.2% historical to 9.0% this month. Recommend investigating VIP-3 satisfaction. Last refit: 3 weeks ago."

Latency: <1s. Cost: ~$0.002.

Worked example 2.4 — Almgren-Chriss impact (NR-19) — machine-to-machine

Optimization engine (not a direct user): the liquidation sequencing solver needs per-market impact coefficients before solving.

Agent decision flow (machine-to-machine):

Solver agent issues templated lookup; returned per-market (α, β) for impact_bps = α × (size / depth)^β:

{
  "BTC-PERP": {"alpha": 1.24, "beta": 0.51, "r_squared": 0.78, "n_obs": 18420},
  "ETH-PERP": {"alpha": 1.55, "beta": 0.49, "r_squared": 0.74, "n_obs": 12100},
  "SOL-PERP": {"alpha": 2.18, "beta": 0.62, "r_squared": 0.66, "n_obs":  5240}
}

Solver uses these as priors in the CP-SAT scheduling model

Latency: <1s for the lookup. If stale, optimizer waits 5–15s for refit.

This is the NR feeding Optimization pattern. NR results aren't always user-facing; they often feed downstream engines.

How the 20 NR cases tile the domain

Category	NR cases	Used by
Market structure	NR-01 (regimes), NR-15 (cap-hits), NR-16 (correlation)	Risk, Trading
Liquidity / impact	NR-03 (depth function), NR-19 (Almgren-Chriss)	Trading, Optimization
Funding mechanics	NR-02 (elasticity)	Risk, Finance, Optimization
User behavior	NR-06 (LTV survival), NR-08 (VIP transitions), NR-12 (retention curves)	Product, Finance, Growth
Compliance / AML	NR-05 (wash trade prevalence), NR-10 (withdrawal z-score), NR-11 (alert P/R)	Compliance
Risk modeling	NR-04 (IF coverage MC), NR-09 (HHI concentration)	Risk
Revenue / pricing	NR-07 (rebate elasticity), NR-14 (Gini concentration), NR-17 (affiliate payback)	Finance, Growth, Optimization
Options	NR-18 (Greeks aggregation)	Risk, Trading
Internal product	NR-20 (PT settlement KS-test)	Product, Risk

Adding a new case is a configuration exercise: catalogue entry + fit script + cadence + sample question. The platform handles the rest.

ENGINE 3 — OPTIMIZATION (NL → math model → solver → reviewable plan)

The decision-making engine. Where Analytics answers "what is X?", Optimization answers "what should X be?" — formulating a math program from a business question, running it through a solver, returning a reviewable plan with assumptions, sensitivity, and alternatives.

How the agent decides:

Triage classifies as Optimization based on intent — decision-making with constraints + objective, regardless of verb cues
LLM emits structured JSON describing the math program (decision variables with bounds, objective with sense, constraints, parameters). The LLM does not write Python — it emits structured data.
Deterministic Python template converts JSON → CVXPY / OR-Tools / Pyomo / HiGHS code
Sandbox executes the model (docker + nsjail + cgroups; no network egress; 60s wall-clock; 2GB cap)
Solver runs — backend chosen by model_type field:
- LP → HiGHS
- MIP → CBC, SCIP, optionally Gurobi or CPLEX
- CP → OR-Tools CP-SAT
- Convex (SOCP / SDP / NLP) → CVXPY with CLARABEL (default), ECOS / SCS fallback
Plan persisted to marts.optimization_runs with full audit trail
Synthesizer renders plan with binding constraints, sensitivity, alternative formulations

Critical: the platform produces a plan, not an action. Human approves before any operational effect. By design — never autonomous.

Worked example 3.1 — Insurance fund allocation (Convex, CVXPY)

User (CRO): "Allocate the $50M insurance fund across BTC/ETH/SOL to minimize worst-case 30-day loss with 5% per-market floor."

Structured JSON emitted by LLM:

{
  "model_type": "convex",
  "decision_variables": [
    {"name": "x_btc", "type": "continuous", "lb": 0},
    {"name": "x_eth", "type": "continuous", "lb": 0},
    {"name": "x_sol", "type": "continuous", "lb": 0}
  ],
  "parameters": {
    "cvar": [0.18, 0.31, 0.42],
    "budget":   50000000,
    "min_pct":  0.05,
    "max_pct":  0.40
  },
  "objective": {
    "sense": "minimize",
    "expression": "cvar_btc*x_btc + cvar_eth*x_eth + cvar_sol*x_sol"
  },
  "constraints": [
    "x_btc + x_eth + x_sol == budget",
    "x_i >= min_pct * budget",
    "x_i <= max_pct * budget"
  ]
}

Template generates CVXPY code; CLARABEL solver runs in sandbox. Plan output:

Plan opt-20260512-001 — Insurance Fund Allocation
Status: optimal · Solver: CVXPY/CLARABEL · Solve time: 47ms

Decision:
  BTC market: $20.0M (40% — at ceiling)
  ETH market: $20.0M (40% — at ceiling)
  SOL market: $10.0M (20%)

Weighted CVaR contribution: $14.0M
  (= 0.18×$20M + 0.31×$20M + 0.42×$10M)

Binding constraints: budget, BTC ceiling, ETH ceiling

Sensitivity:
  Cap relaxed to 50%  → loss drops to $12.5M (-10.5%)
  SOL drawdown ±10%   → allocation unchanged (cap binds)

Alternatives considered:
  Equal-weight ($16.7M each): $15.2M (+8.4%)
  Min-VaR (not CVaR):         $14.9M (+6.1%)

Assumptions:
  - 90-day drawdown distributions (sourced from NR-04)
  - Per-market floor 5% / ceiling 40% (from glossary)

Want to: relax the ceiling? change the lookback? add markets?

Latency: ~3s. Cost: ~$0.05. Decision human-approved before any IF movement.

Worked example 3.2 — Compliance case routing (CP-SAT, OR-Tools)

User (Head of Compliance): "Assign today's 25 open cases (12 SAR drafts, 8 KYC reviews, 5 transaction audits) to the 8 investigators on shift. Minimize SLA breach risk. SAR drafts require senior investigators."

Math type: Mixed-integer assignment problem. Decision variable x[i,j] ∈ {0,1} = "case i assigned to investigator j". Objective: minimize weighted SLA breach risk. Constraints: each case to exactly one investigator; per-investigator capacity in hours; senior-investigator filter for SAR drafts.

Plan output:

Plan opt-20260512-002 — Compliance Case Routing
Status: optimal · Solver: OR-Tools CP-SAT · Solve time: 1.8s

Assignments (25 cases → 8 investigators):
  alice  : 3 cases  (1 SAR, 2 KYC)            load 7.0 / 8.0 hrs
  bob    : 3 cases  (1 SAR, 1 audit, 1 KYC)   load 7.0 / 8.0 hrs
  carol  : 3 cases  (1 SAR, 2 KYC)            load 7.0 / 8.0 hrs
  dan    : 3 cases  (1 SAR, 1 audit, 1 KYC)   load 7.0 / 8.0 hrs
  eve    : 3 cases  (2 KYC, 1 audit)          load 5.0 / 8.0 hrs   [senior, slack]
  frank  : 3 cases  (1 SAR, 2 KYC)            load 7.0 / 8.0 hrs
  grace  : 3 cases  (1 SAR, 2 KYC)            load 7.0 / 8.0 hrs
  henry  : 4 cases  (1 SAR, 2 audit, 1 KYC)   load 8.0 / 8.0 hrs

Total expected SLA breach risk score: 0.42  (vs 1.18 for FIFO)

Binding constraints:
  - Senior investigators (4) — fully loaded with SAR drafts
  - henry hours_available — at capacity

Risks flagged:
  - 1 SAR is unassignable today (no senior capacity)
    → Recommend: escalate to overflow team OR delay 1 lower-priority audit

Want to: see what changes with +1 senior? change SLA hours?

Latency: ~2s. Cost: ~$0.04.

Worked example 3.3 — Maker fee tier optimization (MIP with elasticity)

User (CFO): "Choose maker fee values for tiers 1–5 to maximize monthly fee revenue, subject to ≤5% volume retention loss. Use last 90 days as baseline."

Math type: MIP with discrete fee choices (0.5bp increments) and piecewise-linear elasticity from NR-07. Five integer decision variables. Constraint: total volume ≥ 95% of baseline.

Plan output:

Plan opt-20260512-003 — Maker Fee Tier Optimization
Status: optimal · Solver: HiGHS MIP · Solve time: 230ms

Recommended fees:
  Tier 1 (≤$10M/mo):     +2.0 bps  (current +2.5)
  Tier 2 ($10–50M):      +1.0 bps  (current +1.5)
  Tier 3 ($50–250M):      0.0 bps  (current +0.5)
  Tier 4 ($250M–1B):     -1.5 bps  (current -1.0)
  Tier 5 (>$1B):         -3.5 bps  (current -3.0)

Projected 30-day revenue:  $9.4M   (current $8.5M, +10.6%)
Projected 30-day volume:  $40.4B   (current $42.3B, -4.5%)

Volume loss 4.5% — within 5% glossary cap.

Binding constraints: max_volume_drop_pct = 0.05

Sensitivity:
  Elasticity 1.5× current → revenue gain drops to +6.2%
  Elasticity 0.5× current → revenue gain rises to +14.8%
  → Plan moderately sensitive to elasticity — recommend A/B test before rollout

Assumptions:
  - Linear elasticity 1.2% volume / bp (from glossary NR-07)
  - 90-day baseline volumes
  - Tier boundaries unchanged

Want to: vary elasticity? cap at 3%? add a 6th tier?

Latency: ~1s. Cost: ~$0.04.

What makes Optimization safe:

LLM never writes Python — emits structured JSON; fixed template generates solver code. Successful prompt injection can't escape template structure.
Sandbox containment — code runs with no network, 60s timeout, 2GB cap, ephemeral container.
Reviewable plan not action — every plan ships with assumptions, binding constraints, sensitivity, alternatives. Human approves before any operational effect.

ENGINE 4 — DEEP RESEARCH (multi-step planner / workers / critic / synthesizer)

The highest-stakes engine. Used when a question requires reasoning across multiple data sources, where there's no fixed catalogue match, and where the answer needs a structured investigation report with citations.

How the agent decides:

Triage classifies as Deep Research based on intent (multi-source explanation requiring decomposition + reasoning) — not just on "why" keyword
Planner (LLM) decomposes the question into 7–15 sub-questions, each tagged with a tool hint (SQL / vector search / past plans / web fetch / solver)
Worker pool (LLM-driven, code-coordinated) executes sub-questions in parallel via asyncio.Semaphore(6). Each worker:
- Generates SQL or vector query
- Executes via MCP
- Returns evidence record tagged [ev-N]
Critic (LLM) reads evidence ledger. Decides "sufficient" or "investigate further" (spawn 1–8 more sub-questions). Bounded to 5 rounds max.
Synthesizer (LLM) produces structured report. Every claim must carry [ev-N]. Regex check + 3-retry fallback enforces this.
Persistence — report → marts.research_runs; per-evidence record → marts.research_evidence. Both indexed by embedding for future similar-question retrieval (auto-memory).

Worked example 4.1 — Q2 fee revenue variance attribution

User (CFO): "Why did Q2 fee revenue drop 12% vs Q1?"

Planner output (7 initial sub-questions):

sq	Tool	Question
sq-1	SQL	Volume × rate decomposition Q1 vs Q2
sq-2	SQL	Drop by product (spot / perp / options)
sq-3	SQL	Drop by region
sq-4	SQL	Drop by VIP tier
sq-5	Past plans	Was there a fee schedule change?
sq-6	Vector search	Market regime shift Q2 vs Q1
sq-7	Web fetch	External regulatory events

Workers execute in parallel (~30s):

sq	Finding	Confidence
sq-1	Vol Q1 $130B, Q2 $118B (−9.2%); rate 1.05 → 1.03 bps (−1.9%)	HIGH
sq-2	Perpetuals −15%; spot flat; options +8%	HIGH
sq-3	APAC −18%; EU −2%; Americas +3%	HIGH
sq-4	VIP 4-5 tiers dropped most (−22%); retail flat	HIGH
sq-5	Maker rebate widened 0.5 bp on Apr 14 (decision opt-20260414-007)	HIGH
sq-6	Q2 had 35 low-vol days vs Q1's 12 (cosine 0.91 to 2024-Q3)	MEDIUM
sq-7	No major regulatory events in Q2	LOW

Critic round 1: "VIP-tier × APAC intersection is biggest single factor (−22% in tier 4-5, −18% APAC). Need user-level breakdown to find concentration."

Spawn 2 more sub-questions:

| sq-8 | SQL | Top 50 APAC VIPs by Q1 volume — 8 reduced volume >50%; 2 went to zero |
| sq-9 | SQL | Those 8 share 3 affiliate codes (aff_217, aff_223, aff_341) |

Critic round 2: "Sufficient. Synthesize."

Synthesizer output (citation-enforced):

Investigation rsrch-20260512-001 — Q2 fee revenue variance
Verdict: HIGH CONFIDENCE — VIP migration + secondary regime effect

Decomposition (−12.0%):
  Volume effect: −9.2%   [ev-1]
  Rate effect:   −1.9%   [ev-5]  (Apr 14 maker rebate widening)
  Mix effect:    −0.9%

Headline findings:
  1. 8 APAC VIPs reduced volume >50%; 2 went to zero          [ev-8]
  2. These 8 share 3 affiliate codes — likely competitor migration [ev-9]
  3. Q2 had 35 low-vol days vs Q1's 12 — secondary effect      [ev-6]
  4. Apr 14 rebate widening explains −1.9% rate drop           [ev-5]

Recommendations:
  HIGH:   VIP relations contact 8 APAC accounts directly
  HIGH:   Growth audit aff_217 / aff_223 / aff_341
  MEDIUM: Stress-test fee model under continued low-vol regime
          (handoff to optimization layer)

Confidence: HIGH on factual findings; MEDIUM on competitor-migration hypothesis
Investigation cost: $0.07 · 67s · 9 sub-queries · trace lf-finance-q2-001

Latency: 67s. Cost: $0.07. The same investigation took FP&A 2 days last quarter and got the affiliate angle wrong (discovered 3 weeks later).

Worked example 4.2 — Wash trading investigation

User (Compliance Officer): "Investigate user whale_42 for wash trading suspicion. Be thorough — this may become a SAR filing."

Planner output (9 sub-questions):

sq	Tool	Question
sq-1	Vector	Trade pattern self-similarity vs historical wash-trader templates
sq-2	SQL	Round-trip frequency (buy/sell within N seconds)
sq-3	SQL	Counterparty concentration
sq-4	SQL	Device fingerprint overlap with other accounts
sq-5	SQL	Withdrawal patterns vs deposit patterns (net P&L)
sq-6	SQL	Affiliate / referral chain
sq-7	Vector RAG	KYC document similarity to other accounts
sq-8	Past investigations	Historical confirmed wash traders — pattern match
sq-9	SQL	IP / geo-rotation patterns

Workers execute (~40s):

sq	Finding	Confidence
sq-1	Trade pattern cosine 0.94 to confirmed wash-trader template (Jan 2025)	HIGH
sq-2	78% of trades are round-trips < 5 minutes	HIGH
sq-3	64% of volume against single counterparty `acct_X`	HIGH
sq-4	4 other accounts share device fingerprint hash	HIGH
sq-5	Withdrawals match deposits within 0.1% — net-zero P&L	HIGH
sq-6	All 5 accounts referred by same affiliate aff_991	HIGH
sq-7	KYC doc for whale_42 has 0.87 similarity to KYC of acct_X (different photo, same template)	MEDIUM
sq-8	2 prior cases referred by aff_991 confirmed wash trading	MEDIUM
sq-9	All 5 accounts log in from rotating IPs in same /24 subnet	HIGH

Critic round 1: "Strong evidence across 8 of 9 sub-questions. Recommend immediate compliance escalation."

Synthesizer output:

Investigation rsrch-20260512-002 — whale_42 wash-trading suspicion
Verdict: HIGH CONFIDENCE — wash trading + Sybil network

Hypotheses tested:
  H1  Coordinated wash-trade pattern    CONFIRMED  [ev-1, ev-2]
  H2  Sybil network (5 linked accounts) CONFIRMED  [ev-4, ev-6, ev-9]
  H3  Same-counterparty self-trading    CONFIRMED  [ev-3]
  H4  Affiliate-chain coordination      CONFIRMED  [ev-6, ev-8]
  H5  Inadvertent / unrelated activity  FALSIFIED  [ev-5 net-zero P&L]

Headline findings:
  1. Trade pattern matches confirmed wash-trader template (cosine 0.94) [ev-1]
  2. 5 accounts share device fingerprint, IP subnet, affiliate code   [ev-4, ev-6, ev-9]
  3. 78% round-trip trades < 5 min, 64% against single counterparty   [ev-2, ev-3]
  4. P&L net-zero — economic activity has no purpose other than volume [ev-5]
  5. Affiliate aff_991 has 2 prior confirmed wash-trading cases       [ev-8]

Recommendations:
  IMMEDIATE: Freeze 5 accounts; suspend aff_991
  IMMEDIATE: File SAR (handoff to SAR draft template)
  MEDIUM:    Review all aff_991-referred accounts (~40 users)

Investigation cost: $0.21 · 71s · 9 sub-queries · trace lf-compliance-whale42-001
Filing-ready evidence pack persisted to marts.research_evidence

Latency: 71s. Cost: $0.21.

The same investigation took an analyst 4–8 hours, produced a free-text Slack message with no evidence trail, and required a second analyst to reconstruct reasoning before SAR filing.

What makes Deep Research trustworthy:

Citation enforcement — every claim must carry [ev-N]. Synthesizer output regex-checked; failures trigger up to 3 retries before falling back to LOW confidence with gaps flagged.
Critic gate — synthesis cannot proceed until critic returns "sufficient". Multiple rounds catch premature conclusions.
Persistence + replay — every investigation lands in marts.research_runs with full evidence ledger. Six months later you can replay the exact reasoning steps via the trace ID.

Self-Learning — Four Continuous Feedback Loops

The platform improves weekly without engineering intervention. Each loop operates on a different cadence and improves a different surface.

Loop 1 — Glossary Expansion (1-day cycle)

Purpose: Unfamiliar domain terms become deterministic SQL fragments.

User asks question with unfamiliar term
            │
            ▼
Resolution hierarchy attempts to resolve:
   1. Live MCP list_tables — no exact match
   2. Glossary lookup — no entry for the term
   3. LLM disambiguation fallback — proposes interpretation
            │
            ▼
Agent returns answer with explicit disclaimer:
"I interpreted X as Y — confirm or refine?"
            │
            ├─ 👍 confirmed     → propose entry to Slack queue
            └─ 👎 corrected     → user-supplied definition queued
            │
            ▼
Data team approves entry in Slack (1-click)
            │
            ▼
Glossary updated — next user gets deterministic answer
in <1s without disambiguation

Cadence: real-time inline + nightly batch
Effect: glossary grows from ~50 terms at week 1 to ~400+ by month 6

Worked example: Day 1: User asks "show me VIP user churn this quarter". Glossary has no "churn" entry. Inline LLM proposes last_trade_at < now() - INTERVAL 30 DAY AND prior_30d_volume > 0. User clicks 👍. Slack posts: "Propose glossary entry — churn: [SQL]. Approve?" Data team approves day 2 morning. Day 3: Another user asks "how many churned users last week". Glossary lookup returns deterministic SQL. Answer in 800ms. No LLM disambiguation needed. Same question would have taken ~3s on day 1.

Loop 2 — Mart Recommendation (nightly batch)

Purpose: Frequent slow query patterns become permanent pre-joined marts.

NIGHTLY JOB
Mines marts.langfuse_flat for high-frequency staging queries:
  • > 50 times per week
  • > 5s average latency
  • similar query fingerprint via normalizeQuery()
            │
            ▼
Auto-drafts CREATE MATERIALIZED VIEW recommendation
with predicted speedup (e.g., 8s → <500ms)
            │
            ▼
Posts to #data-platform Slack
            │
            ▼
1-click approve → MV deployed
            │
            ▼
Next query hits the mart instead of computing the JOIN from scratch

Cadence: nightly
Effect: mart count grows from ~15 at week 1 → ~35 by month 6

Worked example: 50 users this week asked variations of "users approaching liquidation" — all answered via a 3-table JOIN taking 8 seconds. The recommendation job detects the pattern; auto-drafts marts.mart_near_liquidation MV definition; predicts speedup to <500ms; posts to Slack. Approved; deployed overnight. Within 24h, every related query hits the mart at sub-second latency.

Loop 3 — LoRA Fine-Tuning (weekly)

Purpose: Per-team specialist accuracy improves from real corrections.

User clicks 👎 with corrected SQL — captured in Langfuse
            │
            ▼
WEEKLY JOB
Batches all corrections since last run:
  ~50–200 corrections per team per week
            │
            ▼
QLoRA training (PEFT + bitsandbytes, NF4 4-bit on Qwen 72B)
  • LoraConfig r=16
  • per-team adapter (e.g., risk_lora_v12)
  • trains overnight on 24GB GPU
            │
            ▼
A/B test harness:
  • Run eval set against current + candidate adapter
  • Route 10% of traffic to candidate for 48h
  • Promote if accuracy +2pp AND latency regression <10%
            │
            ▼
Per-team multi-LoRA serving via vLLM (--enable-lora --max-loras 8)
Each team's specialist gets their own adapter at no GPU cost

Cadence: weekly train, weekly A/B promote
Effect: specialist accuracy improves +2–5pp per quarter for first 6 months, then asymptotes

Worked example: Compliance team's specialist, trained on 6 months of corrections, has learned that SAR draft questions specifically need device-fingerprint joins (the agent kept missing this — corrected ~30 times). After v12 adapter promotion, accuracy on SAR-related questions jumps from 78% → 91%.

Loop 4 — Eval-Set Regression Daemon (weekly automated)

Purpose: Catch drift / regression in CI before production users notice.

SUNDAY 06:00 UTC — k8s CronJob fires
            │
            ▼
Runs against current production pipeline:
  • 30 known-answer deep-research investigations
  • 50 known-answer SQL questions
  • per-team specialist eval sets
            │
            ▼
Compare every result vs ground truth:
  • Did headline findings match?
  • Was confidence calibration correct?
  • Latency within SLA?
  • Cost within budget?
            │
            ▼
Persisted to marts.eval_set_runs
            │
            ▼
If HIGH-confidence accuracy drops >2pp from last week:
  → BLOCK next prompt or model promotion
  → Post diff to Slack with regression details

Cadence: weekly automated — never skip
Effect: prompt-change accidents caught within 24h instead of in production

Worked example: Engineering pushes a prompt change to the planner template Friday afternoon. Saturday a user reports vague "the bot seems off today." Sunday 06:30 UTC eval daemon detects HIGH-confidence accuracy dropped 3.4pp on the 30-investigation eval set. Slack alert. Promotion blocked. Engineering investigates Monday, finds the edit accidentally removed citation-enforcement instructions. Reverts. Eval recovers next Sunday.

Plus six supporting intelligences

#	Intelligence	What it learns	Cadence
2	Semantic Table Router	Top-K relevant tables per question class	Per-query (<100ms)
3	Vector Query Cache	Repeat question → reuse exact past answer	Real-time
7	Adaptive Mart Refresh	Tune MV refresh cadence based on query frequency	Daily
8	Schema Change Detection	New tables / columns → auto-draft glossary	Nightly
9	Multi-Turn Chain Learning	Pre-compute likely follow-up queries	Real-time
10	Slow Query Detector	Recommend skipping indexes for frequent patterns	Nightly

The compound effect — 6-month trajectory

Metric	Week 1	Month 3	Month 6
Glossary size	~50	~200	~400+
% questions answered without analyst escalation	~50%	~75%	~90%
HIGH-confidence accuracy	~70%	~85%	~92%
Median latency	~3s	~1.5s	~1s
Mart count	~15	~25	~35
Recurring failure rate	high	dropping	<2%
Eval-set regressions caught in CI per quarter	0	2–3	4–6

Dynamic vs Deterministic — Where Each Lives

Different layers of the platform use different mixes of LLM intelligence and deterministic code. The rule: spend LLM where ambiguity exists; spend code where determinism is required for safety, speed, or auditability.

Layer	Dynamic (LLM-driven)	Deterministic (code)
Input understanding	✅ Classify engine + extract params	—
Schema discovery	—	✅ Live MCP `list_tables`
Glossary lookup	—	✅ Templated YAML lookup
Schema disambiguation fallback	✅ LLM proposes interpretation	—
Analytics SQL generation	✅ LLM writes grounded SQL	✅ Pre-execution validator + read-only role
NR lookup SQL	—	✅ Templated SELECT against `marts.numeric_research_runs`
NR cache-vs-refit decision	—	✅ Orchestrator code on freshness threshold
Optimization JSON model	✅ LLM emits structured model	✅ Python template converts JSON → solver code
Deep Research planning	✅ LLM decomposes into sub-questions	—
Deep Research workers	✅ LLM picks tool + generates SQL per sub-question	✅ Concurrency via asyncio.Semaphore
Deep Research critic	✅ LLM judges sufficiency	✅ Max 5 rounds (budget enforced)
Citation enforcement	—	✅ Regex check + 3-retry fallback
Synthesizer rendering	✅ LLM writes prose	✅ Confidence label rules
Persistence	—	✅ Parameterized inserts
Budget tracking	—	✅ Per-call token/cost/wall-clock counters
Self-improvement loops	✅ LLM proposes glossary/MV/etc	✅ Human approval workflow + A/B promotion

Trust Mechanisms — Four-Layer Defense

Every output passes through these defenses.

Layer	What	How enforced
1. Grounding	LLM can't reference what it can't see	Live schema via MCP `list_tables`; pre-joined marts eliminate JOINs; glossary terms deterministic; cross-encoder reranking
2. Verification	Every claim is checkable	Citation enforcement (`[ev-N]` regex); critic agent (up to 5 rounds); hypothesis verdicts (CONFIRMED/FALSIFIED/UNTESTED); HIGH/MEDIUM/LOW confidence labels calibrated weekly
3. Containment	If wrong, blast radius is zero	Database-layer read-only (`readonly = 1`); mart-only access; optimization output is reviewable plan never an action; sandboxed code execution with no network egress; per-team RBAC at DB role layer
4. Auditability	Every answer is replayable	Langfuse traces every prompt / tool call / sub-query; source provenance per claim; persisted research / optimization / NR runs; 7-year audit retention for regulated jurisdictions

By the Numbers


Catalogued use cases	~145 (75 team-based + ~70 product-domain)
Compound cross-schema queries	13 (single-SQL, 4–7 table joins)
Numeric research fitted models	20 (5 with full Python implementations)
Worked optimization examples	5 (3 with full code)
Worked deep-research investigations	3 (full traces)
MCP frameworks supported	12
Solvers supported	9 (HiGHS, CBC, SCIP, OR-Tools CP-SAT, CVXPY/CLARABEL/ECOS/SCS, Pyomo, optional Gurobi/CPLEX)
Teams served	7
Self-improvement loops	4 primary + 6 supporting
Deployment shapes	2 (crypto exchange + hedge fund)
Build timeline	24 weeks
Team size to build	5–7 engineers

The Tech Stack

Layer	Component	Notes
Data + vector + observability storage	ClickHouse 25.8+	Native HNSW vector indexes (GA); same DB stores trades + embeddings + Langfuse traces
Chat UI	LibreChat	Acquired by ClickHouse, November 2025; SSO; multi-LLM; 128K context
LLM	Qwen 2.5 72B via vLLM	Self-hosted; ~$0.01/query amortized; multi-LoRA serving
Triage	Qwen 2.5 72B (or 32B in cost-sensitive mode)
Tool layer	Anthropic MCP	12 framework compatibility
Observability	Langfuse	Acquired by ClickHouse, January 2026; traces in ClickHouse
Reranker	BGE-reranker-v2-m3	Cross-encoder, ~600M params, FP16
Solvers	OR-Tools / CVXPY / Pyomo / HiGHS / CBC / SCIP	Open-source; optional Gurobi/CPLEX
Code sandbox	Docker + nsjail + cgroups (v1)	Upgrade path to gVisor / Firecracker
Fine-tuning	PEFT + bitsandbytes (QLoRA)	NF4 4-bit, 24GB GPU sufficient
Pipeline	Debezium + Kafka	CDC from PostgreSQL sources

The Architectural Thesis (One Paragraph)

The platform combines four execution engines (Analytics, Numeric Research lookup, Optimization, Deep Research) on shared infrastructure (ClickHouse + Qwen + MCP + LibreChat + Langfuse) with citation-enforced trust mechanisms (regex-validated [ev-N] per claim, critic agent gating, HIGH/MEDIUM/LOW confidence calibration) and four continuous self-improvement loops (glossary expansion, mart recommendation, LoRA fine-tuning, eval-set regression). Triage is semantic LLM classification, not keyword matching. Engine choice is intent-driven, not verb-matched. The result is a platform that handles open-ended business questions in 1–90 seconds, never takes autonomous action, replays every answer end-to-end via Langfuse, and gets better weekly without engineering intervention.

That's the platform.

Building a Full-Stack Agentic AI Data Platform on ClickHouse: A Complete Architecture Guide

RAKESH THERANI — Thu, 07 May 2026 01:49:43 +0000

A production-grade, end-to-end agentic AI platform — chat UI, self-hosted LLM, MCP server, LLM observability, medallion data architecture, security guardrails, HA, and cost analysis. Same stack ClickHouse uses internally (DWAINE: 250+ employees, ~70% of internal analytics use cases covered, 50-70% workload reduction on the data-warehouse team). Adapted for a crypto-derivatives exchange.

Why I'm writing this

The phrase "AI platform" gets thrown around to describe everything from a single chatbot to a set of disconnected APIs. An actual production AI platform requires far more than a model and an interface — it needs data plumbing, semantic grounding, query safety, observability, role-based access, high availability, and a real cost model.

This is the architecture I shipped for a crypto exchange — covering all of those layers. Every component is open-source and battle-tested. Nothing is theoretical:

Data layer: PostgreSQL → CDC (Debezium + Kafka) → ClickHouse with medallion architecture (Raw → Staging → Marts)
LLM layer: Self-hosted Qwen 2.5 72B (Apache 2.0, all data stays on-prem) + business glossary for domain grounding
Tool layer: ClickHouse's official MCP server with bearer-token auth, SSRF protection, schema discovery + safe SQL execution
UX layer: LibreChat (open-source, SSO, role-based) — same chat UI ClickHouse acquired and uses for DWAINE
Observability layer: Langfuse (open-source, runs on ClickHouse) — every query, response, latency, cost tracked
Operations layer: HA cluster, query timeouts, memory caps, RBAC, full audit trail
JOIN strategy: 3-tier approach (pre-joined marts + dictionaries + UNION ALL + runtime fallback) so the LLM never writes a slow query

This is the same Agentic Data Stack ClickHouse open-sourced and uses internally. I'm documenting how to deploy it on top of any production database (this guide uses a crypto exchange as the worked example, but the architecture transfers to retail, fintech, marketing, ops).

If you're building or evaluating an AI platform for your own data — and you want something more substantial than "GPT-4 with a database connection" — this is the deep-dive.

Note: Costs and table examples reference a crypto-derivatives platform (large trade tables, real-time wallet flows). The architectural patterns and effort estimates apply universally. Numbers are real; mileage will vary.

Executive Summary
What We Are Building
Why ClickHouse
Platform Architecture
Data Consolidation — All DBs Into ClickHouse
How the AI Agent Works
What Questions Can Be Asked — By Team
How Multi-Table Queries Work
ClickHouse MCP Server
LLM Selection
How the LLM Learns Our Tables (No Training Required)
Chat Interface — LibreChat
- Specialist Agents Per Team (Beyond One General Agent)
Observability — Langfuse
Medallion Architecture — Data Organization
Security and Governance
High Availability Architecture
Implementation Roadmap
Cost Analysis
Risk Assessment
Success Metrics
Reference Links

1. Executive Summary

Objective

Build a unified data platform on ClickHouse that consolidates tables from all the crypto exchange databases (trading, wallets, users, risk, compliance) into one place, then layer an AI agent on top so any team member can ask questions in plain English and get instant answers.

The Problem Today

Data lives in multiple PostgreSQL databases across different services
Getting answers requires writing SQL, knowing which DB to query, or filing a ticket to engineering
Analytical queries on production PostgreSQL compete with live trading workloads
No single place to query across trading, wallet, user, and compliance data together

The Solution

Any team member types: "Show me top 10 users by futures volume this week who also had withdrawals > $50K"

The AI agent:
1. Understands the question (via business glossary)
2. Generates SQL across trade_future + wallet_transactions tables (both in ClickHouse)
3. Executes via MCP server (read-only, safe)
4. Returns formatted answer with table/chart

This works because all data from different databases is consolidated into ClickHouse tables — the LLM queries structured tables directly, not embeddings or vectors.

What ClickHouse Already Provides

ClickHouse has built a complete open-source Agentic Data Stack with a ready-to-deploy GitHub repo:

GitHub: https://github.com/ClickHouse/agentic-data-stack
Live demo: https://llm.clickhouse.com (AgentHouse — 37 public datasets)
Internal proof: DWAINE — 250+ ClickHouse employees use it, >200 daily messages across 50-70 daily conversations, ~70% of internal analytics use cases covered, 50-70% workload reduction on the 3-person DWH team (source)

We are not building from scratch. We are deploying ClickHouse's proven stack with our data.

Expected Outcomes

Metric	Target
Analytical query speed	10-100x faster than PostgreSQL for aggregations
Production DB load reduction	60-80% fewer analytical queries hitting production
Self-service data access	Any team member can query data without SQL knowledge
Cross-database queries	Single query across trading, wallets, users, risk data
Time to insight	Seconds instead of hours

2. What We Are Building

Before (Today)

Trading Ops needs a report:
  → Asks engineering → Engineer writes SQL → Queries prod DB → Formats result → Sends back
  → Time: hours to days
  → Impact: Analytical query slows down production

Compliance needs user transaction history:
  → Files ticket → Engineer joins data from 3 DBs → Manual export → Sends CSV
  → Time: 1-3 days

After (With This Platform)

Trading Ops opens LibreChat:
  → Types: "Top 10 markets by volume this week with liquidation count"
  → AI agent generates SQL, queries ClickHouse, returns answer in 5 seconds
  → No engineering involvement, no production DB impact

Compliance opens LibreChat:
  → Types: "All transactions for user X in last 90 days including spot, futures, and withdrawals"
  → AI agent queries across all consolidated tables, returns complete history
  → Instant, audited, repeatable

Who Uses It

Team	What They Ask
Trading Ops	Volume, market metrics, liquidations, position summaries
Risk	Exposure, liquidation cascades, concentration risk
Compliance	User transaction history, suspicious patterns, audit trails
Finance	Fee revenue, P&L, trading volume trends
Product	User activity, feature usage, market popularity
Engineering	System metrics, query performance, data quality checks

3. Why ClickHouse

Production Proof Points in Crypto

Coinhall — Real-time DEX aggregator across 23 blockchains:

Processes billions of trade events
20ms candlestick chart queries on massive datasets
Replaced PostgreSQL for analytics
Similar data model to the crypto exchange (trades, orders, market data)

CryptoHouse — Public ClickHouse-powered crypto analytics:

Bitcoin/Ethereum blockchain data at scale

DWAINE — ClickHouse's own internal AI agent (production):

250+ internal users querying data via natural language
>200 daily messages across 50-70 daily conversations
~70% of internal analytics use cases covered by the agent
~50-70% workload reduction on the 3-person DWH team
Source: ClickHouse — How we made our data warehouse AI-first
This is exactly what we are building for the crypto exchange

ClickHouse vs PostgreSQL for Analytics

Capability	PostgreSQL	ClickHouse
Storage model	Row-based (OLTP)	Columnar (OLAP)
Compression	3-5x	10-30x
Aggregation speed	Seconds-minutes	Milliseconds-seconds
Concurrent analytics	Impacts live trading	Isolated, zero production impact
Partitioning	Manual (daily)	Automatic (MergeTree)
Materialized views	Periodic refresh	Incremental (real-time triggers)
Cross-DB queries	Not possible	All data in one place

Key Advantage — All Data In One Place

Today, querying across trading DB + wallet DB + user DB requires application-level joins or manual exports. In ClickHouse, all tables from all databases are consolidated — the AI agent (or any analyst) can query across everything in a single SQL statement.

Our largest trading table runs to hundreds of GB in PostgreSQL. With ClickHouse's columnar compression (10-30x), this drops by an order of magnitude, and column-specific queries (e.g., SUM(quantity) WHERE market_name = 'BTCPFC') only read the relevant columns.

4. Platform Architecture

This is ClickHouse's Agentic Data Stack — three layers, all open-source:

                         +------------------+
                         |   ALL TEAMS      |
                         |  (Trading Ops,   |
                         |   Risk, Finance, |
                         |   Compliance,    |
                         |   Product)       |
                         +--------+---------+
                                  |
                  ================|================
                  |     LAYER 1: CHAT (LibreChat)  |
                  |     - Web-based chat UI         |
                  |     - SSO login                 |
                  |     - Role-based access          |
                  |     - Conversation history       |
                  =================|================
                                  |
                  ================|=================
                  |     LAYER 2: LLM + MCP          |
                  |                                  |
                  |  User question (plain English)   |
                  |         |                        |
                  |         v                        |
                  |  Qwen 2.5 LLM (self-hosted)     |
                  |  + Business Glossary             |
                  |         |                        |
                  |         v                        |
                  |  ClickHouse MCP Server           |
                  |  (schema discovery + SQL exec)   |
                  |         |                        |
                  ===========|======================
                             |
                  ===========|======================
                  |  LAYER 3: DATA (ClickHouse)     |
                  |                                  |
                  |  +---------------------------+   |
                  |  | Trading DB tables:        |   |
                  |  |  trade_future             |   |
                  |  |  trade_spot               |   |
                  |  |  orders                   |   |
                  |  +---------------------------+   |
                  |  | Wallet DB tables:         |   |
                  |  |  wallet_transactions      |   |
                  |  |  deposits / withdrawals   |   |
                  |  +---------------------------+   |
                  |  | User DB tables:           |   |
                  |  |  users, kyc_status        |   |
                  |  +---------------------------+   |
                  |  | Risk DB tables:           |   |
                  |  |  positions, margins       |   |
                  |  +---------------------------+   |
                  |  | Pre-aggregated views:     |   |
                  |  |  volume_daily             |   |
                  |  |  pnl_daily                |   |
                  |  |  liquidation_metrics      |   |
                  |  |  candlestick_1m/1h/1d     |   |
                  |  +---------------------------+   |
                  |                                  |
                  ==================================+
                             ^
                             |
                  ===========|======================
                  |  CDC PIPELINES (Debezium+Kafka) |
                  |  Real-time sync from all DBs    |
                  ==================================+
                        ^    ^    ^    ^
                        |    |    |    |
                  +-----+  +-+  ++  +-+-----+
                  |Trading| |Wallet| |User| |Risk|
                  |  DB   | |  DB  | | DB | | DB |
                  |  (PG) | | (PG) | |(PG)| |(PG)|
                  +-------+ +------+ +----+ +----+

Observability (Langfuse)

Langfuse runs alongside and tracks every LLM interaction:

What question was asked, by whom
What SQL was generated
Whether the answer was correct
Token usage and cost per query
Langfuse stores its data in ClickHouse itself — no extra DB needed

5. Data Consolidation — All DBs Into ClickHouse

The Core Idea

Every PostgreSQL database at the crypto exchange feeds its tables into ClickHouse via CDC (Change Data Capture). Once in ClickHouse, the AI agent can query across all of them in a single query.

CDC Pipeline

PostgreSQL (WAL) → Debezium Connector → Kafka → ClickHouse Kafka Engine → MergeTree Tables

Tables to Consolidate

Source DB	Tables	Est. Size	ClickHouse Engine	Sync
Trading DB	trade_future	hundreds of GB	ReplacingMergeTree	CDC real-time
	trade_spot	TBD	ReplacingMergeTree	CDC real-time
	orders	TBD	ReplacingMergeTree	CDC real-time
Wallet DB	wallet_transactions	TBD	ReplacingMergeTree	CDC real-time
	deposits	TBD	MergeTree	CDC real-time
	withdrawals	TBD	MergeTree	CDC real-time
User DB	users	Small	ReplacingMergeTree	CDC
	kyc_status	Small	ReplacingMergeTree	CDC
Risk DB	positions	TBD	ReplacingMergeTree	CDC real-time
	margins	TBD	ReplacingMergeTree	CDC real-time
Config	market_config	Small	ReplacingMergeTree	Daily batch
	fee_tiers	Small	ReplacingMergeTree	Daily batch

Why ReplacingMergeTree

PostgreSQL rows get UPDATEd (e.g., order status changes, position updates). ClickHouse's ReplacingMergeTree deduplicates on primary key during background merges, handling CDC update events correctly.

Pre-Aggregated Materialized Views

ClickHouse materialized views are incremental triggers — they process each new row as it arrives (unlike PostgreSQL which requires periodic REFRESH):

Materialized View	Source	What It Computes
volume_daily	trade_future, trade_spot	Volume per market per day
pnl_daily	trades + positions	P&L per user per market per day
liquidation_metrics	trade_future (order_type=1003)	Liquidation count, volume, frequency
candlestick_1m	trade_future, trade_spot	OHLCV per market per minute
candlestick_1h	chart_db.candlesticks (CDC)	OHLCV per market per hour
candlestick_1d	chart_db.candlesticks (CDC)	OHLCV per market per day
fee_revenue	trades	Fee revenue per market per tier per day
withdrawal_summary	withdrawals	Daily withdrawal volume per user

Cross-Database Query Example

Something impossible today (requires joining across separate PostgreSQL databases):

-- "Show users with >$100K futures volume today who also withdrew >$10K"
SELECT
    t.username,
    SUM(t.quantity * t.price) as futures_notional,
    w.total_withdrawn
FROM trade_future t
JOIN (
    SELECT username, SUM(amount) as total_withdrawn
    FROM withdrawals
    WHERE created_at >= today()
    GROUP BY username
    HAVING SUM(amount) > 10000
) w ON t.username = w.username
WHERE t.transaction_time >= today()
GROUP BY t.username, w.total_withdrawn
HAVING futures_notional > 100000
ORDER BY futures_notional DESC

The AI agent generates this SQL automatically from plain English.

6. How the AI Agent Works

The Flow

1. User types question in LibreChat
     "Which users had the most liquidations today?"

2. LLM receives:
   - The question
   - Business glossary (liquidation = order_type 1003, etc.)
   - ClickHouse schema (via MCP list_tables)

3. LLM generates SQL:
     SELECT username, COUNT(*) as liq_count, SUM(quantity * price) as liq_volume
     FROM trade_future
     WHERE order_type = 1003
       AND transaction_time >= today()
       AND username NOT LIKE 'bot_%'
       AND username NOT LIKE 'market_maker_%'
       AND username NOT LIKE 'liquidity_%'
     GROUP BY username
     ORDER BY liq_count DESC
     LIMIT 20

4. MCP Server executes SQL (read-only) against ClickHouse

5. LLM formats result:
     "Today's top users by liquidation count:
      1. user_abc — 12 liquidations, $234K total
      2. user_xyz — 8 liquidations, $156K total
      ..."

6. Langfuse logs: question, SQL, result, latency, tokens used

Key Point — It's All Structured Table Queries

The LLM is not doing anything magical with embeddings or vectors. It is:

Reading the ClickHouse table schema (column names, types)
Using the business glossary to understand domain terms
Writing standard SQL
Executing via MCP server

All our data is in regular ClickHouse tables. The "intelligence" comes from the LLM knowing how to translate English into SQL for our specific schema.

7. What Questions Can Be Asked — By Team

Trading Operations

Question	Tables Used	Complexity
"What is BTC perpetual futures volume today?"	trade_future	Single table
"Show top 10 markets by volume this week"	trade_future, trade_spot	Single table each, union
"Compare BTC futures volume this week vs last week"	trade_future	Single table, time comparison
"Show 1-hour candlestick chart for ETHPFC last 24 hours"	candlestick_1h (view)	Pre-aggregated view
"Which markets had the highest spread in the last hour?"	orders	Single table
"Show order book depth for BTCPFC right now"	orders	Single table, latest snapshot
"Average fill time for limit orders by market today"	orders	Single table
"How does today's volume compare to 30-day average?"	volume_daily (view)	Pre-aggregated view

Risk Management

Question	Tables Used	Complexity
"Which users have the largest open positions?"	positions	Single table
"Show all liquidation events in the last hour"	trade_future	Single table (order_type=1003)
"What percentage of trades were liquidations by market today?"	trade_future	Single table, grouping
"List users with concentrated exposure (>80% in one market)"	positions	Single table, aggregation
"Show liquidation cascade events — markets where >5 liquidations happened within 1 minute"	trade_future	Single table, window function
"Which users traded within 1 minute of a liquidation event?"	trade_future (self-join)	Single table, self-join
"Show users with margin utilization above 90%"	positions, margins	Multi-table join
"Correlation between liquidation volume and price movement for BTCPFC this week"	trade_future, candlestick_1h	Multi-table join

Compliance & Audit

Question	Tables Used	Complexity
"Show all transactions for user X in the last 90 days"	trade_future, trade_spot, withdrawals, deposits	Multi-table union
"List accounts with withdrawal volume > $100K today"	withdrawals	Single table
"Find users who deposited and withdrew within 24 hours"	deposits, withdrawals	Multi-table join
"Show all KYC-pending users who traded this week"	users, kyc_status, trade_future	Multi-table join
"List accounts that received deposits from the same source address"	deposits	Single table, grouping
"Show transaction patterns for user X — frequency, volume, timing"	trade_future, trade_spot, withdrawals	Multi-table union + aggregation
"Which accounts had unusual withdrawal patterns this week?"	withdrawals	Single table, anomaly detection
"Full audit trail: every action by user X since account creation"	trade_future, trade_spot, orders, withdrawals, deposits	Multi-table union (5 tables)

Finance & Revenue

Question	Tables Used	Complexity
"What was total fee revenue yesterday?"	fee_revenue (view)	Pre-aggregated view
"Monthly fee revenue breakdown by market"	fee_revenue (view)	Pre-aggregated view
"Compare spot vs futures fee revenue this month"	fee_revenue (view)	Pre-aggregated view, grouping
"Daily P&L trend for the last 30 days"	pnl_daily (view)	Pre-aggregated view
"Which fee tier generates the most revenue?"	trade_future, trade_spot, fee_tiers	Multi-table join
"Revenue impact if we reduce maker fees by 10%"	trade_future, trade_spot, fee_tiers	Multi-table join + simulation
"Show monthly trading volume trend with MoM growth %"	volume_daily (view)	Pre-aggregated view, window function

Product & Growth

Question	Tables Used	Complexity
"How many unique users traded futures this week?"	trade_future	Single table, COUNT DISTINCT
"What is our DAU trend for the last 30 days?"	trade_future, trade_spot	Multi-table union
"Which markets have growing vs declining user counts?"	trade_future, trade_spot	Single table, time comparison
"New user retention — users who signed up last month, how many traded this month?"	users, trade_future	Multi-table join
"Top 10 users by total trading volume all-time"	trade_future, trade_spot	Multi-table union
"Average trades per user per day — trend over last 90 days"	trade_future, trade_spot	Single table, aggregation
"Geographic distribution of active traders this month"	users, trade_future	Multi-table join

Question Complexity Summary

Type	% of Questions	How LLM Handles
Single table	~40%	Direct SELECT with WHERE/GROUP BY
Pre-aggregated view	~20%	Query materialized view (fastest)
Multi-table JOIN	~25%	LLM generates JOIN across tables
Multi-table UNION	~10%	LLM unions results from multiple tables
Complex (window/self-join)	~5%	LLM uses advanced SQL patterns

8. How Multi-Table Queries Work (JOIN Problem & Solution)

The Challenge

Many real questions require data from multiple sources:

"Show me users who had futures liquidations today AND also made withdrawals"

This needs: trade_future (Trading DB) + withdrawals (Wallet DB) + users (User DB)

Problem: ClickHouse is not optimized for runtime JOINs. Unlike PostgreSQL, JOINs in ClickHouse are memory-heavy, single-threaded (hash build phase), and can be slow on large tables. This is a well-known ClickHouse limitation.

Solution: ClickHouse uses 3 strategies to avoid runtime JOINs. This is exactly how DWAINE (ClickHouse's own AI agent) handles it.

Strategy 1 — Pre-Joined Denormalized Marts (Primary Approach)

JOINs are computed at data load time, not at query time. Refreshable Materialized Views periodically run the JOINs in the background and write the results into flat, wide "mart" tables. The AI agent queries these pre-joined tables directly — no JOINs needed at query time.

How it works:

  Staging Layer (normalized tables):
    staging.trade_future  — raw trades from CDC
    staging.users         — user profiles from CDC
    staging.wallets       — wallet transactions from CDC

         | Refreshable Materialized View (runs every 1-5 minutes)
         | Executes the JOINs in background
         v

  Marts Layer (denormalized, pre-joined):
    mart_user_trading_activity
      — username, kyc_status, registered_at,     ← from users
        futures_volume_today, spot_volume_today,  ← from trades
        withdrawal_total_today, deposit_total,    ← from wallets
        liq_count_today, liq_volume_today         ← from trades (order_type=1003)

The AI agent only sees the Marts layer. When someone asks "Show users with >$50K volume who also withdrew >$10K", the LLM queries one flat table — no JOIN:

-- NO JOIN needed — all data is already in one table
SELECT username, futures_volume_today, withdrawal_total_today
FROM mart_user_trading_activity
WHERE futures_volume_today > 50000
  AND withdrawal_total_today > 10000
ORDER BY futures_volume_today DESC

This is ClickHouse's official recommendation for AI agents:

"LLMs should only be exposed to the Marts layer — which is already pre-joined and denormalized. The agent has no need to write JOINs."
— How to set up ClickHouse for agentic analytics

Strategy 2 — Dictionaries (For Dimension Lookups)

Small, stable lookup tables (users, markets, fee_tiers) are loaded into in-memory Dictionaries. Instead of a JOIN, the LLM uses dictGet() — an O(1) key-value lookup that is 25x faster than a hash join.

-- INSTEAD OF THIS (slow JOIN):
SELECT t.username, t.quantity, u.kyc_status
FROM mart_trades_futures t
JOIN mart_users u ON t.username = u.username
WHERE t.transaction_time >= today()

-- THE LLM GENERATES THIS (fast dictionary lookup):
SELECT
    username,
    quantity,
    dictGet('users_dict', 'kyc_status', username) AS kyc_status
FROM mart_trades_futures
WHERE transaction_time >= today()

Performance comparison (from ClickHouse benchmarks):

Method	Time	Memory
dictGet() with flat layout	0.044 sec	84 MB
dictGet() with hashed layout	0.113 sec	103 MB
Parallel Hash JOIN	0.690 sec	4.8 GB
Standard Hash JOIN	1.133 sec	4.4 GB

Which tables become Dictionaries:

Dictionary	Source	Size	Lookup Key
`users_dict`	users table	Small	username
`markets_dict`	market_config	Small	market_name
`fee_tiers_dict`	fee_tiers	Small	tier_id
`kyc_dict`	kyc_status	Small	username

The business glossary teaches the LLM to use dictGet() instead of JOINs for these dimension tables.

Strategy 3 — Runtime JOINs (Fallback for Ad-Hoc Queries)

For truly ad-hoc questions that don't fit a pre-built mart table, ClickHouse does support runtime JOINs with optimizations:

Optimization	What It Does
`join_algorithm = 'auto'`	ClickHouse picks the best algorithm automatically
Automatic table reordering	Puts smaller table on the right side (since v24.12)
Global join reordering	Optimizes 3+ table JOIN chains (since v25.9)
`ANY JOIN`	Stops at first match — faster when you only need one row
Pre-filter with WHERE	Reduce table size before joining

When this is acceptable:

Small-to-medium result sets (both sides < few million rows after filtering)
Ad-hoc questions that don't match any existing mart table
One-time analysis queries where latency is less critical

How Each Question Type Is Handled

Question Type	Strategy	Example
Single-table query	Direct query on mart table	"Top 10 markets by volume today"
Pre-aggregated metric	Query materialized view	"Daily volume trend last 30 days"
Cross-domain (trading + wallets)	Pre-joined mart table	"Users with high volume AND large withdrawals"
Dimension lookup (user info)	Dictionary (dictGet)	"Show KYC status for top traders"
Full audit trail (multiple activity types)	UNION ALL (no JOIN needed)	"All activity for user X in 30 days"
Rare ad-hoc correlation	Runtime JOIN (fallback)	"Correlation between margin % and liquidation frequency"

Pre-Joined Mart Tables We Build

Mart Table	Pre-Joined Sources	Refreshes	Use Case
`mart_user_trading_activity`	trades + users + wallets	Every 5 min	"Users who traded AND withdrew"
`mart_user_risk_profile`	positions + margins + trades (liquidations)	Every 1 min	"Users at risk of liquidation"
`mart_liquidation_detail`	trades + users + positions	Every 1 min	"Liquidation events with user context"
`mart_user_full_activity`	trades + spot + orders + wallets	Every 5 min	"Complete activity for user X"
`mart_market_daily_summary`	trades + orders + market_config	Every 1 min	"Market performance today"
`mart_compliance_view`	trades + wallets + users + kyc	Every 5 min	"Compliance team queries"

Example — How Pre-Joining Works End-to-End

Question: "Show KYC-verified users who had liquidations this week with their withdrawal history"

Without pre-joining (SLOW — 3-table runtime JOIN):

-- BAD: Runtime JOIN across 3 large tables
SELECT u.username, u.kyc_status, liq.liq_count, wd.withdrawal_total
FROM staging.users u
JOIN staging.trade_future liq ON u.username = liq.username
JOIN staging.wallets wd ON u.username = wd.username
WHERE ...
-- Slow, memory-heavy, may timeout

With pre-joining (FAST — single table scan):

-- GOOD: Query pre-joined mart table, no JOIN needed
SELECT username, kyc_status, liq_count_week, withdrawal_total_week
FROM mart_user_trading_activity
WHERE kyc_status = 'verified'
  AND liq_count_week > 0
ORDER BY liq_count_week DESC

The JOIN happened in the background when the Refreshable Materialized View populated mart_user_trading_activity. The AI agent never writes a JOIN.

Example — Full Audit Trail (UNION ALL, No JOIN)

For "show all activity for user X", no JOIN is needed — we use UNION ALL across tables, which ClickHouse handles efficiently since each branch scans one table independently:

SELECT * FROM (
    SELECT 'futures_trade' AS activity, transaction_time AS event_time,
           market_name, side, quantity, price, quantity * price AS value_usd
    FROM mart_trades_futures
    WHERE username = 'john_doe' AND transaction_time >= today() - 30

    UNION ALL

    SELECT 'spot_trade', transaction_time, market_name, side,
           quantity, price, quantity * price
    FROM mart_trades_spot
    WHERE username = 'john_doe' AND transaction_time >= today() - 30

    UNION ALL

    SELECT 'withdrawal', created_at, currency, 'out',
           amount, 0, amount
    FROM mart_wallets
    WHERE username = 'john_doe' AND transaction_type = 'withdrawal'
      AND created_at >= today() - 30

    UNION ALL

    SELECT 'deposit', created_at, currency, 'in',
           amount, 0, amount
    FROM mart_wallets
    WHERE username = 'john_doe' AND transaction_type = 'deposit'
      AND created_at >= today() - 30
)
ORDER BY event_time DESC

UNION ALL is not a JOIN — each branch runs independently and results are concatenated. This is fast in ClickHouse.

Summary — JOIN Avoidance Strategy

Question comes in from user
        |
        v
Does a pre-joined mart table cover this?
  YES → Query the mart table directly (no JOIN, fastest)
        |
        NO
        v
Does it need a small dimension lookup (user info, market name)?
  YES → Use dictGet() dictionary lookup (25x faster than JOIN)
        |
        NO
        v
Does it need data from multiple activity types for one user?
  YES → Use UNION ALL across tables (no JOIN, each branch independent)
        |
        NO
        v
Truly ad-hoc cross-table correlation?
  → Use runtime JOIN with auto algorithm (fallback, acceptable for small result sets)
  → Log in Langfuse — if this pattern repeats, build a new mart table for it

Performance Comparison

Approach	Query Time	Memory	When to Use
Pre-joined mart table	Milliseconds	Minimal	~60% of questions (cross-domain)
Dictionary (dictGet)	Milliseconds	Minimal	~15% of questions (dimension lookup)
UNION ALL	1-2 seconds	Low	~15% of questions (audit trails)
Runtime JOIN (fallback)	2-10 seconds	High	~10% of questions (rare ad-hoc)

Reference

Resource	URL
Minimize and optimize JOINs	https://clickhouse.com/docs/best-practices/minimize-optimize-joins
Denormalizing Data	https://clickhouse.com/docs/data-modeling/denormalization
Using Dictionaries to Accelerate Queries	https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse
Direct Join (25x faster)	https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4
Choosing the Right Join Algorithm	https://clickhouse.com/blog/clickhouse-fully-supports-joins-how-to-choose-the-right-algorithm-part5
How to set up ClickHouse for agentic analytics	https://clickhouse.com/blog/how-to-set-up-clickhouse-for-agentic-analytics
Postgres to ClickHouse: Data Modeling Tips	https://clickhouse.com/blog/postgres-to-clickhouse-data-modeling-tips-v2

9. ClickHouse MCP Server

What It Is

The MCP (Model Context Protocol) server is an open standard that lets LLMs interact with databases safely. ClickHouse has an official MCP server:

GitHub: https://github.com/ClickHouse/mcp-clickhouse
PyPI: mcp-clickhouse (220K+ downloads)
License: Open source

What It Does (and Does NOT Do)

	Description
Does	Exposes ClickHouse schema to the LLM (tables, columns, types)
Does	Executes read-only SQL queries generated by the LLM
Does	Returns results in LLM-readable format
Does	Enforce read-only access (no INSERT/UPDATE/DELETE by default)
Does NOT	Convert natural language to SQL (that's the LLM's job)
Does NOT	Store data or state

Tools Exposed

MCP Tool	What It Does
`list_databases`	Lists all databases in ClickHouse
`list_tables`	Lists tables with columns, types, pagination
`run_query`	Executes read-only SQL, returns results
`run_chdb_select_query`	Embedded queries against files/URLs

Integration Flow

Qwen LLM ←— MCP Protocol (JSON-RPC) —→ ClickHouse MCP Server ←— Native Protocol —→ ClickHouse Tables

The MCP server is the controlled gateway — the LLM can discover schema and run queries, but cannot modify data or access restricted tables.

How the MCP Server Actually Works (Transport & Security)

Transport — Streamable HTTP (not stdio):
The MCP server runs as a persistent HTTP service. LibreChat sends HTTP POST requests to it when the LLM needs to call a tool (list_tables, run_query, etc.). This is different from subprocess-based MCP (stdio) — the server stays running, handles concurrent requests, and is addressable as a microservice within the deployment.

Authentication — Bearer Token:
LibreChat authenticates to the MCP server using a bearer token in the Authorization header. This ensures only LibreChat can call the MCP server — not other services or arbitrary HTTP clients on the same network.

SSRF Protection — Domain Whitelist:
LibreChat enforces an allowlist of domains it will make outbound HTTP requests to. This prevents a scenario where the LLM instructs LibreChat to call internal infrastructure URLs (e.g., http://internal-service/admin). Only the MCP server address and ClickHouse Cloud endpoint are whitelisted.

chDB — Query Files and URLs Without ETL:
The run_chdb_select_query tool uses chDB (embedded ClickHouse engine) to query local files, S3 URLs, or Parquet files directly — without loading them into ClickHouse tables first. chDB 4 adds a Pandas-like DataStore API with lazy execution and filter/column pushdown, useful for data scientists who upload files to LibreChat and want to query them immediately.

Remote MCP (ClickHouse Cloud):
For ClickHouse Cloud deployments, ClickHouse operates a hosted Remote MCP Server — agents connect directly without self-hosting the MCP service. For self-managed ClickHouse (our case), the MCP server runs as a container alongside ClickHouse.

Compatible Agent Frameworks

ClickHouse has tested their MCP server with 12 agent frameworks:
Agno, DSPy, LangChain, LlamaIndex, PydanticAI, Claude Agent SDK, OpenAI Agents SDK, CrewAI, Google ADK, Microsoft Agent Framework, mcp-agent, Upsonic

10. LLM Selection

Primary: Qwen 2.5 72B (Self-Hosted)

Factor	Detail
License	Apache 2.0 (open source, no vendor lock-in)
SQL generation	Top-tier on text-to-SQL benchmarks
Multilingual	English, Chinese, Japanese
Size	72B params — accurate enough, runs on single GPU node
Data privacy	All data stays on our infrastructure
Cost	Fixed GPU cost, not per-token

Infrastructure

Component	Specification
GPU instance	g5.12xlarge (4x A10G, 96GB VRAM)
VRAM needed	~40GB for 72B INT4 quantized
Framework	vLLM (high-throughput inference)
Throughput	30-50 tokens/second
Fallback	Qwen 2.5 32B on smaller GPU

Alternatives

Model	Size	Notes
Qwen 2.5 32B	32B	Lighter, cheaper GPU, slightly less accurate
Llama 3.1 70B	70B	Meta, strong general purpose
DeepSeek V3	671B MoE (~37B active params)	Strong on code/SQL
Claude Opus 4.7	API	Best for complex multi-step analysis — use only when data is not restricted to internal infra

Model selection rule: Use self-hosted Qwen for all queries involving the crypto exchange trading/user/wallet data (data stays on-prem, zero external API calls). Use Claude Opus 4.7 via API only for non-sensitive analytical work, development, or testing where data residency is not a constraint.

AGENTS.md — Per-Environment Agent Instructions

ClickHouse Assistant and agent frameworks support an AGENTS.md file — a plain text file placed in the agent's working context that injects domain-specific instructions into every session. This is complementary to the business glossary:

	Business Glossary	AGENTS.md
Purpose	Maps domain terms to SQL	Sets agent behavior rules
Example	`"liquidation" = order_type 1003`	`"Always exclude market makers from results"`
Example	`"volume" = SUM(quantity)`	`"Default time window is today() unless specified"`
Format	YAML key-value pairs	Plain text instructions
Effect	Teaches SQL mappings	Shapes how the agent reasons and responds

Both are injected into the LLM's system prompt at query time — no retraining needed.

DWAINE Reference

ClickHouse's own DWAINE agent uses Claude via AWS Bedrock. We choose self-hosted Qwen for data privacy and cost control, but the architecture supports swapping LLM backends.

11. How the LLM Learns Our Tables (No Training Required)

Key Point — No Model Training Needed

The LLM does not need to be trained or fine-tuned to understand the crypto exchange tables. It learns everything it needs at query time through two mechanisms: automatic schema discovery and a business glossary.

Mechanism 1 — MCP Schema Discovery (Automatic)

Every time the LLM receives a question, it calls list_tables via the MCP server. ClickHouse returns the full schema automatically — table names, column names, data types:

MCP returns to LLM automatically:

  Table: mart_trades_futures
    - username           String
    - market_name        String
    - transaction_time   DateTime
    - side               String
    - quantity           Float64
    - price              Float64
    - order_type         Int32        ← LLM sees this but doesn't know 1003 = liquidation

  Table: mart_wallets
    - username           String
    - transaction_type   String       ('deposit', 'withdrawal')
    - amount             Float64
    - currency           String
    - created_at         DateTime

  Table: mart_user_trading_activity
    - username           String
    - kyc_status         String
    - futures_volume_today   Float64
    - withdrawal_total_today Float64
    - liq_count_today    Int32
    ...

No configuration needed for this. When a new table is added to ClickHouse, the LLM sees it automatically on the next query.

Mechanism 2 — Business Glossary (You Configure This)

The schema alone is not enough. The LLM sees order_type Int32 but doesn't know what 1003 means. The business glossary is a configuration file (not model training) that gets injected into the LLM's prompt:

Business Glossary (business_glossary.yaml):

- "liquidation" = trade_future WHERE order_type = 1003
- "market maker" = username LIKE 'bot_%' OR 'market_maker_%' OR 'liquidity_%'
- "perpetual futures" = market_name LIKE '%PFC%'
- "volume" = SUM(quantity) from mart_trades_futures
- "notional" = SUM(quantity * price)
- "BTCPFC" = Bitcoin perpetual futures contract
- "whitelisted user" = user in internal_account_whitelist table
- "P&L" = pnl_daily materialized view
- "candlestick" = candlestick_1m/1h/1d materialized views
- "spot trade" = mart_trades_spot table
- "deposit" = mart_wallets WHERE transaction_type = 'deposit'
- "withdrawal" = mart_wallets WHERE transaction_type = 'withdrawal'

This is just a text file that gets added to the LLM's system prompt. You update it anytime — no retraining, no downtime.

Full Glossary Entries

Term	Definition	SQL Mapping
Liquidation	Forced position closure	`order_type = 1003` in trade_future
Perpetual futures	Non-expiring futures	`market_name LIKE '%PFC%'`
Market maker	Internal trading bots	`username LIKE 'bot_%' OR 'market_maker_%' OR 'liquidity_%'`
Trading volume	Total quantity	`SUM(quantity)` on trade_future/trade_spot
Notional volume	USD value	`SUM(quantity * price)`
Whitelisted user	Excluded from checks	`internal_account_whitelist` table
Deposit	Fiat/crypto incoming	`mart_wallets` WHERE transaction_type = 'deposit'
Withdrawal	Fiat/crypto outgoing	`mart_wallets` WHERE transaction_type = 'withdrawal'
P&L	Realized profit/loss	`pnl_daily` materialized view
Candlestick	OHLCV price bars	`candlestick_1m/1h/1d` views
Spot trade	Immediate exchange	`mart_trades_spot` table
Futures trade	Derivatives trade	`mart_trades_futures` table
ADL	Auto-deleveraging	`order_type = 1004` in trade_future
Funding fee	Periodic futures fee	`mart_funding_payments` table
Open interest	Total open positions	`SUM(quantity)` on mart_positions

What Actually Happens Per Query

User types: "Show top liquidated users today"
                    |
                    v
LLM receives in its prompt:
  ┌─────────────────────────────────────────────────┐
  │ 1. System instructions                          │
  │    "You are a SQL analyst for the crypto exchange.             │
  │     Generate ClickHouse SQL. Read-only only."   │
  │                                                 │
  │ 2. Business glossary (text file)                │
  │    "liquidation = order_type 1003"              │
  │    "market maker = bot_%, market_maker_%, liquidity_%"  │
  │    ... (all glossary entries)                    │
  │                                                 │
  │ 3. ClickHouse schema (from MCP list_tables)     │
  │    mart_trades_futures: username, market_name,   │
  │    transaction_time, order_type, quantity, ...   │
  │    mart_wallets: username, amount, ...           │
  │    ... (all mart tables)                        │
  │                                                 │
  │ 4. The user's question                          │
  │    "Show top liquidated users today"            │
  └─────────────────────────────────────────────────┘
                    |
                    v
LLM generates SQL using all 3 context sources:
  SELECT username, COUNT(*) as liq_count, SUM(quantity * price) as liq_volume
  FROM mart_trades_futures
  WHERE order_type = 1003                    ← from glossary
    AND transaction_time >= today()          ← from schema (knows column type)
    AND username NOT LIKE 'bot_%'          ← from glossary (exclude market makers)
  GROUP BY username
  ORDER BY liq_count DESC LIMIT 20

Training vs Context Injection

	Training / Fine-tuning	Context Injection (What We Do)
What it is	Modify the model's internal weights	Pass information in the prompt at query time
Time to update	Hours-days (retrain model)	Seconds (edit a text file)
Cost	Expensive (GPU hours)	Free
When schema changes	Must retrain	Automatic (MCP reads live schema)
When business terms change	Must retrain	Edit glossary file, instant effect
Risk	Can degrade model quality	None
What DWAINE uses	No training	Context injection (glossary + MCP)

When Would You Fine-Tune? (Optional, Phase 5+)

Fine-tuning is not required but can be done later to push accuracy higher:

Phase	Approach	Expected Coverage
Weeks 1-12	MCP schema + business glossary only (no training)	Aim for DWAINE-class — agent handles ~70% of analytics use cases (the other 30% still go to a data engineer)
Post Week 12 (optional)	Fine-tune Qwen on successful query pairs from Langfuse logs	Higher coverage + lower per-query latency

Fine-tuning would train the model on patterns like:

Input:  "top liquidated users today"
Output: "SELECT username, COUNT(*) FROM mart_trades_futures WHERE order_type = 1003
         AND transaction_time >= today() GROUP BY username ORDER BY COUNT(*) DESC LIMIT 10"

Langfuse collects thousands of these successful question→SQL pairs over time, which become the fine-tuning dataset. But this is an optimization, not a requirement.

How the Glossary Improves Over Time

Coverage and quality grow as the glossary fills in. The exact accuracy curve depends on workload — what we observed (and what ClickHouse reports for DWAINE) is that glossary growth correlates strongly with the share of questions the agent can answer end-to-end without analyst involvement:

Week 1:  Glossary has 50 terms   → narrow coverage; agent fields easy single-table questions
Week 4:  Glossary has 100 terms  → most common cross-domain questions handled
Week 8:  Glossary has 200 terms  → DWAINE-class — ~70% of analytics use cases covered
Week 12: Glossary has 300+ terms → covers long-tail domain language
Post-12: Fine-tune on Langfuse logs → tighter SQL, lower latency, higher first-pass success

The improvement cycle:

User asks a question → LLM generates wrong SQL
Langfuse logs the failure (question, wrong SQL, error)
Team reviews and adds missing glossary entry
Next time the same question is asked → correct SQL

ClickHouse's published takeaway for DWAINE is that business glossary quality is the #1 driver of agent usefulness — they reached the ~70% use-case-coverage milestone primarily through glossary expansion, not LLM fine-tuning.

12. Chat Interface — LibreChat

What It Is

LibreChat is an open-source chat interface acquired by ClickHouse (November 2025). It is the front-end of the Agentic Data Stack.

Self-hosted — no data leaves the crypto exchange infrastructure
Web-based — accessible from any browser
Connects to self-hosted Qwen LLM backend
MCP plugin connects LLM to ClickHouse
Conversation history and bookmarking
Supports tables and chart rendering in responses

Role-Based Access

Role	Sees	Example Questions
Trading Ops	All trading data, metrics	"Top markets by volume this week"
Risk	Positions, liquidations, exposure	"Users with largest open positions"
Compliance	User transactions, audit trails	"All transactions for user X in Q1"
Finance	Revenue, fees, P&L	"Monthly fee revenue by market"
Product	User activity, market stats	"DAU trend for futures last 30 days"

Specialist Agents Per Team (Beyond One General Agent)

The Role-Based Access table above splits who can see what data. The next step is splitting which agent answers which question. One general-purpose agent works for ~80% of questions; the remaining 20% are better handled by specialist agents — narrower tool set, tighter system prompt, glossary scoped to the team's domain language.

Why specialize: a general agent has to choose between dozens of tools and a 300-term glossary on every question. A specialist always knows roughly which tools apply, so it spends fewer tokens reasoning about tool selection and more tokens on the actual analysis. In production, specialists cut p50 latency 30-40% on their target queries and reduce hallucination (suggesting non-existent tools or fields) by ~60%.

The Five Specialists

Specialist	Tools enabled	Glossary scope	Example questions
Trading Ops Analyst	`volume`, `candlestick`, `order_book_snapshot`, `market_metrics`	Trading terms — volume, notional, market types, fee tiers	"Top markets by volume this week" / "ETHPFC 1h candles last 24h"
Risk Manager	`positions`, `liquidations`, `margin_status`, `concentration_check`	Risk terms — liquidation, margin utilization, concentration, ADL	"Users with margin >90%" / "Liquidation cascades in last hour"
Compliance / Audit	`wallet_history`, `kyc_lookup`, `audit_trail` (read-only, fully audit-logged)	Compliance terms — KYC tiers, whitelisted user, sanctioned wallet	"All transactions for user X in Q1" / "First-time wallets >$10K deposit"
Finance / Revenue	`fee_revenue`, `pnl_daily`, `volume_summary`	Finance terms — fee revenue, P&L, settlement, market-maker rebates	"Monthly fee revenue by market" / "Fee tier distribution last 30d"
General SQL	`run_sql` (full marts.* read access)	Full glossary	Internal-only fallback — anything the specialists don't cover

Specialist Definition — One YAML File Per Agent

Each specialist is defined as a single YAML file declaring its tools, glossary namespace, system prompt, and access role. This pattern keeps the specialists in version control, reviewable in PRs, and trivially editable by the team that owns the domain (Risk team owns risk-manager.yaml, Compliance owns compliance.yaml, etc.).

# specialists/risk-manager.yaml
name: risk-manager
display_name: "Risk Manager"
description: "Specialist agent for liquidation, margin, and concentration risk questions"

# Backing model
model:
  endpoint: "http://qwen:8000/v1"
  name: "qwen-72b"
  max_tokens: 4096
  temperature: 0.1                          # deterministic for risk-critical queries

# MCP tools this specialist sees (narrow subset of the full catalog)
mcp_tools:
  - positions
  - liquidations
  - margin_status
  - concentration_check
  - wallet_score
  - find_user

# Glossary namespace — only terms tagged with these scopes load into context
glossary_scopes:
  - risk
  - shared

# Database role — RBAC enforced at the ClickHouse layer, not just glossary convention
clickhouse_role: "agent_risk_readonly"     # GRANT SELECT on marts.{positions, liquidations, margins}

# Langfuse project tag — every query traced under this tag for per-specialist observability
langfuse_tags: ["specialist:risk-manager"]

# System prompt — tight, role-specific, no generic data-analyst boilerplate
system_prompt: |
  You are the Risk Manager specialist agent for the crypto exchange.
  You answer questions about liquidations, margin utilization, and concentration risk
  using ClickHouse marts tables.

  CRITICAL RULES:
  - Always exclude internal market-maker wallets (table: known_market_makers) from "user-side"
    risk metrics. Market makers are not at risk in the same way users are.
  - "Liquidation" means order_type = 1003 in trade_future. Never confuse with normal closes.
  - Margin utilization is current_used_margin / total_available_margin (per the margin_status tool).
  - For concentration questions, use concentration_check — never sum positions yourself.
  - When data is stale (>5 min), warn the user before answering.

  When you need data outside risk (trading volume, user KYC, fees), tell the user to ask the
  appropriate specialist. Do not attempt to answer.

The five specialists each have a file like this. ~60 lines per file, all owned and edited by the team that uses the agent.

Glossary Namespacing — Avoiding Term Conflicts Across Specialists

A 300-term glossary will have conflicts. "Withdrawal" means a wallet transaction in Trading Ops, but a candidate dropping out of a race in a different domain. "Liquidation" means forced position closure to Risk, but selling a token position to Finance. Namespacing the glossary by domain is non-optional past ~50 terms.

# glossary/risk.yaml — loaded only into Risk Manager's prompt
domain: risk
terms:
  - term: liquidation
    definition: Forced position closure when margin drops below maintenance level
    sql_mapping: "trade_future WHERE order_type = 1003"
    confidence_for_inclusion: high

  - term: margin utilization
    definition: Used margin / available margin, expressed as percentage
    sql_mapping: "margin_status.utilization_pct"
    confidence_for_inclusion: high

  - term: concentration risk
    definition: A user with >80% of position notional in one market
    sql_mapping: "concentration_check tool"
    confidence_for_inclusion: high

# glossary/finance.yaml — loaded only into Finance specialist
domain: finance
terms:
  - term: liquidation
    definition: Selling a position to realize P&L. Distinct from forced liquidation in trading.
    sql_mapping: "trade_future WHERE side = 'sell' AND user_initiated = true"
    confidence_for_inclusion: medium       # ambiguous — confirm with user if unclear

# glossary/shared.yaml — loaded into ALL specialists
domain: shared
terms:
  - term: USDC
    definition: USD Coin stablecoin — primary collateral
    sql_mapping: "currency = 'USDC'"

The MCP server reads each specialist's glossary_scopes and concatenates only the matching files into the system prompt. A Risk Manager session loads risk.yaml + shared.yaml; a Finance session loads finance.yaml + shared.yaml. They never see each other's "liquidation" definition. Conflict eliminated structurally.

Triage / Orchestrator — Routing the User to the Right Specialist

For higher-touch deployments, a triage agent (smaller, faster — Qwen 2.5 32B) reads the user's question first and routes to the right specialist. Adds 200-300ms latency but improves accuracy materially because the specialist's narrow context produces better SQL.

# specialists/triage.yaml
name: triage
display_name: "Question Router"
model:
  endpoint: "http://qwen-32b:8000/v1"     # smaller model, faster
  name: "qwen-32b"
  max_tokens: 256                          # only outputs a routing decision

system_prompt: |
  You are a question router for a crypto-exchange data platform.

  Read the user's question and respond ONLY with a JSON object:
  { "specialist": "<name>", "reasoning": "<one sentence>" }

  Available specialists:
  - trading-ops-analyst: volume, candlesticks, order books, market metrics
  - risk-manager: liquidations, margin, concentration, position risk
  - compliance: user transactions, KYC, audit trails, sanctions
  - finance-revenue: fee revenue, P&L, market-maker rebates
  - general-sql: anything outside the above; cross-domain questions

  Examples:
  Question: "Which users had margin above 90% this morning?"
  Output: { "specialist": "risk-manager", "reasoning": "Margin utilization is risk-domain" }

  Question: "Show me all withdrawals over $50K from wallet 0x..."
  Output: { "specialist": "compliance", "reasoning": "Wallet-level transaction audit is compliance-domain" }

  Question: "Top traders by volume who also had liquidations and withdrew funds"
  Output: { "specialist": "general-sql", "reasoning": "Cross-domain query — risk + trading + wallet" }

In LibreChat, the triage agent is the default endpoint. The user's first message goes to triage, and LibreChat then forwards the conversation to the chosen specialist (with the original question as context). The user sees a brief "Routing to Risk Manager…" indicator and then gets the specialist's full response.

Per-Specialist RBAC at the Database Layer

The glossary scope and tool subset are prompt-level guardrails. The hard guardrail is the ClickHouse role grant — each specialist authenticates as a different database role with different SELECT grants:

-- The five specialist database roles
CREATE ROLE agent_trading_ops_readonly;
CREATE ROLE agent_risk_readonly;
CREATE ROLE agent_compliance_readonly;
CREATE ROLE agent_finance_readonly;
CREATE ROLE agent_general_readonly;

-- Trading Ops sees: trades, orders, candlesticks, market metrics
GRANT SELECT ON marts.mart_trades_futures   TO agent_trading_ops_readonly;
GRANT SELECT ON marts.mart_trades_spot      TO agent_trading_ops_readonly;
GRANT SELECT ON marts.mart_volume_daily     TO agent_trading_ops_readonly;
GRANT SELECT ON marts.mart_candlesticks     TO agent_trading_ops_readonly;
GRANT SELECT ON marts.mart_market_summary   TO agent_trading_ops_readonly;
-- but NOT mart_users, mart_wallets, mart_kyc — those are off-limits

-- Risk sees: positions, margins, liquidations
GRANT SELECT ON marts.mart_positions        TO agent_risk_readonly;
GRANT SELECT ON marts.mart_liquidations     TO agent_risk_readonly;
GRANT SELECT ON marts.mart_margin_status    TO agent_risk_readonly;
GRANT SELECT ON marts.mart_user_risk_profile TO agent_risk_readonly;

-- Compliance sees: user transactions, KYC, audit-relevant tables (highest scope)
GRANT SELECT ON marts.mart_users            TO agent_compliance_readonly;
GRANT SELECT ON marts.mart_wallets          TO agent_compliance_readonly;
GRANT SELECT ON marts.mart_kyc              TO agent_compliance_readonly;
GRANT SELECT ON marts.mart_user_full_activity TO agent_compliance_readonly;

-- Finance sees: fees, revenue
GRANT SELECT ON marts.mart_fee_revenue      TO agent_finance_readonly;
GRANT SELECT ON marts.mart_pnl_daily        TO agent_finance_readonly;
GRANT SELECT ON marts.mart_volume_daily     TO agent_finance_readonly;

-- General SQL fallback gets read on all marts (NOT raw or staging)
GRANT SELECT ON marts.*                     TO agent_general_readonly;

-- All specialists get the same execution profile (timeouts, memory caps from §15)
ALTER USER risk_manager_agent      SETTINGS PROFILE 'agent_readonly_guardrails';
ALTER USER trading_ops_agent       SETTINGS PROFILE 'agent_readonly_guardrails';
-- ... etc.

If the Risk Manager's system prompt has a bug and the LLM tries to query marts.mart_kyc, the database rejects it. The glossary convention is now backed by hard access control. This is the difference between a prompt-engineering trick and a production guardrail.

Worked Example — Same Question, General Agent vs Specialist

USER: "Which users have margin above 90% right now?"

GENERAL AGENT (no specialist routing):
  - Reasons through 47 available tools, picks 4 candidates
  - Considers whether to use mart_users, mart_positions, or mart_user_risk_profile
  - Generates SQL joining 3 tables
  - Total tokens: ~1,400 input + 380 output
  - Latency: 4.2s
  - Result: correct, but did unnecessary join on mart_users

RISK MANAGER SPECIALIST:
  - Sees only 6 tools (none from other domains)
  - Glossary already maps "margin above 90%" → margin_status.utilization_pct > 0.9
  - Calls margin_status tool directly with threshold=0.9
  - Total tokens: ~600 input + 120 output
  - Latency: 1.8s
  - Result: correct, no extra join, lower memory query

Same correct answer, ~60% less latency, ~70% fewer tokens, simpler downstream query. Multiplied across thousands of daily queries, the cost difference is meaningful.

How Specialists Evolve Over Time

A specialist's quality is determined by its glossary, not its model. The operational loop:

Daily — review Langfuse traces tagged by specialist; surface the failed questions per specialist
Weekly — the team owning that specialist (Risk team, Finance team, etc.) reviews the failures and either expands the glossary or flags missing tools
Monthly — review whether any specialist is too narrow (high "I can't answer that, try X" rate) or too broad (drifting into another specialist's domain)

When a new specialist is needed (e.g., "Treasury / Inventory" emerges as a recurring question category that doesn't fit Finance), the process is:

New treasury.yaml specialist file
New ClickHouse role + grants
New glossary scope file
Updated triage agent prompt to know about the new specialist
Roll out behind a feature flag, observe Langfuse for 2 weeks, promote to default routing once accuracy >70%

Specialists don't replace the general agent; they wrap it. The general agent remains the fallback for cross-domain or novel questions. Over time, recurring "general agent" question patterns get promoted into new or existing specialists — that's how the system gets sharper.

Supporting Services — Why Each Exists

LibreChat is not a standalone service — it depends on four supporting components:

MongoDB — Conversation History Store:
LibreChat is stateless by design. MongoDB stores everything that needs to persist: all conversation threads, message history, user accounts, bookmarks, and presets. Without MongoDB, every browser refresh loses the conversation. MongoDB is chosen because LibreChat's data model is document-oriented (conversations are nested JSON objects with variable structure), not relational.

Meilisearch — Conversation Search Index:
Meilisearch provides full-text search over conversation history. When a user types in the LibreChat search bar to find a past query ("find my liquidation analysis from last week"), Meilisearch returns results in milliseconds. It maintains a search index that mirrors the MongoDB conversations.

pgvector + RAG API — Document Query:
LibreChat supports document uploads (PDFs, CSVs, text files). The RAG API processes uploaded files: it chunks the text, generates embeddings using an embedding model, and stores them in pgvector (PostgreSQL with vector extension). When a user asks a question while a document is attached, the RAG API retrieves the most relevant chunks from pgvector and injects them into the LLM prompt — enabling "ask questions about this document" without fine-tuning.

Qwen Connection — OpenAI-Compatible API:
LibreChat connects to Qwen via the OpenAI-compatible API that vLLM exposes. vLLM serves Qwen 2.5 72B with an endpoint that speaks the same protocol as OpenAI's API (/v1/chat/completions). From LibreChat's perspective, Qwen is just another OpenAI-compatible provider — no custom integration needed. Switching to a different model (e.g., Llama, DeepSeek) only requires changing the endpoint URL.

Live Demo

ClickHouse operates a public demo at https://llm.clickhouse.com (AgentHouse) showing this exact flow on 37 public datasets. Our deployment is the same architecture with the crypto exchange private data.

13. Observability — Langfuse

What It Is

Langfuse (acquired by ClickHouse, January 2026) is an LLM observability platform. It tracks every interaction between users and the AI agent.

What It Tracks

Metric	Why It Matters
Every question asked	Know what teams need
SQL generated	Debug wrong answers
Latency per query	Monitor performance
Token usage	Track compute cost
Accuracy (correct/incorrect)	Measure improvement
User identity	Compliance audit trail

Why This Matters

Without Langfuse, the AI agent is a black box. With it:

We know when answers are wrong and can fix the glossary
We measure accuracy improvement over time (target: 70% → 85%)
Compliance can audit every question and data access
We track cost per team/query type

How Traces Actually Flow (LibreChat → Langfuse → ClickHouse)

Langfuse has a built-in integration in LibreChat — no code changes or custom instrumentation needed. LibreChat automatically sends every LLM interaction to Langfuse via three environment variables (LANGFUSE_PUBLIC_KEY, LANGFUSE_SECRET_KEY, LANGFUSE_BASE_URL). Once set, every prompt and response is traced automatically.

Async trace pipeline — why Redis is needed:
LibreChat does not write traces to ClickHouse directly. It writes trace events to a Redis queue. A separate Langfuse worker process reads from the Redis queue and writes the processed traces to ClickHouse. This decouples trace ingestion from query execution — a slow Langfuse write never adds latency to the user's chat response.

Why Langfuse uses ClickHouse as its own backend:
Langfuse stores trace events (prompts, responses, latency, token counts) in ClickHouse because traces are time-series append-only data — exactly the workload ClickHouse is optimized for. The Langfuse analytics dashboard (cost per team, accuracy trends, slow queries) runs SQL aggregations over these traces in milliseconds. Metadata (projects, users, API keys) is stored in PostgreSQL. Binary assets (uploaded files, large trace payloads) are stored in MinIO (S3-compatible object storage).

The Shared ClickHouse Instance

The same ClickHouse instance serves two roles simultaneously:

Role	Database	What It Stores
Agent data warehouse	`marts`, `staging`, `raw`	business data — trades, wallets, users, risk
Langfuse event store	`langfuse`	LLM traces — prompts, SQL, latency, token counts

This is intentional. It means you can write SQL that crosses both roles — for example: "which teams generated the most incorrect SQL this week, and what business tables were they querying?" — joining Langfuse trace data with business context in a single query.

14. Medallion Architecture — Data Organization

ClickHouse recommends a three-layer data organization for AI agents (from their setup guide):

+------------------+     +------------------+     +------------------+
|   RAW DATABASE   | --> | STAGING DATABASE | --> |  MARTS DATABASE  |
|                  |     |                  |     |                  |
| Untransformed    |     | Deduplicated     |     | Curated tables   |
| CDC events from  |     | Normalized       |     | Aggregated views |
| all PostgreSQL   |     | Cleaned          |     | AI agent queries |
| databases        |     |                  |     | THIS layer only  |
+------------------+     +------------------+     +------------------+
   (ingestion)            (transformation)           (consumption)

Why Three Layers

Layer	Purpose	Who Accesses
Raw	Unmodified CDC events, full history	Data engineers only
Staging	Deduplicated, normalized, cleaned	Data engineers, advanced analysts
Marts	Curated, aggregated, business-ready	AI agent and all users

The AI agent only accesses the Marts layer — curated, pre-validated tables with clear column names and business meaning. This reduces LLM errors and prevents access to raw/sensitive data.

Marts Tables (What the AI Agent Sees)

Marts Table	Source	Description
`mart_trades_futures`	trade_future (staging)	Clean futures trades, no internal accounts
`mart_trades_spot`	trade_spot (staging)	Clean spot trades
`mart_orders`	orders (staging)	Order history with status
`mart_wallets`	wallet_transactions (staging)	Deposits, withdrawals, transfers
`mart_users`	users + kyc (staging)	User profiles (PII masked)
`mart_positions`	positions (staging)	Current open positions
`mart_volume_daily`	Materialized view	Daily volume by market
`mart_pnl_daily`	Materialized view	Daily P&L by user/market
`mart_liquidations`	Materialized view	Liquidation events and metrics
`mart_candlesticks`	Materialized view	OHLCV candles (1m/1h/1d)
`mart_fee_revenue`	Materialized view	Fee revenue by market/tier

15. Security and Governance

Data Access Controls

Control	Implementation
Authentication	SSO integration (existing corporate identity)
Authorization	Role-based — teams see only permitted mart tables
Query restrictions	MCP server enforces read-only (no mutations)
AI agent access	Marts layer only (no raw or staging data)
PII protection	User PII masked in marts tables
Audit trail	Every query logged in Langfuse with user identity
Network	ClickHouse in private VPC, no public access

LLM Role Resource Guardrails

The LLM database user must have hard resource limits to prevent runaway agent queries from affecting the cluster. ClickHouse's own agentic analytics setup guide prescribes these exact values:

Guardrail	Limit	Why
Query timeout	30 seconds	Agent queries that scan too much will abort cleanly
Memory per query	2 GB	Prevents a single agent query from exhausting node memory
Max rows scanned	100 million	Forces the LLM to use indexes and partitions — catches unfiltered full scans
Max bytes scanned	5 GB	Secondary byte-level cap alongside row limit
Max threads	4 CPUs	Limits CPU contention with other workloads
Access type	SELECT-only on `marts.*`	No mutations, no access to raw or staging layers

The LLM role gets SELECT access only on the marts schema — never on raw or staging. This is a hard access control, not just a glossary convention. If the LLM generates a query against a staging table, the database rejects it.

LLM Data Safety

Self-hosted Qwen 2.5 — no data sent to external APIs
Business glossary contains schema mappings, not actual data
LLM sees query results only — no direct database access
All interactions logged and auditable via Langfuse

Compliance

Data residency: ClickHouse deployed in the same region as production OLTP databases
Retention: Configurable TTL per table
Right to erasure: CDC propagates DELETE events from PostgreSQL
Access logs: Full audit trail via Langfuse

16. High Availability Architecture

ClickHouse Cluster (Production Region)

                    +-------------------+
                    |   Load Balancer   |
                    +---+----------+----+
                        |          |
               +--------v--+  +---v--------+
               | CH Node 1 |  | CH Node 2  |
               | (Shard 1  |  | (Shard 1   |
               |  Replica 1)|  |  Replica 2)|
               +------------+  +------------+

               ClickHouse Keeper (3 nodes for consensus)

Deployment Options

Option	Description	Est. Monthly Cost
ClickHouse Cloud	Fully managed, auto-scaling	~$2,000-5,000/mo
Self-hosted on EC2	2x m7g.2xlarge (Graviton3)	~$1,500-3,000/mo

Recommendation: Start with ClickHouse Cloud for faster deployment. Migrate to self-hosted if cost optimization is needed later.

17. Implementation Roadmap

Phase 1 — Data Foundation (Weeks 1-3)

Task	Details
Deploy ClickHouse	ClickHouse Cloud or 2-node self-hosted cluster
Identify all source tables	Catalog tables from all PostgreSQL databases
Set up CDC pipelines	Debezium + Kafka for each source database
Design medallion schema	Raw → Staging → Marts table definitions
Initial data load	Backfill historical data from all PostgreSQL databases
Validation	Row counts and checksum verification

Phase 2 — Marts + LLM (Weeks 4-6)

Task	Details
Build marts tables	Curated, aggregated, AI-ready tables
Materialized views	Volume, P&L, liquidations, candlesticks, revenue
Deploy Qwen 2.5 72B	Self-hosted on GPU instance with vLLM
MCP server setup	Connect ClickHouse MCP to Qwen
Business glossary v1	50-100 domain-specific terms
Test SQL generation	Validate against known queries

Phase 3 — Chat UI + Observability (Weeks 7-9)

Task	Details
Deploy LibreChat	Self-hosted, SSO integration
Connect to Qwen backend	MCP plugin configuration
Langfuse setup	LLM tracing and accuracy monitoring
Role-based access	Team-specific table permissions
Beta launch	Trading Ops team as first users
Feedback loop	Review Langfuse failures, expand glossary

Phase 4 — Production Hardening (Weeks 10-12)

Task	Details
Glossary expansion	Target 200+ terms based on real usage
Accuracy tuning	Fix common failure patterns from Langfuse
Performance tuning	Query caching, materialized view optimization
Security audit	Access review, penetration testing
GA launch	Roll out to all teams

Phase 5 — Advanced (Post Week 12)

Scheduled reports (daily P&L email, weekly risk summary)
Alert-driven queries (agent triggered by anomaly detection)
Multi-step analysis (agent chains multiple queries)
Fine-tune Qwen on the crypto exchange query patterns for higher accuracy
Add more source databases as needed

18. Cost Analysis

Monthly Infrastructure Costs

Component	Est. Monthly Cost
ClickHouse Cloud	$3,000-5,000
Kafka/CDC (Amazon MSK)	$500
GPU for Qwen 2.5 (g5.12xlarge)	$7,000 (1-yr reserved) / $11,700 (on-demand)
LibreChat (t3.medium)	$100
Langfuse	Included in ClickHouse
Total	~$11,000-14,000 (reserved) / ~$15,000-19,000 (on-demand)

Cost Optimization Path

Optimization	Savings
Self-hosted ClickHouse instead of Cloud	-$1,500-3,000/mo
Qwen 2.5 32B on smaller GPU	-$2,000/mo
Reserved instances (1-year)	-30% on GPU
Spot instances for dev/test	-60%

ROI Justification

Benefit	Value
Engineering time saved (ad-hoc queries)	20-40 hours/month
Faster incident response	50% reduction in MTTR
Production DB load reduction	Fewer analytical queries on live trading DB
Self-service analytics	Teams don't need SQL knowledge
Cross-database insights	Previously impossible queries now instant
Compliance efficiency	Instant audit queries vs manual extraction

19. Risk Assessment

Risk	Likelihood	Impact	Mitigation
LLM generates incorrect SQL	Medium	Medium	Business glossary, Langfuse monitoring, human review
CDC lag (stale data)	Low	Medium	Monitor Kafka consumer lag, alert if >5 min
ClickHouse cluster failure	Low	High	2-replica HA, automated failover, daily backups
GPU instance unavailable	Low	Medium	Reserved capacity, fallback to smaller model
Low user adoption	Medium	Medium	Start with power users, iterate on feedback
Schema drift (PG changes not in CH)	Low	Medium	Automated schema sync in CDC pipeline
Wrong answers not caught	Medium	High	Langfuse accuracy tracking, glossary reviews
PostgreSQL replication slot bloat	Medium	High	If Debezium falls behind, PG WAL accumulates indefinitely and can crash production DB — monitor slot lag, set `max_slot_wal_keep_size`, alert if lag > 10GB

20. Success Metrics

90-Day Targets

Metric	Target	Measurement
Query accuracy	>70% correct SQL	Langfuse evaluation
Active users	20+ weekly	LibreChat usage logs
Query volume	500+ queries/week	Langfuse trace count
Response time	<10 seconds	Langfuse latency p50
Source databases consolidated	4+ databases in ClickHouse	Table count
Business glossary	200+ terms	Glossary config
Production DB offload	50%+ analytical queries moved	PG query log

6-Month Targets

Metric	Target
Query accuracy	>85%
Active users	50+
Self-service resolution	70% of questions answered without engineering
Automated reports	10+ scheduled reports

21. Reference Links

ClickHouse Agentic Data Stack

Resource	URL
Agentic Data Stack GitHub repo	https://github.com/ClickHouse/agentic-data-stack
MCP Server GitHub repo	https://github.com/ClickHouse/mcp-clickhouse
AgentHouse live demo	https://llm.clickhouse.com

ClickHouse Blog Posts

Post	URL
The Agentic Data Stack	https://clickhouse.com/blog/the-agentic-data-stack
How we made our data warehouse AI-first (DWAINE)	https://clickhouse.com/blog/ai-first-data-warehouse
How to set up ClickHouse for agentic analytics	https://clickhouse.com/blog/how-to-set-up-clickhouse-for-agentic-analytics
Building a data platform for agents	https://clickhouse.com/blog/building-a-data-platform-for-agents
Integrating with ClickHouse MCP (5 frameworks)	https://clickhouse.com/blog/integrating-clickhouse-mcp
12 agent framework comparison	https://clickhouse.com/blog/how-to-build-ai-agents-mcp-12-frameworks
Introducing AgentHouse	https://clickhouse.com/blog/agenthouse-demo-clickhouse-llm-mcp
Ask AI agent & Remote MCP beta	https://clickhouse.com/blog/agentic-analytics-ask-ai-agent-and-remote-mcp-server-beta-launch
ClickHouse acquires LibreChat	https://clickhouse.com/blog/clickhouse-acquires-librechat
LibreChat + Agentic Data Stack	https://clickhouse.com/blog/librechat-open-source-agentic-data-stack
ClickHouse acquires Langfuse	https://clickhouse.com/blog/clickhouse-acquires-langfuse-open-source-llm-observability
Langfuse scaling with ClickHouse	https://clickhouse.com/blog/langfuse-llm-analytics
Agent-facing analytics	https://clickhouse.com/blog/agent-facing-analytics
LLM chat UIs that support MCP	https://clickhouse.com/blog/llm-chat-mcp-support
Wix Wild Moose — AI incident response on ClickHouse	https://clickhouse.com/blog/wix-wild-moose
Building an agentic coding app (retail demo)	https://clickhouse.com/blog/agentic-coding-app
Agentic coding at scale — CTO perspective	https://clickhouse.com/blog/agentic-coding
ClickHouse AI Policy (public)	https://github.com/ClickHouse/ClickHouse/blob/master/AI_POLICY.md

ClickHouse Documentation

Doc	URL
MCP setup guide	https://clickhouse.com/docs/use-cases/AI/MCP
Langfuse integration	https://clickhouse.com/docs/cloud/features/ai-ml/langfuse

Appendix A — Technology Stack Summary

Component	Technology	Purpose
Analytical DB	ClickHouse	All the crypto exchange data consolidated, columnar storage, fast aggregations
CDC	Debezium + Kafka	Real-time sync from all PostgreSQL databases
LLM	Qwen 2.5 72B (self-hosted)	Natural language to SQL generation
MCP Server	ClickHouse MCP	Schema discovery + safe SQL execution
Chat UI	LibreChat (self-hosted)	Web-based chat interface for all teams
Observability	Langfuse	LLM tracing, accuracy tracking, cost monitoring
Source of Truth	PostgreSQL databases	Production OLTP (unchanged)

Appendix B — DWAINE Reference (ClickHouse Internal Agent)

ClickHouse built DWAINE (Data Warehouse AI Natural Expert) for their own internal use:

Metric	Value
Users	250+ employees
Daily messages	200+
Daily conversations	50-70
Internal analytics use cases covered	~70%
DWH team workload reduction	50-70%
Stack	LibreChat + Claude (Bedrock) + ClickHouse MCP
Key learning	Business glossary is the #1 factor for accuracy
Source	ClickHouse — How we made our data warehouse AI-first

Our platform follows the same architecture, with Qwen replacing Claude for data privacy.

Closing thoughts — the parts that actually matter

After shipping this platform, the things that determine success are not the parts you'd expect:

The business glossary is everything. ClickHouse's internal post-mortem on DWAINE concluded the same: glossary quality is the #1 driver of query accuracy. You don't fine-tune the LLM — you write a YAML file that maps domain terms to SQL fragments, and the LLM uses it via in-context learning. Budget more time for the glossary than for the LLM.
The Marts layer is non-negotiable. Exposing the LLM to staging or raw data is how you get hallucinated SQL. Marts are pre-joined, denormalized, semantically clean, and explicitly access-controlled. The LLM should only ever see Marts.
JOIN avoidance dominates query performance. The 3-strategy approach — pre-joined marts, dictionaries (dictGet), UNION ALL — handles ~90% of cross-table questions without a single runtime JOIN. The other 10% gets ClickHouse's auto-algorithm fallback. This was the single biggest architectural decision.
Langfuse observability is what makes the platform improvable over time. Without per-query traces, accuracy scores, and cost-per-question, you have no idea where to invest. With it, you can see exactly which question patterns fail and feed that back into the glossary.
The PostgreSQL replication slot bloat risk is the one most teams forget. If Debezium falls behind, PostgreSQL accumulates WAL indefinitely and can crash production. Set max_slot_wal_keep_size and alert on slot lag. Don't learn this the hard way.
Security guardrails matter more than people think. A 30-second query timeout, 2GB memory cap, 100M-row scan limit, and SELECT-only access on the Marts schema together mean the LLM cannot accidentally take down the cluster. ClickHouse's official agentic-analytics setup guide prescribes these exact values for a reason.

The full implementation — including the ClickHouse AI DBA MCP server (152 tools for cluster operations) referenced in this guide, knowledge base with 47 production-tested rules, and 16 recovery runbooks — lives in the companion repo on GitHub.

If you've shipped something similar (or are building it now), I'd love to compare notes. Drop a comment below or reach out on LinkedIn.

The companion piece — Replacing Elasticsearch with ClickHouse for OTEL Logs, Traces & Metrics: A 90% Cost-Reduction Migration — covers the observability layer of this same architecture in more depth.

Replacing Elasticsearch with ClickHouse : A 90% Cost-Reduction Migration

RAKESH THERANI — Wed, 06 May 2026 04:20:01 +0000

A practical guide based on shipping this for a crypto-derivatives platform — annual observability bill went from high six figures to ~$50K, with faster queries and AI-powered log search as a bonus.

Why I'm writing this

If you're paying mid-six figures for Elasticsearch and ~90% of your queries are aggregations (error counts, latency percentiles, service health), you're paying full price for a feature you barely use — full-text search.

This post walks through how a crypto exchange replaced Elasticsearch with ClickHouse for OpenTelemetry logs, traces, and metrics. Same OTEL instrumentation, just a different backend. Result: 5× smaller storage footprint, 2-6× faster queries on benchmarks, and natural-language log queries via an AI agent — at ~10% of the cost.

If you have an existing Elasticsearch + Kibana observability stack and you've been wondering whether ClickHouse is a serious alternative, this is the deep-dive. Includes the schema, the migration plan, the OTEL Collector configuration, the cost numbers, and the gotchas.

Note on numbers: The cost figures below ($400K Elasticsearch → ~$50K ClickHouse) are real annual numbers from this deployment, on log volumes typical of a high-traffic trading platform (low-tens of TB ingested per year, 90-day hot retention, multi-region). Your mileage will vary substantially with log volume, retention, and cluster size. The architectural patterns are universal.

Executive Summary
The Problem — Elasticsearch at $400K/Year
Why ClickHouse for Observability
Platform Architecture
OpenTelemetry — The Standard We Keep 5b. Collection Agent Options — OTEL vs Fluent Bit vs Vector
ClickHouse Schema — Logs, Traces, Metrics
End-to-End Distributed Tracing — HTTP to ClickHouse
Full-Text Log Search — Replacing Kibana Discover
Visualization — Grafana Replaces Kibana
Data Retention & Tiered Storage
AI Layer — Natural Language Over Logs and Traces
Migration Plan — Zero Downtime Cutover (Standard OTEL or BindPlane)
Cost Analysis
Risk Assessment
Success Metrics
Reference Links

1. Executive Summary

Objective

Replace the current Elasticsearch-based observability stack (application logs + OTEL traces + metrics) with ClickHouse — reducing annual infrastructure cost from $400K to ~$35-60K while gaining faster aggregations, better compression, unified storage with business data, and AI-powered log querying.

The Problem Today

Paying $400K/year for Elasticsearch managed service
Elasticsearch is optimized for full-text search — most log queries are aggregations (error counts, latency percentiles, service health) where ClickHouse is 2-6x faster on cold queries, 1.7-2.6x on hot queries (ClickHouse/TextBench benchmark, OTEL logs at 1B–50B rows)
Logs, traces, metrics, and business data live in separate systems — no cross-correlation
Storage costs are high: same OTEL dataset takes 5x more space in Elasticsearch (49 GB vs 245 GB at 1B rows; 2.4 TB vs 12 TB at 50B rows)
Kibana is the only query interface — no programmatic access, no AI layer

The Solution

Keep OpenTelemetry (OTEL) as the instrumentation standard — zero application changes.
Change only the destination: swap Elasticsearch exporter → ClickHouse exporter in OTEL Collector.

All logs, traces, and metrics land in ClickHouse.
Grafana reads ClickHouse for dashboards and alerts.
AI agent queries logs/traces in plain English via the same LibreChat + MCP platform.

Expected Outcomes

Metric	Target
Annual cost reduction	$340-365K saved (~85-90% reduction)
Storage reduction	5x smaller total footprint (16x on column files) — real benchmark at 1B–50B OTEL rows
Query speed improvement	2-6x faster cold queries, 1.7-2.6x hot queries (ClickHouse/TextBench)
Retention period	Same or longer — at lower cost
Unification	Logs + traces + metrics + business data in one DB
AI queries over logs	Plain English → SQL → instant answer

2. The Problem — Elasticsearch at $400K/Year

Where the Cost Comes From

Cost Driver	Elasticsearch Behaviour	Annual Cost Share
Storage	Row-oriented index, 2-3x compression, needs SSD	~$100K
Compute	CPU-heavy indexing on every write, inverted index maintenance	~$150K
Licensing	Elastic managed service / Elastic Cloud premium	~$100K
Operations	Shard management, index lifecycle management (ILM), tuning	~$50K (eng time)

The Technical Mismatch

Elasticsearch was built for full-text search on documents (web pages, articles). Application logs and OTEL telemetry are structured time-series data — they need aggregations, not document search.

What teams actually query	Elasticsearch efficiency	ClickHouse efficiency
"Error count per service last hour"	Slow (aggregation on inverted index)	Fast (columnar scan)
"P99 latency for /api/trade endpoint"	Slow (percentile aggregation)	Fast (built-in quantile functions)
"Show logs for TraceId = abc123"	Fast (indexed term lookup)	Fast (bloom filter)
"Which services degraded after deploy at 14:00?"	Medium	Fast
"Free-text: find logs containing OutOfMemoryError"	Fast (native)	Good (tokenbf bloom filter)

~90% of real observability queries are aggregations. Elasticsearch is paying full price for a capability (full-text search) that covers only ~10% of use cases.

Where ClickHouse wins most — benchmark by query type (cold, 50B rows):

Query Category	Examples	ClickHouse speedup
Log retrieval (text match + fetch rows)	"Find logs containing OutOfMemoryError"	Narrowest gap — ES competitive
Error/match counts	"Count 500 errors in last hour"	Moderate advantage
Service-level breakdowns	"Group errors by service"	Large advantage
Time-series trend analysis	"Error rate per minute over last 24h"	Widest gap — 6x+ faster

The speedup grows with analytical complexity. Retrieval-only queries (find and show matching rows) is ES's home turf. The moment you add grouping, aggregation, or time-bucketing on top of a text match — the dominant pattern in observability — ClickHouse's vectorized engine pulls away decisively.

Current Stack Pain Points

Application
    │
    ▼ OTEL SDK (instrumented)
OTEL Collector
    │
    ▼ Elasticsearch Exporter
Elasticsearch Cluster
    │
    ▼
Kibana                   ← only UI, no programmatic access

No way to join log data with business data (e.g., "which users were affected by this error?")
Kibana dashboards require manual setup — no AI layer
Retention limited by cost — older logs are deleted or archived to cold storage with no query access
Every new service that emits logs increases Elasticsearch cost linearly

3. Why ClickHouse for Observability

Companies Already Doing This

Cloudflare — Replaced Elasticsearch with ClickHouse for HTTP request logs:

35-45M HTTP requests/sec across the edge; logging pipeline generates "TBs of data daily"
Per-row size dropped from ~600 bytes (Elasticsearch) to ~60 bytes (ClickHouse) — ~10× smaller on disk
Insert-side CPU and memory 8× lower than the Elasticsearch pipeline
Now stores 100% of events (vs heavy sampling on Elasticsearch, which was blowing the resource budget at "hundreds of TBs")
Source: blog.cloudflare.com/log-analytics-using-clickhouse

Uber — Migrated logging from Elasticsearch to ClickHouse:

"Millions of logs per second from thousands of services across regions"; storage in the multi-petabyte range
A single ClickHouse node ingests 300K logs/sec — ~10× a single Elasticsearch node
80%+ of queries are aggregations (terms / histogram / percentile) — exactly ClickHouse's strength
Source: uber.com/blog/logging

Contentsquare — OTEL traces and logs on ClickHouse (replaced 14× 30-node Elasticsearch clusters):

11× cheaper infrastructure cost
10× p99 query speedup
Retention extended from 1 → 13 months at lower spend
Source: clickhouse.com/blog/contentsquare-migration-from-elasticsearch-to-clickhouse

ClickHouse's own benchmark vs Elasticsearch (TextBench) — reproducible, identical hardware (AWS m6i.8xlarge):

At 50B rows: Elasticsearch needed 12.01 TiB, ClickHouse needed 2.43 TiB — 5× less storage
Cold-cache queries 4-6× faster, hot-cache 1.7-2.6× faster
ClickHouse ingests 50B rows in <4 hours; Elasticsearch took ~5 days after pipeline tuning
Source: clickhouse.com/blog/elasticsearch-log-analytics-clickhouse + github.com/ClickHouse/TextBench

Langfuse (now part of ClickHouse) — LLM observability platform that stores every LLM trace (prompts, responses, latency, token cost) in ClickHouse. Same Langfuse used in the Agentic AI Platform described in the companion piece.

ClickHouse vs Elasticsearch for Observability

Capability	Elasticsearch	ClickHouse
Storage model	Inverted index (document-oriented)	Columnar (OLAP)
Compression ratio	2-3x	10-30x
Aggregation speed	Seconds (post-indexing)	Milliseconds
Write throughput	Medium (index maintenance overhead)	Very high (append-only MergeTree)
Full-text search	Excellent (native inverted index)	Good (bloom filter indexes)
OTEL native support	Via Logstash/Beats (extra hop)	Native OTEL exporter (direct)
Cross-data joins	Not possible	Join logs with business tables
AI/LLM integration	Kibana AI (limited)	Full MCP + LLM stack
Tiered storage	ILM (complex config)	TTL + S3 (2 lines of SQL)
License	Elastic License (paid tiers)	Apache 2.0 (fully open source)
Operational complexity	High (shards, replicas, ILM)	Low (MergeTree auto-manages)

Key Architectural Advantage

ClickHouse already stores business data (trades, wallets, users, risk) via the Agentic AI Platform. Adding observability data to the same cluster means:

-- This query is IMPOSSIBLE in Elasticsearch:
-- "Which users were affected by the 500 errors on trading-service between 14:00-14:30?"

SELECT DISTINCT l.LogAttributes['user_id'] AS user_id,
       u.kyc_status,
       count() AS error_count
FROM otel_logs l
JOIN mart_users u ON l.LogAttributes['user_id'] = u.username
WHERE l.ServiceName = 'trading-service'
  AND l.SeverityText = 'ERROR'
  AND l.Timestamp BETWEEN '2026-04-17 14:00:00' AND '2026-04-17 14:30:00'
GROUP BY user_id, u.kyc_status
ORDER BY error_count DESC;

In ClickHouse: one query, instant answer.
In Elasticsearch + separate business DB: impossible without custom ETL.

Horizontal Scaling — Parallel Replicas

For high-volume workloads, ClickHouse scales a single query across multiple nodes using parallel replicas — the query is split across the replica fleet and results are merged. At 50B rows (same TextBench dataset):

Nodes	Total query runtime	Speedup
1 node	19.1s	baseline
3 nodes	12.5s	1.5x
9 nodes	6.45s	3x
20 nodes	3.27s	5.8x

For text-search queries specifically, index sharding distributes the inverted index analysis across the replica fleet — delivering a 5.8x speedup on full-text queries at scale. This is relevant for any high-volume environment as log volume grows: add nodes, queries get proportionally faster, no schema or application changes needed.

4. Platform Architecture

Target Architecture

                    +------------------------------------------+
                    |         CRYPTO EXCHANGE APPS             |
                    |                                          |
                    | Trading Service  Wallet Service          |
                    | Risk Service     User Service            |
                    | API Gateway      Background Workers      |
                    +-------------------+----------------------+
                                        |
                           OTEL SDK (zero app code change)
                           Auto-instruments: HTTP, DB, Kafka, Redis
                                        |
                    +-------------------v----------------------+
                    |           OTEL COLLECTOR                 |
                    |                                          |
                    |  Receivers: OTLP gRPC/HTTP               |
                    |  Processors: batch, resource, filter     |
                    |  Exporters: clickhouseexporter           |
                    +-------------------+----------------------+
                                        |
                    +-------------------v----------------------+
                    |           CLICKHOUSE CLUSTER             |
                    |                                          |
                    |  database: otel                          |
                    |  ┌─────────────┬──────────┬──────────┐   |
                    |  │ otel_logs   │otel_traces│otel_metrics│|
                    |  │ (Logs)      │(Traces)  │(Metrics) │   |
                    |  └─────────────┴──────────┴──────────┘   |
                    |                                          |
                    |  database: marts (business data)         |
                    |  ┌────────────────────────────────────┐  |
                    |  │ mart_trades  mart_users  mart_wallets│|
                    |  └────────────────────────────────────┘  |
                    +---+------------------+-------------------+
                        |                  |
           +------------v---+     +--------v-----------+
           |    GRAFANA     |     |   LIBRECHAT + LLM  |
           |                |     |   (AI queries over |
           | Dashboards     |     |   logs in plain    |
           | Trace Viewer   |     |   English)         |
           | Log Explorer   |     |                    |
           | Alerts         |     |                    |
           +----------------+     +--------------------+

What Changes vs Current Stack

Component	Before	After
OTEL SDK (app)	Same	Same — zero changes
OTEL Collector	Same	Same — only exporter config changes
Storage	Elasticsearch	ClickHouse
Log/Trace UI	Kibana	Grafana
Alerting	Kibana Alerts	Grafana Alerting
AI queries	None	LibreChat + Qwen + MCP
Cross-data correlation	Not possible	Native SQL joins

OTEL Collector Deployment — Two Architectures

There are two ways to deploy the OTEL Collector. Choosing the wrong one causes data loss during ClickHouse restarts or network blips.

Agent-Only (not recommended for production)

App → OTEL Collector (on same host) → ClickHouse directly

Each service runs its own collector. Simple to set up but fragile — if ClickHouse is briefly unreachable, the agent has no buffer and drops data. Works for development or low-stakes services.

Aggregator (recommended for production)

App → OTEL Agent (lightweight, on each host)
          │
          ▼
    OTEL Aggregator (central, 1–2 instances)
    - batches writes
    - retries on ClickHouse failure
    - filters/transforms before storage
          │
          ▼
    ClickHouse

The agent on each host is lightweight — just forwards to the aggregator. The aggregator does the heavy work: batching, retry on failure, PII filtering, routing. If ClickHouse goes down for 5 minutes, the aggregator queues data and flushes when it comes back. No data loss.

# OTEL Agent config (on each service host — minimal, just forwards)
exporters:
  otlp:
    endpoint: otel-aggregator.internal:4317   # sends to aggregator, not ClickHouse

# OTEL Aggregator config (central — does batching, retry, filtering)
processors:
  batch:
    timeout: 10s
    send_batch_size: 50000
  retry_on_failure:
    enabled: true
    initial_interval: 5s
    max_elapsed_time: 300s

exporters:
  clickhouse:
    endpoint: tcp://clickhouse.internal:9000
    database: otel
    compress: lz4
    timeout: 10s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_elapsed_time: 300s

For a crypto exchange: Use the Aggregator pattern. Trading and wallet services cannot drop observability data — if an incident occurs during a ClickHouse maintenance window, you need the logs.

5. OpenTelemetry — The Standard We Keep

Why OTEL is the Right Foundation

OpenTelemetry is a CNCF standard for instrumentation — vendor-neutral, supported by every major cloud provider and observability tool. We keep OTEL as our instrumentation layer. Only the backend destination changes.

Application Code → OTEL SDK → OTEL Collector → [ANY BACKEND]
                                                   ↑
                              Swap this: Elasticsearch → ClickHouse

OTEL Data Types We Capture

Signal	What It Is	Example
Logs	Structured log events with severity, body, attributes	"ERROR: failed to execute trade, user=john_doe, error=InsufficientBalance"
Traces	Distributed request flow across services with timing	Full journey of a trade order: API → trading-service → PostgreSQL → Kafka
Metrics	Numeric measurements over time	HTTP request rate, error rate, latency histogram, DB connection pool size

OTEL Collector — Only the Exporter Changes

# otel-collector-config.yaml

receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

processors:
  batch:
    timeout: 5s
    send_batch_size: 10000
  memory_limiter:
    limit_mib: 512
  resource:
    attributes:
      - key: environment
        value: production
        action: upsert

exporters:
  # ─── BEFORE (Elasticsearch) ───────────────────────────────
  # elasticsearch:
  #   endpoints: [https://elastic-cluster:9200]
  #   logs_index: logs-%{+yyyy.MM.dd}
  #   traces_index: traces-%{+yyyy.MM.dd}

  # ─── AFTER (ClickHouse) ────────────────────────────────────
  clickhouse:
    endpoint: tcp://clickhouse:9000
    database: otel
    logs_table_name:    otel_logs
    traces_table_name:  otel_traces
    metrics_table_name: otel_metrics
    ttl:     90               # days — auto-delete old data
    compress: lz4
    timeout:  5s
    retry_on_failure:
      enabled:           true
      initial_interval:  5s
      max_interval:      30s
      max_elapsed_time:  300s

service:
  pipelines:
    logs:
      receivers:  [otlp]
      processors: [memory_limiter, batch, resource]
      exporters:  [clickhouse]
    traces:
      receivers:  [otlp]
      processors: [memory_limiter, batch, resource]
      exporters:  [clickhouse]
    metrics:
      receivers:  [otlp]
      processors: [memory_limiter, batch, resource]
      exporters:  [clickhouse]

That is the only config change needed. Applications keep emitting OTEL. The collector keeps receiving. Only the destination changes.

Application Instrumentation — Zero Code Changes

For most languages, attach the OTEL agent at startup:

Java (Spring Boot / any JVM)

# Add to JVM startup — auto-instruments HTTP, JDBC, Kafka, Redis
java -javaagent:opentelemetry-javaagent.jar \
     -Dotel.service.name=trading-service \
     -Dotel.exporter.otlp.endpoint=http://otel-collector:4317 \
     -Dotel.traces.exporter=otlp \
     -Dotel.metrics.exporter=otlp \
     -Dotel.logs.exporter=otlp \
     -jar trading-service.jar

Python (FastAPI / Django)

# Add 3 lines at startup — auto-instruments HTTP, PostgreSQL, Redis
from opentelemetry.instrumentation.fastapi import FastAPIInstrumentor
from opentelemetry.instrumentation.psycopg2 import Psycopg2Instrumentor
from opentelemetry.instrumentation.requests import RequestsInstrumentor

FastAPIInstrumentor.instrument_app(app)
Psycopg2Instrumentor().instrument()
RequestsInstrumentor().instrument()

Node.js

// tracing.js — load before anything else via --require
require('@opentelemetry/auto-instrumentations-node/register');
// Auto-instruments: express, pg, http, redis, mongoose, kafka

5b. Collection Agent Options — OTEL vs Fluent Bit vs Vector

OTEL Collector is not the only way to ship data to ClickHouse. Three agents are production-proven. The compression difference between them is significant enough to affect your storage cost.

Compression Comparison (real benchmarks — identical log dataset)

Agent	Compression Ratio	Status	Protocol
Fluent Bit	33×	Mature	HTTP JSONEachRow
Vector	21×	Beta / widely used	HTTP JSON
OTEL Collector	14×	Alpha for ClickHouse exporter	Native ClickHouse TCP

Fluent Bit achieves 33× compression because it gives you full control over the schema — you define exactly which fields land in which typed columns. OTEL Collector uses a fixed schema (Map types for attributes) which is more flexible but less compressible.

For a crypto exchange: OTEL Collector is the right default because we already use OTEL instrumentation end-to-end and the fixed schema covers all signals. If storage cost becomes a concern at high volume, Fluent Bit is the migration path — it requires a custom schema but delivers the best compression.

When to Use Each

Agent	Use When
OTEL Collector	You already emit OTEL signals (our case). Single config change, zero app changes. Traces + logs + metrics in one pipeline.
Fluent Bit	K8s environment, log-heavy workload, storage cost is a priority. Best compression. Mature and battle-tested. Does not handle traces.
Vector	You want a fully custom schema with maximum flexibility. Good middle ground — better compression than OTEL, handles more data types than Fluent Bit.

Fluent Bit → ClickHouse Config (for reference)

# fluent-bit.yaml — ships K8s pod logs directly to ClickHouse
[OUTPUT]
    Name          clickhouse
    Match         *
    Host          clickhouse.internal
    Port          8123
    Database      otel
    Table         fluent_logs
    # Critical: async inserts prevent too-small-batch errors
    async_insert  1
    flush         10s

-- Custom schema for Fluent Bit (33x compression)
CREATE TABLE otel.fluent_logs
(
    timestamp               DateTime64(9)             CODEC(Delta, ZSTD(1)),
    pod_name                LowCardinality(String),
    namespace               LowCardinality(String),
    container_name          LowCardinality(String),
    severity                LowCardinality(String),
    message                 String                    CODEC(ZSTD(1)),
    kubernetes_labels       Map(LowCardinality(String), String) CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY (namespace, pod_name, timestamp)
TTL toDateTime(timestamp) + INTERVAL 90 DAY;

Important: Fluent Bit requires async_insert=1 and flush intervals ≥10 seconds. Without this, each log line triggers a separate HTTP insert and ClickHouse performance degrades significantly from too many small writes.

6. ClickHouse Schema — Logs, Traces, Metrics

Logs Table

CREATE TABLE otel.otel_logs (
    Timestamp           DateTime64(9)              CODEC(Delta, ZSTD(1)),
    TraceId             String                     CODEC(ZSTD(1)),
    SpanId              String                     CODEC(ZSTD(1)),
    TraceFlags          UInt32,
    SeverityText        LowCardinality(String),    -- ERROR, WARN, INFO, DEBUG
    SeverityNumber      Int32,
    ServiceName         LowCardinality(String),
    Body                String                     CODEC(ZSTD(1)),
    ResourceSchemaUrl   String                     CODEC(ZSTD(1)),
    ResourceAttributes  Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    ScopeSchemaUrl      String                     CODEC(ZSTD(1)),
    ScopeName           String                     CODEC(ZSTD(1)),
    ScopeVersion        String                     CODEC(ZSTD(1)),
    ScopeAttributes     Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    LogAttributes       Map(LowCardinality(String), String) CODEC(ZSTD(1)),

    -- Skip indexes for fast filtering (replaces Elasticsearch inverted index for common patterns).
    -- Body uses tokenbf_v1 here as the safe default — it's stable, no flags, works on every CH 23+.
    -- See §8 for the newer `text` index, which is the recommended upgrade once you're on a recent
    -- ClickHouse build (better selectivity, S3-friendly). You'd swap idx_body for the text index there.
    INDEX idx_trace_id   TraceId     TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_body       Body        TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
    INDEX idx_service    ServiceName TYPE set(100)             GRANULARITY 4,
    INDEX idx_severity   SeverityText TYPE set(10)             GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, toUnixTimestamp(Timestamp))
TTL toDateTime(Timestamp) + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

Traces Table

CREATE TABLE otel.otel_traces (
    Timestamp           DateTime64(9)              CODEC(Delta, ZSTD(1)),
    TraceId             String                     CODEC(ZSTD(1)),
    SpanId              String                     CODEC(ZSTD(1)),
    ParentSpanId        String                     CODEC(ZSTD(1)),
    TraceState          String                     CODEC(ZSTD(1)),
    SpanName            LowCardinality(String),
    SpanKind            LowCardinality(String),    -- SERVER, CLIENT, PRODUCER, CONSUMER
    ServiceName         LowCardinality(String),
    ResourceAttributes  Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    SpanAttributes      Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    Duration            Int64,                     -- nanoseconds
    StatusCode          LowCardinality(String),    -- STATUS_CODE_OK, STATUS_CODE_ERROR
    StatusMessage       String,
    Events              Nested (
        Timestamp       DateTime64(9),
        Name            LowCardinality(String),
        Attributes      Map(LowCardinality(String), String)
    ),
    Links               Nested (
        TraceId         String,
        SpanId          String,
        TraceState      String,
        Attributes      Map(LowCardinality(String), String)
    ),

    INDEX idx_trace_id   TraceId     TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_span_name  SpanName    TYPE set(50)             GRANULARITY 4,
    INDEX idx_service    ServiceName TYPE set(50)             GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp))
TTL toDateTime(Timestamp) + INTERVAL 90 DAY;

Metrics Tables (5 separate tables — one per OTEL metric type)

Important: Unlike logs and traces, the ClickHouse OTEL exporter does not use a single otel_metrics table. It creates five separate tables — one per OpenTelemetry metric type — because each type has different fields (sums and gauges are scalar, histograms have buckets, summaries have quantiles, etc.). All five share the same Resource/Scope/Attributes prefix.

Table	OTEL Metric Type	Typical Use
`otel_metrics_sum`	Sum (counter)	request counts, bytes sent
`otel_metrics_gauge`	Gauge	CPU %, queue depth, memory in use
`otel_metrics_histogram`	Histogram	request latency buckets
`otel_metrics_exponential_histogram`	Exponential histogram	high-cardinality latency
`otel_metrics_summary`	Summary (quantiles)	client-precomputed p50/p95/p99

Below is the otel_metrics_sum schema (other tables share the same prefix and add type-specific fields):

CREATE TABLE otel.otel_metrics_sum (
    ResourceAttributes      Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    ResourceSchemaUrl       String CODEC(ZSTD(1)),
    ScopeName               String CODEC(ZSTD(1)),
    ScopeVersion            String CODEC(ZSTD(1)),
    ScopeAttributes         Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    ScopeDroppedAttrCount   UInt32 CODEC(ZSTD(1)),
    ScopeSchemaUrl          String CODEC(ZSTD(1)),
    ServiceName             LowCardinality(String) CODEC(ZSTD(1)),   -- materialized from ResourceAttributes['service.name']
    MetricName              LowCardinality(String) CODEC(ZSTD(1)),
    MetricDescription       String CODEC(ZSTD(1)),
    MetricUnit              String CODEC(ZSTD(1)),
    Attributes              Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    StartTimeUnix           DateTime64(9) CODEC(Delta, ZSTD(1)),
    TimeUnix                DateTime64(9) CODEC(Delta, ZSTD(1)),
    Value                   Float64 CODEC(ZSTD(1)),
    Flags                   UInt32 CODEC(ZSTD(1)),
    Exemplars               Nested (
        FilteredAttributes  Map(LowCardinality(String), String),
        TimeUnix            DateTime64(9),
        Value               Float64,
        SpanId              String,
        TraceId             String
    ) CODEC(ZSTD(1)),
    AggregationTemporality  Int32 CODEC(ZSTD(1)),    -- 1=Delta, 2=Cumulative
    IsMonotonic             Bool CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(TimeUnix)
ORDER BY (ServiceName, MetricName, Attributes, toUnixTimestamp(TimeUnix))
TTL toDateTime(TimeUnix) + INTERVAL 90 DAY
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

The histogram table adds Count, Sum, BucketCounts Array(UInt64), ExplicitBounds Array(Float64), Min, Max. The summary table adds ValueAtQuantiles Nested(Quantile Float64, Value Float64). See the OTEL ClickHouse exporter source for the full DDL.

Note on ServiceName: the exporter does not materialize this column by default — it lives inside ResourceAttributes['service.name']. To include it in ORDER BY (as above), either let the exporter create the column with service_name_attribute_key, or add a materialized column: ServiceName LowCardinality(String) MATERIALIZED ResourceAttributes['service.name']. Without it, drop ServiceName from ORDER BY and filter via ResourceAttributes['service.name'] = 'trading-service'.

Why `LowCardinality` and `CODEC` Matter

Optimization	What It Does	Impact
`LowCardinality(String)`	Dictionary-encodes repeated values (ServiceName, SeverityText)	3-5x compression + faster filtering
`CODEC(Delta, ZSTD(1))` on Timestamp	Delta encodes sequential timestamps, then ZSTD compresses	5-10x compression on time columns
`CODEC(ZSTD(1))` on String cols	General purpose compression	3-7x
`bloom_filter` index on TraceId	Skips data blocks that can't contain the TraceId	Near O(1) lookup
`tokenbf_v1` index on Body	Token-based bloom filter for keyword search	Skips irrelevant blocks without full scan
`PARTITION BY toDate(Timestamp)`	One partition per day — old days auto-deleted by TTL	Instant TTL, no maintenance
`ORDER BY (ServiceName, SeverityText, Timestamp)`	Most queries filter by service + severity + time	Queries read minimal data

7. End-to-End Distributed Tracing — HTTP to ClickHouse

The Full Trace Flow

Every incoming HTTP request gets a TraceId. That same ID propagates through every service and DB call. ClickHouse stores all spans. Grafana shows the waterfall.

Browser → [traceparent header injected]
              │
              ▼
[1] Nginx               → Span: http.server (method, url, status, duration)
              │           propagates traceparent to upstream
              ▼
[2] API Gateway         → Span: route handling
              │
              ▼
[3] Trading Service     → Span: business logic
              │
        ┌─────┴──────┐
        ▼             ▼
[4] PostgreSQL      [5] ClickHouse    → Span: db.query (SQL text, duration, rows)
(OTEL JDBC auto)    (native OTEL)
        │
        ▼
[6] Kafka Producer      → Span: messaging.publish (topic, partition)

All spans → OTEL Collector → ClickHouse otel_traces table
                                         │
                                         ▼
                                   Grafana Trace UI
                                   (full waterfall)

Layer 1 — Nginx (Trace Entry Point)

# nginx.conf
load_module modules/ngx_otel_module.so;

http {
    otel_exporter {
        endpoint otel-collector:4317;
    }
    otel_service_name  "api-gateway";
    otel_trace         on;
    otel_trace_context propagate;    # injects traceparent into upstream request

    server {
        location /api/ {
            otel_trace      on;
            otel_span_name  "$request_method $uri";
            otel_span_attr  http.method  $request_method;
            otel_span_attr  http.url     $uri;
            otel_span_attr  http.status  $status;
            proxy_pass      http://backend;
        }
    }
}

Layer 2 — Application Services (Auto-Instrumented)

OTEL Java agent auto-instruments every JDBC query. For ClickHouse queries, pass the context explicitly:

Python — Tracing ClickHouse Queries

from opentelemetry import trace
from opentelemetry.propagate import inject
import clickhouse_connect

tracer = trace.get_tracer("trading-service")

def get_user_risk_profile(username: str):
    with tracer.start_as_current_span("clickhouse.mart_user_risk_profile") as span:
        span.set_attribute("db.system",    "clickhouse")
        span.set_attribute("db.name",      "marts")
        span.set_attribute("db.operation", "SELECT")
        span.set_attribute("db.statement",
            "SELECT * FROM mart_user_risk_profile WHERE username = ?")

        # Inject current trace context into ClickHouse HTTP headers.
        # IMPORTANT: clickhouse_connect caches `http_headers` on the client object — they
        # are reused for every subsequent query on this client. To get a *unique* traceparent
        # per query, either (a) create a fresh client per request (as below), or (b) pass the
        # traceparent via the per-query `settings={"opentelemetry_traceparent": ...}` argument
        # so each query carries its own trace ID.
        headers = {}
        inject(headers)

        client = clickhouse_connect.get_client(
            host="clickhouse",
            http_headers=headers    # ClickHouse reads traceparent from here
        )
        result = client.query(
            "SELECT * FROM mart_user_risk_profile WHERE username = {username:String}",
            parameters={"username": username}
        )
        span.set_attribute("db.rows_returned", len(result.result_rows))
        return result.result_rows

Go — Tracing ClickHouse Queries

import (
    "github.com/ClickHouse/clickhouse-go/v2"
    "go.opentelemetry.io/otel"
    "go.opentelemetry.io/otel/attribute"
    "go.opentelemetry.io/otel/propagation"
)

tracer := otel.Tracer("trading-service")

func getTopTraders(ctx context.Context) ([]Trader, error) {
    ctx, span := tracer.Start(ctx, "clickhouse.mart_trades_futures")
    defer span.End()

    span.SetAttributes(
        attribute.String("db.system",    "clickhouse"),
        attribute.String("db.operation", "SELECT"),
        attribute.String("db.statement", "SELECT username, SUM(quantity*price) FROM mart_trades_futures ..."),
    )

    // clickhouse-go/v2 does NOT auto-inject the OTEL traceparent into the
    // native protocol. Extract it explicitly from ctx and pass it via the
    // `opentelemetry_traceparent` query setting — ClickHouse server reads
    // that and emits the correct trace_id into system.opentelemetry_span_log.
    carrier := propagation.MapCarrier{}
    otel.GetTextMapPropagator().Inject(ctx, carrier)

    conn, _ := clickhouse.Open(&clickhouse.Options{
        Addr: []string{"clickhouse:9000"},
        Settings: clickhouse.Settings{
            "opentelemetry_traceparent": carrier["traceparent"],
            // optional, only if your propagator emits it:
            "opentelemetry_tracestate":  carrier["tracestate"],
        },
    })

    rows, err := conn.QueryContext(ctx, `
        SELECT username, SUM(quantity * price) as volume
        FROM mart_trades_futures
        WHERE transaction_time >= today()
        GROUP BY username
        ORDER BY volume DESC
        LIMIT 10
    `)
    return parseRows(rows), err
}

Heads up — sample rate: ClickHouse only writes its own internal spans to system.opentelemetry_span_log if opentelemetry_start_trace_probability > 0 (default 0). Set this in users.xml (or via SET per session) to capture server-side spans for traceparent-tagged queries. A common production setting is 0.01 (1%) for load, 1.0 for staging.

Layer 3 — ClickHouse Native Tracing

ClickHouse reads the traceparent header from every query and emits its own internal spans to system.opentelemetry_span_log:

-- ClickHouse internal spans for a specific trace.
-- NOTE: in modern ClickHouse, system.opentelemetry_span_log.trace_id is type UUID,
-- not String — direct hex-string comparison fails. Convert with lower(hex(trace_id))
-- or build the UUID from the hex form.
SELECT
    lower(hex(trace_id))                     AS trace_id_hex,
    lower(hex(span_id))                      AS span_id_hex,
    lower(hex(parent_span_id))               AS parent_span_id_hex,
    operation_name,
    (finish_time_us - start_time_us) / 1000  AS duration_ms,
    attribute['clickhouse.query']             AS sql_query
FROM system.opentelemetry_span_log
WHERE lower(hex(trace_id)) = '4bf92f3577b34da6a3ce929d0e0e4736'
ORDER BY start_time_us;

Export these to otel_traces via a materialized view so they appear in Grafana alongside app spans:

-- Auto-export ClickHouse internal spans to otel_traces
CREATE MATERIALIZED VIEW otel.clickhouse_spans_mv TO otel.otel_traces AS
SELECT
    fromUnixTimestamp64Micro(start_time_us)  AS Timestamp,
    lower(hex(trace_id))                     AS TraceId,
    lower(hex(span_id))                      AS SpanId,
    lower(hex(parent_span_id))               AS ParentSpanId,
    ''                                       AS TraceState,
    operation_name                           AS SpanName,
    'SPAN_KIND_SERVER'                       AS SpanKind,
    'clickhouse'                             AS ServiceName,
    map()                                    AS ResourceAttributes,
    attribute                                AS SpanAttributes,
    (finish_time_us - start_time_us) * 1000  AS Duration,   -- convert to nanoseconds
    'STATUS_CODE_OK'                         AS StatusCode,
    ''                                       AS StatusMessage,
    []                                       AS `Events.Timestamp`,
    []                                       AS `Events.Name`,
    []                                       AS `Events.Attributes`,
    []                                       AS `Links.TraceId`,
    []                                       AS `Links.SpanId`,
    []                                       AS `Links.TraceState`,
    []                                       AS `Links.Attributes`
FROM system.opentelemetry_span_log;

What a Full Trace Looks Like in ClickHouse

-- Full waterfall for a single trade request
SELECT
    SpanName,
    ServiceName,
    Duration / 1e6                          AS duration_ms,
    SpanAttributes['http.method']           AS http_method,
    SpanAttributes['http.route']            AS route,
    SpanAttributes['db.statement']          AS sql,
    SpanAttributes['db.rows_affected']      AS rows,
    StatusCode,
    Timestamp
FROM otel.otel_traces
WHERE TraceId = '4bf92f3577b34da6a3ce929d0e0e4736'
ORDER BY Timestamp ASC;

Result — full waterfall in one query:

SpanName                          Service              duration_ms
──────────────────────────────    ──────────────────── ───────────
http.server POST /api/trade       api-gateway   245.3 ms
  └─ trade.execute                trading-service      241.1 ms
       ├─ db.query (risk check)   trading-service       18.4 ms  ← SELECT mart_user_risk_profile
       ├─ db.query (insert trade) trading-service        4.2 ms  ← INSERT INTO mart_trades_futures
       ├─ SELECT (internal CH)    clickhouse             3.1 ms  ← ClickHouse internal span
       └─ kafka.produce           trading-service        2.1 ms

8. Full-Text Log Search — Replacing Kibana Discover

Updated Position — ClickHouse Now Competitive on Full-Text Search

Full-text search used to be the primary reason to keep Elasticsearch. ClickHouse has significantly closed this gap with a new text index type that works natively on object storage (S3) with the same performance as local disk — removing the last major technical advantage Elasticsearch held.

Reference: ClickHouse Full-Text Search on Object Storage

New `text` Index — How to Add It to otel_logs

The text index is currently behind an experimental flag. Enable it before the ALTER:

SET allow_experimental_full_text_index = 1;

-- Add the text index to the Body column.
-- `tokenizer = 'default'` splits on non-alphanumeric characters (good general default).
-- `case_sensitive = false` lowercases tokens at index *build time* — query the raw column.
ALTER TABLE otel.otel_logs
    ADD INDEX body_text_idx(Body)
    TYPE text(tokenizer = 'default', case_sensitive = false)
    GRANULARITY 1;

-- Materialize the index on existing data
ALTER TABLE otel.otel_logs MATERIALIZE INDEX body_text_idx;

Don't wrap the column in queries. The optimizer matches the index against hasToken(Body, ...) — wrapping it as hasToken(lower(Body), ...) defeats the index and forces a full scan. Because the index was built with case_sensitive = false, hasToken(Body, 'OutOfMemoryError') already matches outofmemoryerror, OOM, etc.

What the text index supports:

Function	Example	Use Case
`hasToken`	`hasToken(Body, 'OutOfMemoryError')`	Exact token match — fastest
`hasAllTokens`	`hasAllTokens(Body, ['trade', 'failed'])`	All tokens must appear
`hasAnyTokens`	`hasAnyTokens(Body, ['ERROR', 'FATAL'])`	Any token match
`LIKE`	`Body LIKE '%OutOfMemoryError%'`	Wildcard match
`startsWith`	`startsWith(Body, 'WARN')`	Prefix match
`match` (regex)	`match(Body, 'user_[0-9]+')`	Regex search

Performance: 7.4x speedup vs full table scan on text search (ClickHouse benchmark, 10M rows with array tags).

Why it works on S3: The index uses sequential dictionary reads with front-coding compression — no random I/O, which is the key constraint on object storage. 94.5% of tokens appear in ≤6 rows, so embedded posting lists handle the vast majority of lookups without reading large posting lists.

Real-world proof point — gitTrends: ClickHouse's own reference demo (github.com/ClickHouse/gitTrends) searches 10 billion+ GitHub events using hasToken() on a body text index — the exact same pattern used for log search here. The app lets users compare FTS index vs bloom-filter skip index vs full table scan in real time, with live row-scan counters streamed from ClickHouse. Sub-second queries at 10B rows validate the production viability of hasToken() for high-volume text search workloads.

How ClickHouse Handles Log Search

Keyword Search — New `text` index (preferred)

-- Uses new text index — 7.4x faster than full scan
SELECT Timestamp, ServiceName, SeverityText, Body
FROM otel.otel_logs
WHERE hasToken(Body, 'OutOfMemoryError')
  AND Timestamp >= now() - INTERVAL 1 HOUR
ORDER BY Timestamp DESC
LIMIT 100;

-- LIKE also uses the text index automatically
SELECT Timestamp, ServiceName, SeverityText, Body
FROM otel.otel_logs
WHERE Body LIKE '%OutOfMemoryError%'
  AND Timestamp >= now() - INTERVAL 1 HOUR
ORDER BY Timestamp DESC
LIMIT 100;

Exact TraceId Lookup (bloom filter)

-- Near O(1) — bloom_filter index makes this very fast
SELECT *
FROM otel.otel_logs
WHERE TraceId = 'abc123def456'
ORDER BY Timestamp;

Structured Attribute Search

-- Filter on OTEL log attributes — common in structured logging
SELECT ServiceName, count() AS error_count
FROM otel.otel_logs
WHERE LogAttributes['http.status_code'] = '500'
  AND Timestamp >= today()
GROUP BY ServiceName
ORDER BY error_count DESC;

Multi-Condition Log Search (most common Kibana query)

-- "All ERROR logs from trading-service in last 2 hours that mention user_id"
SELECT
    Timestamp,
    SeverityText,
    Body,
    LogAttributes['user_id']    AS user_id,
    LogAttributes['error_code'] AS error_code,
    TraceId
FROM otel.otel_logs
WHERE ServiceName  = 'trading-service'
  AND SeverityText = 'ERROR'
  AND Timestamp    >= now() - INTERVAL 2 HOUR
  AND hasToken(Body, 'user_id')     -- uses text index
ORDER BY Timestamp DESC
LIMIT 200;

Case-Insensitive Search (built into the index)

-- The text index was built with case_sensitive = false, so tokens are lowercased
-- at index time. Query the raw column — wrapping it in lower() defeats the index.
SELECT Timestamp, ServiceName, Body
FROM otel.otel_logs
WHERE hasToken(Body, 'OutOfMemoryError')   -- matches OOM/oom/Oom case-insensitively
  AND Timestamp >= now() - INTERVAL 1 HOUR;

Full-Text Search Comparison — Updated

Query Type	Elasticsearch	ClickHouse	Notes
Exact token match (`hasToken`)	~10ms	~15-30ms	Near-comparable with text index
Keyword search (`LIKE`)	~10ms	~20-50ms	text index — 7.4x faster than scan
Structured attribute filter	~50ms	~5-20ms	CH wins (columnar)
Aggregation (error count by service)	~200ms-2s	~10-50ms	CH wins significantly
Time-range + service filter	~100ms	~10-30ms	CH wins (partition pruning)
TraceId lookup	~10ms	~20-50ms	Comparable (bloom filter)
Free-text fuzzy search	Excellent	Good (regex via `match()`)	ES still leads for fuzzy
Search on S3/object storage	Degraded (SSD required)	Full speed on S3	CH advantage — no SSD needed

Bottom line: ~95% of observability queries are structured (service + time + severity + attribute) — ClickHouse wins on all of those. For the remaining ~5% requiring keyword search in log bodies, the new text index brings ClickHouse to near-Elasticsearch performance. The only remaining ES advantage is fuzzy/phrase search, which is rarely needed for structured application logs.

Coming soon in ClickHouse: phrase search (position-aware token matching) and JSON column indexing — which will close the remaining gap further.

9. Visualization — Grafana Replaces Kibana

Setup

# Install ClickHouse datasource plugin
grafana-cli plugins install grafana-clickhouse-datasource

# Or in docker-compose:
environment:
  - GF_INSTALL_PLUGINS=grafana-clickhouse-datasource

Datasource Configuration

# grafana/provisioning/datasources/clickhouse.yaml
apiVersion: 1
datasources:
  - name: ClickHouse-OTEL
    type: grafana-clickhouse-datasource
    uid: clickhouse-otel
    jsonData:
      host: clickhouse
      port: 9000
      database: otel
      username: grafana_readonly
    secureJsonData:
      password: ${GRAFANA_CH_PASSWORD}

Key Dashboards to Build

1. Service Health Overview

-- Error rate per service — last 1 hour, 1-minute buckets
SELECT
    toStartOfMinute(Timestamp)   AS time,
    ServiceName,
    countIf(SeverityText = 'ERROR') AS errors,
    count()                          AS total,
    errors / total * 100             AS error_rate_pct
FROM otel.otel_logs
WHERE Timestamp >= now() - INTERVAL 1 HOUR
GROUP BY time, ServiceName
ORDER BY time ASC;

2. Latency Distribution (P50 / P95 / P99)

-- HTTP endpoint latency percentiles — last 30 minutes
SELECT
    SpanAttributes['http.route']          AS endpoint,
    quantile(0.50)(Duration) / 1e6        AS p50_ms,
    quantile(0.95)(Duration) / 1e6        AS p95_ms,
    quantile(0.99)(Duration) / 1e6        AS p99_ms,
    count()                               AS request_count
FROM otel.otel_traces
WHERE SpanKind    = 'SPAN_KIND_SERVER'
  AND Timestamp  >= now() - INTERVAL 30 MINUTE
GROUP BY endpoint
ORDER BY p99_ms DESC;

3. Slowest Database Queries

-- Slowest ClickHouse queries in last 1 hour
SELECT
    SpanAttributes['db.statement']     AS sql,
    count()                             AS calls,
    avg(Duration) / 1e6                 AS avg_ms,
    max(Duration) / 1e6                 AS max_ms,
    quantile(0.99)(Duration) / 1e6      AS p99_ms
FROM otel.otel_traces
WHERE SpanAttributes['db.system'] IN ('clickhouse', 'postgresql')
  AND Timestamp >= now() - INTERVAL 1 HOUR
GROUP BY sql
ORDER BY p99_ms DESC
LIMIT 20;

4. Log Explorer (Kibana Discover equivalent)

-- Live log tail with filtering — wire to Grafana Logs panel
SELECT
    Timestamp,
    ServiceName,
    SeverityText,
    Body,
    TraceId,
    LogAttributes
FROM otel.otel_logs
WHERE Timestamp >= $__timeFrom()    -- Grafana time variable
  AND Timestamp <= $__timeTo()
  AND ServiceName = '$service'      -- Grafana template variable
  AND SeverityText IN ($severity)
ORDER BY Timestamp DESC
LIMIT 1000;

5. Distributed Trace Waterfall

Configure Grafana Explore → Traces with ClickHouse datasource:

Config Field	Value
Table	otel.otel_traces
TraceID column	TraceId
SpanID column	SpanId
Parent SpanID	ParentSpanId
Start time	Timestamp
Duration	Duration (nanoseconds)
Service name	ServiceName
Operation name	SpanName

Click any log line with a TraceId → jump directly to full trace waterfall.

Correlating Logs + Traces + Business Data in Grafana

-- Grafana panel: "Affected users for errors in selected time range"
-- This is impossible in Kibana — requires joining log data with business data

SELECT
    l.LogAttributes['user_id']    AS user_id,
    u.kyc_status,
    count()                        AS error_count,
    max(l.Timestamp)               AS last_error
FROM otel.otel_logs l
JOIN marts.mart_users u ON l.LogAttributes['user_id'] = u.username
WHERE l.ServiceName  = '$service'
  AND l.SeverityText = 'ERROR'
  AND l.Timestamp    BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY user_id, u.kyc_status
ORDER BY error_count DESC
LIMIT 50;

Grafana Plugin — Upcoming Features

These are confirmed items being prototyped by the ClickHouse Grafana plugin team — not yet shipped. Reference: Our Vision for the ClickHouse Grafana Plugin

1. Deployment & K8s Annotation Presets

Grafana annotations are vertical markers on time-series panels that flag notable events. The plugin will generate these automatically from OTel data — no manual query writing:

Preset	Source	What it surfaces
Deployment detection	`ResourceAttributes['service.version']` change	Service version change / rollback markers on dashboards
K8s lifecycle events	OTel resource attributes	Pod restarts, OOM kills, autoscaling events

For a crypto exchange: deploy a new trading-service version → the timestamp appears as a marker on all latency/error dashboards automatically. Immediately answers "did this error spike start at deployment?"

2. JWT Per-User Query Identity

Currently, all Grafana users share a single ClickHouse datasource credential. The roadmap item forwards each Grafana user's JWT identity to ClickHouse, enabling:

Row-level access control based on actual user identity — compliance team sees only compliance-relevant tables, risk team sees risk tables
Per-user audit trail in ClickHouse query log — every dashboard query is attributed to a named person, not a shared service account
Per-user cost tracking — token/compute cost per team member

This closes the gap between Kibana's per-user access model and the current Grafana/ClickHouse setup.

3. Visual Metrics Builder (OTel Map Column Support)

OTel metrics (CPU, memory, network I/O) currently require writing SQL aggregation queries by hand. The upcoming metrics builder provides:

Select metric name from a dropdown (populated from the otel_metrics table)
Choose aggregation (sum, avg, max, p99)
Add group-by dimensions

The key improvement: OTel uses Map-type columns (ResourceAttributes, LogAttributes) containing key-value pairs. The builder will expose a key picker so users can filter on ResourceAttributes['k8s.namespace.name'] or ResourceAttributes['host.name'] without writing bracket notation. Makes infrastructure metrics explorable without SQL.

4. Out-of-the-Box OTel + K8s Dashboards

The plugin will ship importable JSON dashboards covering:

Log volume by severity with per-service breakdown
Trace duration distribution and service dependency map
Per-service RED metrics (request rate, error rate, duration)
Top spans visibility
Kubernetes observability (namespaces, pods, nodes)

Goal: from data ingestion to usable dashboards in minutes, not hours of manual panel building.

5. Compact Search-First Mode

A new query mode resembling the ClickStack UI — a search bar with filter pills, no SQL required for common tasks:

Click + / - on any field value in a log detail panel to instantly add include/exclude filters
Select text within a log body to add a "line contains" full-text filter (backed by hasToken())
Facet autocomplete for column names, operators, and values
SQL preview pane shows the generated query live — "Edit as SQL" button opens the editor pre-populated

Aimed at operators and on-call engineers who need to investigate quickly without knowing ClickHouse SQL.

10. Data Retention & Tiered Storage

The Cost Driver — Retention × Volume

Elasticsearch cost is roughly: daily_volume_GB × retention_days × cost_per_GB_per_day

ClickHouse changes the equation at two levels:

Compression: 1TB of raw logs → ~200GB in ClickHouse (5x smaller end-to-end; 16x on column files — ClickHouse/TextBench)
Tiered storage: Hot recent data on SSD, cold older data on cheap S3

TTL — Simple One-Line Retention

-- Delete logs older than 90 days — runs automatically in background
ALTER TABLE otel.otel_logs
    MODIFY TTL toDateTime(Timestamp) + INTERVAL 90 DAY;

-- Different retention per severity.
-- ClickHouse evaluates TTL expressions in order and applies the FIRST one whose
-- WHERE clause matches a row. Put the most-specific (longest-retained) classes
-- first; the no-WHERE clause acts as the default fallback.
ALTER TABLE otel.otel_logs
    MODIFY TTL
        toDateTime(Timestamp) + INTERVAL 365 DAY DELETE WHERE SeverityText = 'CRITICAL',  -- critical: 1 year
        toDateTime(Timestamp) + INTERVAL  90 DAY DELETE WHERE SeverityText = 'ERROR',     -- errors:   90 days
        toDateTime(Timestamp) + INTERVAL   7 DAY DELETE;                                  -- default:   7 days

Tiered Storage — Hot/Cold/Archive

-- Storage policy: SSD (0-7 days) → S3 (7-90 days) → delete (90+ days)
-- Define in storage_configuration in config.xml:

-- hot:  /var/lib/clickhouse/data  (local SSD, fast)
-- cold: s3://clickhouse-cold-tier/ (S3, cheap)

ALTER TABLE otel.otel_logs
    MODIFY TTL
        toDateTime(Timestamp) + INTERVAL 7 DAY TO VOLUME 'cold',    -- move to S3
        toDateTime(Timestamp) + INTERVAL 90 DAY DELETE;             -- delete

Storage Comparison

Real benchmark numbers from ClickHouse/TextBench (identical hardware: AWS m6i.8xlarge, OTEL log data):

Dataset	Elasticsearch	ClickHouse	Ratio
1B rows	245 GB	49 GB	5x smaller
10B rows	~1.2 TB	~245 GB	5x smaller
50B rows	12 TB	2.4 TB	5x smaller
Column file compression	—	16x	—

Why Elasticsearch Uses 5x More Storage — Component Breakdown

The 5x gap is not just compression. It comes from four on-disk structures that Elasticsearch maintains but ClickHouse either handles more efficiently or doesn't need at all (at 50B rows):

Storage Component	Elasticsearch	ClickHouse	Why the difference
Columnar storage (doc_values / column files)	5.02 TiB	1.92 TiB	CH chains codecs per column (Delta+ZSTD, GCD) vs ES generic compression
Inverted index	3.37 TiB	515 GiB	CH inverted index is designed for analytics granules, not Lucene doc IDs
Stored fields (`_source`)	3.00 TiB	0	ES stores original JSON to reconstruct documents; CH reconstructs from columns directly
Points + norms (BKD trees, relevance scoring)	617 GiB	0	ES maintains numeric range indexes and per-doc relevance weights; CH uses sparse primary index (320 MiB)
Total	12.01 TiB	2.43 TiB	5x smaller

The two biggest drivers of Elasticsearch's overhead:

_source (3 TiB) — a near-complete second copy of all log data stored as compressed JSON so Elasticsearch can reconstruct the original document on retrieval. ClickHouse has no equivalent because it reconstructs rows directly from individual column files.
Points + norms (617 GiB) — BKD-tree numeric range indexes and per-document relevance scoring metadata. Useful for web search ranking; irrelevant for observability queries. ClickHouse's entire sparse primary index for 50B rows is 320 MiB.

Ingestion speed (50B rows):

System	Time
ClickHouse	Under 4 hours
Elasticsearch	~5 days (after pipeline tuning)

Estimated storage at this scale:

Scenario	Elasticsearch	ClickHouse
90-day retention	~9TB on SSD	~1.8TB on SSD (or much less on S3)
1-year retention	~36TB	~7TB on S3 (cheap)
Storage cost/year	~$100K+	~$2-5K

11. AI Layer — Natural Language Over Logs and Traces

The Unique Advantage

Since ClickHouse already hosts the Agentic AI Platform (LibreChat + Qwen + MCP), observability data in the same cluster is automatically queryable via plain English. No additional setup.

Example AI Queries Over Logs

User: "Which services had the most errors in the last hour?"
→ SQL: SELECT ServiceName, count() FROM otel_logs WHERE SeverityText='ERROR'
        AND Timestamp >= now()-INTERVAL 1 HOUR GROUP BY ServiceName ORDER BY count() DESC

User: "Show me all traces where ClickHouse queries took more than 500ms today"
→ SQL: SELECT TraceId, ServiceName, Duration/1e6 AS ms, SpanAttributes['db.statement'] AS sql
        FROM otel_traces WHERE SpanAttributes['db.system']='clickhouse'
        AND Duration > 500000000 AND Timestamp >= today() ORDER BY Duration DESC

User: "Which users were affected by the trading-service errors between 2pm and 3pm today?"
→ SQL: SELECT l.LogAttributes['user_id'], u.kyc_status, count() as errors
        FROM otel_logs l JOIN mart_users u ON l.LogAttributes['user_id'] = u.username
        WHERE l.ServiceName='trading-service' AND l.SeverityText='ERROR'
        AND l.Timestamp BETWEEN today()+toIntervalHour(14) AND today()+toIntervalHour(15)
        GROUP BY 1,2 ORDER BY errors DESC

mart_users is the consolidated user mart from the existing data platform — KYC status, country, account tier, registration date — not part of this observability migration. The point is that observability data (logs/traces in otel.*) and business data (marts.*) live in the same ClickHouse cluster and join in a single SQL statement. Replace mart_users with whatever your equivalent users table is.

The last query is impossible in Kibana — it crosses observability data (logs) with business data (users). In ClickHouse, it's one SQL query the AI generates automatically.

Business Glossary Additions for Observability

Add to business_glossary.yaml:

# Observability terms
- "error logs" = otel_logs WHERE SeverityText = 'ERROR'
- "slow query" = otel_traces WHERE SpanAttributes['db.system'] IN ('clickhouse','postgresql') AND Duration > 500000000
- "HTTP 5xx" = otel_logs WHERE LogAttributes['http.status_code'] LIKE '5%'
- "trade service" = ServiceName = 'trading-service'
- "trace" = otel_traces WHERE TraceId = '<id>'
- "latency" = Duration / 1e6 (milliseconds)
- "p99" = quantile(0.99)(Duration) / 1e6
- "error rate" = countIf(SeverityText='ERROR') / count() * 100

12. Migration Plan — Zero Downtime Cutover

Option A — Standard OTEL Collector (Recommended for Simple Setups)

If you run a small number of centralized OTEL Collectors (one per environment), the standard approach is sufficient — edit the collector config YAML to add the ClickHouse exporter alongside the existing Elasticsearch exporter for dual-write. No extra tooling needed.

Option B — BindPlane (Recommended for Large Collector Fleets)

If you run OTEL Collectors on many individual servers/services, BindPlane is worth considering. It is a centralized management platform for OTEL Collector fleets — instead of editing YAML configs on each server manually, you manage all collector configurations from one dashboard.

Without BindPlane:
  Edit otel-collector.yaml on server 1
  Edit otel-collector.yaml on server 2
  Edit otel-collector.yaml on server N
  Restart each collector
  ...

With BindPlane:
  Add ClickHouse destination once in BindPlane UI
  Roll out to entire fleet in one click

What BindPlane adds for this migration:

Capability	Value
Central config management	One change pushes to all collectors instantly
Dual-write in one click	Route to Elasticsearch AND ClickHouse simultaneously without touching individual collectors
Service-by-service cutover	Route `trading-service` logs to ClickHouse first, validate, then add more services gradually
Severity-based routing	Route ERROR logs to Elasticsearch (keep during validation), INFO/DEBUG to ClickHouse only
Safe fleet rollout	Progressive rollout with automatic rollback on failure
130+ sources and destinations	Supports standard OTEL ClickHouse exporter as destination

BindPlane for self-managed ClickHouse:

BindPlane's native "ClickStack destination" connects to ClickHouse Cloud managed product. For self-managed ClickHouse, use the standard OTEL clickhouseexporter as a generic OTLP destination in BindPlane — same outcome, slightly more manual config. Example BindPlane destination config:

# BindPlane destination config for self-managed ClickHouse
destination:
  type: otlp
  name: clickhouse-self-managed
  config:
    endpoint: tcp://clickhouse.internal:9000
    headers:
      - key: x-clickhouse-database
        value: otel
    tls:
      insecure: false

Reference: https://clickhouse.com/blog/bindplane-faster-otel-migrations-to-clickstack

Phase 1 — Deploy & Validate (Week 1-2)

Task	Details
Deploy ClickHouse schema	Create otel_logs, otel_traces, otel_metrics tables
Add ClickHouse exporter to OTEL Collector	Dual-write: send to both Elasticsearch AND ClickHouse (via YAML edit or BindPlane)
Set up Grafana	Install ClickHouse datasource, build core dashboards
Validate data parity	Compare row counts, spot-check log content
Test trace waterfall	Pick 5-10 real TraceIds, verify waterfall in Grafana matches Kibana

Phase 2 — Parallel Run (Week 3-4)

Task	Details
Run both stacks simultaneously	Elasticsearch + ClickHouse receive same data
Migrate dashboards	Rebuild all Kibana dashboards in Grafana
Migrate alerts	Recreate all Kibana alerts in Grafana Alerting
Train teams	Grafana walkthrough for each team
Build AI log queries	Add observability terms to business glossary
Gradual service cutover (optional)	Use BindPlane to route one service at a time to ClickHouse-only, validate, expand

Phase 3 — Cutover (Week 5)

Task	Details
Confirm all dashboards working in Grafana	Sign-off from each team
Remove Elasticsearch exporter from OTEL Collector	Single line config change (or one click in BindPlane)
Verify ClickHouse-only flow	24-hour monitoring window
Cancel Elasticsearch subscription	After 48-hour clean run

Phase 4 — Optimise (Week 6+)

Task	Details
Tune TTL and tiered storage	Configure S3 cold tier based on actual usage patterns
Enable AI log queries in LibreChat	Add observability glossary, test with teams
Set up cross-data dashboards	Logs + business data correlation panels in Grafana
Performance tuning	Review slow queries via system.query_log, tune ORDER BY keys if needed

Risk Mitigation During Migration

Dual-write period: Both systems receive data simultaneously for 4 weeks — no data loss risk
Rollback: Removing ClickHouse exporter (or reverting BindPlane config) restores Elasticsearch-only in seconds
No application changes: OTEL SDK configuration is unchanged throughout
Gradual cutover: Cut over one service at a time using BindPlane routing rules if preferred

13. Cost Analysis

Current Elasticsearch Cost Breakdown

Component	Annual Cost
Elasticsearch managed service (compute)	~$200K
Storage (SSD, replicated)	~$100K
Licensing (Elastic managed / premium)	~$50K
Engineering time (ops, tuning, ILM)	~$50K
Total	~$400K

ClickHouse Cost (Self-Hosted)

Component	Annual Cost
ClickHouse nodes (2x m7g.4xlarge, Graviton3)	~$18K
Storage SSD (hot, last 7 days)	~$3K
S3 (cold, 7-90 days)	~$2K
ClickHouse Keeper (3x t3.small for consensus)	~$2K
Engineering time (minimal ops)	~$10K
Total	~$35K

ClickHouse Cost (ClickHouse Cloud — Recommended Start)

Component	Annual Cost
ClickHouse Cloud (auto-scaling)	~$36-60K
S3 tiered storage	Included
Engineering time	~$5K (fully managed)
Total	~$41-65K

Savings Summary

Scenario	Annual Cost	Saving vs Elasticsearch
Current (Elasticsearch)	$400K	—
ClickHouse Cloud	$41-65K	$335-359K saved (84-90%)
ClickHouse Self-Hosted	$35K	$365K saved (91%)

Additional Value (Not Counted in Savings)

AI queries over logs — eliminates ad-hoc log digging by engineering (~20 hrs/month)
Cross-data correlation — compliance/risk can correlate errors with affected users instantly
Longer retention — at ClickHouse costs, retain 1 year vs 90 days for same budget
Unified cluster — observability + business data in one system, one operations team

14. Risk Assessment

Risk	Likelihood	Impact	Mitigation
Full-text search gaps	Low	Low	95% of queries are structured; bloom filters cover keyword search
Data loss during migration	Very Low	High	4-week dual-write window eliminates risk
Grafana learning curve	Medium	Low	Grafana is widely used; team familiarity is high
ClickHouse cluster instability	Low	High	2-replica HA, Keeper for consensus, daily S3 backups
OTEL Collector overload	Low	Medium	Batch processor + memory limiter configured; scale collector horizontally
Schema changes in new service	Low	Low	MergeTree handles new columns gracefully; OTEL schema is stable
Cold data access latency (S3)	Medium	Low	S3 queries are slower but acceptable for historical lookups

15. Success Metrics

30-Day Targets (Post Cutover)

Metric	Target
All dashboards migrated to Grafana	100%
Elasticsearch subscription cancelled	Done
Log query latency (aggregation)	< 500ms for last 24h queries
Trace lookup latency	< 2 seconds for TraceId lookup
Data compression vs Elasticsearch	> 5x smaller footprint
Alert parity	All Kibana alerts recreated in Grafana

90-Day Targets

Metric	Target
Annual cost reduction	> $300K vs Elasticsearch baseline
AI log queries active	Teams using LibreChat for log analysis
Cross-data queries	At least 5 dashboards joining logs + business data
Retention extended	From 90 days to 180+ days (same cost)
Engineering time saved	20+ hrs/month (no more ad-hoc log queries)

16. Reference Links

ClickHouse Observability

Resource	URL
ClickHouse Observability docs	https://clickhouse.com/docs/use-cases/observability
Observability solution guide	https://clickhouse.com/docs/use-cases/observability/overview
Do you still need Elasticsearch for log analytics?	https://clickhouse.com/blog/elasticsearch-log-analytics-clickhouse
Building an Observability solution with ClickHouse — Part 1 (Logs)	https://clickhouse.com/blog/storing-log-data-in-clickhouse-fluent-bit-vector-open-telemetry
Full-text search on object storage	https://clickhouse.com/blog/clickhouse-full-text-search-object-storage
gitTrends — 10B GitHub events with text index (reference impl)	https://github.com/ClickHouse/gitTrends

OpenTelemetry + ClickHouse

Resource	URL
OTEL Collector ClickHouse exporter	https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/exporter/clickhouseexporter
OTEL Collector contrib repo	https://github.com/open-telemetry/opentelemetry-collector-contrib
OTEL ClickHouse schema reference	https://clickhouse.com/docs/use-cases/observability/schema-design

Grafana + ClickHouse

Resource	URL
Grafana ClickHouse datasource	https://grafana.com/grafana/plugins/grafana-clickhouse-datasource
Grafana ClickHouse plugin docs	https://grafana.com/docs/grafana/latest/datasources/clickhouse

Benchmarks & Case Studies

Resource	URL
Cloudflare: ClickHouse for HTTP logs	https://blog.cloudflare.com/log-analytics-using-clickhouse
ClickHouse vs Elasticsearch log analytics benchmark	https://clickhouse.com/blog/elasticsearch-log-analytics-clickhouse
Benchmark source code (reproducible)	https://github.com/ClickHouse/TextBench
BindPlane — OTEL fleet management for migrations	https://clickhouse.com/blog/bindplane-faster-otel-migrations-to-clickstack
Langfuse + ClickHouse (LLM observability)	https://clickhouse.com/blog/langfuse-llm-analytics

Appendix A — Technology Stack Summary

Component	Technology	Purpose
Instrumentation	OTEL SDK (unchanged)	Auto-instrument apps — zero code changes
Collection	OTEL Collector + clickhouseexporter	Receives and ships logs/traces/metrics to ClickHouse
Storage	ClickHouse otel database	Logs, traces, metrics — compressed columnar storage
Visualization	Grafana + ClickHouse datasource	Dashboards, trace waterfall, log explorer, alerting
AI queries	LibreChat + Qwen + MCP (existing)	Plain English queries over logs and traces
Cold storage	S3 (tiered via ClickHouse TTL)	Cheap long-term retention for historical data
HA	ClickHouse 2-replica cluster	Same HA setup as the existing ClickHouse cluster

Appendix B — Quick Reference: Kibana → Grafana Equivalents

Kibana Feature	Grafana Equivalent
Discover (log search)	Explore → Logs panel with ClickHouse query
Dashboard	Dashboard (same concept)
Visualize	Panel with ClickHouse SQL query
APM (traces)	Explore → Traces panel with ClickHouse datasource
Alerts	Grafana Alerting (same or better)
Index Lifecycle Management	ClickHouse TTL (simpler — one SQL line)
KQL (Kibana Query Language)	SQL (standard, more powerful)
Lens (drag-drop charts)	Grafana panel builder

Closing thoughts

If you're considering this migration, the decisions that matter most:

Don't skip the OTEL Aggregator pattern. Agent-only loses data on ClickHouse blips. Run a couple of central aggregators with retry-on-failure — that's the production-grade choice.
Use BindPlane if you have a large collector fleet. Worth it for fleet-wide config rollout. For a handful of central collectors, standard YAML is fine.
Get the schema right the first time. ORDER BY (ServiceName, SeverityText, Timestamp) and the right CODECs are the difference between a 3× and 30× compression ratio. The schema in Section 6 has been validated at production scale.
Run dual-write for 4 weeks, not 1. The gradual cutover is cheap insurance and lets you validate every dashboard/alert before cutting Elasticsearch off.
The AI layer pays for itself. Plain-English log queries via LibreChat + an LLM means no more pinging engineering when the compliance team needs a one-off analysis. Once ClickHouse has the data, the AI integration is one config change.

The full schema, OTEL Collector configs, Grafana queries, migration plan, and 16 production-tested recovery runbooks live in the companion repo on GitHub. The repo also includes the AI DBA MCP server (152 tools for ClickHouse operations) — if you're operating at scale, that's worth a look.

If you've migrated off Elasticsearch (or are mid-migration), I'd love to compare notes. Reach out via LinkedIn or comment below.

If this is useful to your team, the deeper architectural piece — Building an Agentic AI Data Platform on ClickHouse — is coming next.

Forem: RAKESH THERANI

Four LLM Engines, One ClickHouse Cluster: An Agentic AI Architecture

Why This Exists

The problem in current operations

The fix

End-to-End Flow

Triage — How the Agent Chooses an Engine

It's semantic classification, not keyword matching

What the triage prompt actually contains

Real triage output for ambiguous cases

When confidence is low

The Four Engines (In Detail)

ENGINE 1 — ANALYTICS (~80% of all questions)

Worked example 1.1 — Trading volume + liquidation roll-up

Worked example 1.2 — Compliance cross-table screening

Worked example 1.3 — Trading bot performance breakdown

ENGINE 2 — NUMERIC RESEARCH LOOKUP (cached fitted models)

Worked example 2.1 — Volatility regime (NR-01)

Worked example 2.2 — Insurance fund coverage (NR-04)

Worked example 2.3 — VIP tier transitions (NR-08)

Worked example 2.4 — Almgren-Chriss impact (NR-19) — machine-to-machine

How the 20 NR cases tile the domain

ENGINE 3 — OPTIMIZATION (NL → math model → solver → reviewable plan)

Worked example 3.1 — Insurance fund allocation (Convex, CVXPY)

Worked example 3.2 — Compliance case routing (CP-SAT, OR-Tools)

Worked example 3.3 — Maker fee tier optimization (MIP with elasticity)

ENGINE 4 — DEEP RESEARCH (multi-step planner / workers / critic / synthesizer)

Worked example 4.1 — Q2 fee revenue variance attribution

Worked example 4.2 — Wash trading investigation

Self-Learning — Four Continuous Feedback Loops

Loop 1 — Glossary Expansion (1-day cycle)

Loop 2 — Mart Recommendation (nightly batch)

Loop 3 — LoRA Fine-Tuning (weekly)

Loop 4 — Eval-Set Regression Daemon (weekly automated)

Plus six supporting intelligences

The compound effect — 6-month trajectory

Dynamic vs Deterministic — Where Each Lives

Trust Mechanisms — Four-Layer Defense

By the Numbers

The Tech Stack

The Architectural Thesis (One Paragraph)

Building a Full-Stack Agentic AI Data Platform on ClickHouse: A Complete Architecture Guide

Why I'm writing this

Table of Contents

1. Executive Summary

Objective

The Problem Today

The Solution

What ClickHouse Already Provides

Expected Outcomes

2. What We Are Building

Before (Today)

After (With This Platform)

Who Uses It

3. Why ClickHouse

Production Proof Points in Crypto

ClickHouse vs PostgreSQL for Analytics

Key Advantage — All Data In One Place

4. Platform Architecture

Observability (Langfuse)

5. Data Consolidation — All DBs Into ClickHouse

The Core Idea

CDC Pipeline

Tables to Consolidate

Why ReplacingMergeTree

Pre-Aggregated Materialized Views

Cross-Database Query Example

6. How the AI Agent Works

The Flow

Key Point — It's All Structured Table Queries

7. What Questions Can Be Asked — By Team

Trading Operations

Risk Management

Compliance & Audit

Finance & Revenue

Product & Growth

Question Complexity Summary

8. How Multi-Table Queries Work (JOIN Problem & Solution)

The Challenge

Strategy 1 — Pre-Joined Denormalized Marts (Primary Approach)