ClickHouse Certified: ClickHouse Cloud: noisy-neighbour BI load, tenant isolation and private networking

ClickHouse Cloud: noisy-neighbour BI load, tenant isolation and private networking

A 120-person analytics team runs ClickHouse Cloud on AWS in us-east-1 with one production service. Three clients share it: a BI tool using a single shared service account that refreshes 60 dashboards every 10 seconds; about 120 analysts running ad-hoc SQL through a web SQL editor; and an ingestion job loading 400 million rows/day. During business hours analysts complain that queries queue for minutes while the BI refresh consumes most of the CPU. A security review found that every analyst can see all 50 tenants' rows in the events table. The platform team has also been asked to remove public internet access to the data endpoint and allow connections only from the corporate VPC in us-east-1 - which hosts the BI tool, the SQL editor and the ingestion job, except for one SaaS dashboard vendor with no VPC presence. Budget to double compute is denied.

1. Without doubling compute, how do you stop the BI refresh from starving the analysts? Give both the isolation mechanism and the governance controls, and say how you would prove the change worked.

Read the worked answer

Two separate problems are hiding under 'the cluster is slow'.

The load itself. 60 dashboards × one refresh per 10 seconds is 6 sustained queries/second, each presumably scanning a large slice of events because nothing is pre-aggregated. That is predictable background load that should never compete with interactive users. First, cut it at the source: most dashboards do not need 10-second freshness, so serve them from an AggregatingMergeTree summary table or a refreshable materialized view refreshed once a minute. Same dashboards, a fraction of the scan.

Isolation. ClickHouse Cloud supports compute-compute separation: create a second warehouse on the same service, sharing storage but with its own compute, and point the BI service account's endpoint at it. The dashboards then burn their own CPU and analysts are unaffected. The extra warehouse costs only while active, so it is cheaper than doubling the primary.

Governance. Give the BI tool a dedicated user with a settings profile: readonly = 1, max_threads = 4, max_execution_time = 30. Add CREATE QUOTA bi_hourly FOR INTERVAL 1 hour MAX queries = 25000, result_rows = 2000000000 TO bi_user. Quotas cap the query rate and result volume that drive CPU, and readonly stops a dashboard tool from issuing stray DDL.

Proof: compare system.query_log grouped by user and by warehouse for peak hours, before and after - you want analyst p95 latency down and the BI account's CPU confined to its own warehouse.

2. Design the row-level isolation for `events` so each analyst sees only their tenants, including the failure modes you must audit before shipping.

Read the worked answer

Attach the filter to the table and let roles decide who gets it.

CREATE ROW POLICY tenant_isolation ON prod.events
FOR SELECT
USING tenant_id = dictGet('tenant_map', 'tenant_id', currentUser())
TO analyst_role;

The mapping lives in a dictionary over a small tenant_access(user, tenant_id) table, so onboarding or moving a user is a data change, not a DDL change. Grant analyst_role to the 120 analysts and deliberately keep the BI service account and the ingestion user out of it - a policy listed TO analyst_role does not apply to other users.

Audit before shipping:

SELECT * FROM system.row_policies to confirm there is no stale permissive policy such as USING 1. Permissive policies OR together, so a single sloppy policy re-exposes every tenant. Where a filter must apply no matter what else exists, declare it AS RESTRICTIVE.
Confirm the analysts' profile is readonly = 1 and that they lack grants for CREATE TEMPORARY TABLE, file(), url() and remote() table functions, any of which can move data around the policy.
Check that no dictionary or materialized view is used by analysts as a side channel around events.

Test with a real analyst account, not with an admin: run SELECT tenant_id, count() FROM events GROUP BY tenant_id and confirm exactly one row comes back, then repeat for a second tenant and for a user with no mapping (which should return zero rows, not an error).

3. Lay out the sequence for moving the data endpoint behind private networking, including the client that cannot join the VPC.

Read the worked answer

Order matters, because the step that cuts you off happens mid-day if you get it wrong.

Inventory every client. BI tool, SQL editor, ingestion job, Terraform/CI, monitoring, and the SaaS dashboard vendor. Anything outside us-east-1 or without a VPC presence breaks the moment the public endpoint is disabled.
Create the private connection for in-VPC clients. An AWS VPC endpoint (PrivateLink) for the ClickHouse Cloud service in us-east-1, accepted in the console, with each client switched to the private DNS name rather than the public hostname. Verify from a bastion that the private hostname resolves and connects while the public one still works - you want both alive during the transition.
Resolve the SaaS vendor separately. Either it supports PrivateLink from its own AWS account (a cross-account endpoint), or you keep the public endpoint and restrict it with the IP access list to the vendor's published egress ranges. Do not disable public access until this is settled.
Disable the public endpoint. Console and OpenAPI/Terraform access are separate from the data endpoint, so SSO and API keys keep working - confirm that before assuming anything.
Re-verify. Run SELECT hostName(), currentUser() from inside the VPC, confirm a curl against the public hostname times out from the internet, and check system.query_log for connections from unexpected addresses.

Keep the IP allowlist in place even after PrivateLink is live, as defence in depth, and document the rollback: re-enabling the public endpoint plus allowlisting the VPC NAT addresses.