AI Agents for ClickHouse: Connecting LLMs to Analytical DBs Without Cloud SubscriptionsArticle · digest for October 23
Cozy late-night still life of a data engineer's desk with a laptop, a structured ClickHouse prompt, and security configuration notes.
Article4 sections≈ 4 min

AI Agents for ClickHouse: Connecting LLMs to Analytical DBs Without Cloud Subscriptions

Handing SQL generation (Text-to-SQL) over to AI sounds like an executive dream: business users ask questions in plain language, and a model instantly spins up dashboards. Yet any data engineer who has tried wiring an LLM to a real analytical database knows how that story usually ends.

At a System Design & Highload community meetup, Alexey Belozersky (CDO at inSales / Sber B2B) shared practical engineering lessons from building a local AI agent for ClickHouse—with zero paid cloud subscriptions or foreign VPNs. Earlier, we looked at general requirements for corporate AI agent architecture and security. Today, let's dive into the practical specifics of working with a columnar DBMS.

Why Standard Models Trip Over ClickHouse

ClickHouse is built to scan billions of rows fast, but its SQL dialect has sharp edges you can't ignore:

  1. Aggregation quirks: instead of heavy COUNT(DISTINCT), you need uniqExact() or uniq().
  2. Partition pruning: filtering on the partition primary key (event_date) is mandatory—otherwise a single query can scan terabytes.
  3. Data types: LowCardinality(String) and quantiles like quantilesExact() need explicit handling.

The "Master Prompt" Concept: Packing Your Data Dictionary

In practice, 90% of project success comes down not to agent frameworks, but to how well you prepare your schema context—what the team calls the "Master Prompt":

  • Working with a strong model, the engineer thoroughly documents table structures, non-obvious column meanings, business entity definitions, and common pitfalls;
  • Then asks the model to distill and pack that entire body of knowledge into a dense, structured system prompt in English;
  • The prompt always includes a strict Negative Constraints section: explicitly forbidding COUNT(DISTINCT), requiring partition filters on every query, and demanding raw SQL output inside a single code fence.
# System prompt structure ("Master Prompt")
You are an expert ClickHouse Data Analyst.
Rules:
1. Always generate valid ClickHouse SQL dialect queries.
2. For unique counting use uniqExact(user_id), NEVER count(distinct user_id).
3. Always filter by partition key 'event_date' using toDate(now()) - INTERVAL 30 DAY.
4. Available Tables:
   - analytics.orders_daily: order_id (UInt64), user_id (UInt64), amount (Decimal64(2)), event_date (Date)
   - analytics.traffic_events: event_id (UUID), session_id (String), utm_source (LowCardinality(String))
Output ONLY the raw SQL query inside ```sql code fence.

Security and Sandboxing: Taming a Curious Agent

When developers give autonomous agents permission to run scripts in a local environment, a funny thing happens: models start showing a little too much initiative. In real-world tests, agents with terminal access routinely tried scanning the filesystem, peeking into /etc/passwd, or hunting for environment variables holding tokens.

That is why security has to be enforced at the DBMS layer itself, not just with software-level prompt filters:

-- Create a strict settings profile for the AI agent account
CREATE SETTINGS PROFILE ai_agent_profile
  SETTINGS
    readonly = 1,
    max_execution_time = 15,
    max_memory_usage = 4000000000,
    max_rows_to_read = 10000000;

-- Create a user bound to the restriction profile
CREATE USER ai_analyst IDENTIFIED BY 'StrongAgentSecret2026'
  SETTINGS PROFILE ai_agent_profile;

-- Grant SELECT strictly on specific analytical marts
GRANT SELECT ON analytics.orders_daily TO ai_analyst;
GRANT SELECT ON analytics.traffic_events TO ai_analyst;

Setting readonly = 1 guarantees the agent physically cannot run DROP, ALTER, INSERT, or create temporary tables. Meanwhile, hard caps on max_execution_time and max_memory_usage protect your server from freezing up if the model generates a runaway join.

Always verify generated queries

Even with an airtight prompt, an analytical agent is an assistant, not an infallible oracle. Any non-trivial SQL query should still be reviewed by a human before it feeds into financial reporting.

Practical Takeaway

Building an AI analyst for ClickHouse proves that reliable tooling doesn't require expensive cloud APIs. A local language model, tightly packed schema context in a "Master Prompt," and strict database-level permissions give you a fast, private, and safe way to query corporate data.

Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 2