ClickHouse Reverse ETL source

Renta reads tables, views, or SQL query results from ClickHouse and sends the data to business tools through Reverse ETL pipelines. A data model defines the rows to sync and the key that identifies each row.

Before you begin

Prepare the connection details and a ClickHouse user with the permissions listed below.

Requirement	What to prepare
Network access	Allow connections from the Renta IP addresses to the cluster's HTTP or HTTPS interface. Common ports are `8123` for HTTP and `8443` for HTTPS. Use the port configured for your deployment.
ClickHouse user	Create a dedicated database user. Renta needs to read model data and write working tables to a separate database on the same cluster.
Reverse ETL connection	Add a Reverse ETL source connection even if you already use the cluster as an ETL destination. These are separate connections in Renta.

For Yandex Managed Service for ClickHouse over HTTPS, Renta includes the Yandex root certificate.

Grant the required privileges

Renta stores working tables in the planner database. Its default name is renta_planner. Grant the following privileges to the ClickHouse user:

Privilege	Scope	Purpose
`CREATE DATABASE`	`.`	Create the planner database.
`CREATE TABLE`, `DROP TABLE`, `INSERT`, `SELECT`, `ALTER`, `OPTIMIZE`	Planner database	Create, read, and update working tables, and remove old tables.
`SELECT`	Databases queried by models	Preview queries, validate keys, and read models during syncs.

Run these statements from an account with access management rights. Replace the password and analytics with your values. If you use a different planner database name, replace renta_planner too.

CREATE USER renta IDENTIFIED WITH sha256_password BY 'your-strong-password';

GRANT CREATE DATABASE ON *.* TO renta;
GRANT CREATE TABLE, DROP TABLE, INSERT, SELECT, ALTER, OPTIMIZE ON renta_planner.* TO renta;
GRANT SELECT ON analytics.* TO renta;

Repeat the SELECT grant for every database the models read. Renta executes CREATE DATABASE IF NOT EXISTS when saving the connection and starting a run, so the user needs permission to run that statement even if the database already exists.

Connect ClickHouse for Reverse ETL

Have the HTTP hostname, port, database name, and user credentials ready.

Open the source catalogStep 1

Go to Connections > Sources > Add source. Under Source type, select Reverse ETL, then ClickHouse.

Source catalog filtered by Reverse ETL with the ClickHouse tile highlighted

Enter the connection detailsStep 2

Enter the connection settings.

Source name.
The connection name in Renta.
Server hostname.
The address including the protocol, such as https://ch.example.com. Without a protocol, Renta uses HTTP.
Database name.
The default database for queries. The model wizard lists its tables and views.
Port.
The HTTP or HTTPS interface port.
User and Password.
The ClickHouse user credentials.
Planner database.
The database for working tables. Leave it empty for renta_planner, or use a name containing Latin letters, digits, and underscores. It must not start with a digit.

ClickHouse connection form with the server hostname and database name filled in

Save the connectionStep 3

Click Save. Renta checks the connection, runs a test query, and tries to create the planner database. If a check fails, the connection is not saved. A database creation permission error includes the required grants.

Bottom of the connection form with the empty Planner database field and the Save button

Verify the connectionStep 4

Check that the connection appears in Connections > Sources with the Active status.

Sources list with the new ClickHouse connection in the Active state

Create a data model

Open Tools > Context layers and click New model. One model can be used by several pipelines.

Context layers list with the New model button and the scope counters

The wizard covers the data source, scope, schema, and metadata. If the scope includes AI agents, it also shows Measures and Access.

Define the query

On Data source, select your ClickHouse connection. You cannot change the source after creating the model.

Data source step with the ClickHouse connection selected, the SQL query option, and a query in the editor

Choose Table or view to select an object from the connection's database. Choose SQL query to select columns, filter rows, or join tables. Use ClickHouse SQL syntax and click Format to format the query.

A model accepts one read-only query. You can use subqueries, CTEs, and UNION. Write statements and administrative commands are rejected, including inside CTEs. Remove trailing FORMAT and INTO OUTFILE clauses: Renta controls the output format.

Preview the query

Click Run preview to check the returned columns and values. The preview shows up to 100 rows, execution time, and bytes read.

Query preview with the returned rows and the row count, run time, and processed bytes above the table

The preview and the separate row-count query each have a server query timeout of 60 seconds and a read limit of 10 GiB. Exceeding the read limit stops the query with an error. Add filters to reduce the scan.

If the row-count query fails, the displayed count is the number of preview rows, not the full model size. The preview can still succeed.

Set the scope

On Scope, choose where the model is available.

Scope step with the Reverse ETL only option selected

Scope	Model availability	Unique key
AI agents & Reverse ETL	AI agents and Reverse ETL pipelines	Required
AI agents only	AI agents	Optional
Reverse ETL only	Reverse ETL pipelines	Required

Only models with a Reverse ETL scope appear in a pipeline's Data model list. Scope determines how the model can be used. User access for AI queries is configured separately on Access.

Select the unique key

On Schema, review the returned columns, sample values, and whether each column allows NULL. Hover over a type to see its original ClickHouse type.

Schema step with the customer_id column selected as the unique key

Select one column under Unique to identify the same row across runs. Its values must be unique and non-null. The column name must start with a Latin letter or underscore and contain only Latin letters, digits, and underscores.

Click Next step to validate the key. The check uses the same 10 GiB read limit and 60-second server query timeout as the preview. If it finds both nulls and duplicates, it reports nulls first. Filter out rows without a key or choose another column, then check again. If the key depends on several columns, create one expression whose values remain unique for every combination.

Renta displays common ClickHouse types as follows:

ClickHouse type	Type in Renta
`Int8, Int16, Int32, UInt8, UInt16, UInt32`	`int`
`Int64, Int128, Int256, UInt64, UInt128, UInt256`	`int64`
`Float32, Float64, BFloat16`	`float`
`Decimal`	`decimal`
`String, FixedString, UUID, Enum, IPv4, IPv6`	`text`
`Date, Date32`	`date`
`DateTime, DateTime64`	`datetime`
`Bool`	`bool`
`Array(T)`	An array of the matching type. Decimal uses array_float.

Nullable and LowCardinality wrappers do not change the displayed base type. LowCardinality alone does not make a column nullable.

For Reverse ETL, convert complex types such as Map, Tuple, Nested, JSON, Variant, and Dynamic to String in the query, or exclude them from the field mapping. This also applies to arrays of unsupported types. A json label in the schema does not mean that Reverse ETL supports the original type.

Add metadata

On Metadata, name the model and describe its data.

Metadata step with the model name, the description, the Auto-generate with AI button, and the tool definition preview

In Model name, enter a name such as customer_orders.
In Description, explain what one row represents, when to use the model, and any rules for interpreting its data. Markdown is supported.
In Column descriptions, explain the fields that need additional context, such as units, date meanings, or status values.

Auto-generate with AI uses the query and up to ten sample rows to suggest column descriptions. Those rows are sent to the AI provider. Use this option only if the data can be shared with that provider, and review the generated descriptions.

Configure AI access

For AI agents only or AI agents & Reverse ETL, configure the additional steps:

On Measures, select the aggregations available to AI assistants.
On Access, grant access to users or workspace groups. Workspace owners already have access.

For examples and query syntax, see Context Layer.

Create the model

Click Create model to save it and return to the model list.

Context layers list filtered to the new model with the ClickHouse source and the Reverse ETL scope badge

To edit the model later, open it and use Query, Schema & key, or Metadata. Models available to AI agents also have Measures and Access tabs.

Start a Reverse ETL pipeline

Use a saved model with the Reverse ETL only or AI agents & Reverse ETL scope.

Select ClickHouse as the sourceStep 1

Click New pipeline, open the Reverse ETL tab, select ClickHouse, and click Next step.

Source step of the pipeline wizard with the Reverse ETL tab and the ClickHouse card selected

Select the connectionStep 2

Select the connection used by your model and click Next step. In the destination settings, choose the model from Data model.

Select your ClickHouse connection step with the connection card and the Next step button

Continue with the destination setup. Its documentation describes field mapping, sync modes, and scheduling.

Storage and synchronization

Each new run reads the model result and uses working tables in the planner database to compare rows and track delivery. These tables use storage in your ClickHouse cluster.

Renta keeps the plan and rejected-row tables for the current run and the previous run used for comparison. It removes older working tables. If cleanup fails, the remaining tables continue to use storage, but the cleanup error does not fail the run. Check the user's DROP TABLE permission if old tables accumulate.

Changes to the names or types of columns used by the pipeline can trigger a full resync. Reading the model and building the comparison each have a server query timeout of 30 minutes. Other SQL operations use a 10-minute timeout by default. These are per-query limits, not a total run duration.

Troubleshooting

Use the error message to identify which setting to check.

Problem	What to do
Connection failed	Check the protocol, HTTP port, credentials, and access from Renta IP addresses. If ClickHouse Cloud is resuming from an idle state, retry after a minute.
Planner database creation is denied	Grant CREATE DATABASE to the user. An existing database does not bypass this check.
Model is missing from Data model	On the Query tab, select Reverse ETL only or AI agents & Reverse ETL and save the model.
Key contains NULL or duplicate values	Check the key values in the query result. Exclude rows without a key, remove duplicates, or select another column.
Unsupported ClickHouse type	Convert the field to String, for example with toString or toJSONString, or exclude it from the field mapping.
Read limit or timeout exceeded	Narrow the date range, add filters, or query a prepared table or view.

What's next

Configure a destination or learn how to query the model from an AI assistant.

Reverse ETL destinations

Field mapping and sync settings.

Context Layer

Measures, access, and model queries from an AI assistant.

Renta IP addresses

Addresses to allow through your network.

Ready to get started?

Build your data pipeline today or get a personalized demo. Start free!

Need help?

Get expert support to ensure your project succeeds. We're here to help!

Feature requests?

Help shape our product! Share your ideas for new features and integrations.