ClickHouse to Databricks: How to Move Your Data

ClickHouse and Databricks are built around opposite convictions. ClickHouse assumes the query is known and shapes the storage for it, which is why a well-designed ClickHouse table answers in milliseconds. Databricks assumes the query is not known yet and optimises for governed access across many workloads, which is why it carries a catalogue, table history, and permissions machinery that ClickHouse does not bother with.

Moving from the first to the second usually means you want ClickHouse event data joined against everything else in the lakehouse, or a governed copy for workloads that should not be pointed at the serving layer. Both are good reasons, and one characteristic of the source shapes how you build it.

Why does the source have no change data capture?

The ClickHouse source is built on the JDBC code base and supports full refresh and cursor-based incremental sync. Its documentation lists both replicating incremental deletes and logical replication from the write-ahead log as coming soon, so neither is available today.

For most ClickHouse workloads that is fine, because ClickHouse tables are usually append-heavy event stores where rows are written once and a timestamp cursor captures everything.

Where it becomes a problem is ReplacingMergeTree. If your ClickHouse table deduplicates on merge, the version of a row Airbyte extracts depends on when the sync ran relative to that merge. You can read a row, have ClickHouse later replace it during a background merge, and never see the replacement, because a merge does not advance the cursor. If your source tables use ReplacingMergeTree, either read through a view applying FINAL or accept that the destination holds a point-in-time read rather than a converged one.

One version note matters before you build. Connector version 0.4.0 declared temporal column typing a breaking change, and connections hitting schema evolution errors need to follow the connector's migration guide. If you are upgrading an existing connection rather than starting fresh, read that first.

Setting up the ClickHouse source

You need ClickHouse Server 21.3.10.1 or later, and a dedicated read-only user is recommended over reusing an existing account. Create the user and grant SELECT on the relevant database, or narrow the grant to specific tables.

One structural point catches people out. To replicate from multiple ClickHouse databases you cannot use a single source. You grant access to each schema and then configure a separate source per schema, connecting to the same instance more than once. Plan the connection count accordingly rather than expecting one source to cover the whole cluster.

SSL is supported, and if the instance has no public address, the connector can reach it through an SSH tunnel to a bastion host using an RSA key or a password. As with other tunnelled connectors, the SSH login username is the operating system user on the bastion rather than a ClickHouse account.

What does the Databricks destination require?

The Databricks destination requires a workspace with Unity Catalog enabled. This is a hard prerequisite and the most common reason a first attempt fails.

You also need a SQL warehouse or all-purpose compute cluster, and permission to create schemas, tables, and Unity Catalog Volumes in the target catalog. That last one is load-bearing rather than incidental: the connector stages Avro files in a Volume before loading them into tables, so without Volume creation permission the sync cannot stage data at all.

For credentials, OAuth2 with a service principal is the recommended path: create the principal, generate a client ID and secret, and grant it access to the target catalog and schema. A personal access token also works but ties the pipeline to one person's account. You must also accept the Databricks JDBC driver terms, which appear as a checkbox in the connector configuration.

Collect the Server Hostname, HTTP Path, and Port from the SQL warehouse Connection Details tab, with 443 as the default port. The Unity Catalog name is the top-level catalog in the workspace sidebar, not a schema or a table.

Since version 4.0.0 the destination uses direct load, writing straight to final tables, and the raw tables prefixed with an underscore are no longer produced. If you are upgrading from 3.x and have downstream models reading raw tables, rewrite them before the upgrade rather than after.

How do ClickHouse types land in Databricks?

Most types map cleanly. Strings stay strings, integers become LONG, booleans stay booleans, dates stay DATE, and timestamps arrive at microsecond precision as TIMESTAMP or TIMESTAMP_NTZ depending on whether they carry a timezone.

Two mappings deserve attention. Numbers become DECIMAL(38, 10), allowing 28 integer digits and 10 fractional, so a ClickHouse Decimal configured beyond that should be checked rather than assumed.

More significantly, objects and arrays both become STRING, serialised as JSON. ClickHouse's Array and Nested types are commonly used for tags, event properties, and similar structures, and they will arrive in Databricks as JSON text that needs parsing. If arrays are central to your schema, plan a downstream model that expands them into native Databricks types rather than leaving every analyst to parse strings in each query.

Schema and table names are lowercased automatically, since Databricks treats them as case-insensitive identifiers. Column names keep their source casing.

Frequently asked questions

Will rows deleted in ClickHouse disappear from Databricks?

No. The source lists incremental deletes as coming soon, so deletions are not captured. A periodic full refresh overwrite is the only way to bring the destination back in line.

Will ReplacingMergeTree deduplication be reflected?

Not reliably. A background merge that replaces a row does not advance the cursor, so the replacement may never be extracted. Read through a view applying FINAL if convergence matters to you.

Why does the connector need permission to create Volumes?

It stages Avro files in a Unity Catalog Volume before loading them into tables. Without that permission the sync cannot stage data, and the failure appears at connection check time.

Can one source cover several ClickHouse databases?

No. You configure a separate source per schema, each connecting to the same instance. Grant the read-only user access to every schema you intend to replicate.

Get your ClickHouse data into Databricks

Enable Unity Catalog, grant Volume permissions, pick a cursor suited to an append-heavy table, and plan a model for arrays. Moving the same data to a warehouse instead? See our guide to ClickHouse to BigQuery. Loading files into the same lakehouse? See SFTP to Databricks.

Start syncing now →