How to get your ClickHouse data ready for agentic AI
Load source data into typed ClickHouse tables with Airbyte, with dedup on ReplacingMergeTree, so AI agents run analytical queries on a governed copy.

Airbyte loads source data into typed ClickHouse tables with every sync mode, including deduplication on ReplacingMergeTree, so agents can run fast analytical queries on a governed copy in ClickHouse Cloud or your own cluster.
ClickHouse is built for analytical queries over large tables, which is exactly the kind of query agents tend to write. "Break down last month's events by plan and country" or "which accounts had the biggest drop in usage" are scans and aggregations, and ClickHouse is designed for them.
What agents need from ClickHouse is the same as from any warehouse: a centralized, governed copy of source data with predictable tables, clear types, and a known story about duplicates and deletes. This guide covers how Airbyte's ClickHouse destination provides that, and where ClickHouse behaves differently from other destinations in ways agents should know about.
Why ClickHouse matters for agentic AI
- Analytical speed. Agents often iterate, running several queries to answer one question. A columnar OLAP engine keeps that loop fast.
- Large event data. Product events, logs, and ad reports grow quickly. ClickHouse handles high-volume tables that agents need to aggregate.
- Your choice of deployment. ClickHouse Cloud or a self-managed cluster, with the same connector.
- SQL that agents already know. Agents generate SQL, and ClickHouse exposes its data through SQL.
What landing data in ClickHouse means for agents
- Typed tables, not JSON blobs. The connector writes directly to typed columns that match the source schema, so agents filter and aggregate real types instead of parsing JSON.
- One table per stream. Each stream lands in its own table, in the configured database or in the database that matches the connection's namespace.
- Deduplicated tables, with a caveat. Append + Deduped produces one row per key after ClickHouse merges data. Agents should query with
FINALwhen they need guaranteed deduplicated results. - Joinable sources. CRM, billing, product, and ad data can share one ClickHouse instance, so agents can join across systems.
How Airbyte gets ClickHouse data ready
Version 2 of the destination
The current ClickHouse destination is a rewrite on Airbyte's Bulk CDK, replacing the legacy v1 connector. Compared with v1:
- All sync modes: Full Refresh Overwrite and Append, Overwrite + Deduped, Incremental Append, and Incremental Append + Deduped.
- Direct Load with typed columns rather than raw JSON tables, which improves query performance and reduces storage.
- ClickHouse's native binary protocol and batch inserts for loading.
Deduplication
Deduplication uses the ReplacingMergeTree table engine. ClickHouse removes duplicates as data parts merge, so until a merge happens a query can return duplicated or deleted rows. To guarantee deduplicated results, add FINAL:
SELECT * FROM your_table FINALYou can also tune merge settings to match your query patterns. For the best results, use a cursor column of an integer type, Date, or DateTime64. With other cursor types, such as strings, the connector falls back to _airbyte_extracted_at for ordering and logs a warning.
For agents, this is worth putting in their instructions: query deduplicated tables with FINAL, or query a view that applies it.
Types
- Decimals become
Decimal(38, 9). - Timestamps become
DateTime64(3), with millisecond precision. - Integers become
Int64, booleansBool, stringsString. - Objects become
JSONif you turn on Enable JSON, otherwiseString. - Arrays and unions become
String. Agents can parse them with ClickHouse's JSON functions.
Schema evolution
The connector evolves schemas automatically: it adds new columns and changes column types as the source changes. One difference from most destinations matters for agents: when a column is removed from the source, ClickHouse drops the column and its historical data. Other destinations usually keep the column and write NULL for new rows. If agents rely on a column's history, keep a copy elsewhere before the source removes it.
Setup
- Requirements: ClickHouse Cloud or self-hosted, server version 21.8.10.19 or later.
- Network: allow Airbyte Cloud's IP addresses, on port 8443 (HTTPS) or 8123 (HTTP) for self-managed servers. If ClickHouse can't be exposed, use an SSH tunnel through a bastion host. SSH tunneling is in beta.
- User: create a dedicated ClickHouse user for Airbyte with rights to create and manage databases and tables and to insert and select data, on the default database and each namespace you use. If
async_insertis enabled, turn it off for this user. - Batching: Record Window Size caps records per batch, and batches are limited to 70 MB regardless.
Self-managed clusters
By default, data is stored only on the node that receives each insert, so on a multi-replica cluster, reads through a load balancer can miss rows. Two options fix that:
- Enable Replication so data is replicated across nodes.
- Cluster Name so table definitions reach every node, which is needed when databases use the default Atomic engine.
ClickHouse Cloud uses SharedMergeTree and needs neither.
How agents reach ClickHouse data
The Airbyte context layer's read-only SQL access is documented for BigQuery and Snowflake destinations, not ClickHouse. Agents query ClickHouse through your own SQL tooling. A good pattern is a separate, read-only ClickHouse user for agents, distinct from the Airbyte user that writes data, limited to the databases agents should see.
Airbyte helps with the pipeline. Through the Airbyte MCP at https://mcp.airbyte.com/mcp, an agent can create connections to a ClickHouse destination, run syncs, check status, and read job logs. Use ?read-only=true when the agent should only observe.
If your agents also need the context layer's direct reads or search, you can sync the same sources to BigQuery or Snowflake in a separate connection.
What this unlocks
- Typed, analytical tables that agents can scan and aggregate quickly.
- Deduplicated tables with a clear rule for querying them.
- Automatic schema changes, with a known behavior for removed columns.
- The same pipeline for ClickHouse Cloud and self-managed clusters.
- Pipeline management by agents through the Airbyte MCP.
Getting started
- Create a dedicated Airbyte user in ClickHouse and allow network access.
- Create a ClickHouse destination in Airbyte and decide whether to enable JSON.
- Connect sources with Incremental Append + Deduped and integer or timestamp cursors.
- Give agents a read-only ClickHouse user and tell them to use
FINALon deduplicated tables.
Try Airbyte free or talk to sales about ClickHouse for agents.
Frequently asked questions
Why do I see duplicate rows in ClickHouse after a deduplicated sync?
The connector deduplicates with the ReplacingMergeTree engine, which removes duplicates when parts merge, not at insert time. Add FINAL to queries, such as SELECT * FROM your_table FINAL, to guarantee deduplicated results, or tune merge settings to fit your query patterns.
Which cursor column types work best for deduplication?
Integer types, Date, and DateTime64 timestamps. With other cursor types, such as strings, the connector falls back to the _airbyte_extracted_at timestamp for ordering and logs a warning.
What happens when a source column is removed?
Unlike most Airbyte destinations, ClickHouse drops the column and its historical data from the destination table. Other destinations usually keep the column and write NULL for new rows. Plan for this if agents query historical values.
Does the connector work with self-managed ClickHouse clusters?
Yes. For clusters with multiple replicas, turn on Enable Replication so data is replicated across nodes, and set Cluster Name when your databases use the Atomic engine so table definitions reach every node. ClickHouse Cloud needs neither option.
Can agents query ClickHouse through the Airbyte context layer?
Not at the time of writing. The context layer's read-only SQL access is documented for BigQuery and Snowflake destinations. Agents query ClickHouse through your own SQL tooling, ideally with a dedicated read-only ClickHouse user.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.
