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.

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:

SELECT version();

4. Make sure your server accepts HTTPS connections. RudderStack does not connect over plain HTTP.

5. If your server restricts network access, allowlist the RudderStack and Lookout IP addresses. Allow them on the HTTPS port only.

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:

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:

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:

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

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

• Keep the name _rudderstack. The connection form has no setting for this name.
• Create the database before you connect. RudderStack never creates a database.
• The working database must be different from the database in the Database field. It also can’t be default, system, or information_schema.
• The working database must use the Atomic engine or the Shared engine. ClickHouse Cloud uses Shared. RudderStack refuses every other engine, Replicated included.

Step 3: Check the grants

Log in to ClickHouse as rudder_retl and run these checks:

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

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:

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

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:

• Snapshot tables, one for each retained sync
• Tables that track failed records, so the next sync can retry them
• A sync log table, when sync logs are on
• Short-lived test tables named _rudder_probe_<16 hex characters>, which RudderStack creates and drops during validation

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.

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:

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

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:

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:

• RudderStack: The addresses in the table above for your plan and region. RudderStack runs syncs and validation from these.
• Lookout: Lookout connects to your server directly to test the connection and to explore your data. Allow these addresses:

• 3.216.35.97
• 34.198.90.241
• 54.147.40.62

Error codes

Validation and sync errors carry one of the following codes.

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

Troubleshooting

Limitations

• On ClickHouse Cloud with several replicas, a read from a lagging replica can return too few members. The sync then deletes members who did not leave, and the next correct sync inserts them again. The destination can count these as changes.
• A table reload or a broken audience query that returns too few members makes the sync delete them. RudderStack has no stop for large deletes on any warehouse.
• A full sync that RudderStack starts on its own sends no deletes, and it does not tell you. Members who left before that sync stay in the destination.
• If a member’s identifier changes but the primary key stays the same, the old identifier stays in the destination.
• The sync leaves out a row with an empty (NULL) or duplicate key. If that member was synced before, the sync deletes them.

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).