[clickhouse.build](https://github.com/ClickHouse/clickhouse.build) is an open source, agentic CLI that accelerates the adoption of ClickHouse within your existing Postgres-backed TypeScript application. The goal is not to replace Postgres, but to seamlessly combine it with ClickHouse for analytical workloads, using the strengths of each database together, within the same application. 

It uses a multi-agent workflow that:

* Scans your Postgres-backed codebase to identify analytical queries (whether SQL in-line or ORM-based)  
* Determines which tables are required to support those analytical queries and creates a plan.  
* Helps to automatically sync the relevant tables to ClickHouse Cloud using ClickPipes API.  
* Rewrites the relevant portions of the code so that it uses ClickHouse for analytics and Postgres for transactions, while keeping the application backwards-compatible by introducing a feature flag.

clickhouse.build is intended as an accelerator, and can help you to have a working proof of concept (PoC) in under an hour. You can use the PoC to evaluate how your application performs with a combined Postgres+ClickHouse backend.

Already evaluating Postgres + ClickHouse? [Skip to how it works](#how-clickhousebuild-works), or read on to understand why these two  databases are so good together.

![chbuild.png](/uploads/chbuild_8f68f3f55e.png)

Try clickhouse.build today with a free trial of ClickHouse Cloud. Get a working PoC in under an hour.

Try clickhouse.build today

Work on clickhouse.build was initiated in partnership with the AWS Prototyping and Cloud Engineering ([PACE](https://www.linkedin.com/groups/12710205/)) team and uses Amazon Bedrock and the [Strands Agents SDK](https://github.com/strands-agents/sdk-python). We would like to thank our partners for this collaboration.

## Postgres + ClickHouse: the default data stack

Postgres & ClickHouse have seen huge community adoption; they are the most popular open source databases in their respective categories, each with flourishing ecosystems around them. That adoption has seen [the two become an unbeatable duo](https://about.gitlab.com/blog/two-sizes-fit-most-postgresql-and-clickhouse/), with [agentic AI only accelerating the trend](https://clickhouse.com/blog/langchain-why-we-choose-clickhouse-to-power-langchain).

### Adding ClickHouse to a Postgres application

ClickHouse doesn’t replace Postgres, instead, the two work in tandem to handle workloads that are best suited to them; transactional workloads go to Postgres, and analytical workloads go to ClickHouse. 

Many application backends are sending all queries to Postgres, whether it's a `SELECT *` of a single row by ID, or performing a `SUM() … GROUP BY` over 100,00 rows. The former is where Postgres shines, while the latter can be 100-1000x faster in ClickHouse.

![chb_pgonly_diag.jpg](/uploads/chb_pgonly_diag_8a9fd8e7ea.jpg)

With Postgres + ClickHouse, your application backend can send queries to the right database for the job. You avoid needing to oversize Postgres or fight with replication, and improve the user-facing performance and experience. Prototyping the new architecture can take a few weeks, particularly if it's your first time with ClickHouse, while you identify the right queries to migrate, setup data synchronization, and update backend integration. Building this prototype is where clickhouse.build can help.

![chb_pg_and_ch_diag.jpg](/uploads/chb_pg_and_ch_diag_b7774d3bf6.jpg)

## How clickhouse.build works

The clickhouse.build CLI uses multiple, task-specific agents to migrate Postgres-backed TypeScript apps to a Postgres+ClickHouse architecture. These agents can be used independently, or called via an all-in-one workflow. A Quality Assurance (QA) sub-agent checks every proposed code change, which has been augmented with high-quality reference examples.

There are 3 primary agents:

### Scanner

The scanner agent scans your TypeScript code base and looks for Postgres queries. It currently works whether you use a direct Postgres driver with queries in code, or using the query builders of Prisma and Drizzle ORMs.

When it finds queries, it will inspect them to determine whether the query is better suited for Postgres or ClickHouse: single-row lookups and insert/update/delete operations are typically best fit for Postgres, while COUNTs, SUMs and GROUP BYs are more optimal in ClickHouse. The scanner agent produces a structured output report with the identified queries that should be migrated to ClickHouse.

scanner_agent_edited.mp4

*A demo of the scanner agent, called on its own outside of the all-in-one workflow*

### Data migrator

The data migrator agent assists in syncing the necessary Postgres tables to ClickHouse. Based on the queries identified by the scanner agent, it determines which tables ClickHouse needs to serve the queries. It then generates the relevant ClickPipes API payloads to configure the [ClickPipes Postgres CDC connector](https://clickhouse.com/docs/integrations/clickpipes/postgres). This connector keeps ClickHouse in sync with Postgres, first backfilling all historical data, and then maintaining a continuous, real-time flow of changes from Postgres to ClickHouse.



data_agent_edited.mp4

*A demo of the data migrator agent, called on its own outside of the all-in-one workflow*

### Code migrator

The code migrator agent handles the job of updating queries and code. It can adjust SQL syntax to be correct for ClickHouse (queries between Postgres and ClickHouse are often compatible without change, but not always, especially if ORM query builders are used), and update TypeScript code to call ClickHouse via the [official ClickHouse JS client](https://clickhouse.com/docs/integrations/javascript). This agent will pause the migrations after each significant step, requesting input for the human to accept or reject the changes proposed. It’s also possible to accept all changes in one command.

Rather than remove your existing, working Postgres queries, the code migrator uses a backwards compatible indirection pattern. The queries for each database are moved to separate utility files, and a feature flag lets you pick which backend to use at run time. This means you can easily toggle between the two modes to compare performance and correctness while you evaluate your resulting application.

#### QA agent

The QA agent is a sub-agent of the code migrator, which validates any changes made. The code migrator will suggest what it wants to do, and then ask the QA agent if the change is appropriate. The QA agent scans the change and runs checks and validations comparing patterns against patterns from a series of reference code bases that already use Postgres+ClickHouse successfully.

code_agent_edited.mp4

*A demo of the code migrator agent, called on its own outside of the all-in-one workflow*

### All-in-one workflow

While each agent can be used on its own, the CLI has an all-in-one workflow that invokes each agent in the correct order (scanner -> data migrator -> code migrator). This is generally the recommended way to use the CLI for your PoC, but we won’t hold it against you if you want to call the agents yourself.

## Using clickhouse.build

You can get the CLI from the [clickhouse.build GitHub repo](https://github.com/ClickHouse/clickhouse.build). The README contains detailed instructions on setting up and configuring the CLI.

With the CLI configured, **we recommend creating a new branch in your application repository to work in**. You can then run the all-in-one migration workflow:

```py
uv run main.py migrate /path/to/your/repo --replication-mode cdc
```

This will walk through all agents in order: scanner -> data migrator -> code migrator. **The agents are not fully autonomous** by default. The CLI will prompt you to review and accept/deny changes as you go, though you can choose to “accept all” if you wish.

The scanner agent will go through your code, discover your Postgres queries, and output a structured JSON file of the queries, a description, and their file location. The data migrator agent will read this scan file and extract the names of tables that need to be replicated into ClickHouse. With the `--replication-mode cdc` option set, the **data migrator agent will produce the `curl` commands needed to configure ClickPipes Postgres CDC** via the [ClickHouse Cloud API](https://clickhouse.com/docs/cloud/manage/api/api-overview).

Currently, **the ClickPipes configuration commands require some manual input**. Along with the command, the output contains some environment variables you’ll need to complete some details about your ClickHouse Cloud account and Postgres instance. With these details completed, you’ll need to run the `curl` commands yourself.

clickpipes_edited.mp4

*A demo of running the ClickPipes configuration commands and validating the running ClickPipes service in ClickHouse Cloud*

The code migrator agent is the final piece of the workflow. It begins by loading the scan file and installing the [clickhouse.js driver](https://clickhouse.com/docs/integrations/javascript) using your project’s package manager. After loading some reference examples of Postgres+ClickHouse implementations in its context, the agent begins analysing your code base to build a plan of what to change. 

The agent aims to implement the ‘strategy pattern’, where the specific backend implementation is abstracted. This pattern means you can toggle between using the original Postgres backend or the new ClickHouse+Postgres backend with a simple feature flag. A file for each of Postgres and ClickHouse is created under `lib/strategies` which contains the logic for serving your queries via each of the respective databases. The agent will attempt to find the correct way to integrate this into your project, which means the next step can vary a bit. A common pattern is to create a database configuration at `lib/db.ts` which implements the logic to toggle between these strategies, and then update your existing routes to use the backend as configured in `db.ts`.

With each step, you’ll be asked to review the changes (unless you have chosen to accept all), and the QA sub-agent will automatically review changes for baseline quality against the reference examples.

The workflow will wrap up by ensuring your project builds, producing a summary of the changes it made, and instructions on how to use the feature flag to toggle between strategies. You can now evaluate the impact of using ClickHouse in your application.

### Evaluating the result

You can run your application as normal, and it should continue to use your existing Postgres implementation by default. To use the new ClickHouse implementation, **add the new feature flag to your environment variables: `USE_CLICKHOUSE=true`**. You can flip this flag to `false` to switch back.

evaluate_poc_edited.mp4

*A demo of an application being switched between Postgres and ClickHouse backends, showing a 3x gain in performance.*

We recommend testing the normal flows of your application, having new rows inserted into Postgres to test replication, and comparing the performance of features with and without the ClickHouse feature flag enabled. 

In many cases, the difference is immediately obvious; you should see that stats, charts, and tables that display analytics load several times faster without perceptible latency. Though it can be more fun to put some logging in and see the real numbers!

The change in performance you’ll see can be variable, but generally, the larger your data, the bigger the performance gain.

## The clickhouse.build design principles

clickhouse.build is a highly opinionated implementation. Its design is informed by lessons we have learned building agents for [data warehousing](https://clickhouse.com/blog/ai-first-data-warehouse), [for SREs](https://www.youtube.com/watch?v=og8ieNxixp4), and [agentic analytics](https://clickhouse.com/blog/agent-facing-analytics).

We believe that the best agentic flows **keep the human in the loop**, not only keeping you informed, but also involved in the actions that are taken. **Backwards compatibility** ensures we can validate correctness and assess the real impact of changes.

Agentic workflows are new, our understanding is still evolving, and we should approach changes to production with caution, but **agents are an obvious accelerator in development and proof of concepts**.

Agents can be tuned for different tasks by supplying specific context and instructions, which means **we can have one agent optimised to make changes, and another optimised to validate those changes**. We’ve seen that this can be effective in reducing errors, but it isn’t always perfect - that’s why we keep the human around, too.

**Evaluations might just be the highest impact and under-explored area** that we see in agentic workflows today. Getting to an 80% level of quality can be achieved relatively easily, but going beyond this barrier takes significant effort. We think this is where evaluations become critical, allowing us to constantly review, assess, and improve. But they can also be hard to do, and easy to forget. To mitigate this, we aim to bake evaluations into the workflow itself, adopting new techniques and tooling as the space develops.

**Open-source is the way forward.** There’s no secret sauce behind clickhouse.build. While most companies are building proprietary agents that are served from some black-box API, we decided to build an open, client-side application that’s free to use and free to adapt. Want a new LLM API? To support a new database? [We’d love for you to contribute!](https://github.com/ClickHouse/clickhouse.build)

## What’s next?

Accelerating Postgres with ClickHouse is just the beginning for clickhouse.build. We started with Postgres because this combination has become the default data stack for high-performance applications. But other transactional databases, like MySQL or MongoDB, benefit just as much and are a natural next step.

Similarly, we expect to expand support beyond TypeScript apps, with likely next candidates being Python, Go, Rust, or Ruby.

We also see this model benefiting not just application builders, but data and analytics engineers as well, where ClickHouse can reduce the cost and accelerate the workloads of cloud data warehouses.

clickhouse.build is open source and open for contributions. Feedback, suggestions, and comments are welcome; we’d love to see you in our [Community Slack](https://clickhouse.com/slack).

clickhouse.build is an open source, agentic CLI that accelerates the adoption of ClickHouse within your existing Postgres-backed TypeScript application.

large_clickhousebuildbanner.jpg

medium_clickhousebuildbanner.jpg

small_clickhousebuildbanner.jpg

thumbnail_clickhousebuildbanner.jpg

clickhousebuildbanner.jpg

clickhouse.build: An agentic CLI to accelerate Postgres apps with ClickHouse

Compare 12 AI agent frameworks with MCP support. Complete guide with code examples for Claude SDK, OpenAI Agents, LangChain, and more. Build production-ready AI agents with Model Context Protocol.

large_how-to-build-ai-agents-banner.jpg

medium_how-to-build-ai-agents-banner.jpg

small_how-to-build-ai-agents-banner.jpg

thumbnail_how-to-build-ai-agents-banner.jpg

how-to-build-ai-agents-banner.jpg

How to build AI agents with MCP: 12 framework comparison (2025)

> Traditional BI assumes a dashboard is the destination. Most often, the destination is a conversation. [This post continues the series on creating a **chat‑native BI layer** inside Slack using ClickHouse + MCP](https://clickhouse.com/blog/agentic-analytics-slack-clickhouse-mcp): * Answers analytical questions in a Slack thread by querying ClickHouse via MCP. * **Generates visualizations** from returned data and adds them to the Slack thread. * Uses MCP tools to push query execution, semantic context, and guardrails closer to the data. * Is simple enough to fork, run, and adapt to your own stack in an afternoon.

Dashboards carry hidden tax: handovers between the person with the question and the BI team, the overhead of learning a new BI tool if you want to self‑serve, granting and managing secure access, or falling back to writing SQL when you really just need a quick check. A chat‑native BI layer collapses that tax:

* **Ask in natural language.** “How did signups trend last 90 days by channel?” 
* **Let AI do the heavy lifting.** The agent generates SQL through MCP tools, so you don’t need to learn the syntax yourself. 
* **Run it where the data lives.** Queries execute directly in ClickHouse - no ETL hops, just fast results. 
* **See the answer, trust the path.** You get both a visualization and the exact SQL, so if the analysis is valuable, you can turn it into a KPI or dashboard later.

We’re not replacing BI; we’re **right‑sizing** it. Dashboards remain for curated KPIs. Chat picks up the fluid, investigative 80%.

## What’s new since the last post

[In the first version of our Slack BI agent, the agent responded with a table or a short textual summary](https://clickhouse.com/blog/agentic-analytics-slack-clickhouse-mcp). In this iteration, we added **visualization capability**:

1. The LLM generates a **vega-lite** visualisation spec (JSON) 
2. The Python bot renders the chart from the spec using **vega-altair** and attaches an image to the Slack message

This small addition changes the feel entirely - threads become lightweight, *shareable* mini‑reports.

## A straightforward solution

We added visualization in about [50 lines of code](https://github.com/ClickHouse/examples/pull/311/files#diff-c2732de3eb904c5f74440667747fb319d949309b4966924ecc6a345fcf2471ec). The approach is simple:

1. The system prompt instructs the LLM to generate a vega-lite compatible spec if a visualisation was requested by the user 
2. If a spec is present in the LLMs output, create the visualisation with vega-altair 
3. Render the chart as a PNG and upload the image to the Slack thread

The system prompt now looks like:

```none
Available capabilities:
1) ClickHouse tools to explore databases, tables, and execute SQL queries
2) Chart generation by providing Vega-Lite specifications

When users ask for data analysis with visualizations:
1. First query the database using available tools
2. If a visualization would be helpful, create a Vega-Lite chart specification
3. Format your Vega-Lite spec as JSON within ```json blocks
4. Choose appropriate chart types: bar charts for categories, line charts for time series, scatter for correlations, pie for proportions

Example Vega-Lite specification format:
```json
{
 "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
 "title": "Chart Title",
 "data": {"values": [{"category": "A", "value": 100}, {"category": "B", "value": 200}]},
 "mark": "bar",
 "encoding": {
 "x": {"field": "category", "type": "nominal"},
 "y": {"field": "value", "type": "quantitative"}
 }
}
```

And to turn a returned spec into a visualisation:

```py
async def render_and_upload_chart(client, channel, thread_ts, vega_lite_spec, title="Chart"):
 """Render Vega-Lite spec to PNG and upload to Slack"""
 try:
 # Parse the Vega-Lite specification
 if isinstance(vega_lite_spec, str):
 spec = json.loads(vega_lite_spec)
 else:
 spec = vega_lite_spec
 
 # Render to PNG using vl-convert
 png_data = vlc.vegalite_to_png(spec)
 
 # Create temporary file
 with tempfile.NamedTemporaryFile(suffix='.png', delete=False) as tmp_file:
 tmp_file.write(png_data)
 tmp_file.flush()
 
 # Upload file to Slack
 response = await client.files_upload_v2(
 channel=channel,
 file=tmp_file.name,
 title=title,
 thread_ts=thread_ts
 )
 
 # Clean up temp file
 os.unlink(tmp_file.name)
 return response
 
 except Exception as e:
 logging.error(f"Error rendering and uploading chart: {e}")
 return None
```

No visualization servers. No complex rendering pipeline. Just using the LLM's understanding of both the data and Vega-Lite to create appropriate charts on demand.

## What this looks like in practice

Here's a typical interaction:

**User**: 

"What are the top 10 most downloaded python packages on pypi? create a chart"

**Bot**: 

"**Key Findings:**

The top 10 most downloaded Python packages on PyPI are:1. **boto3** - 43.1 billion downloads (AWS SDK)

2. **urllib3** - 25.6 billion downloads (HTTP client library)

3. **botocore** - 23.8 billion downloads (AWS core library)

4. **requests** - 21.7 billion downloads (HTTP library)

5. **setuptools** - 20.5 billion downloads (packaging tool)

6. **certifi** - 19.6 billion downloads (SSL certificates)

7. **idna** - 19.1 billion downloads (domain name handling)

8. **python-dateutil** - 17.2 billion downloads (date/time utilities)

9. **typing-extensions** - 16.8 billion downloads (type hints backport)

10. **s3transfer** - 16.7 billion downloads (AWS S3 transfer utility)"

![tmpd4gjyf5h.png](/uploads/tmpd4gjyf5h_453fbee1c5.png)

The chart appears inline in the Slack thread. No new tabs, no context switching. The visualization and interpretation are right where the question was asked.

![mcp_slackbot_image_generation.gif](/uploads/mcp_slackbot_image_generation_e60f7dae94.gif)

## Prompting strategy for charts

When a user asks a question, we prompt the model with:

* [The pre-set system prompt](https://github.com/sdairs/examples/blob/b1116b92788f5f5f17ecd03046392ee70d390aab/ai/mcp/slackbot/main.py#L53) 
* The natural‑language question from the user

The system prompt contains instructions for how the LLM should behave, and what it should produce. Including:

* Instructions to include the **final SQL** and a **preview** of the result rows. 
* A **style guide** (e.g., prefer lines for time series, bars for discrete categories)

The vega spec for the conversation shown above looks something like this:

```
{
 "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
 "title": {
 "text": "Top 10 Most Downloaded Python Packages on PyPI",
 "fontSize": 16,
 "fontWeight": "bold"
 },
 "data": {
 "values": [
 {
 "package": "boto3",
 "downloads": 43084499578,
 "downloads_formatted": "43.1B"
 },
 {
 "package": "urllib3",
 "downloads": 25600943735,
 "downloads_formatted": "25.6B"
 },
...
 ]
 },
 "mark": {
 "type": "bar",
 "color": "#1f77b4",
 "tooltip": true
 },
 "encoding": {
 "y": {
 "field": "package",
 "type": "nominal",
 "sort": "-x",
 "axis": {
 "title": "Package Name",
 "labelFontSize": 11
 }
 },
 "x": {
 "field": "downloads",
 "type": "quantitative",
 "axis": {
 "title": "Total Downloads",
 "format": ".2s"
 }
 },
 },
 ...
 "width": 500,
 "height": 300
}
```

LLMs turn out to be quite good at generating Vega-Lite specifications. They can:

* Match chart types to data patterns (time series → line chart, categories → bar chart) 
* Set reasonable scales and formatting 
* Add helpful touches like rotated labels or formatted axes

## Slack UX details that matter

When designing the Slack experience, small details make the difference between a gimmick and a tool your team actually uses. The agent is structured so that every answer feels like a complete mini-report:

* **One message, three artifacts:** summary → chart image → SQL block. 
* **Thread-native:** follow-ups inherit context (same table, filters, window) unless the user says otherwise. 
* **Flexible surfaces:** you can chat with the bot in public threads by tagging it, or work privately in direct messages if the analysis is sensitive.

## Why ClickHouse is a great brain for chat BI

Behind the scenes, performance and flexibility matter. ClickHouse provides a natural foundation for this type of chat-native analytics:

* **Speed at scale:** columnar engine with vectorized execution makes the agent feel instant. 
* **SQL-first:** easy to trace, review, and cache. 
* **Time-series native:** materialized views + aggregations make “what changed?” a 1-liner.

## Why MCP is the glue

Finally, the MCP layer is what keeps the agent robust, portable, and trustworthy. It defines what the model can do and how it does it:

* **Explicit capability boundaries.** Tools expose exactly what the model may do. 
* **Portability.** The same agent code can sit behind Slack, a web UI, or any other surface without changing the data brain. 
* **Extensibility.** You can add more MCP servers to let the agent talk to additional data sources or tools.

## Compared to classic BI

| Concern | Classic BI | Chat‑Native BI (this approach) |
| ----- | ----- | ----- |
| Curated KPIs | Excellent | Fine (defer to dashboards) |
| Exploratory Q&A | Slower (new tiles) | Instant (ask in thread) |
| Reuse | Dashboards | Thread history + re‑prompt |
| Explainability | Varies | SQL + spec posted |
| Simplicity | Learning curve to build charts, or SQL knowledge | Just natural language chat |

**Takeaway:** It’s not either/or. Use dashboards for executive reporting; use chat for the investigative loop.

## The tradeoffs

Let's be realistic about what this doesn't do:

**Complex visualizations**: You won't get interactive 3D scatter plots or sophisticated statistical visualizations. For most ad-hoc questions in Slack, you don't need them.

**Noisy visualisations**: The LLM is formatting a JSON spec to create the visualisation, so the data to plot is being loaded into its context window. Visualizing many thousands of points isn't practical.

**Pixel-Perfect Design**: The charts are clean and functional, but they won't match your brand guidelines or win design awards. That's fine for internal analytics.

**Caching**: Every request generates a new query and visualization. While ClickHouse will help with query and result caching, for frequently-accessed metrics, a traditional dashboard might be more efficient.

## The bigger picture

This isn't about replacing all BI tools. Dashboards, especially for operational monitoring and executive reporting, serve important purposes. But a lot of analytical work is exploratory and conversational. It happens in Slack threads, during stand-ups, while debugging issues.

For these use cases, the ability to generate visualizations on-demand, right where the conversation is happening, removes friction. It's not revolutionary - it's just practical.

## Try it yourself

[The code is available on GitHub](https://github.com/ClickHouse/examples/tree/main/ai/mcp/slackbot). With your own ClickHouse instance (or using our public playground), you can have this running in a few minutes:

1. Clone the repository 
2. Configure your Slack bot and API keys 
3. Run the bot: `uv run main.py`

Start with simple questions and iterate from there. You might be surprised how much analytical ground you can cover without leaving Slack.

If you're experimenting with similar ideas or have thoughts on how to improve this approach, we'd love to hear from you in our [community Slack](https://clickhouse.com/slack) or on [GitHub](https://github.com/ClickHouse/examples).

Traditional BI assumes a dashboard is the destination. Most often, the destination is a conversation. We’re not replacing BI; we’re right‑sizing it. Dashboards remain for curated KPIs. Chat picks up the fluid, investigative 80%.

large_466104368-3cbfc980-f9bd-4b7e-93e3-3728d973e3d0.png

medium_466104368-3cbfc980-f9bd-4b7e-93e3-3728d973e3d0.png

small_466104368-3cbfc980-f9bd-4b7e-93e3-3728d973e3d0.png

thumbnail_466104368-3cbfc980-f9bd-4b7e-93e3-3728d973e3d0.png

466104368-3cbfc980-f9bd-4b7e-93e3-3728d973e3d0.png

Agentic BI in Slack: MCP-powered analytics and visualisations

We often hear that LLMs will soon replace SREs. We wanted to test that claim, so we ran an experiment. Read the blog to see what we found.

large_llm-observability-banner.png

medium_llm-observability-banner.png

small_llm-observability-banner.png

thumbnail_llm-observability-banner.png

llm-observability-banner.png

Can LLMs replace on call SREs today?

See how ClickHouse’s update speed stacks up against PostgreSQL on the same data and hardware.

large_clickhouse vs postgresql update findn.png

medium_clickhouse vs postgresql update findn.png

small_clickhouse vs postgresql update findn.png

thumbnail_clickhouse vs postgresql update findn.png

clickhouse vs postgresql update findn.png

You can’t UPDATE what you can’t find: ClickHouse vs PostgreSQL

We're often asked, "Can I use ClickHouse for BI?", and the answer is "Yes!", but perhaps we should be looking a bit differently at BI in 2025?

In the past, we’ve relied on BI tools to let analysts build charts and place them on a dashboard. This typically means a multi-step process: 

1. personA thinks of a question 
2. personA asks analystA to answer the question 
3. analystA spends a while figuring out what personA **actually** means 
4. analystA spends time figuring out the right tables and columns 
5. analystA shows personA the chart 
6. personA realises it's not what they want, go back to step 3 
7. personB thinks of a question…

And that’s assuming we're not bottle-necked behind a ticket queue longer than you'd find outside "The world's largest concrete garden gnome" (it's real, thanks Iowa.)

So, let’s try something different; let's build a self-service analytics agent that we can talk to directly in Slack, that transparently queries our ClickHouse data warehouse.

## What we’re building

We’re going to build a Slack bot that can be added to a channel, or messaged privately. The Slack bot will be connected to a ClickHouse data warehouse via the [ClickHouse MCP server](https://github.com/ClickHouse/mcp-clickhouse).

We’ll use [PydanticAI](https://ai.pydantic.dev/) to integrate the Slack bot with the Anthropic API to process natural language, invoke MCP tools and generate SQL queries.

Via MCP, the bot will send the SQL queries to ClickHouse, and the LLM will interpret the result. We’ll return a natural language response to the original question in Slack.

The diagram below shows a high level view of the flow, but note that there could be multiple back-and-forth interactions between the bot, LLM and ClickHouse.

You can find the [full code for this example on GitHub](https://dub.sh/EsZMXl7).

![466107429-3fbca03a-7659-4e81-933b-5b709e79357a.png](/uploads/466107429_3fbca03a_7659_4e81_933b_5b709e79357a_d9c40c688f.png)

:::global-blog-cta:::

## 1. Setting Up Your Slack Bot

We’re running the bot locally and using Slack’s “Socket Mode” to receive events as this is much easier to get started - if you were to deploy this for real usage, you’d probably want to switch to “HTTP mode”.

**a. Create a Slack App**

Go to [https://api.slack.com/apps](https://api.slack.com/apps) and click "**Create New App**". Then, choose "**From scratch**" and give your app a name. You'll be prompted to select your Slack workspace.

**b. Install the app to your workspace**

**c. Configure Slack App Settings**

Go to **App Home** , then under "Show Tabs" → "Messages Tab" enable **Allow users to send Slash commands and messages from the messages tab** .

Then, go to **Socket Mode** and enable **Socket Mode**. Note down the **Socket Mode Handler** for the environment variable `SLACK_APP_TOKEN` 

Go to **OAuth & Permissions** and add the following **Bot Token Scopes**: 
 * `app_mentions:read` 
 * `assistant:write` 
 * `chat:write` 
 * `im:history` 
 * `im:read` 
 * `im:write` 
 * `channels:history` 


Now install the app to your workspace and note down the **Bot User OAuth Token** for the environment variable `SLACK_BOT_TOKEN`. 
 
 
Finally, go to **Event Subscriptions** and enable **Events**. Then, under **Subscribe to bot events**, add: 
 * `app_mention` 
 * `assistant_thread_started` 
 * `message:im` 

**d. Add the bot to a channel in your workspace.**

## 2. Configuring and loading EnvVars

We need to configure some variables that we’ll use in our code. In a `.env` file, add the following template. 

Update the `SLACK_BOT_TOKEN` and `​​SLACK_APP_TOKEN` with the values noted when creating the Slack bot. Update the `ANTHROPIC_API_KEY` with your own Anthropic API key. 

You can customise the ClickHouse settings to use your own instance, or leave them as-is to connect to the public [sql.clickhouse.com](http://sql.clickhouse.com) playground which has 35+ datasets ready to go. You’re welcome to use it for testing!

```
SLACK_BOT_TOKEN=
SLACK_APP_TOKEN=
ANTHROPIC_API_KEY=
CLICKHOUSE_HOST="sql-clickhouse.clickhouse.com"
CLICKHOUSE_PORT="8443"
CLICKHOUSE_USER="demo"
CLICKHOUSE_PASSWORD=""
CLICKHOUSE_SECURE="true"
```

In our Python script, we need to load the configured variables:

```py
load_dotenv()

# --- CONFIGURATION ---
SLACK_BOT_TOKEN = os.environ.get("SLACK_BOT_TOKEN", "xoxb-your-token")
SLACK_APP_TOKEN = os.environ.get("SLACK_APP_TOKEN", "xapp-your-app-token")
ANTHROPIC_API_KEY = os.environ.get("ANTHROPIC_API_KEY", "sk-ant-xxx")

# ClickHouse MCP env
CLICKHOUSE_ENV = {
 "CLICKHOUSE_HOST": os.environ.get("CLICKHOUSE_HOST", "sql-clickhouse.clickhouse.com"),
 "CLICKHOUSE_PORT": os.environ.get("CLICKHOUSE_PORT", "8443"),
 "CLICKHOUSE_USER": os.environ.get("CLICKHOUSE_USER", "demo"),
 "CLICKHOUSE_PASSWORD": os.environ.get("CLICKHOUSE_PASSWORD", ""),
 "CLICKHOUSE_SECURE": os.environ.get("CLICKHOUSE_SECURE", "true"),
}
```

## 2. Configuring PydanticAI to use mcp-clickhouse

We use the `MCPServerStdio` class from PydanticAI to configure our ClickHouse MCP server. This lets us run the MCP server as needed in response to a request, rather than leaving a process running. That can introduce a bit of latency, but makes it easier to manage. In the future, [ClickHouse Cloud will provide a remote MCP server](https://clickhouse.com/ai) than can be connected to without this step.

We then configure the `Agent`, which controls which model to use, adds the MCP server and allows us to inject a system prompt. You can customize the system prompt to tailor the agent’s behaviour to your needs, but the prompt below is a good place to start.

```py
mcp_server = MCPServerStdio(
 'uv',
 args=[
 'run',
 '--with', 'mcp-clickhouse',
 '--python', '3.13',
 'mcp-clickhouse'
 ],
 env=CLICKHOUSE_ENV
)

agent = Agent(
 "anthropic:claude-sonnet-4-0",
 mcp_servers=[mcp_server],
 system_prompt="You are a data assistant. You have access to a ClickHouse database from which you can answer the user's questions. You have tools available to you that let you explore the database, e.g. to list available databases, tables, etc., and to execute SQL queries against them. Use these tools to answer the user's questions. You must always answer the user's questions by using the available tools. If the database cannot help you, say so. You must include a summary of how you came to your answer: e.g. which data you used and how you queried it."
)
```

## 3. Configuring message handling

Next we need to initialize the Slack bot and create handlers that deal with messages arriving.

We’re listening for `message` (direct messages) and `app_mention` (when you @ the bot in a channel) events. In both cases, we’re pushing the message to the same function with the main logic for our agentic flow.

```py
app = AsyncApp(token=SLACK_BOT_TOKEN)

@app.event("app_mention")
async def handle_app_mention(event, say):
 await handle_slack_query(event, say)

@app.event("message")
async def handle_dm(event, say):
 if event.get("channel_type") == "im":
 await handle_slack_query(event, say)

async def main():
 handler = AsyncSocketModeHandler(app, SLACK_APP_TOKEN)
 await handler.start_async()

if __name__ == "__main__":
 import asyncio
 asyncio.run(main())
```

## 4. Working on the message

Finally, we need to let our agent fly. We send an immediate response to the user saying so they know something is happening. Then, if needed, we collect messages from the current chat thread as context (so the LLM knows about previous messages).

The new question (and any context) is sent as a prompt, and the PydanticAI `Agent` we configured does all the magic to handle the back-and-forth communication with the LLM. If the LLM wants to interact with ClickHouse, it uses the MCP tools from the mcp-clickhouse server we configured.

```py
async def handle_slack_query(event, say):
 user = event["user"]
 text = event.get("text", "")
 thread_ts = event.get("thread_ts") or event["ts"]
 channel = event["channel"]

 await say(text=f"<@{user}>: Let me think...", thread_ts=thread_ts)

 async def do_agent():
 # Build context from thread if present
 context = ""
 if thread_ts and thread_ts != event["ts"]:
 client = AsyncWebClient(token=SLACK_BOT_TOKEN)
 replies = await client.conversations_replies(channel=channel, ts=thread_ts)
 # Exclude the current message, and bot messages
 messages = [m for m in replies["messages"] if m["ts"] != event["ts"]]
 # Format as "user: message"
 context_lines = []
 for m in messages:
 uname = m.get("user", "bot")
 msg = m.get("text", "")
 context_lines.append(f"{uname}: {msg}")
 context = "n".join(context_lines)

 # Compose prompt for the agent
 if context:
 prompt = f"Thread context so far:n{context}nnNew question: {text}"
 else:
 prompt = text

 async with agent.run_mcp_servers():
 result = await agent.run(prompt)
 await say(text=f"{result.output}", thread_ts=thread_ts)

 asyncio.create_task(do_agent())
```

## 5. Running & testing

We can run the example locally using `uv`:

```` 
uv run [main.py](http://main.py)

INFO:slack_bolt.AsyncApp:A new session (s_xxxx) has been established 
INFO:slack_bolt.AsyncApp:⚡️ Bolt app is running! 
````

In Slack, we can now ask our bot questions: 
![mcp_slack_bot.gif](https://cms.clickhouse-dev.com:1337/uploads/mcp_slack_bot_81aa68071a.gif)

In your terminal, you should see output like the following. If you read through the output, you can understand how the LLM is interacting with MCP and ClickHouse. We can see that the LLM first lists the available tools, then uses those tools to interact with ClickHouse - listing databases, tables and finally sending a SQL query.

``` 
[07/09/25 13:24:46] INFO Starting MCP server 'mcp-clickhouse' with transport 'stdio' server.py:1352 
2025-07-09 13:24:46,428 - mcp.server.lowlevel.server - INFO - Processing request of type ListToolsRequest 
INFO:httpx:HTTP Request: POST https://api.anthropic.com/v1/messages?beta=true "HTTP/1.1 200 OK" 
2025-07-09 13:24:48,683 - mcp.server.lowlevel.server - INFO - Processing request of type ListToolsRequest 
2025-07-09 13:24:48,684 - mcp.server.lowlevel.server - INFO - Processing request of type CallToolRequest 
2025-07-09 13:24:48,686 - mcp-clickhouse - INFO - Listing all databases 
2025-07-09 13:24:48,686 - mcp-clickhouse - INFO - Creating ClickHouse client connection to sql-clickhouse.clickhouse.com:8443 as demo (secure=True, verify=True, connect_timeout=30s, send_receive_timeout=300s) 
2025-07-09 13:24:49,282 - mcp-clickhouse - INFO - Successfully connected to ClickHouse server version 25.6.2.5432 
2025-07-09 13:24:49,434 - mcp-clickhouse - INFO - Found 37 databases 
2025-07-09 13:24:49,435 - mcp.server.lowlevel.server - INFO - Processing request of type ListToolsRequest 
INFO:httpx:HTTP Request: POST https://api.anthropic.com/v1/messages?beta=true "HTTP/1.1 200 OK" 
2025-07-09 13:24:51,587 - mcp.server.lowlevel.server - INFO - Processing request of type ListToolsRequest 
2025-07-09 13:24:51,588 - mcp.server.lowlevel.server - INFO - Processing request of type CallToolRequest 
2025-07-09 13:24:51,589 - mcp-clickhouse - INFO - Listing tables in database 'github' 
2025-07-09 13:24:51,589 - mcp-clickhouse - INFO - Creating ClickHouse client connection to sql-clickhouse.clickhouse.com:8443 as demo (secure=True, verify=True, connect_timeout=30s, send_receive_timeout=300s) 
2025-07-09 13:24:51,965 - mcp-clickhouse - INFO - Successfully connected to ClickHouse server version 25.6.2.5432 
2025-07-09 13:24:54,143 - mcp-clickhouse - INFO - Found 19 tables 
2025-07-09 13:24:54,145 - mcp.server.lowlevel.server - INFO - Processing request of type ListToolsRequest 
INFO:httpx:HTTP Request: POST https://api.anthropic.com/v1/messages?beta=true "HTTP/1.1 200 OK" 
2025-07-09 13:25:00,234 - mcp.server.lowlevel.server - INFO - Processing request of type ListToolsRequest 
2025-07-09 13:25:00,235 - mcp.server.lowlevel.server - INFO - Processing request of type CallToolRequest 
2025-07-09 13:25:00,236 - mcp-clickhouse - INFO - Executing SELECT query: SELECT repo_name, COUNT(*) as contributor_count 
FROM github.actors_per_repo 
WHERE repo_name LIKE '%ClickHouse%' OR repo_name LIKE '%clickhouse%' 
GROUP BY repo_name 
ORDER BY contributor_count DESC 
LIMIT 10 
``` 

## Conclusion

Creating a Slack bot that can process natural language questions and execute them as queries over a ClickHouse data warehouse is surprisingly simple! This post covers a basic example that has enormous room for improvement and expansion; the bot could be extended to produce images from query results, save or share analytics work in external systems, and Slack is adding new ways to support agents in their interface which improve the experience.

Let’s build a self-service analytics agent that we can talk to directly in Slack, that transparently queries our ClickHouse data warehouse.

Al Brown

Blog posts by Al Brown

clickhouse.build: An agentic CLI to accelerate Postgres apps with ClickHouse

How to build AI agents with MCP: 12 framework comparison (2025)

Agentic BI in Slack: MCP-powered analytics and visualisations

Can LLMs replace on call SREs today?

You can’t UPDATE what you can’t find: ClickHouse vs PostgreSQL

Agentic analytics in Slack with ClickHouse, MCP, and PydanticAI

Resources by Al Brown

The definitive guide to ClickHouse query optimization (2026)

MCP and Data Warehouses: everything you need to know