In our [first](https://clickhouse.com/blog/building-a-data-warehouse-with-clickhouse) and [second](https://clickhouse.com/blog/building-a-data-warehouse-with-clickhouse-part-2) blog posts about building ClickHouse's internal data warehouse, we shared our journey from a small-scale analytics system to a comprehensive enterprise DWH serving as for now over 300 users with tens of data sources and 2.1 PB of compressed data. 

Today, I'm excited to share the next chapter of this evolution: how we transformed our traditional BI-first approach into an AI-first data warehouse that handles approximately 70% of our internal analytics use cases.

By the end of this blog post, you'll have learnt how to let your data warehouse users ask questions and get insights without writing a single line of SQL.

![image1.png](/uploads/image1_cc9857577a.png)

> Full disclosure: I was a complete AI skeptic just less than a year ago. With 15 years of experience in data warehousing, I had tried early LLMs with data sources and found the results disappointing and unreliable. The hallucinations, the lack of context awareness, and the inability to handle complex business logic made me dismiss AI as a viable solution for enterprise analytics. But sometimes, being wrong feels incredibly good.

## Before AI: The traditional BI workflow bottleneck

To understand the magnitude of this transformation, it's important to describe how our teams interacted with the DWH before AI. Despite having a robust data infrastructure with ClickHouse Cloud and Superset, the traditional workflow created significant friction between business questions and actionable insights:

* Our **Engineering team** spent considerable time writing complex SQL queries to investigate service health issues. A typical investigation into why a specific customer service was experiencing performance problems would require joining data across multiple tables \- service metadata from our Control Plane, query performance metrics from the Data Plane, scaling events from our autoscaler logs, and any related support tickets from Salesforce. An engineer would need to remember table schemas, write JOINs across 4-5 tables, apply correct date filters, and often iterate multiple times to get the right level of detail. What should be a 5-minute question ("What happened to service X last week?") became a 30-45 minute SQL writing exercise.

> What should be a 5-minute question became a 30-45 minute exercise.

* The **Sales team** faced similar challenges when prospects asked specific questions during calls. Simple requests like "How much is this customer consuming compared to similar companies in their industry?" required joining usage data with account information, filtering by company size and vertical, calculating percentiles, and often building quick visualizations. Sales engineers would either interrupt analyst time for urgent requests or spend their own time learning our data model instead of focusing on customer relationships.

* Our **Product team** had the most complex analytical needs, often requiring deep cohort analysis and feature usage research. Understanding customer retention patterns meant writing sophisticated window functions across multiple data marts, calculating complex metrics like "time to first value" or "feature adoption curves," and often creating temporary staging tables for multi-step analyses. A single product insight like "Which onboarding steps correlate with higher 90-day retention?" could take a senior analyst an entire day to properly investigate.

> A single product insight could take a senior analyst an entire day to properly investigate.

* The **Finance team** regularly needed to prepare board reports and investor materials, requiring precise calculations of MRR, churn rates, customer lifetime value, and revenue forecasts. These analyses demanded not just technical SQL skills but deep understanding of our business logic \- how credits convert to revenue, how different pricing plans should be treated, which organizations to exclude from calculations. The margin for error was zero, meaning extensive quality checks and manual validation of every number.

* Finally, our **Cost Optimization** team conducted some of the most complex analyses in our DWH, correlating our ClickHouse Cloud service consumption with underlying AWS, GCP, and Azure costs. Understanding whether a spike in customer usage was efficiently handled by our autoscaling or resulted in cost overruns required joining billing data from multiple cloud providers, mapping it to customer services, and analyzing the efficiency of our resource allocation algorithms. These investigations were critical for maintaining healthy unit economics but required deep expertise in both our internal systems and cloud provider billing models.

The result was a classic BI bottleneck: either teams developed advanced SQL skills (reducing focus on their core responsibilities), or they created a queue of requests for our small analyst team. Even with Superset's user-friendly interface, the underlying complexity of our data model and business logic made self-service analytics largely theoretical for most users.

## The transformation catalyst

Even with the described bottleneck, our DWH was BI-centric without any parts of the workload transferred to AI. The fundamental barriers to AI adoption in enterprise data environments were seemingly insurmountable: unreliable model outputs, lack of standardized data source integrations, and the inability to maintain business context across complex analytical workflows. Before late 2024, attempting to use LLMs for serious data warehouse operations was more likely to create confusion than insights. Several technological advances converged in late 2024 that fundamentally changed the AI-for-data landscape:

### Model quality breakthrough

The release of Anthropic's Claude 3.5 Sonnet, and subsequently Claude 3.7 and 4.0, marked a significant leap in LLM capabilities for technical tasks. Unlike previous models, Claude demonstrates remarkable proficiency in:

* Writing complex SQL queries with JOINs, window functions, and database-specific syntax 
* Self-correcting query errors based on database feedback 
* Understanding table sizes and writing optimized queries with appropriate filters 
* Generating interactive charts and visualizations on-the-fly 
* Taking into account large portions of business context from multiple data sources

![Agentic tool use benchmark](/uploads/Claude_3_7_Sonnet_b5cc0d6a3d.webp)

*Image from the [Claude 3.7 release blog post](https://www.anthropic.com/news/claude-3-7-sonnet)*

### MCP: The missing integration layer

In November 2024, Anthropic released the [Model Context Protocol (MCP)](https://github.com/modelcontextprotocol) \- an open-source standard for connecting LLMs to external data sources and tools. This protocol solved our biggest challenge: providing reliable, structured access to multiple data sources without vendor lock-in. Within weeks of its release, hundreds of MCP servers became available, covering everything from databases to file systems to APIs. Recognizing the potential of this new protocol, we quickly developed our [own ClickHouse MCP server](https://github.com/ClickHouse/mcp-clickhouse), making it even easier for organizations to connect their ClickHouse instances to LLMs.

<iframe width="768" height="432" src="https://www.youtube.com/embed/y9biAm_Fkqw?si=VFrXvxGHvQlQziPD" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Building the foundation: More than just connecting databases

Connecting an LLM to your data warehouse is the easy part. Building a reliable, enterprise-grade AI-first analytics platform requires addressing several critical components:

### Comprehensive business glossary

Data without context is useless, and this is exponentially true for LLMs. We already had a robust internal data wiki built with [MDBook](https://rust-lang.github.io/mdBook/) that documents every significant field across our data marts, including:

* Field definitions and business context 
* Possible values and data ranges 
* Business processes that generate the data 
* Relationships between entities across different source systems

The key was making this glossary LLM-accessible through GitHub and filesystem MCP server integrations, allowing our AI assistant to reference documentation in real-time.

### Enhanced data quality processes

While human analysts can often work around data quality issues, LLMs amplify them dramatically. A missing customer\_id that a human would flag as problematic could lead an LLM to generate completely incorrect analyses. Even before the AI era, we invested heavily in:

* Automated data validation rules 
* Comprehensive data lineage tracking 
* Real-time data quality monitoring 
* Clear data freshness indicators

Most of these tasks are executed seamlessly inside DBT models when building the data.

### Enterprise-grade, self-hosted LLM interface

Following our principle of avoiding vendor lock-in, we needed a self-hosted solution that could:

* Work with multiple LLM providers 
* Support MCP server integration 
* Provide enterprise features like SSO and audit logging 
* Generate and display visual artifacts 
* Enable chat sharing and collaboration

After evaluating multiple options, we selected [LibreChat](https://github.com/danny-avila/LibreChat), an open-source ChatGPT alternative that met all our requirements while maintaining the flexibility to switch LLM providers as needed.

## Architecture: DWAINE is born

Meet **DWAINE (Data Warehouse AI Natural Expert)** - our internal AI assistant that has fundamentally changed how we interact with our data warehouse.

![Image 506552983 3512x1808.jpg](/uploads/Image_506552983_3512x1808_c410690871.jpg)

*Fig. 1 - DWAINE architecture*

The architecture consists of:

| Component | Technology | Purpose |
| :---- | :---- | :---- |
| UI Layer | LibreChat | User interface, chat management, artifact rendering |
| LLM Provider | Anthropic Claude 4.0 | Natural language processing, SQL generation, and analysis. We use the LLM through AWS Bedrock for billing consolidation, cross-region inference, etc. |
| Integration Layer | MCP Servers | ClickHouse MCP server for accessing the DWH GitHub MCP server for getting context from DBT models and business glossary Filesystem MCP server for getting the dynamic column dictionary |
| Data Warehouse | ClickHouse Cloud | Main analytical database |
| Documentation | GitHub \+ filesystem | Business glossary, field definitions, process docs |

### Security and privacy considerations

DWAINE has access to a limited number of primary data marts containing:

* Anonymized service usage metrics 
* Credit consumption and billing data 
* Internal sales and operational metrics

Critically, DWAINE never accesses:

* Customer PII (names, emails, addresses) 
* Customer data (all customer data in ClickHouse Cloud is encrypted) 
* Sensitive internal communications or strategic information

This security model follows our existing DWH principles where we collect metadata about database operations without ever accessing the actual customer data.

## Real-world performance

Six months after deployment, DWAINE's adoption metrics are compelling, but the qualitative impact has been even more transformative. DWAINE has dramatically lowered the barrier to entry for data exploration, enabling non-technical users across sales, finance, and operations to get immediate answers to business questions without writing a single line of SQL or understanding our complex charts and dashboards. This democratization of data access has reduced pressure on our three-person DWH team by approximately 50-70%, freeing up our analysts to focus on complex strategic analysis rather than fielding routine data requests. The impact is particularly pronounced with our Sales and Support teams, who frequently ask relatively simple but urgent questions.

Currently, DWAINE is used by more than 250 internal users, that send \>200 daily messages in 50-70 daily conversations.

### Types of queries DWAINE excels at

Here are some real examples of questions our teams ask DWAINE daily:

* *"Show top 5 services by memory utilization in February 2025"* 
* *"Build a monthly forecast for data stored in AWS us-east-1 based on the last 3 months, take into account seasonality"* 
* *"Show an MRR chart for the last 5 months stacked by salesperson. Show first 5 sales reps by revenue, hide others in 'Other'. Exclude EMEA"* 
* *"Find the customer with the largest number of insert queries in 2025 and build a monthly bar chart showing the number of select queries made by this customer stacked by service name"*

What's remarkable is that these queries often require multiple JOINs across different data marts, complex aggregations, and business logic that would typically take an analyst 15-30 minutes to write and validate.

![image1.png](/uploads/image1_cc9857577a.png)

*Fig. 2 - Example answer and a visual*

## The 70/30 split: What stays in traditional BI

Despite DWAINE's success, approximately 30% of our analytics workload remains in Superset and other traditional BI tools. These include:

### Repeatable metrics

In general, these are standardized reports and dashboards that teams access regularly with no variation in the underlying questions or presentation format. When you need the same metric displayed the same way multiple times per day or week, there's no advantage to asking an AI assistant to regenerate identical charts when a bookmarked dashboard provides instant, consistent access to the information. 
On the other hand, such questions and metrics do not overload our DWH team, as internal users can check them independently. 

### Certified financial metrics

* Board-level KPIs that require formal certification 
* Regulatory reporting metrics 
* Metrics with complex, multi-stakeholder approval processes

### Deep operational dashboards

* Real-time operational monitoring for SRE teams 
* Complex system health dashboards 
* Multi-dimensional performance tracking interfaces

### Specialized technical analysis 

* Advanced statistical analysis requiring specific visualization libraries 
* Custom interactive applications built on our data 
* Integration with external specialized tools

Also, we ask our internal users not to make really important decisions based on DWAINE’s analysis only. If a really important decision must be made, we ask users to ask DWAINE to write a SQL query to prove the results of his research with comprehensive comments. The user could then take this query to a traditional ad-hoc SQL tool and check DWAINE’s logic.

## Lessons learned and best practices

### What worked well

* **ClickHouse Cloud's performance was crucial:** Sub-second query response times maintain the illusion of conversing with a human analyst 
* **Comprehensive documentation pays dividends**: Our existing MDBook wiki reduced hallucinations dramatically 
* **MCP standardization**: Using open standards prevented vendor lock-in and enabled easy integration expansion 
* **Gradual rollout**: Starting with power users and expanding based on feedback improved adoption

### Challenges we faced

* **Context window management:** Large schemas require careful prompt engineering to maintain relevant context 
* **Error handling:** Building graceful degradation when LLM queries fail or produce unexpected results. Users should not rely on AI solely when making important decisions

## Conclusion

The transformation from BI-first to AI-first analytics has been one of the most impactful changes in our data culture at ClickHouse. DWAINE hasn't just changed how we query data \- it's fundamentally altered how we think about data, making analytics more accessible, conversational, and integrated into daily decision-making.

For organizations still relying primarily on traditional BI tools, the question isn't whether AI will transform analytics, but how quickly you can adapt to stay competitive. The technical foundations are now mature, the tools are available, and the business case is compelling.

Learn how we evolved our internal 2.1 PB data warehouse from traditional BI to an AI-first analytics platform

large_Blog Banner AI-first ClickHouse.jpg

medium_Blog Banner AI-first ClickHouse.jpg

small_Blog Banner AI-first ClickHouse.jpg

thumbnail_Blog Banner AI-first ClickHouse.jpg

Blog Banner AI-first ClickHouse.jpg

How we made our internal data warehouse AI-first 

![Blog_IntroducingAgentHouse_202504_FNL.png](/uploads/Blog_Introducing_Agent_House_202504_FNL_0639a4c186.png)

## AgentHouseのご紹介

2024年にAnthropic社が[MCPプロトコル](https://docs.anthropic.com/en/docs/agents-and-tools/mcp)をリリースしてから数週間後、ClickHouseの統合チームは、Anthropic社のSonnetモデルがClickHouseデータベースにアクセスする小規模な社内デモを披露しました。これは、ランダムなデータに対して単純なクエリを実行し、LLMに結果を返すという非常に基本的な統合でした。

ClickHouseの社内DWHチームリーダーとして、そのデモを見た私はすぐにこれを[私のデータウェアハウス](https://clickhouse.com/blog/building-a-data-warehouse-with-clickhouse)に導入したいと思いました。ClickHouseの素晴らしい社内ユーザー（営業、運用、製品、財務、エンジニアリングの各チーム）には、従来のBIツールを使ったりクエリを実行したりする代わりに、データと対話できるようになってほしいのです。

2ヶ月後、私たちはDwaine（Data Warehouse AI Natural Expert）を立ち上げました。これは、社内ユーザーがデータに基づいて質問に答えるのを支援する社内LLMです。「私たちの収益は？」「この顧客は何をしているのか？」「顧客は今どんな問題に直面しているのか？」「ウェブサイトの訪問者数は？コンバージョン率は？」Dwaineは、社内ユーザーがこれらの洞察やその他の情報を得るのに劇的に役立ちました。[LinkedInでの私の小さな個人的な記事](https://www.linkedin.com/pulse/bi-dead-change-my-mind-dmitry-pavlov-2otae)をご覧になった方もいるかもしれません。

この経験を説明した後、多くの人々が私に連絡を取り、デモを依頼してきました。数人の友人やパートナーにDwaineを実演しましたが、彼らは非常に興奮していたものの、機密情報を扱っていたためDwaineと直接対話することができず、その可能性を十分に体験できていないと感じました。

このようにして、[llm.clickhouse.com](https://llm.clickhouse.com)で利用可能なAgentHouseが構築されました。しかし、彼に自己紹介をしてもらいましょう :) これ以降のテキストは、AgentHouse LLMによって書かれています。

## こんにちは、AgentHouseです！

私は[AgentHouse](https://llm.clickhouse.com)です。ClickHouseのリアルタイム分析能力と大規模言語モデルの強力な組み合わせを紹介する、完全に対話型のデモ環境です。私の名前は「Agent」（LLMエージェントを表す）と「House」（ClickHouseから）を組み合わせたもので、これらのテクノロジーがどのようにシームレスに連携するかを強調しています。他のデモ環境（[ClickHouse SQL Playground](http://sql.clickhouse.com)および[ADSBビジュアライザー](https://adsb.exposed/)）と共に、アカウントを作成したりデータをアップロードしたりすることなく、さまざまな実際のシナリオでClickHouse Cloudデータベースを試すことができます。

<img preview="/uploads/agent_house_v3_7e163b96ca.gif" src="/uploads/agent_house_high_res_1518b84bbd.gif" alt="agenthouse.gif" class="h-auto w-auto max-w-full" style="width: 100%;">

## 私の構成要素は何ですか？

これらが私の主要な構成要素です。

1. **[Anthropicの大規模言語モデル Claude Sonnet](https://www.anthropic.com/claude/sonnet)** - このLLMは、複雑なコンテキストの理解と構造化データに関する推論に特に優れており、ClickHouseの分析能力にとって理想的なパートナーです。データベーススキーマを理解し、正確なSQLを生成し、クエリ結果を解釈するモデルの能力は、ClickHouseと高度なLLMがなぜ自然な組み合わせであるかを示しています。

2. **[LibreChat UIプロジェクト](https://www.librechat.ai)** - 人気のあるLLMをすぐに利用できるオープンソースのLLM UIです。オープンソースであること、クリーンなデザイン、そして成長するコミュニティサポートのため、ユーザーインターフェースとしてLibreChatを選択しました。また、このデモの構築にご協力いただいたLibreСhatチームにも感謝いたします。

3. 私の秘密のソースは、ClickHouseチームが開発した **[ClickHouse MCP](https://github.com/ClickHouse/mcp-clickhouse)**（Model Context Protocol）サーバーです。この専用サーバーは、ClickHouseデータベースと大規模言語モデルの間の橋渡しとして機能し、以下を可能にします。

* ClickHouseとLLM間の効率的なデータ転送
* LLMが生成したSQLのインテリジェントなクエリ最適化
* データに関するステートフルな会話のためのコンテキスト管理
* データベースリソースへの安全で制御されたアクセス
* さまざまな公開データセットの合理化された処理

4. **[ClickHouse Cloudデータベース](https://clickhouse.com)** - ClickHouseデータベースをSaaS（Software-as-a-Service）として提供するフルマネージドのクラウドサービスです。

![Images_PoweringAIAgentsAnalytics_202504_FNL(1).png](/uploads/Images_Powering_AI_Agents_Analytics_202504_FNL_1_54be5a6747.png)

## なぜSonnetとLibreChatなのですか？

AnthropicのSonnetモデルは、LLMの能力、特に複雑なコンテキストの理解と構造化データに関する推論において大きな進歩を示しており、ClickHouseの分析能力にとって理想的なパートナーです。データベーススキーマを理解し、正確なSQLを生成し、クエリ結果を解釈するモデルの能力は、ClickHouseと高度なLLMがなぜ自然な組み合わせであるかを示しています。

オープンソースであること、クリーンなデザイン、そして成長するコミュニティサポートのため、ユーザーインターフェースとしてLibreChatを使用しています。このインターフェースにより、ユーザーはデータについて自然な会話をしたり、視覚的な成果物（チャート、テーブルなど）を作成したりすることができ、SQLの知識がない人でも複雑な分析タスクにアクセスできるようになります。

## 私の目的


私は、ユーザーがMCPサーバーを介してClickHouseがLLMアプリケーションの理想的なバックエンドとしてどのように機能するかを試すためのテストグラウンドとして特別に作成されました&#x1F609;。さまざまなユースケースを紹介する複数の公開データセットにアクセスでき、簡単な会話型インターフェースを通じて可能性を探ることができます。これには、以下を含む37の異なるデータセットが含まれます。


* **github** - GitHubのアクティビティデータ、リポジトリ、ユーザーインタラクションが含まれています。毎時更新されます。
* **pypi** - `pip`でダウンロードされたすべてのPythonパッケージの行が含まれ、毎日更新されます - 1.3兆行以上。
* **rubygems** - インストールされたすべてのgemの行が含まれ、毎時更新されます - 1800億行以上。
* **hackernews** - Hacker Newsの投稿とコメントが含まれています。
* **imdb** - IMDBの映画データベース情報が含まれています。
* **nyc_taxi** - NYCのタクシー乗車データが含まれています。
* **opensky** - OpenSky Networkの航空データが含まれています。
* **reddit** - Redditの投稿とコメントが含まれています。
* **stackoverflow** - Stack Overflowの質問と回答が含まれています。
* **uk** - 英国の不動産取引データと関連する地理情報の包括的なコレクションが含まれています。

その他。

## 私の主な機能

* 自然言語クエリのテスト: 通常の英語の質問がMCPサーバーを介してClickHouse用に最適化されたSQLクエリに変換される様子をご覧ください。
* リアルタイム分析の体験: MCPサーバーがClickHouseの有名な速度を最小限の遅延でAI搭載の洞察と組み合わせる方法をご覧ください。
* 対話型データ探索: MCP-LLM接続を利用した会話型インターフェースを通じてデモデータセットを探索してください。
* 自動視覚化の表示: MCPサーバーを流れるデータがどのように自動的に視覚化されるかをご覧ください。

## デモの探索

AgentHouseを始めるには、[llm.clickhouse.com](https://llm.clickhouse.com)にアクセスし、Googleアカウントでデモ環境にログインして質問を開始してください。始めるのに最適な方法は、「どのデータセットがありますか？」と尋ねることです。これにより、データベースのリストが表示され、それらを探索し始めることができます。

皆様からのご質問にお答えできることを楽しみにしています！

AgentHouseをご紹介します。これは、ClickHouseのリアルタイム分析とAnthropicのLLMをMCPサーバー経由で組み合わせた対話型のパブリックデモで、誰もが自然言語を使ってデータと対話する方法を紹介するために構築されました。

large_Blog_IntroducingAgentHouse_202504_FNL_faff69.png

medium_Blog_IntroducingAgentHouse_202504_FNL_faff69.png

small_Blog_IntroducingAgentHouse_202504_FNL_faff69.png

thumbnail_Blog_IntroducingAgentHouse_202504_FNL_faff69.png

Blog_IntroducingAgentHouse_202504_FNL_faff69.png

 AgentHouseのご紹介

![Blog_IntroducingAgentHouse_202504_FNL.png](/uploads/Blog_Introducing_Agent_House_202504_FNL_0639a4c186.png)

## Introducing AgentHouse

A few weeks after Anthropic released its[ MCP protocol](https://docs.anthropic.com/en/docs/agents-and-tools/mcp) in 2024, the ClickHouse integrations team showed a small internal demo of Anthropic’s Sonnet model accessing a ClickHouse database. It was a very basic integration involving running a simple query against random data and getting a result to the LLM.

As an internal DWH team lead at ClickHouse, once I saw the demo, I immediately wanted to have this in[ my Data Warehouse](https://clickhouse.com/blog/building-a-data-warehouse-with-clickhouse). I want my lovely internal users (sales, ops, product, finance, and engineering teams at ClickHouse) to be able to talk to the data instead of using the traditional BI tool or running queries.

Two months later, we launched Dwaine (Data Warehouse AI Natural Expert) - an internal LLM that helps internal users answer their questions based on data. What is our revenue? What is this customer doing? What problems do our customers face right now? How many visitors do we have on our website, and what is our conversion rate? Dwaine dramatically helped our internal users to get those and other insights. You may have seen my[ small personal article in LinkedIn](https://www.linkedin.com/pulse/bi-dead-change-my-mind-dmitry-pavlov-2otae).

After I described this experience, many people reached out to me and asked for a demo. I demonstrated Dwaine to a few friends and partners, but though they were super excited, I felt they could not experience its full potential as they could not talk to Dwaine by themselves because it worked with confidential information.

This is how AgentHouse, available at [llm.clickhouse.com](https://llm.clickhouse.com), was built. But let him introduce himself :) All further text is written by the AgentHouse LLM.

## Hi, I’m AgentHouse!

I'm [AgentHouse](https://llm.clickhouse.com) - a fully interactive demo environment that showcases the powerful combination of ClickHouse's real-time analytics capabilities with large language models. My name combines "Agent" (representing the LLM agent) and "House" (from ClickHouse), highlighting how these technologies work together seamlessly. Together with other demo environments ([ClickHouse SQL Playground](http://sql.clickhouse.com) and [ADSB visualizer](https://adsb.exposed/)), I allow you to try the ClickHouse Cloud database in different real-world scenarios without creating an account or uploading any data.

<img preview="/uploads/agent_house_v3_7e163b96ca.gif" src="/uploads/agent_house_high_res_1518b84bbd.gif" alt="agenthouse.gif" class="h-auto w-auto max-w-full" style="width: 100%;">

:::global-blog-cta:::

## What am I made of?

These are my main body parts:

1. **[Anthropic’s large language model Claude Sonnet](https://www.anthropic.com/claude/sonnet)** - this LLM is especially good at understanding complex contexts and reasoning about structured data – making it an ideal partner for ClickHouse's analytical prowess. The model's ability to understand database schemas, generate accurate SQL, and interpret query results demonstrates why ClickHouse and advanced LLMs are natural companions.

2. **[LibreChat UI project](https://www.librechat.ai)** - an open-source LLM UI that helps you work with popular LLMs out of the box. We selected LibreChat as the user interface because of its open-source nature, clean design, and growing community support. We would also like to thank the LibreСhat team for their assistance when building this demo.

3. My secret sauce is the **[ClickHouse MCP](https://github.com/ClickHouse/mcp-clickhouse)** (Model Context Protocol) server that the ClickHouse team developed. This specialized server acts as the bridge between ClickHouse databases and large language models, enabling:

* Efficient data transfer between ClickHouse and LLMs
* Intelligent query optimization for LLM-generated SQL
* Context management for stateful conversations about data
* Secure and controlled access to database resources
* Streamlined handling of various public datasets

4. **[ClickHouse Cloud database](https://clickhouse.com)** - a fully-managed cloud service that provides the ClickHouse database as a Software-as-a-Service (SaaS) offering. 

![Images_PoweringAIAgentsAnalytics_202504_FNL(1).png](/uploads/Images_Powering_AI_Agents_Analytics_202504_FNL_1_54be5a6747.png)

## Why Sonnet and LibreChat?

Anthropic's Sonnet model represents a significant advancement in LLM capabilities, particularly in understanding complex contexts and reasoning about structured data – making it an ideal partner for ClickHouse's analytical prowess. The model's ability to understand database schemas, generate accurate SQL and interpret query results demonstrates why ClickHouse and advanced LLMs are natural companions.

I use LibreChat as the user interface because of its open-source nature, clean design, and growing community support. The interface allows users to have natural conversations about their data and to build visual artifacts (charts, tables, etc), making complex analytical tasks accessible even to those without SQL knowledge.

## My purpose


I was created specifically as a testing ground for users to delve &#x1F609; into how ClickHouse, through our MCP server, can serve as an ideal backend for LLM applications. I have access to multiple public datasets that showcase various use cases, allowing you to explore the possibilities through a simple conversational interface. This includes 37 different datasets, including:


* **github** - Contains GitHub activity data, repositories, and user interactions. Updated hourly.
* **pypi** - a row for every Python package downloaded with `pip`, updated daily - over 1.3 trillion rows
* **rubygems** - a row for every gem installed - updated hourly - over 180 billion rows 
* **hackernews** - Contains posts and comments from Hacker News
* **imdb**- Contains movie database information from IMDB
* **nyc_taxi** - Contains NYC taxi trip data
* **opensky** - Contains aviation data from the OpenSky Network
* **reddit** - Contains posts and comments from Reddit
* **stackoverflow** - Contains questions and answers from Stack Overflow
* **uk** - contains a comprehensive collection of UK property transaction data and related geographical information

And others.

## My key features

* Test Natural Language Queries: See how plain English questions transform into optimized SQL queries for ClickHouse via the MCP server
* Experience Real-Time Analytics: Witness how our MCP server enables ClickHouse's renowned speed to be combined with AI-powered insights with minimal latency
* Try Interactive Data Exploration: Explore demo datasets through a conversational interface powered by the MCP-LLM connection
* View Automated Visualizations: See how data flowing through our MCP server can be automatically visualized

## Exploring the Demo

To start with AgentHouse, go to [llm.clickhouse.com](https://llm.clickhouse.com), and log into the demo environment with your Google account and start asking questions. A great way to start is to ask "Which datasets do you have?" - this will give you a list of databases and you can start exploring them.

I look forward to answering all your questions!

Meet AgentHouse - an interactive public demo that combines ClickHouse's real-time analytics with Anthropic's LLM via the MCP server, built to showcase how anyone can talk to data using natural language.

Introducing AgentHouse

本ブログでは、コンピュート-コンピュートの仕組みによって ClickHouse Cloud のお客様がテナント分離をより強化し、全体的なリソース消費とコストを最適化する方法について解説します。

large_Blog_WarehousesCHC_012025_FNL.png

medium_Blog_WarehousesCHC_012025_FNL.png

small_Blog_WarehousesCHC_012025_FNL.png

thumbnail_Blog_WarehousesCHC_012025_FNL.png

Blog_WarehousesCHC_012025_FNL.png

ウェアハウスの紹介: ClickHouse Cloud におけるコンピュート-コンピュート分離

For modern cloud database services, compute-compute separation provides a powerful approach to optimizing database performance and resource management by isolating compute resources for specific workloads, users, or business functions. Unlike traditional resource-sharing models, this method allocates dedicated compute instances to different types of database operations—such as reads and writes—reducing the risk of interference between them. This setup is particularly beneficial in high-demand environments, where fluctuating workloads might otherwise affect query speed and reliability.

In this post, we announce the introduction of compute-compute separation in ClickHouse Cloud, explain its significance, and, most importantly, highlight what it means for our users.

## What is compute-compute separation?

Compute-compute separation in a database refers to the ability to allocate dedicated compute resources for different users, workloads, or operation types, ensuring they do not interfere with one another. By isolating compute resources, we can guarantee that the performance and stability of a query will not be impacted by another workload. While similar outcomes can be partially achieved with quotas and limits, this approach has limitations and lacks the flexibility and guarantees provided by compute-compute separation.

Effectively this means users can have different pools of isolated compute, reading and writing the same data but dedicated to different tasks. 

## Why is this useful?

Such separation is extremely useful in the following scenarios:

### 1. Separating writes and reads

In some use cases write operations can be extremely sensitive to query execution time. In these scenarios, other queries in the database should not affect the execution time of INSERTS and UPDATES. This is especially hard to achieve if there are ad-hoc queries in the database submitted by users directly or via BI tools. Such queries can be inefficient, contain mistakes that lead to huge resource consumption, and affect other queries in the database. 
 
With compute-compute separation, we can allocate dedicated compute resources to the most critical workload - such as INSERT operations - ensuring their performance remains unaffected by other queries. In some cases, specific read operations may be more critical than writes. In such scenarios, these operations are also assigned to separate compute resources, following the same underlying logic.

### 2. Providing dedicated compute resources to different teams and functions

Large companies can have tens or hundreds of teams and departments accessing the same data in a database or a data warehouse. Some teams may require different performance levels when accessing the same data and prefer not to share compute resources with others, as shared resources can lead to inconsistent query performance.

Additionally, these teams often wish to separate their database usage costs for accountability and budgeting purposes.

In this scenario, compute-compute separation provides multiple benefits:

- Isolating the workloads of different teams, so their queries do not impact each other’s performance.
- Providing different compute size and idling settings to each team, allowing them to choose the optimal compute size and cost that suits their specific needs.
- Providing separate billing for each team enabling clear visibility into the cost of their queries, especially if they have idling enabled. 

### 3. Providing different HA level for different workloads

Some workloads are extremely critical while others can tolerate lower uptime to reduce costs. For example:
- End-user product charts and visuals should have the highest availability, so they should use three availability zones. Such queries can be quite simple, however, and do not demand significant CPU and RAM
- ETL/ELT queries are important and resource intensive, but they can be retried automatically if failed. Therefore, it is acceptable to allocate only two nodes across two availability zones for these workloads.
- Finally, there may be heavy ad-hoc queries that require significant memory and are executed manually by an analyst. Since this person only works during the working hours (5x8), their compute resources can be idled when they are inactive. Furthermore, they can tolerate occasional query failures if a node goes down briefly. In such cases, a single node in one availability zone should suffice.

In this scenario, compute-compute separation ensures that each workload receives isolated compute resources and achieves its desired level of high availability. Additionally, it enables cost separation for different teams using the same database. 

## Introducing Warehouses

In ClickHouse Cloud each database instance includes:

* A group of ClickHouse nodes (or replicas) - depending on availability settings an instance can consist of 1 or more nodes.
* An endpoint (or multiple endpoints created via ClickHouse Cloud UI console) with a service URL that you use to connect (for example, <code>https://dv2fzne24g.us-east-1.aws.clickhouse.cloud:8443</code>).
* An object storage folder where the service stores all the data and partially metadata.
* A [keeper](https://clickhouse.com/clickhouse/keeper) instance, usually containing three nodes:

![compute_compute_01.png](/uploads/compute_compute_01_ba2b32bcdd.png)

Now, we are introducing *Warehouses*. A warehouse is a set of services that share the same data - tables, views, functions, etc. Each warehouse has a primary service (this service was created first) and secondary service(s). For example, here is a warehouse containing two services:

![compute_compute_02.png](/uploads/compute_compute_02_9bccc649bb.png)

Each service in such a warehouse has its own:

* Endpoint - so you can adjust which of your workloads, BI tools and ETL/ELT systems use which set of nodes.
* Compute nodes - so your workload will not interfere with each other. That also means that you can scale your services separately. For example, you can independently:

 * Adjust the number of nodes per service
 * Adjust the size of the nodes in GiB of RAM, with larger instances also having a higher number of vCPUs.
 * Set up idling for services 
 * Our Enterprise tier customers can even select different CPU/RAM ratios per service! Some workloads require more CPU for compression/decompression, while others need more memory for more complex queries. Now, these two workloads can be separated.
* Network access settings - so you can restrict your apps and users from accessing specific services 


The following are common for all services in a warehouse:

* **Data** - all services work with the same data. That means, a regular SELECT query will return the same data if you run it on different services in a warehouse
* **Keeper instances** - as keepers help to coordinate and replicate the data across nodes, all services that use the same data have a shared keeper cluster.
* **Backups** - obviously, we do not want to back up the same data from multiple services. We, therefore, backup the data on the primary service only (the service in a warehouse that was created first).
* For now, all services in a warehouse should have the same:
 * Cloud Service Provider (AWS, GCP or Azure)
 * Region
 * ClickHouse Database version

From the UI perspective, when the first warehouse with more than two services is created, all services belonging to a single WH are grouped together. In the example below, we have two warehouses:

* `DWH_pre_prod` has four services
* `DWH_for_tests_us_east_1` has only one service:

![compute_compute_04.png](/uploads/compute_compute_04_93c203d689.png)

### Under the hood

In order to achieve compute-compute separation, we needed to tackle mainly two problems. The first being the data synchronization between different compute groups. The second is to ensure the query workload respects the service boundaries, and doesn’t use resources belonging to another service within the same warehouse.

Two important architectural decisions in ClickHouse Cloud help us achieve data synchronization efficiently. Firstly, we use [separation of storage and compute](https://clickhouse.com/docs/en/guides/separation-storage-compute) architecture. This established an important foundation for compute-compute separation by sharing the same data in cloud provider storage, such as S3, allowing quick data synchronization between different services. Also, for table schema and metadata synchronization, the [SharedMergeTree table engine ](https://clickhouse.com/docs/en/cloud/reference/shared-merge-tree)is leveraged to achieve a lightweight metadata synchronization in the shared keeper fleet. 

To implement compute separation, we utilize the [replica-group concept](https://github.com/ClickHouse/ClickHouse/issues/53620) in the Replicated database. This approach ensures that for distributed queries or parallel replicas, the load is contained within each replica group, limiting resource sharing to only the services within that group. This prevents the consumption of additional resources from other services within the same warehouse, maintaining isolation and efficiency.

With these technical foundations in place, we are well prepared to support compute-compute separation. In order to fully support compute-compute separation, we also made the following changes:


* We monitor different services within a warehouse separately. This allows us to idle and auto scale services independently.
* We enforce warehouse level networking isolation and storage isolation for security.
* We enforce database synchronization among different services within the same warehouse. 
* We disable merges and inserts queries in read-only services to ensure background jobs (e.g. for mutations) don't impact the performance of read-only services (see section "Background operations" for details).


### Database credentials[​](https://clickhouse.com/docs/en/cloud/reference/compute-compute-separation#database-credentials)

Because all services in a warehouse share the same set of tables, they also share access controls (grants) and users/roles. This means that all database users that are created in "Service 1" will also be able to use "Service 2" with the same permissions (grants for tables, views, etc), and vice versa. Users can use another endpoint for each service but will use the same username and password. In other words, *users are shared across services in a single warehouse:*

![compute_compute_05.png](/uploads/compute_compute_05_43a6621b33.png)

### Network access control[​](https://clickhouse.com/docs/en/cloud/reference/compute-compute-separation#network-access-control)

However, it is often useful to restrict specific services from being accessed by other applications or ad-hoc users. This can be achieved through network restrictions, configured similarly to how they are currently set up for regular services. To apply these restrictions, navigate to **Settings** in the **Service** tab of the specific service within the ClickHouse Cloud console.

You can apply IP filtering settings to each service separately, allowing you to control which application and users can access which service:

![compute_compute_06.png](/uploads/compute_compute_06_b0c4f114cb.png)

### Read vs read-write

Sometimes it is useful to restrict write access to a specific service and allow writes only by a subset of services in a warehouse. This can be done by creating any service except the first service as *read-only* (the first service should always be read-write):

![compute_compute_07.png](/uploads/compute_compute_07_749da423e7.png)

#### Background operations

ClickHouse performs several resource-intensive operations in the background, such as merging parts after data inserts. The database then merges these parts in the background, a process that can consume significant memory and CPU resources.

To achieve full compute-compute isolation, we allowed only read-write services to perform background operations. This means that when you dedicate a read-only service for a critical read workload, you can be sure that reads will not be affected by merges. 

However, with multiple read-write (RW) services, any of them can perform background merges for INSERT queries initiated on any of the services. This is because merges are not directly tied to the queries that triggered them. As a result, in rare cases, heavy write operations may impact each other, even when executed on different services. In the future, we plan to introduce a specialized setting that will enable users to control which RW services handle background operations and distribute these operations among RW services based on the queries that caused them.

### Warehouses limitations

Though warehouses bring a lot of flexibility to ClickHouse Cloud, there are a few limitations that are presented in the current implementation and that we plan to remove later:

1. The first service in the warehouse should always be up and should not be idled. You cannot stop or idle the first service if there is at least one secondary service. Once all secondary services are removed, you can stop or idle the original service again. 

2. As described, all read-write services perform background merge operations. This means that there can be a situation where there is an INSERT query in Service 1, but the merge operation is completed by Service 2. In short, this means that the workload from one RW service can affect the performance of another RW service. Note that read-only services do not execute background merges, so they don't spend their resources on this operation. Consider using a single RW service for ingestion and transformations, and RO services for other needs. 

3. Inserts in one read-write service can prevent another read-write service from idling if idling is enabled. Because of the previous point, a second service can perform background merge operations for the first service. These background operations can prevent the second service from going to sleep when idling. Once the background operations are finished, the service will be idled. Read-only services are not affected and will be idled without delay. 

4. CREATE/RENAME/DROP DATABASE queries could be blocked by idled/stopped services by default, causing these queries to not complete. To bypass this, you can run database management queries with the <code>setting `distributed_ddl_task_timeout=0`</code> at the session or query level. For example: 

<pre>
<code type='click-ui' language='sql'>
create database db_test_ddl_single_query_setting
settings distributed_ddl_task_timeout=0
</code>
</pre>


### Customer feedback

Before releasing warehouses, we launched a private preview program for a subset of our customers so they can test it in real production workloads. These are a few pieces of feedback from these users:

<blockquote style="font-size: 16px;">
"We have a smaller dedicated primary cluster for writes only that is isolated from the main bigger cluster for reads. Then we have an ad hoc cluster which goes to sleep when not in use. Ad hoc has been fantastic as it allows users to run whatever inefficient query they want without impacting production"
Beehiiv.com
</blockquote>


<blockquote style="font-size: 16px;">
"So far it’s been working great! We had some extra load on the primary service this week and were able to send traffic to the secondary service to keep customer facing queries fast."
CommonRoom.io
</blockquote>


<blockquote style="font-size: 16px;">
"Just wanted to drop a note and say thank you on the above. We are now running compute-compute in production along with `query_cache` and `allow_experimental_analyzer`, `allow_experimental_parallel_reading_from_replicas`.
With this new setup we are in a new dream world."
Vantage.sh
</blockquote>


<blockquote style="font-size: 16px;">
"Compute-compute separation gives satisfaction. From the experimentations, our regular compute task now takes something like 30 seconds, against 30 minutes currently in BigQuery, because we now can separate workloads."
ABTasty.com
</blockquote>


<blockquote style="font-size: 16px;">
"What used to take 8 hours now takes 30 minutes or less. No noticeable impact on the other services. A+ feature."
Cypress.io
</blockquote>


## Get started now!

This blog describes how compute-compute is helping ClickHouse Cloud customers achieve better tenant isolation and optimize their overall resource consumption and costs.

To experience warehouses in ClickHouse Cloud as a new customer, start a free $300 trial [here]( https://console.clickhouse.cloud/signUp). As an existing customer, follow the in-product prompts to migrate an existing deployment to one of the new tiers that supports warehouses and refer to this [FAQ](https://clickhouse.com/docs/en/cloud/manage/jan-2025-faq) for any questions.

This blog describes how compute-compute is helping ClickHouse Cloud customers achieve better tenant isolation and optimize their overall resource consumption and costs.

Introducing Warehouses: Compute-compute separation in ClickHouse Cloud

Read about how we built our own internal data warehouse with ClickHouse and only open-source technology.

Dmitry Pavlov

Blog posts by Dmitry Pavlov

How we made our internal data warehouse AI-first

AgentHouseのご紹介

Introducing AgentHouse

ウェアハウスの紹介: ClickHouse Cloud におけるコンピュート-コンピュート分離

Introducing Warehouses: Compute-compute separation in ClickHouse Cloud

How we built the Internal Data Warehouse at ClickHouse