ClickHouse

This guide covers how to configure ClickHouse as a warehouse in Zeotap, including connection setup over the HTTP interface, authentication, and required permissions. Both ClickHouse Cloud and self-managed ClickHouse deployments are supported.

Prerequisites

• A ClickHouse deployment (ClickHouse Cloud or self-managed, version 24.x recommended)
• The HTTP interface enabled and reachable (port 8123 for HTTP, 8443 for HTTPS — ClickHouse Cloud uses 8443)
• A ClickHouse user with a password (password authentication is the only supported method)
• Network access from Zeotap to your ClickHouse instance

Connection Configuration

Required Fields

Host

Use just the hostname, without https://:

# ClickHouse Cloud
your-instance.clickhouse.cloud

# Self-managed
clickhouse.internal.mycompany.com

Port and Protocol

Zeotap talks to ClickHouse over the HTTP interface (not the native TCP protocol on port 9000). The defaults are:

• 8123 — plain HTTP (the ClickHouse default; suitable for dev environments)
• 8443 — HTTPS (what ClickHouse Cloud exposes)

Set Protocol to https for any production deployment. Plain http should only be used for local development or private-network test environments.

Database

The database you specify is the default database for schema browsing and model queries. In ClickHouse, a database is the equivalent of a schema in other warehouses — tables are referenced as `database`.`table`.

SELECT * FROM `customer_data`.`users`

ClickHouse identifiers are case-sensitive: Customer_Data and customer_data are different databases. Zeotap preserves the exact case of every identifier it discovers and quotes identifiers with backticks.

Authentication

Password

Zeotap authenticates to ClickHouse with a username and password, sent as X-ClickHouse-User / X-ClickHouse-Key headers on every HTTP request. Always use HTTPS in production so credentials are encrypted in transit.

Password authentication is the only supported method in this release — SSH keys, client TLS certificates, and interserver credentials are not supported.

To create a dedicated user:

CREATE USER zeotap_cdp IDENTIFIED WITH sha256_password BY 'a-strong-password';

In ClickHouse Cloud, you can also create the user and set its password from the console under Settings > Users.

Required Permissions

Grant the Zeotap user read access to your source database, plus read/write access to the operational databases Zeotap manages (cdp_planner, cdp_audit, cdp_journey, cdp_identity, cdp_raw, and audit_logs) — plus cdp_prep, but only if Data Prep is enabled for the workspace, and cdp_metadata, but only if the metadata export is enabled for the workspace (see below):

-- Read access to the source database
GRANT SELECT ON customer_data.* TO zeotap_cdp;
 
-- Zeotap operational databases (created on first use)
GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON cdp_planner.* TO zeotap_cdp;
GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON cdp_audit.* TO zeotap_cdp;
GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON cdp_journey.* TO zeotap_cdp;
GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON cdp_identity.* TO zeotap_cdp;
GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON cdp_raw.* TO zeotap_cdp;
GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON audit_logs.* TO zeotap_cdp;

• cdp_planner — plan tables used for incremental sync change detection
• cdp_audit — sync run and audit history
• cdp_journey — orchestration member state and event log tables
• cdp_identity — identity resolution output (default database; configurable per identity graph)
• cdp_raw — landing database for loader (inbound ELT) runs (default database; configurable per loader)
• audit_logs — observability events and event delivery logs

The connection test verifies each of these permissions and reports the exact GRANT statement to run if one is missing.

cdp_prep — only if Data Prep is enabled for the workspace

Data Prep is a per-workspace entitlement, off by default. A workspace that has it builds prepared tables into one more database, cdp_prep, and its connection test then carries a seventh write probe (write_prep). Until Data Prep is enabled nothing creates or reads this database, so there is nothing to grant.

GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON cdp_prep.* TO zeotap_cdp;

That is the same grant as the six above, and every privilege in it is load-bearing for a prepared table, which — unlike the other operational databases — is REBUILT and, when it is incremental, updated in place:

• CREATE TABLE — each build materialises the table plus a transient <slug>__next / <slug>__delta sibling
• INSERT — an append build writes the new window; a merge build writes the merged result
• ALTER — ClickHouse expresses row deletion as ALTER TABLE … DELETE, which is how a delete_when predicate and a window replacement are applied
• DROP TABLE — the transient siblings are dropped after the swap, and EXCHANGE TABLES (the atomic swap itself) requires CREATE TABLE and DROP TABLE on both sides
• SELECT — the build reads the table’s own previous contents to compute the delta, and a Model then reads the result

Revoking the entitlement does not drop the database or its tables, so this grant can be withdrawn at your convenience rather than urgently.

cdp_metadata — only if the metadata export is enabled for the workspace

The metadata export is a per-workspace entitlement, off by default, that keeps the workspace’s audience catalogue as the table cdp_metadata.audiences. When it is enabled for a workspace, the connection test of the source chosen to hold the table carries one more write probe (write_metadata); the workspace’s other sources are tested exactly as before. Until then nothing creates or reads this database, so there is nothing to grant.

GRANT CREATE DATABASE, CREATE TABLE, SELECT, INSERT, ALTER, DROP TABLE
  ON cdp_metadata.* TO zeotap_cdp;

The same grant as above. Each export creates audiences__next, inserts into it, publishes it with EXCHANGE TABLES (which needs CREATE TABLE and DROP TABLE on both sides) and drops what is left. Grant readers SELECT ON cdp_metadata.* rather than on the table, since the exchange replaces the table object.

Revoking the entitlement does not drop the database or its table.

Data Types

When Zeotap creates tables in ClickHouse (operational tables, event tables), it maps its logical types the other way: strings become String, integers Int64, floats Float64, booleans Bool, timestamps DateTime64(3), dates Date, JSON values are stored as String, and string arrays become Array(String). Nullable columns are wrapped in Nullable(...) — except arrays, since ClickHouse does not allow Nullable(Array).

ClickHouse-Specific Notes

• MergeTree tables — Tables Zeotap creates use the MergeTree engine, ClickHouse’s standard table engine for production workloads.
• Lightweight deletes — Where rows must be removed, Zeotap uses lightweight DELETE FROM ... WHERE ... statements, which are supported on MergeTree tables in ClickHouse 24.x.
• No native CDC — ClickHouse has no equivalent of Snowflake Streams or Delta Lake Change Data Feed. Incremental syncs work by diffing plan tables between runs, the same mechanism used for BigQuery. This is transparent — syncs behave identically, with change detection computed by Zeotap.
• No staged loading — When Zeotap loads data into ClickHouse (loaders, event forwarding), records are inserted directly over the HTTP interface in batches (INSERT ... FORMAT JSONEachRow). ClickHouse’s HTTP inserts are high-throughput, so no cloud-storage staging step is needed — there is nothing like the GCS staging setup that Databricks requires.
• No custom-code transforms — JavaScript and Python custom-code field transforms are not available on ClickHouse sources. ClickHouse executable UDFs require server-filesystem configuration, which is not viable for managed deployments. Use SQL-based transforms instead; attempts to configure a JS/Python transform are rejected with a clear message.
• Query validation without execution — SQL is validated with EXPLAIN, which resolves tables and columns without running the query. ClickHouse has no bytes-scanned dry run, so query cost estimates are not available for ClickHouse sources.
• Journey rehearsal with event conditions — Journey rehearsal (simulation) evaluates event conditions as-of each member’s simulated clock, which requires correlated subqueries that ClickHouse does not support. Rehearsing a journey whose tiles use event conditions fails with a clear compile-time message on ClickHouse sources; live journey execution of the same conditions is fully supported (compiled as semi-joins instead).

Example Configuration

curl -X POST https://agentic.zeotap.com/api/v1/sources \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Production ClickHouse",
    "type": "clickhouse",
    "config": {
      "host": "your-instance.clickhouse.cloud",
      "port": 8443,
      "protocol": "https",
      "database": "customer_data",
      "auth_method": "password"
    },
    "credentials": {
      "username": "zeotap_cdp",
      "password": "a-strong-password"
    }
  }'

Network Configuration

If your ClickHouse deployment restricts inbound traffic (IP filters in ClickHouse Cloud, firewalls or security groups for self-managed instances), ensure that Zeotap can reach the HTTP interface port.

All Zeotap connections to your instance originate from these static egress IPs:

To configure IP filtering in ClickHouse Cloud:

1. Open your service in the ClickHouse Cloud console
2. Go to Settings > IP Access List
3. Add Zeotap’s egress IPs (34.76.7.172, 34.22.225.249) to the allowlist

These addresses are stable — Zeotap does not rotate them. If the list ever changes, this page is updated first.

Troubleshooting

Next Steps

• Create a model using your ClickHouse warehouse
• Use the SQL editor to write ClickHouse SQL queries
• Set up a Reverse ETL sync to activate your data