ClickHouse

Connect a ClickHouse database to Lakehousecat as a source for semantic extraction and analytics.

note

ClickHouse is also used internally by Lakehousecat as the analytics warehouse. This page covers connecting an external ClickHouse instance as a data source.

Connection Fields

Field	Required	Description
Connection URI	Yes	Full connection string. Example: clickhouse://user:password@host:8123/database
Use SSH	No	Enable to connect through an SSH bastion host.

Prerequisites

Lakehousecat connects to the ClickHouse HTTP interface (default port 8123). The technical user in your connection URI must meet all of the following requirements.

Required privileges

Privilege	Scope	Why it is required
SELECT	On every table you want to load	Data reads for Full Load and Incremental Load
SHOW	Database / table scope	Schema introspection (SHOW TABLES, column metadata)
OPTIMIZE	On every table you want to load incrementally	OPTIMIZE TABLE … FINAL on ReplacingMergeTree sources before incremental reads

SELECT alone is not sufficient for incremental loads from ClickHouse. The connection user must also be able to run OPTIMIZE TABLE on the source tables.

Example grant (adjust database, table, and user names):

GRANT SELECT, SHOW ON my_db.* TO lakehousecat_source;
GRANT OPTIMIZE ON my_db.my_table TO lakehousecat_source;

Official reference: ClickHouse GRANT statement — OPTIMIZE privilege

Network and access

• Admin or Builder role in Lakehousecat
• Network connectivity from the Lakehousecat backend to the ClickHouse HTTP interface (default port 8123)
• The connection URI must point to the table interface the customer actually queries (typically the cluster entry point; ENGINE = Distributed tables are supported transparently)

Do not use a read-only-only account

A dedicated service account is recommended, but it must include SELECT, SHOW, and OPTIMIZE on the relevant tables — not SELECT alone.

Incremental Load

Incremental Load is supported for ClickHouse sources. On the first incremental attempt, Lakehousecat auto-detects merge keys from ClickHouse system metadata (system.columns, system.tables) instead of SQLAlchemy reflection.

How merge keys are detected

1. Read the table's sorting key (or explicit primary key) from ClickHouse system tables.
2. Use the full sorting key as the candidate merge key — no assumptions about column names such as id or _id.
3. Verify uniqueness on the source: count() must equal countDistinct(sorting_key_columns). If the key is not unique, detection fails and the load falls back to Gentle Full Load (G4).
4. Persist detected keys to the data source configuration for subsequent incremental runs.

Engine types and fallback behavior

Source engine / pattern	Incremental merge	Notes
MergeTree with ORDER BY (cols…)	Supported if sorting key is unique	Most common case
MergeTree with explicit PRIMARY KEY	Supported if key is unique	Higher detection confidence
ReplacingMergeTree	Supported if sorting key is unique	Lakehousecat runs OPTIMIZE TABLE … FINAL before each incremental read
AggregatingMergeTree	Not supported → G4	Pre-aggregated state is incompatible with row-level DLT merge
ORDER BY tuple()	Not supported → G4	No meaningful merge key
Distributed	Same as underlying table	Transparent to the load process; metadata is read from the connected node

Gentle Full Load fallback (G4)

If no unique merge key can be detected, incremental triggers fall back to Gentle Full Load automatically. This is non-destructive: the semantic layer, views, and chart references are preserved.

Incremental column

For change detection, Lakehousecat looks for timestamp columns by name (for example updated_at, modified_at) and DateTime / DateTime64 types. When present, the best match is stored alongside the merge key configuration.

Notes

• Lakehousecat uses the ClickHouse HTTP interface for connectivity.
• For ReplacingMergeTree sources, ensure the technical user has OPTIMIZE rights — without it, incremental loads may read duplicate row versions.
• Design your ORDER BY / sorting key to match the logical row grain. The sorting key is used as the merge key and must be unique.

Next Steps

After creating the datasource, go to the Operations tab to trigger semantic extraction.