ClickHouse and Kafka integration: what the Kafka table engine does
A team with Kafka topics carrying events or logs usually wants those records queryable in self-hosted ClickHouse without running a separate loader. The supported pattern is a Kafka table engine table paired with a materialized view that reads from it and writes into a MergeTree table. The Kafka table engine does not store rows. It acts as a consumer: ClickHouse itself connects to the brokers, pulls messages from the topics it is pointed at, and hands each batch to the materialized view, which inserts the transformed rows into the table your analysts query. The first design decision is therefore whether ClickHouse should be the consumer at all, because every ClickHouse node that reads the topic adds broker connections, consumer group membership and load that your Kafka operators need to account for.
Because the engine runs inside the database, the settings that matter are configuration keys on the Kafka table and related server settings, not a separate connector process. Broker addresses, the consumer group name, the topic list, the data format, and the security options such as SASL mechanism and TLS parameters all live in that definition. The exact setting names, the SASL mechanisms supported, and how the engine behaves on flush and fsync differ between releases. Confirm each of them against the ClickHouse Kafka engine documentation for the precise version you run, starting from the ClickHouse production FAQ guidance on choosing a release and testing it first. Treat that check as a gate before the design is fixed, not as a later tuning task.
Plan the pre-production test around your own topics: message sizes, schema evolution, the rate at which consumer lag builds during a broker outage, and what happens to offsets when a ClickHouse node restarts mid-batch. The durability behavior is the point most often misunderstood, so confirm in writing with your vendor or in the release notes how committed offsets relate to rows already written to disk before you promise a delivery guarantee to the business. Those results determine whether the materialized view pattern is sufficient or whether a separate sink with stronger semantics belongs in the design.
What the ClickHouse and Kafka integration needs before setup
The Kafka integration depends on decisions made outside ClickHouse: which release line you run, which environment proves the design, and who controls the brokers, topics and certificates. Each card below names one prerequisite and says whether you supply it or DeployOpen sets it up.
A supported ClickHouse release
Start on a release line that still receives security updates. The security policy lists 26.9, 26.8, 26.7 and 26.3 as supported, and all other versions as unsupported. Stable releases ship roughly monthly, and LTS releases ship twice a year. Choose the line before writing the Kafka table DDL, because engine behavior and settings can change between releases. DeployOpen confirms the version with you, and you approve it.
A non-production test environment
Build a pre-production ClickHouse cluster that receives representative topics, message sizes, key distribution and peak volumes. The ClickHouse production guidance recommends testing with representative data and queries before any change reaches production. Client supplies the sample topics and a volume estimate. DeployOpen sets up the test cluster in your infrastructure and runs the replay.
Kafka brokers and topic ACLs
The integration needs a dedicated Kafka principal with read access to the source topics, the consumer group and the topic metadata it uses. Confirm that the broker authentication mechanism matches what ClickHouse will present, and that the account cannot write to production topics. Client supplies the broker endpoints, the principal and the ACL grants. DeployOpen documents the minimum set and checks it in the test environment.
Network routes and firewall rules
Every ClickHouse node that consumes from Kafka must reach every broker address the client library returns, not only the bootstrap address. Advertised listener names are a common cause of failed consumers. Check routes and firewall rules from each ClickHouse node to each broker port, including any cross-zone or cross-site path. Client supplies the network change requests. DeployOpen supplies the port matrix and runs connectivity tests from each node.
CA and client certificates for TLS
Decide which certificate authority signs broker and client certificates, who issues them, and how renewal is handled before expiry. Keep the CA chain and the ClickHouse client certificate in a secret store with a named rotation owner. Client supplies the internal CA and the issuance process. DeployOpen configures the ClickHouse side and tests the handshake, including a rotation dry run.
A named owner for the topic schema
Name one person or team who owns each topic's message format, field types and version changes. The Kafka table engine maps messages onto columns, so an unannounced producer change can break parsing or drop rows into errors. The owner approves the schema before the materialized view is created, and agrees how changes are announced. Client supplies the owner and the schema definitions. DeployOpen maps them to the ClickHouse table design.
How consumer groups and offsets decide what gets loaded
The consumer group decides what gets loaded. Each Kafka table engine in ClickHouse joins the group named in its settings, and Kafka assigns the topic's partitions across the consumers in that group, so each partition is read by one consumer at a time. Committed offsets are stored per group, per partition. When a table is detached, restarted or the server is upgraded, the engine resumes from the last committed offset for its group, not from the start of the topic. Two tables that share a group name will split one stream between them, which is rarely what teams intend. Confirm the group naming rules and the offset reset behavior (what happens when no committed offset exists) in the Kafka engine documentation for your release before you pick names.
The main design choice is how many topics and consumers sit behind each table. One topic per table keeps offsets, lag and failure handling easy to reason about, and it is the simpler model to operate. Several topics per table can work when the rows share one schema, but a malformed message on one topic then affects the whole table's consumer. The number of consumers is capped by the partition count: consumers beyond the number of partitions sit idle. Ask the platform team to size partitions and consumer threads together, and to write down the expected behavior of the group when a consumer crashes and its partitions are reassigned.
Topic lifecycle events cause the most surprises. If retention expires before ClickHouse reads a message, that data is gone from the topic and cannot be loaded later from Kafka, so consumer lag must be alarmed well below retention. If a topic is deleted and recreated with the same name, committed offsets for the old partitions may no longer match the new log, and the engine can start from an unexpected position. Plan a runbook that states who decides whether to reset offsets, how the decision is recorded, and how the affected time range is backfilled from a source you control.
Configuring the ClickHouse and Kafka integration in order
This sequence takes a self-hosted ClickHouse cluster from an empty state to a working path from a Kafka topic into a MergeTree table. Each step ends with a check so you can confirm that the consumer, the security settings and the data flow work before you rely on them.
Create the test topic and consumer identity
Your Kafka administrator creates a dedicated test topic and a service account that can read it, with no write or admin rights beyond what the integration needs. Use a separate topic for testing so that production data is not affected by early mistakes. Record the broker addresses, the topic name, the SASL mechanism and the consumer group name you plan to use. Done means the account can read the topic from a Kafka client outside ClickHouse, and the topic shows the expected partition count on the broker.
Configure TLS and SASL on the ClickHouse side
On each ClickHouse server, add the Kafka connection settings in the server configuration or in a named collection, following the Kafka engine documentation for your version. Include the CA certificate used by the brokers, the SASL mechanism and the service account credentials, and keep the password out of plain text where your secrets process allows it. Confirm the exact configuration keys against your ClickHouse release before you write them. Done means the server starts cleanly after a configuration reload and the settings are visible in the server logs without errors.
Create the Kafka engine table
Create a table with the Kafka table engine that names the broker list, the topic, the message format and a consumer group unique to this integration. The Kafka engine table does not store data; it reads from the topic when queried or when a materialized view is attached. Keep the consumer group name specific to this pipeline so offsets are not shared with other consumers. Done means the consumer group appears on the broker with an active member assigned to the topic partitions.
Create the target MergeTree table
Create the destination table with a MergeTree engine, column types that match the message fields, and an ordering key chosen from the query patterns you expect, such as a timestamp plus a tenant or source identifier. Decide how the table will be replicated before production, since a ReplicatedMergeTree table is the usual choice for a replicated cluster. Keep the ordering key stable, because changing it later means rebuilding the table. Done means the table exists on every node that will hold it and a manual insert returns the expected row count.
Create the materialized view that moves rows
Create a materialized view that selects from the Kafka engine table and writes to the MergeTree table, with any parsing or type conversion applied in the SELECT. The view runs on each block of consumed rows, so the query should be simple and deterministic. Attach it only after the target table and the Kafka table both exist. Done means the view is listed in the system tables for the database and the Kafka consumer is no longer idle when messages arrive.
Produce test messages and verify the row count
Produce a known number of test messages to the topic, using a fixed set of payloads that includes at least one malformed record so you can see how parsing errors surface. Wait for ingestion, then compare the row count in the MergeTree table with the number of valid messages you produced. Check the consumer group offsets on the broker to confirm they advanced to the end of the topic. Done means the counts match, the offsets match, and any malformed records are accounted for in your error handling.
Confirm the durability settings before production
Review the durability behavior described in the Kafka engine documentation for your ClickHouse version, including how offsets are committed and what happens when a node stops between consuming and writing. Decide whether the fsync-related settings need to change for your risk tolerance, and record that decision with the reasons. Ask your ClickHouse engineers to confirm each setting against your version rather than relying on older examples. Done means the decision is written down and a controlled restart test produces no duplicate or missing rows beyond what you have accepted.
Mapping Kafka messages to ClickHouse columns
This table maps each part of a Kafka message to a suggested ClickHouse column type, the reason for it, and the decision your team must make before building the Kafka table engine and materialized view. Read it as a checklist: settle each row with your producers and analysts before writing the table DDL.
| Message part | Column type recommendation | Reason | Decision for your team |
|---|---|---|---|
| Message key | String, or a typed column if keys are fixed-format identifiers | Keys are often identifiers or partitioning hints and may be absent or binary | Confirm whether keys carry business identity, whether they are unique, and whether any query filters on them |
| Value payload | Raw String column for the original bytes, plus typed columns parsed from it | Keeping the raw payload lets you re-parse after schema changes or parser bugs | Decide whether to store raw payloads and parse later (more storage, more flexibility) or parse at ingest (less storage, stricter schema) |
| Timestamp | DateTime64 with explicit precision, using the producer's event time | Kafka's broker timestamp reflects arrival, which can differ from when the event happened | Choose which clock defines event time, and whether late or out-of-order events are acceptable in the same table |
| Partition | UInt32 or Int32 column populated from the Kafka metadata | Partition identifies the source stream and helps trace duplicates or gaps during investigations | Decide whether you need the column at all, since it is useful for debugging but adds little to analytics queries |
| Offset | UInt64 column populated from the Kafka metadata | The offset, combined with partition and topic, identifies a message position for replay and reconciliation | Decide whether offsets are retained for audit trails and how they are reconciled against replays |
| Headers | Map(String, String) or a raw String column for header content | Headers often carry trace IDs, tenant identifiers or content types that are useful for filtering | Confirm which headers your producers set, their encoding, and which ones must be kept for compliance |
| Message format (for example JSON) | Parse with a documented format on the Kafka table engine, then cast into typed columns in the materialized view | The format determines how fields map to columns, and mismatched formats cause parse errors that stop ingestion | Confirm the format with the producer team and agree how malformed messages are logged, counted and routed |
| Nullable fields and missing keys | Use Nullable only for fields that are genuinely optional, with defaults for the rest | Nullable columns add storage and query overhead, and missing fields can silently become default values | Decide which fields may be absent, what default each should take, and how absent values appear in dashboards |
Securing the Kafka connection and the ClickHouse side
The Kafka table engine holds a long-lived consumer in your ClickHouse cluster, so it needs its own identity on the Kafka side and its own limits on the ClickHouse side. The cards below cover the Kafka principal, transport encryption, Kafka ACLs, ClickHouse user roles, credential handling and the default account.
Dedicated SASL principal
Create a Kafka principal used only by ClickHouse, not a shared application or administrator account. Scope it to the specific topics and the consumer group the engine will use, so a leaked credential cannot read other streams. Confirm which SASL mechanisms your Kafka distribution and your ClickHouse release support for the Kafka engine before you pick one, and plan how the secret is provisioned and rotated.
TLS for broker traffic
Encrypt traffic between ClickHouse and the brokers, and verify the broker certificate chain against a CA your team controls. Confirm the TLS settings the Kafka engine accepts in your ClickHouse version, including the CA file, client certificate and hostname checks. Plan certificate rotation from the start, with a documented window in which ClickHouse picks up the renewed certificate and a test that proves consumption continues afterward.
Least-privilege Kafka ACLs
Grant the ClickHouse principal read access on the source topics and access to its consumer group, and nothing else. Deny produce and administrative rights so the consumer cannot write back into a stream or change topic configuration. Review ACLs whenever a topic is added to the engine, because a missing group permission often surfaces only as a stalled consumer rather than a clear authorization error.
Scoped ClickHouse engine users
Give the team that creates Kafka tables and materialized views a role that can create and drop the engine tables and view the pipeline, while keeping analysts on read-only roles with row policies where needed. The ClickHouse access control documentation describes users, roles, row policies and settings profiles as the building blocks. Confirm which grants your release needs to create a Kafka engine table, since they are broader than a plain SELECT.
Keeping credentials out of logs
Avoid putting SASL passwords directly in CREATE TABLE statements, because those statements can appear in query logs, system tables and shared migration files. Check whether your ClickHouse release supports named collections or another secret store for Kafka settings, and confirm how masking behaves in logs before relying on it. Keep the broker configuration file out of shared repositories and restrict who can read the server configuration directory.
Restricting the default user
The default ClickHouse user starts with all permissions, so a cluster exposed with that account has no meaningful access boundary. Disable or restrict the default user, set a password or certificate for administrators, and move application and pipeline access to named users. Verify the change on every replica, because a server that still accepts the default account is a gap that a single-node test will not reveal.
The durability gap and the fsync setting to decide before go-live
The durability gap is the window in which data a Kafka consumer has read and handed to ClickHouse can be lost if the server crashes before that data reaches stable storage. ClickHouse writes parts to disk and flushes them on its own schedule, so a crash in that window can drop rows that the Kafka offset has already advanced past. Before go-live, the team needs to confirm in the ClickHouse documentation for the exact release it runs how fsync-related settings control how much of that window is closed, what the defaults are, and what each option costs in write throughput. Those names and defaults change between releases, so treat any value you read in a blog post or older guide as unverified until you check it against your version.
Record the decision as a written risk acceptance, not as a server default someone happened to keep. The record should state the business tolerance for both loss and duplication, because the two trade against each other: a configuration that reduces loss can increase replays after a restart, and the downstream tables must tolerate those replays. Ask the platform owner, the data owner and whoever signs off on data integrity to agree on the number of seconds or messages of loss they can accept, and link that figure to the fsync and commit settings chosen. Revisit the decision whenever the ClickHouse version or the ingestion rate changes.
Replication does not close this gap on its own. A replica can only copy what the leader has committed, and a Kafka consumer on a crashed node may already have advanced its offset for rows that were never durably written, so the healthy replica may not hold them either. Replication protects against node loss, while backups protect against deletion, corruption and a bad consumer that writes wrong data. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly, so schedule a restore of a backup into a spare cluster and compare row counts and checksums against the Kafka offsets you expect to find.
Common failures in ClickHouse and Kafka integration
This table maps the failures teams most often hit when a Kafka topic feeds ClickHouse through the Kafka table engine and a materialized view. Each row gives a symptom, the likely cause and a check the team can run before changing anything. Work through the checks in order, since many symptoms share a root cause at the consumer or materialized view layer.
| Symptom | Likely cause | Fix or check to run |
|---|---|---|
| No rows arrive although the topic has messages | Message format does not match the table's declared format, or the materialized view that moves rows into the MergeTree table is missing | Read a sample message from the topic, compare its format with the Kafka table's format setting, and confirm a materialized view reads from the Kafka table and writes to the target table |
| No rows arrive and the consumer group looks idle | The consumer group name is shared with another reader or changed after deployment, so offsets point elsewhere | List the consumer group offsets with Kafka tooling, confirm the group name in the table definition, and check that only one intended ClickHouse table uses it |
| Consumers stop after a broker or certificate change | Consumers hold stale connection settings, or the new certificate is not trusted by the ClickHouse host | Confirm the broker list and CA bundle on every ClickHouse node, restart or detach and reattach the Kafka table after the change, and check the server log for connection errors |
| Duplicate rows appear after a restart or failover | Offsets were not committed after inserts completed, so messages were read again on recovery | Compare row counts against offsets for a test window, confirm the target table tolerates repeated rows or uses a deduplication strategy, and test restart behavior on a non-production cluster |
| TLS handshake fails | Certificate chain is incomplete, the CA is missing from the ClickHouse trust store, or the broker hostname does not match the certificate name | Verify the chain with an OpenSSL client from the ClickHouse host, check that the advertised broker hostname appears in the certificate subject or SAN, and confirm the CA file path in the server configuration |
| SASL authentication is rejected | Wrong mechanism, expired or rotated credentials, or a user without read permission on the topic or consumer group | Confirm the mechanism configured on ClickHouse matches the broker listener, test the credential with a standard Kafka client from the same host, and review the ACLs for the topic and group |
| Consumer lag grows during bursts | Insert batches are too small, a single consumer handles all partitions, or merges on the target table fall behind | Compare consumed partitions with the table's consumer count, review batch size and flush settings, and check merge and part counts on the target table during the burst |
| Parsing errors after a producer schema change | A new or renamed field, a changed type or a reordered column no longer matches the table's column definitions | Sample messages from the topic after each producer release, add a validation step in a staging table, and version the materialized view so the mapping can change without losing offsets |
Monitoring consumer lag so the pipeline does not fall behind silently
Lag in this integration has two parts, and you need to watch both. The first is consumer group lag: the distance between the newest offset on each partition of the topic and the offset the ClickHouse consumer group has committed. The second is row freshness: the age of the newest row in the target MergeTree table. A consumer can keep its offsets moving while a materialized view silently drops or fails to write rows, so offset lag alone does not prove data is arriving. Track both, and set the thresholds yourself, because they depend on your event volume, your acceptable delay and how your dashboards use the data. Ask your ClickHouse team to document the threshold for each signal before go-live.
For consumer group lag, use the Kafka tooling your platform team already runs, such as the consumer group describe command in the Kafka distribution or your exporter for it. Confirm the consumer group name that the Kafka table engine uses in your release, since it is set in the table definition. On the ClickHouse side, query the Kafka consumer state and error columns in the system tables that the release exposes for Kafka consumers, and alert on rising exception counts and on consumers that stop reporting assignments. For freshness, schedule a query that returns the maximum event timestamp in the target table and compare it with the current time. Confirm the exact system table and column names against the documentation for your version before writing alert rules.
Connect these signals to the monitoring stack you already operate, whether that is Prometheus with Grafana, Zabbix or another platform, so that lag alerts reach the same on-call rotation as the rest of your data platform. Where ClickHouse metrics are exported, confirm the metric names for your release, because names change between versions and the upgrade process can break dashboards. Treat a freshness alert as the primary page and an offset alert as the diagnostic, and test both by pausing the consumer in a pre-production environment with representative traffic. A monitoring setup that has never fired is not yet proven to work.
Questions buyers ask about ClickHouse and Kafka integration cost and effort
What drives the effort of a ClickHouse and Kafka integration?
The effort is driven mostly by how many topics you need to ingest, how varied their message formats are, and how stable their schemas stay over time. Each topic usually needs its own Kafka table engine, a materialized view that maps fields into typed MergeTree columns, and tests for malformed messages. Durability requirements add work, because the documented durability gap and the fsync settings must be explained to the data owners and accepted in writing. Existing Kafka security also matters: if brokers already use SASL and TLS with managed credentials, the work is mostly configuration; if they do not, certificates, principals and ACLs must be designed first. Ask for a written inventory of topics, formats and security before any scope is agreed.
Can an internal team integrate ClickHouse with Kafka without outside help?
Yes, if the team already operates both Kafka and ClickHouse and has a test environment that holds representative messages and realistic volumes. The core work is well documented: consumer group settings, table engine definitions, materialized views and monitoring of consumer lag. What usually takes time is the data contract work with topic owners, the handling of messages that cannot be parsed, and the restore and replay procedures that must be tested before production use. Teams that lack one of these skills often bring in a partner for a fixed-scope review or build. Confirm your team's coverage of both platforms and of the test environment before deciding.
What must our team provide before an integration project starts?
Plan to provide several items before work begins:
- Named owners for each Kafka topic who can confirm schemas, field meanings and expected volumes.
- Broker addresses and a service account with read access to the agreed topics, plus network paths from the ClickHouse nodes.
- The TLS certificates, CA chain and SASL mechanism your brokers use, with a plan for how they are stored and rotated.
- Sample messages from production or a representative source, including malformed examples.
- The target retention for the ClickHouse tables and the replay window you need if a load fails.
- A test ClickHouse cluster that resembles production.
Missing items are the most common cause of schedule slips, so ask for this list in the written scope.
How long does a ClickHouse and Kafka integration take to deliver?
The duration depends on the drivers, not on a fixed number. The main ones are the number of topics, how many message formats need parsing, whether schemas are versioned, how quickly topic owners and security teams respond, and whether a test environment already exists. Durability decisions and load testing at production rates also add time, since they often reveal tuning needs. A proper plan splits the work into a discovery stage, a build stage for one pilot topic, and a rollout stage for the rest. Ask any provider to show how each driver affects the schedule in the written scope, and to name the stages and the acceptance tests for each.
What recurring work follows go-live for a Kafka-fed ClickHouse cluster?
Once the integration is live, recurring work includes ClickHouse upgrades, since the production FAQ asks teams to read the changelog and test in a pre-production environment before each upgrade. Certificate and credential rotation for Kafka and ClickHouse must be scheduled and tested. Consumer lag needs continuous monitoring with alert thresholds agreed with the data owners. Schema changes in source topics require review and sometimes new materialized views. Restore tests are also recurring, because the backup documentation warns that a backup never test-restored will probably not restore properly. Decide who performs each task, how often, and whether a retainer will cover it.
How does DeployOpen produce a quote for this integration?
DeployOpen produces a quote from a written scope. Most engagements are fixed-scope, so scope, deliverables and timelines are agreed in writing before work starts. The scope should list the topics in and out of the work, the message formats, the security model for brokers and ClickHouse, the durability decisions, the monitoring and restore tests, and the handover material. Ongoing support, upgrades and continuous testing can be handled through a retainer that is also defined in writing. DeployOpen does not publish a price list, so the quote follows from that written scope. Ask for each deliverable and each assumption to be listed separately so you can compare options.
Which security controls add cost to a Kafka to ClickHouse integration?
Security adds work in three places. On the Kafka side, SASL authentication and TLS for client connections need credentials, certificates and ACLs for each consumer group. On the ClickHouse side, the Kafka engine connection settings, named collections or stored secrets and the users that run the materialized views must be scoped to the minimum permissions, and the default user should be restricted. Operationally, certificate rotation and credential audits recur. Cost rises when security teams have not yet defined certificate authorities or secret stores, because those decisions must come first. Confirm which controls your security policy requires before the scope is written.
How DeployOpen scopes and delivers ClickHouse and Kafka integration
DeployOpen plans, builds, documents and supports the Kafka to ClickHouse pipeline inside your own infrastructure, whether that is your cloud accounts, data centers or restricted networks. Engagements are fixed-scope, with the scope, deliverables and timeline agreed in writing before work starts.
How we scope
- We confirm the Kafka cluster version, topic list, message formats and expected event volumes before writing the scope.
- The written scope names each Kafka table engine, materialized view and target MergeTree table the engagement will deliver.
- We agree in writing how SASL, TLS and consumer group ownership will be handled, and who owns each credential.
How we build
- We build the pipeline in a pre-production environment with representative messages and replay of realistic topic traffic.
- We test consumer group rebalancing, offset handling and the fsync trade-off against your durability requirements before production.
- We set up lag and consumer error monitoring so the team can see whether data is keeping up with the topics.
How we hand over
- You receive configuration, DDL, runbooks and the documented durability limits, written for the engineers who will operate the pipeline.
- We walk your platform and data teams through restart, offset reset and failed-message procedures on your own environment.
- Handover includes a checklist of the settings and credentials your team must review before each ClickHouse or Kafka upgrade.
How we support
- Ongoing support, upgrades and continuous testing are available as retainers after handover.
- A retainer can cover ClickHouse version reviews against the security and release policy before each planned upgrade.
- Ask any quote to state which Kafka and ClickHouse versions are covered, and who answers pipeline incidents.
