ClickHouse - Coalesce Quality Documentation

Coalesce Quality has deep ClickHouse support. In fact, our platform is built on ClickHouse. See the talk by our CEO: Building a Unified Data Platform with ClickHouse

This guide will show you how to securely connect Coalesce Quality to your ClickHouse service. We need this information so we collect metadata about your tables.To be able to finish this guide, you’ll need the following:
→ Access to create users and grant privileges in ClickHouse (on ClickHouse Cloud, the default user)⏱️ Estimated time to finish: 10 minutes.

Data we collect

We read metadata from your ClickHouse service using the dedicated user described below. Every read goes through system.* tables — we never scan your table data to build the catalog. Depending on which features you enable, we ingest:

Catalog — databases, tables, views, materialized views, columns, data types, positions, and table/column comments.
SQL definitions — the CREATE TABLE / CREATE VIEW / CREATE MATERIALIZED VIEW statement per object. Used for lineage and to detect references between objects.
Keys and indexes — primary keys, sorting keys, partition keys, and data-skipping indexes (minmax, set, bloom filter, and so on), surfaced as table constraints.
Table metrics — row counts and last-modified timestamps, derived from system.tables and system.parts, for anomaly detection.
Query logs (opt-in) — system.query_log for performance, cost, usage, and lineage signals.

These map to a small, fixed set of system tables:

What	System table
Databases	`system.databases`
Tables, views, engines, comments, keys, DDL	`system.tables`
Columns, types, comments	`system.columns`
Data-skipping indexes	`system.data_skipping_indices`
Row counts and last-modified	`system.tables`, `system.parts`
Query logs (opt-in)	`system.query_log`

Metadata vs. data access. The grants below give Coalesce Quality access to your schema and DDL, never to row data. Data-reading queries are issued only when you grant SELECT on your own databases — needed for Custom SQL monitors, data previews, and column profiling — or when you enable SQL test execution against the audit table.

We connect on a schedule (typically every 30 minutes). The metadata queries read system tables only and are negligible against a normal workload.

Requirements

How metadata is read

ClickHouse system tables are per-node, so on a service with more than one replica a plain read reflects whichever replica answered rather than the whole warehouse. Coalesce Quality therefore reads them through clusterAllReplicas(<cluster>, …), and the Metadata reads setting on the integration decides which cluster:

ClickHouse Cloud — nothing to do. Every service exposes a cluster named default, which is the setting’s default.
Self-managed ClickHouse — list what your server has with SELECT DISTINCT cluster FROM system.clusters and enter that name in the Cluster field.
A single node with no cluster defined — choose Read the connected node only. System tables are then read directly, which is the correct read when there is nothing to fan out to, and it needs no REMOTE privilege. The Cluster field does not apply.

Choose single-node reads only when the server really is one node. On a service with replicas it reports the metadata of whichever node answered, which looks like a successful but incomplete scrape.

Network access

For the complete list of IP addresses by region, see Coalesce Quality IP Whitelist.

Coalesce Quality connects over ClickHouse’s native protocol. Allow inbound connections from the Coalesce Quality IPs for your region on port 9440, the native TLS port. On ClickHouse Cloud this is Settings → Security → IP access list for the service. If the list is set to Anywhere no change is needed, but we recommend restricting it to our published addresses.

Connect over TLS. Coalesce Quality reaches your service from the public internet, across which credentials and metadata must never travel in the clear. Use port 9440 with Use SSL enabled.The only case for a plaintext connection — port 9000, Use SSL off — is one that never leaves a private network, such as a VPN or peered network between your infrastructure and ours. If that is your setup, talk to support before turning TLS off. For everything else, including every ClickHouse Cloud service, TLS is not optional.

Setup ClickHouse access

Create a dedicated user

Run the statements in this section as an admin user. On ClickHouse Cloud, open your service’s SQL console from the left menu — it runs as default, which has the privileges to create users and grant on system.*. Replace <password> with a secret you define for the integration.

CREATE USER coalesce_quality IDENTIFIED WITH sha256_password BY '<password>';

Grant metadata access

-- Required when metadata is read across a cluster, which is the default.
-- Skip it if the integration is set to read the connected node only.
GRANT REMOTE ON *.* TO coalesce_quality;

-- Catalog, DDL, keys, indexes, metrics
GRANT SELECT ON system.databases              TO coalesce_quality;
GRANT SELECT ON system.tables                 TO coalesce_quality;
GRANT SELECT ON system.columns                TO coalesce_quality;
GRANT SELECT ON system.data_skipping_indices  TO coalesce_quality;
GRANT SELECT ON system.parts                  TO coalesce_quality;

Grant the whole set, even if a smaller one appears to work today.ClickHouse has historically let some system.* tables be read without an explicit grant, filtering rows by what the user could otherwise see. Newer server versions enforce an explicit grant on the table instead. A service that upgrades — which on ClickHouse Cloud happens automatically, without notice — will then start rejecting reads that worked the day before, with Not enough privileges naming one table at a time. Granting the full list up front avoids discovering this one table per upgrade. See Troubleshooting.

Grant query log access (optional)

Required only if you enable Fetch query logs:

GRANT SELECT ON system.query_log TO coalesce_quality;

Grant data access (optional)

Required if you want Coalesce Quality to read rows — Custom SQL monitors, catalog data previews, column profiling, and SQL test execution:

GRANT SELECT ON <database>.* TO coalesce_quality;

If you configure an audit table for SQL tests, the user also needs to create and write it:

GRANT CREATE TABLE, INSERT ON <database>.<audit_table> TO coalesce_quality;

Verify

SHOW GRANTS FOR coalesce_quality;

Input data in the Coalesce Quality UI

Go to Settings → Integrations → Add integration → ClickHouse.

Where to find these values on ClickHouse Cloud

Open your service in the ClickHouse Cloud console and click Connect in the left menu.
In the dialog, switch the protocol drop-down from HTTPS to Native. This matters: the dialog opens on HTTPS and shows port 8443, which this integration cannot use. On Native it shows port 9440.
Copy the hostname from the clickhouse client --host … example. It has the form <service-id>.<region>.<csp>.clickhouse.cloud, for example abc123xyz.europe-west4.gcp.clickhouse.cloud. Take the hostname only — not the --host flag, and not an https:// prefix.

Two things the dialog will not give you:

The user. It shows credentials for the built-in default user. Use the dedicated coalesce_quality user you created above instead, so this integration’s access is scoped and revocable on its own.
The password. ClickHouse Cloud shows a service password once, at creation. This integration uses the password you set in Create a dedicated user, which you control.

If your service has private endpoints configured, the dialog’s path selector changes the hostname it shows. Pick the path that Coalesce Quality actually reaches you on — for an internet-facing service, the public one.

Integration name

For example ClickHouse.

Instance name

A unique name for this instance, for example prod or staging. It becomes part of the asset paths for everything this integration ingests, so it cannot be changed after the integration is saved — changing it later means creating a new integration.

Host

The hostname of your service, without a scheme and without a trailing slash — for example abc123xyz.europe-west4.gcp.clickhouse.cloud. On ClickHouse Cloud, take it from the Connect dialog as described above.

Port

Pre-filled with 9440, the native TLS port. That is the right value for every ClickHouse Cloud service and for any service reachable over the internet — leave it alone unless you are on a private network, where a plaintext native connection uses 9000 (see Network access).

Username and password

coalesce_quality and the password you set above.

Use SSL

On by default, paired with port 9440. Turning it off sends your credentials and metadata unencrypted, which is only ever acceptable on a connection that stays inside a private network — see Network access.

Default database

The database to connect to when a table reference omits one — for example default.

Audit table FQN (optional)

Fully qualified name (database.table) of the table where Coalesce Quality should write SQL test audit logs, for example default.quality_sql_test__audit. Coalesce Quality creates the table if it does not exist. Leave empty if you only run metadata monitors and don’t execute custom SQL tests against the warehouse.

Metadata reads

Whether system tables are read across a cluster or on the connected node only. Choosing the cluster read reveals the Cluster field below it. See How metadata is read.

Cluster

The cluster to read through, as named under remote_servers in your ClickHouse configuration. Defaults to default, which every ClickHouse Cloud service provides; SELECT DISTINCT cluster FROM system.clusters lists what a self-managed server has. Only shown when Metadata reads is set to read across a cluster.

Additional parameters (optional)

ClickHouse settings applied to every connection this integration opens, as name/value pairs — for example max_execution_time 300 to give a large warehouse longer than the 60 seconds Coalesce Quality allows by default. Values are typed the way ClickHouse types them in a connection string: true and false become 1 and 0, whole numbers become integers, anything else is passed through as text. A name given here replaces the value Coalesce Quality would otherwise use, so this is also how you raise a default that is too low for your warehouse.

Fetch query logs (Recommended)

Pulls from system.query_log. Required for proactive anomaly-monitor training, unused-table detection, usage insights, and cost insights. Needs the system.query_log grant above.

Catalog scope and Metrics scope (optional)

Restrict which databases and tables are ingested into the catalog, and which of those are scanned for metrics. Leave both empty to ingest everything the user can see. system, information_schema, and INFORMATION_SCHEMA are always excluded.

Personal credentials

In addition to the workspace credentials above, you can let each Coalesce Quality member authenticate as themselves against ClickHouse. Queries then run under the member’s own ClickHouse user and respect the grants configured on it. See Data Warehouse Access for the full model and when to enable it.

Troubleshooting

`Not enough privileges … it's necessary to have the grant SHOW COLUMNS ON system.columns`

The user is missing a grant on one of the system tables. The message names the table it stopped on, but there is usually more than one missing — fix them all at once by re-running the full Grant metadata access block, then re-running the same for query logs if that option is enabled. This most often appears after a ClickHouse server upgrade on a connection that had been working for months, because newer versions enforce explicit grants on system tables that were previously readable implicitly. Nothing changed on your side, and re-granting is the fix.

`Requested cluster '…' not found. (CLUSTER_DOESNT_EXIST)`

Your ClickHouse has no cluster by that name. List what it does have with SELECT DISTINCT cluster FROM system.clusters and set Metadata reads accordingly; if the result is empty, choose Read the connected node only. See How metadata is read.

`Not enough privileges … it's necessary to have the grant REMOTE ON .`

Reading across a cluster needs GRANT REMOTE ON *.*. Either grant it, per Grant metadata access, or set Metadata reads to Read the connected node only if the server is a single node.

`All connection tries failed … Connection refused`

Reported when replicas behind the service are unreachable — commonly a service that has scaled down to zero, or is mid-restart. It usually clears on the next scrape. If it persists, check the service is running and that the IP access list allows our addresses.

`Connect failed`

Check the host has no https:// prefix and no trailing slash, that the port is 9440 with Use SSL enabled, and that the credentials are correct. If you changed the port, check it is a native-protocol one. 8123 and 8443 are ClickHouse’s HTTP ports and this integration speaks the native protocol, so they cannot connect — use 9440 with TLS, or 9000 without. A port that disagrees with the Use SSL setting fails outright rather than falling back, which surfaces the same way bad credentials do.

In some cases, permission grants can take a while to propagate. If you keep getting a Connect failed error after double-checking your details, try again after a minute.

​Data we collect

​Requirements

​How metadata is read

​Network access

​Setup ClickHouse access

​Create a dedicated user

​Grant metadata access

​Grant query log access (optional)

​Grant data access (optional)

​Verify

​Input data in the Coalesce Quality UI

​Where to find these values on ClickHouse Cloud

​Integration name

​Instance name

​Host

​Port

​Username and password

​Use SSL

​Default database

​Audit table FQN (optional)

​Metadata reads

​Cluster

​Additional parameters (optional)

​Fetch query logs (Recommended)

​Catalog scope and Metrics scope (optional)

​Personal credentials

​Troubleshooting

​Not enough privileges … it's necessary to have the grant SHOW COLUMNS ON system.columns

​Requested cluster '…' not found. (CLUSTER_DOESNT_EXIST)

​Not enough privileges … it's necessary to have the grant REMOTE ON *.*

​All connection tries failed … Connection refused

​Connect failed

Data we collect

Requirements

How metadata is read

Network access

Setup ClickHouse access

Create a dedicated user

Grant metadata access

Grant query log access (optional)

Grant data access (optional)

Verify

Input data in the Coalesce Quality UI

Where to find these values on ClickHouse Cloud

Integration name

Instance name

Host

Port

Username and password

Use SSL

Default database

Audit table FQN (optional)

Metadata reads

Cluster

Additional parameters (optional)

Fetch query logs (Recommended)

Catalog scope and Metrics scope (optional)

Personal credentials

Troubleshooting

`Not enough privileges … it's necessary to have the grant SHOW COLUMNS ON system.columns`

`Requested cluster '…' not found. (CLUSTER_DOESNT_EXIST)`

`Not enough privileges … it's necessary to have the grant REMOTE ON .`

`All connection tries failed … Connection refused`

`Connect failed`