# ClickHouse Reverse ETL Source

The ClickHouse Reverse ETL source is in Private Beta, where we work with early users and customers to test new features and get feedback before making them generally available.

Reach out to Customer Success to enable it for your workspace.

ClickHouse is an open source, column-oriented database for analytics. It runs as a managed service (ClickHouse Cloud) or on your own servers.

RudderStack reads audience members from your ClickHouse database and keeps them in sync with your destinations. You connect ClickHouse and build the audience in Rudder Lookout. RudderStack then runs each sync. It keeps its working tables in a separate database in your ClickHouse server, and it never writes to your data.

What you can sync

The ClickHouse source supports one path: a SQL audience that you build in Lookout and activate to a destination.

Supported Not supported
  • Audiences built in Lookout
  • Mirror mode, the only sync mode
  • ClickHouse Cloud, with one replica or several
  • A self-hosted ClickHouse server on a single node
  • Table, model, and Audiences (data graph) sources
  • Creating the source from Add source in the RudderStack dashboard or through the API
  • Upsert and full sync modes
  • Self-hosted clusters, and Replicated databases outside ClickHouse Cloud
  • Aiven and the Tinybird ClickHouse interface
  • SSH tunnels, PrivateLink, and a custom CA certificate

Prerequisites

  1. Reach out to Customer Success to enable ClickHouse for your workspace.
  2. Link your Lookout workspace to your RudderStack workspace. Lookout creates the source in that RudderStack workspace when you activate an audience.
  3. Check that your ClickHouse server runs version 26.3 or later. Run this query and compare the result:
sql 
SELECT version();
  1. Make sure your server accepts HTTPS connections. RudderStack does not connect over plain HTTP.
Use the HTTPS endpoint of your service on port 8443.
  1. If your server restricts network access, allowlist the RudderStack and Lookout IP addresses. Allow them on the HTTPS port only.
Add the addresses to the IP access list of your service.
Lookout connects only to public addresses. A host name that resolves to a private network address (for example, 10.0.0.0/8 or 192.168.0.0/16) fails with CH_HOST_NOT_ALLOWED. IPv6 addresses and localhost are not accepted in the Host field.

Grant permissions

Ask a ClickHouse administrator to run the queries in the following steps, in order. The steps use these placeholders:

Placeholder Replace with
rudder_retl The name of the user RudderStack connects as
<YOUR_DATABASE> The database that holds the data your audiences read
<strong_unique_password> A strong password for the user

Names must use only letters, digits, and underscores, start with a letter or an underscore, and have at most 128 characters.

Step 1: Create the user

Create a dedicated user for RudderStack:

sql 
CREATE USER rudder_retl IDENTIFIED BY '<strong_unique_password>';

The password can’t contain control characters, and it can’t start or end with a space.

Expected result: The query succeeds. SHOW USERS lists rudder_retl.

Step 2: Create the working database and grant permissions

RudderStack keeps its working tables in a separate database called _rudderstack. Run the following script as an administrator:

sql 
-- Create the RudderStack working database.
CREATE DATABASE IF NOT EXISTS `_rudderstack`;

-- Lets RudderStack read your database. It cannot change your data.
GRANT SELECT
ON `<YOUR_DATABASE>`.*
TO `rudder_retl`;

-- Lets RudderStack create, write, read and drop tables in the working database, where it keeps its sync state.
GRANT SELECT, INSERT, CREATE TABLE, DROP TABLE
ON `_rudderstack`.*
TO `rudder_retl`;

-- Lets RudderStack delete old rows from its own sync log in the working database. It cannot delete anything in your data.
GRANT ALTER DELETE ON `_rudderstack`.`sync_log` TO `rudder_retl`;

-- Lets RudderStack check and stop its own running queries. It also shows the running queries of every user on this server, with their text.
GRANT SELECT ON system.processes TO `rudder_retl`;

-- Lets RudderStack check whether its own queries finished. It also shows the logged queries of every user on this server, with their text.
GRANT SELECT ON system.query_log TO `rudder_retl`;

Repeat the GRANT SELECT ON <YOUR_DATABASE>.* statement for each database your audiences read. You can also grant SELECT on single tables instead of the whole database. RudderStack then shows a warning during validation, but it accepts the narrower grant.

Lookout generates this script with your names filled in. It appears under Setup SQL while you fill in the connection form. You can copy or download it from there.

Expected result: Each statement succeeds. ClickHouse Cloud does not support GRANT ALL, so the script lists each privilege.

Why RudderStack needs each permission

Permission Why RudderStack needs it
SELECT on <YOUR_DATABASE>.* Reads the rows your audience query selects.
CREATE TABLE and INSERT on _rudderstack.* Creates a snapshot table for each sync, then fills it with the audience members.
SELECT on _rudderstack.* Compares the new snapshot with the previous one, and reads the records to send.
DROP TABLE on _rudderstack.* Removes old snapshots and other working tables RudderStack no longer needs.
All four privileges on _rudderstack.* RENAME TABLE and EXCHANGE TABLES need SELECT, INSERT, CREATE TABLE, and DROP TABLE. RudderStack uses them to publish each new snapshot.
ALTER DELETE on _rudderstack.sync_log Deletes sync log rows older than the Sync log retention. The script always grants it, so you can turn on retention later without a new grant. The grant works before RudderStack creates the sync_log table.
SELECT on system.processes Stops a sync query that runs past its time limit. ClickHouse Cloud refuses the stop without this grant.
SELECT on system.query_log Checks whether a query finished when its response was lost. ClickHouse Cloud refuses this check without this grant.

The script grants nothing else. RudderStack needs no KILL QUERY privilege, because it stops only its own queries. It also needs no write access to your data, no CREATE DATABASE or DROP DATABASE, no cluster privilege, and no access-management privilege.

The two system grants let the user read the running and logged queries of every user on the server, including their query text. RudderStack reads only the rows of its own user. RudderStack also refuses audience SQL that names the system or information_schema databases, and Lookout does not read these two tables when it explores your data.

Working database rules

Step 3: Check the grants

Log in to ClickHouse as rudder_retl and run these checks:

sql 
CHECK GRANT SELECT ON `_rudderstack`.*;
CHECK GRANT INSERT ON `_rudderstack`.*;
CHECK GRANT CREATE TABLE ON `_rudderstack`.*;
CHECK GRANT DROP TABLE ON `_rudderstack`.*;
CHECK GRANT ALTER DELETE ON `_rudderstack`.`sync_log`;
CHECK GRANT SELECT ON system.processes;
CHECK GRANT SELECT ON system.query_log;

Expected result: Each statement returns 1. A 0 means the grant is missing. Run the script in Step 2 again.

RudderStack runs the same checks when you connect, and stops at the first missing grant. A missing ALTER DELETE grant only gives a warning while sync log retention is off. With retention on, each sync fails at the start with CH_PERMISSION and names ALTER DELETE.

Connect ClickHouse in Lookout

Only Lookout admins can add a warehouse connection. See Roles and Permissions in Rudder Lookout.
  1. In Lookout, go to Settings > Warehouses.
  2. Click Add account.
  3. In Step 1: Select warehouse type, select ClickHouse.
  4. In Step 2: Account details, fill in the connection settings.
  5. Check the Setup SQL below the form. If your administrator has not run it yet, copy it and ask them to run it now.
  6. Click Validate and create.

Expected result: Lookout shows Account created. The ClickHouse connection appears under Settings > Warehouses.

Connection settings

Setting Description
Account name Enter a name to identify this connection in Lookout.
Host Enter the host name of your ClickHouse server, without the scheme, port, or path. For example, enter clickhouse.example.com, not https://clickhouse.example.com:8443. A public IPv4 address also works.
Port (optional) Enter the HTTPS port of your server. The default is 8443.
Database Enter the database that holds the data your audiences read. This is <YOUR_DATABASE> in the grant script. It can’t be system or information_schema, in any letter case. RudderStack refuses these names when you save the account, and again when it connects.
Username Enter the user you created in Step 1, for example, rudder_retl.
Password Enter the password of that user.

The form has no option to turn off TLS or certificate checks. Every connection uses HTTPS and verifies the server certificate.

The password goes to RudderStack’s secrets store. Lookout does not store it.

Validation steps

When you click Validate and create, RudderStack checks the connection before it creates the account. It runs these steps in order and stops at the first failure:

Step What RudderStack checks
Connect and check the version The host, port, and address, the HTTPS connection and certificate, the user name and password, that the server runs version 26.3 or later, and that the current database of the connection is the Database you entered.
Check the settings That the user can set the query settings RudderStack sends with each query.
Check the grants and the RudderStack working database engine Each grant in Step 3, and the engine of the working database.
Write to the _rudderstack database That RudderStack can create a test table named _rudder_probe_<16 hex characters>, write a row, read it back, and drop the table.
Read diagnostics Extra details for support. This step never fails.

If a step fails, Lookout shows the step name, a message, and an error code. RudderStack creates no account. See Error codes to fix the problem, then click Validate and create again.

An idle ClickHouse Cloud service can take a while to wake up. The first validation request waits up to 90 seconds for it.

Activate an audience

After you connect ClickHouse:

  1. Build an audience on the ClickHouse connection.
  2. Activate the audience to a destination.

Lookout creates the ClickHouse source and its connection in your linked RudderStack workspace. The connection syncs in mirror mode.

How a sync works

Each sync runs these steps in your ClickHouse server:

  1. RudderStack connects over HTTPS and repeats the version, settings, and grant checks.
  2. It checks the audience SQL against the Audience SQL limits.
  3. It creates a new snapshot table in the working database and fills it with the current audience members. The rows go from your tables to the working database without leaving the server.
  4. It compares the new snapshot with the previous one.
  5. It sends the changes to the destination: removed members first, then changed members, then new members.
  6. It publishes the new snapshot with RENAME TABLE, so the new snapshot becomes the baseline for the next sync. If an earlier run stopped halfway, RudderStack uses EXCHANGE TABLES to recover.

If a step fails, the previous snapshot stays the baseline. The next sync compares against it.

A sync run can take at most 2 hours. RudderStack stops a query that is still running at that limit.

What a sync sends

Sync What the destination receives
First sync One insert for each current member. No deletes.
Normal sync A delete for each member who left, an update for each member whose values changed, and an insert for each new member.
Full sync One insert for each current member. No deletes. The full sync becomes the new baseline, and the next normal sync sends deletes again.

You start a full sync with Run full sync in Lookout. RudderStack also runs a full sync on its own when you add or change a mapping, change the identifier, add a column, or change a column’s type.

What you see in the working database

The working database holds only RudderStack’s own tables:

Table names are in lowercase and include the connection ID. See _rudderstack Schema Reference for the table families.

Do not edit or drop tables in the working database while a connection uses it. RudderStack removes old tables itself.

Audience SQL limits

Lookout builds the audience SQL for you. Before each sync, RudderStack checks that SQL. It refuses any audience SQL that uses a construct in the following table. The sync then fails with CH_QUERY_INVALID and names the refused construct.

Refused construct Label in the error Reason
SQL that RudderStack cannot split into tokens, or whose brackets do not balance SYNTAX RudderStack must read the whole query before it can check it.
More than one statement, or any text after the final semicolon (a comment included) MULTIPLE STATEMENTS An audience is a single query.
A query that does not start with SELECT or WITH, or that starts with a parenthesized SELECT NOT SELECT, PARENTHESIZED SELECT An audience reads data. It never writes or changes it.
A table name or column qualified by system, information_schema, or INFORMATION_SCHEMA, a table alias named system, or a backslash in a quoted part of a qualified name SYSTEM TABLE The RudderStack user can read other users’ query text in system.query_log and system.processes.
A call to a table function, such as url(), s3(), remote(), format(), or view(), and any call to file() TABLE FUNCTION Most of these functions read data from outside your databases, such as other servers or server files. RudderStack refuses, by name, every table function that ClickHouse 26.3 has.
Any call to hasColumnInTable NETWORK FUNCTION This function can make your server connect to any host.
A query parameter of type Identifier or Array(Identifier), such as {t:Identifier}. Parameters of other types are not refused. IDENTIFIER PARAMETER The value is bound after the check, so it can name a system table.
The word APPLY in any letter case and any position, outside strings, quoted names, and comments. This includes a column or alias named apply. COLUMN TRANSFORMER The APPLY column transformer calls a function by its bare name, so SELECT * APPLY(file) reaches file().
The word EXPLAIN in any letter case and any position, outside strings, quoted names, and comments. This includes a column or alias named explain. EXPLAIN A subquery such as SELECT * FROM (EXPLAIN SELECT 1) runs as the viewExplain table function.
SETTINGS, at any level SETTINGS RudderStack sets the query settings itself, so preview and sync return the same members.
A FORMAT clause, or a bare column named format. A format() call is refused as TABLE FUNCTION. FORMAT RudderStack reads the result in its own format.
INTO OUTFILE INTO OUTFILE An audience never writes a file.
PREWHERE, FINAL, SAMPLE, LIMIT BY, or ARRAY JOIN The clause name RudderStack controls how a query merges and samples rows, so every sync returns the complete audience.
Audience SQL longer than 245,760 bytes (240 KiB) SIZE Longer SQL does not fit in a ClickHouse query together with RudderStack’s own statements.

A quoted column name is not refused. For example, `apply` and `format` pass, and so does a qualified name such as u.format. Quotes do not make a table function call or a system qualifier pass. If Lookout writes such a column without quotes, create a view that renames the column, and build the audience on the view.

Audience rules in Lookout support filter columns of these types: String, signed and unsigned integers, Bool, DateTime, and DateTime64. A rule on a column of another type fails with CH_TYPE_UNSUPPORTED.

Remove access

To stop using ClickHouse with RudderStack:

  1. In Lookout, delete the audience activations that use the ClickHouse connection.
  2. Copy the teardown SQL below. Replace rudder_retl with your user name.
  3. Ask a ClickHouse administrator to run the teardown SQL.
  4. In Lookout, go to Settings > Warehouses and delete the ClickHouse connection.

Expected result: SHOW USERS no longer lists the RudderStack user.

If your workspace shows Revoke in RudderStack in the connection’s account menu, you can use it before step 4. It removes the account that RudderStack stores and shows the same teardown SQL under Finish in your warehouse.

Teardown SQL

The teardown SQL drops the user:

sql 
-- RudderStack -- remove ClickHouse access for user `rudder_retl`
-- ⚠ This user came from you, not from RudderStack. Run this only if nothing else logs in as `rudder_retl`.
DROP USER IF EXISTS `rudder_retl`;

-- RudderStack kept its working tables in the database `_rudderstack`.
-- Other RudderStack accounts on this server can share it, so this script never drops it.
-- Drop it yourself only after no RudderStack account uses it.

The script never drops the working database, because other RudderStack accounts on the same server can use it. If the user is the built-in default user, the script drops nothing. Change its password, or revoke the grants yourself.

After no RudderStack account uses the working database, you can drop it.

This statement deletes every RudderStack working table in _rudderstack on this server. It deletes them for every RudderStack account that uses this database, not only for this connection. Run it only when no RudderStack account uses the database.
sql 
DROP DATABASE IF EXISTS `_rudderstack` SYNC;

IPs to allowlist

This section applies if your ClickHouse server accepts connections only from known addresses. RudderStack connects from these addresses for validation, preview, and every sync.

To enable network access to RudderStack, allowlist the following RudderStack IPs depending on your region and RudderStack plan:

Plan
Region
US
EU
IN
Free and Growth
  • 3.216.35.97
  • 18.214.35.254
  • 23.20.96.9
  • 34.198.90.241
  • 34.211.241.254
  • 52.38.160.231
  • 54.147.40.62
  • 3.123.104.182
  • 3.125.132.33
  • 18.198.90.215
  • 18.196.167.201
Not applicable
Enterprise
  • 3.216.35.97
  • 34.198.90.241
  • 44.236.60.231
  • 54.147.40.62
  • 100.20.239.77
  • 3.66.99.198
  • 3.64.201.167
  • 3.123.104.182
  • 3.125.132.33
  • 3.7.235.227
  • 13.200.113.188
  • 35.154.198.69
  • 3.6.122.214

All the outbound traffic is routed through these RudderStack IPs.

Rudder Lookout connects to your warehouse from three of the US addresses: 3.216.35.97, 34.198.90.241, and 54.147.40.62. Lookout uses these addresses for workspaces in every region. If you use Lookout with an EU or IN workspace, allow them too. See Allow Lookout to reach your warehouse.

Allow two sets of addresses:

Error codes

Validation and sync errors carry one of the following codes.

Error code Meaning What to do
CH_AUTHENTICATION ClickHouse did not accept the user name or password. Check the Username and Password, then try again.
CH_PERMISSION The user is missing a grant, or its settings profile refuses a setting RudderStack sends. The message names the grant or setting. Run the grant script again. If the message names a setting, ask your administrator to let the user change that setting. A profile with readonly = 1 refuses every setting change.
CH_VERSION_BELOW_FLOOR The server runs a version older than 26.3. Upgrade the server to 26.3 or later.
CH_TLS The TLS connection failed. The server certificate check failed, the TLS handshake failed, or the server answered without TLS. Check that the port serves HTTPS. Use a certificate from a publicly trusted authority that matches the host name.
CH_HOST_NOT_ALLOWED The host is not valid, or it resolves to an address RudderStack does not connect to, such as a private, loopback, or link-local address. Use the public host name of your server.
CH_DNS_FAILED The host name did not resolve. Check the Host field.
CH_NETWORK RudderStack could not reach the server, or the connection timed out. Check the Host and Port (optional) fields, that the server is running and accepts HTTPS, and that the RudderStack IPs are allowlisted.
CH_UNAVAILABLE The server is not available. Check that your ClickHouse service is running, then try again.
CH_REDIRECT_REFUSED The server answered with a redirect to another address. RudderStack does not follow redirects. Enter the host name the server answers on directly.
CH_CONFIG_INVALID A connection setting is not valid. The message names the field. This code also covers a working database name that is not allowed, a working database that does not exist, a Database of system or information_schema, and a connection whose current database is not the Database you entered. Fix the named field. See Connection settings and Working database rules.
CH_WORKSPACE_NOT_ENABLED The workspace is not enabled for the ClickHouse source. Contact Customer Success to enable this source for your workspace.
CH_OBJECT_NOT_FOUND A database or table that the query uses does not exist. Check that the database and the tables in your audience query exist. Check the Database field.
CH_CLUSTER_UNSUPPORTED The host points to a self-hosted cluster with more than one server. Connect to a single-node server or to ClickHouse Cloud.
CH_SCRATCH_CLEANUP_FAILED RudderStack could not drop a test table or an old working table. Check that the user still has DROP TABLE on the working database. RudderStack retries the cleanup at the next sync.
CH_TIMEOUT ClickHouse took too long to answer. Try again. If it keeps happening, check that your ClickHouse service is running.
CH_RATE_LIMITED ClickHouse is limiting requests. Wait a few minutes, then try again.
CH_RESOURCE ClickHouse ran out of a resource or hit a server limit, such as the memory limit of a query. Narrow the audience, or ask your administrator to raise the limit for the RudderStack user.
CH_QUERY_INVALID The audience SQL uses a refused construct. See Audience SQL limits.
CH_TYPE_UNSUPPORTED An audience rule uses a column type that Lookout does not support yet. Use a column of a supported type.
CH_SCHEMA_MISMATCH A column type changed between syncs, and the previous snapshot no longer fits the new type. RudderStack keeps the previous snapshot and sends nothing. Fix the audience so the column keeps a compatible type, or start a full sync with Run full sync. A full sync sends no deletes.
CH_DDL_NOT_VISIBLE A new working table did not become visible in time. RudderStack sends nothing. Wait for the next sync. Contact support if it keeps happening.
CH_OUTCOME_UNKNOWN RudderStack lost the answer from ClickHouse and could not confirm whether a query finished. Try again. RudderStack keeps the working tables of that run for recovery.
CH_CANCELLED The check or sync stopped before it finished. Try again.

For a code not listed here, contact RudderStack Support and include the code.

Troubleshooting

Issue Solution
Validation fails at Connect and check the version with an HTTP 400 error You probably entered the native protocol port (for example, 9440 on ClickHouse Cloud). Enter the HTTPS port, usually 8443.
Validation fails at Check the grants and the RudderStack working database engine Run the checks in Step 3 as the RudderStack user. Run the grant script again for any check that returns 0. If all checks return 1, check that the working database uses the Atomic or Shared engine.
Saving the account fails because the database cannot be system or information_schema Enter the database that holds your audience data in the Database field. RudderStack refuses the ClickHouse built-in databases in any letter case.
Validation fails at Write to the _rudderstack database Check that the working database exists and that the user has CREATE TABLE, INSERT, SELECT, and DROP TABLE on it.
A self-hosted server fails with CH_CLUSTER_UNSUPPORTED A load balancer sends RudderStack’s connections to different servers. Connect to a single server.

Limitations

FAQ

Can I create a ClickHouse source from the RudderStack dashboard?

No. You create ClickHouse sources from Lookout only, when you activate an audience. The ClickHouse source supports SQL audiences built in Lookout, synced in mirror mode.

Does RudderStack support ClickHouse over plain HTTP?

No. Every connection uses HTTPS and verifies the server certificate against publicly trusted certificate authorities.

Is SSH tunneling supported?

No. RudderStack does not support SSH tunnels or PrivateLink for the ClickHouse source.

Which ClickHouse versions are supported?

ClickHouse 26.3 or later. RudderStack checks the version when you connect and at the start of each sync. An older server fails with CH_VERSION_BELOW_FLOOR before RudderStack writes anything.

Why does RudderStack need access to system.query_log and system.processes?

RudderStack reads them to stop its own slow queries and to check whether a query finished when its answer was lost. RudderStack reads only the rows of its own user. See Why RudderStack needs each permission.

Does RudderStack write to my data?

No. RudderStack reads your data and writes only to the working database (_rudderstack).