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, and cdp_journey):

-- 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;

• 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

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

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://composable.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