ClickHouse Cloud FinOps engineering: cost telemetry, governance and right-sizing built into the platform

What we build

Four layers of ClickHouse Cloud FinOps engineering

Layer 1 · Loaders

Loading the ClickHouse Cloud usage cost API, and query telemetry every hour

The ClickHouse Cloud usage cost API returns ClickHouse Credits (CHC) per entity, day and meter, for at most 31 days per call. Our loader re-sends the last 35 days on every run, so restated days are corrected automatically. It skips the transfer detail meters, which are a breakdown of dataTransferCHC; loading them as well double-counts transfer. The API reference documents every field.

#!/usr/bin/env python3
"""
ClickHouse Cloud FinOps engineering: load the ClickHouse Cloud usage cost API into finops.usage_cost_daily.

Run daily. It re-sends the last 35 days on every run, because days stay open for restatement
until the API marks them locked; ReplacingMergeTree(loaded_at) keeps the newest copy.
Secrets come from the environment only: CH_CLOUD_KEY_ID, CH_CLOUD_KEY_SECRET (API key),
CH_URL, CH_USER, CH_PASSWORD (the ClickHouse service that hosts the finops database).
"""
import base64, json, os, sys, urllib.parse, urllib.request
from datetime import date, timedelta

API = "https://api.clickhouse.cloud/v1/organizations/{org}/usageCost?from_date={f}&to_date={t}"
TRANSFER_DETAIL = {"publicDataTransferCHC", "interRegionTier1DataTransferCHC", "interRegionTier2DataTransferCHC",
                   "interRegionTier3DataTransferCHC", "interRegionTier4DataTransferCHC"}   # breakdown of dataTransferCHC

def basic(user: str, secret: str) -> str:
    return "Basic " + base64.b64encode(f"{user}:{secret}".encode()).decode()

def fetch(org_id: str, start: date, end: date) -> list[dict]:
    """Daily per-entity cost records, in windows of at most 31 days (the API limit)."""
    auth, out, cur = basic(os.environ["CH_CLOUD_KEY_ID"], os.environ["CH_CLOUD_KEY_SECRET"]), [], start
    while cur <= end:
        stop = min(cur + timedelta(days=30), end)
        req = urllib.request.Request(API.format(org=org_id, f=cur, t=stop), headers={"Authorization": auth})
        with urllib.request.urlopen(req, timeout=60) as r:
            out += json.load(r)["result"]["costs"]
        cur = stop + timedelta(days=1)
    return out

def to_rows(org_id: str, records: list[dict]):
    """One row per entity, day and meter; transfer detail meters are skipped to avoid double counting."""
    for rec in records:
        for meter, chc in (rec.get("metrics") or {}).items():
            if meter in TRANSFER_DETAIL or not chc:
                continue
            yield {"usage_date": rec["date"], "organization_id": org_id, "entity_type": rec["entityType"],
                   "entity_id": rec["entityId"], "entity_name": rec.get("entityName") or "",
                   "service_id": rec.get("serviceId") or "", "meter": meter, "chc": chc,
                   "locked": bool(rec.get("locked"))}

def insert(rows) -> int:
    body = "\n".join(json.dumps(r) for r in rows).encode()
    q = urllib.parse.quote("INSERT INTO finops.usage_cost_daily (usage_date, organization_id, entity_type, entity_id, "
                           "entity_name, service_id, meter, chc, locked) FORMAT JSONEachRow")
    req = urllib.request.Request(f"{os.environ['CH_URL']}/?query={q}", data=body, method="POST",
                                 headers={"Authorization": basic(os.environ["CH_USER"], os.environ.get("CH_PASSWORD", ""))})
    urllib.request.urlopen(req, timeout=120).read()
    return body.count(b"\n") + 1 if body else 0

if __name__ == "__main__":
    org = os.environ["CH_CLOUD_ORG_ID"]
    if len(sys.argv) > 2 and sys.argv[1] == "--sample":            # offline test with a saved API response
        records = json.load(open(sys.argv[2]))["result"]["costs"]
    else:
        end = date.today() - timedelta(days=1)
        records = fetch(org, end - timedelta(days=34), end)
    print(f"loaded {insert(to_rows(org, records))} meter-rows from {len(records)} records")

# Lab test: the same synthetic 30-day response loaded twice
# $ python3 e_loader.py --sample sample_usage.json   -> loaded 360 meter-rows from 90 records
# $ python3 e_loader.py --sample sample_usage.json   -> loaded 360 meter-rows from 90 records
# SELECT count() FROM finops.usage_cost_daily          -> 720   (until background merges run)
# SELECT count() FROM finops.usage_cost_daily FINAL    -> 360   (the re-load replaced, it did not double)

Query telemetry comes from a refreshable materialized view. Every hour it rolls the last two closed hours of system.query_log into finops.query_cost_hourly. Failed queries are included, because they consume CPU too. Hours are bucketed in UTC, the timezone in which the bill’s days are defined. Our first run bucketed in server-local time and produced half-hour offsets, which is exactly the class of error a test catches and a dashboard hides.

-- ClickHouse cost telemetry, refreshable materialized view: every hour, roll the last two closed hours of query_log into
-- finops.query_cost_hourly. Re-processing an hour is safe because the target replaces by key.
-- On ClickHouse Cloud, read every replica: FROM clusterAllReplicas('default', system.query_log)
CREATE MATERIALIZED VIEW IF NOT EXISTS finops.mv_query_cost_hourly
REFRESH EVERY 1 HOUR OFFSET 5 MINUTE APPEND
TO finops.query_cost_hourly
AS
SELECT
    toStartOfHour(toTimeZone(event_time, 'UTC'))                      AS hour,     -- bill days are UTC
    'svc-lab'                                                         AS service_id,   -- the service this view runs on
    ifNull(nullIf(extract(log_comment, 'team=([^;]+)'), ''), 'untagged')    AS team,
    ifNull(nullIf(extract(log_comment, 'feature=([^;]+)'), ''), 'untagged') AS feature,
    user                                                              AS db_user,
    count()                                                           AS queries,
    sum(ProfileEvents['OSCPUVirtualTimeMicroseconds']) / 1e6          AS cpu_seconds,
    sum(read_rows)                                                    AS read_rows,
    sum(read_bytes)                                                   AS read_bytes,
    sum(result_bytes)                                                 AS result_bytes,
    max(memory_usage)                                                 AS peak_memory,
    now()                                                             AS refreshed_at
FROM system.query_log
WHERE type IN ('QueryFinish', 'ExceptionWhileProcessing')            -- failed queries cost money too
  AND event_time >= toStartOfHour(now('UTC') - INTERVAL 2 HOUR)
  AND event_time <  toStartOfHour(now('UTC'))
GROUP BY hour, team, feature, db_user;

-- Lab test: SYSTEM REFRESH VIEW finops.mv_query_cost_hourly, then
-- SELECT hour, team, feature, db_user, queries, round(cpu_seconds, 2) AS cpu FROM finops.query_cost_hourly FINAL
-- hour                 team          feature          db_user         queries  cpu
-- 2026-10-16 01:00:00  risk          adhoc            analyst               6  13.55
-- 2026-10-16 01:00:00  risk          adhoc-unbounded  analyst               1   7.12
-- 2026-10-16 01:00:00  untagged      untagged         default              62   5.60
-- 2026-10-16 01:00:00  growth        hourly-traffic   bi_dashboard         91   0.56
-- 2026-10-16 01:00:00  platform      lb-probe         healthcheck         290   0.42

Layer 3 · Governance guardrails

ClickHouse Cloud cost governance in four layers

Effective ClickHouse Cloud cost governance does not rely on one control. Each layer in Exhibit 3 acts at a different moment and catches what the previous layer missed: a pull request, a query start, an hour of team usage, a day of service spend.

The CI cost gate is the cheapest control to run and the one teams most often lack. For each changed .sql file, it asks a staging service for EXPLAIN ESTIMATE: the rows and marks the primary index says the query would read, without running it. Anything above the budget fails the build.

#!/usr/bin/env python3
"""
ClickHouse Cloud cost governance in CI: fail a pull request whose SQL would scan too much.

For every .sql file changed in the PR, ask the server for EXPLAIN ESTIMATE (rows and marks the
query would read, from the primary index, without running it) and compare against a budget.
Runs against a staging service with production-shaped data. Credentials from the environment.
"""
import base64, os, sys, urllib.parse, urllib.request

MAX_ROWS = int(os.environ.get("COST_GATE_MAX_ROWS", 250_000_000))   # same ceiling as the ad-hoc guardrail

def estimate(sql: str) -> int:
    q = urllib.parse.quote("EXPLAIN ESTIMATE " + sql.strip().rstrip(";"))
    auth = base64.b64encode(f"{os.environ['CH_USER']}:{os.environ.get('CH_PASSWORD', '')}".encode()).decode()
    req = urllib.request.Request(f"{os.environ['CH_URL']}/?query={q}&default_format=TabSeparated",
                                 headers={"Authorization": f"Basic {auth}"})
    out = urllib.request.urlopen(req, timeout=60).read().decode()
    # columns: database, table, parts, rows, marks  (one line per table read)
    return sum(int(line.split("\t")[3]) for line in out.splitlines() if line)

failed = 0
for path in sys.argv[1:]:
    rows = estimate(open(path).read())
    verdict = "FAIL" if rows > MAX_ROWS else "ok"
    failed += verdict == "FAIL"
    print(f"{verdict:4}  {rows:>15,} rows  {path}")
sys.exit(1 if failed else 0)

# Lab test against lab.events (1,000,000,000 rows, ORDER BY (tenant_id, event_time)):
# ok             16,384 rows  ci/tenant_day.sql
# ok        165,030,400 rows  ci/weekly_country.sql
# FAIL    1,000,000,000 rows  ci/user_lookup.sql        -> exit code 1, the pull request is blocked

At runtime, a settings profile on each ad-hoc role rejects the same query in 0.2 seconds, as we measured in our ClickHouse Cloud FinOps metrics study. Above the profile sits a quota per team, so one team cannot spend another’s budget.

-- ClickHouse Cloud cost governance: one role and one quota per team, so no team can spend another's budget;
-- each limit is set from ClickHouse cost telemetry (observed read_rows per role), not from a round number
CREATE ROLE IF NOT EXISTS team_risk;
GRANT SELECT ON lab.events TO team_risk;

CREATE QUOTA IF NOT EXISTS q_team_risk
    KEYED BY user_name
    FOR INTERVAL 1 hour MAX queries = 600, read_rows = 5000000000, execution_time = 1800,
    FOR INTERVAL 1 day  MAX read_rows = 50000000000
    TO team_risk;

GRANT team_risk TO analyst;
SET DEFAULT ROLE team_risk TO analyst;

-- Verified: SELECT quota_name, quota_key, duration, queries, max_queries, read_rows, max_read_rows FROM system.quotas_usage
-- q_team_risk  analyst   3600   1  600    98305   5000000000
-- q_team_risk  analyst  86400   1  \N     98305  50000000000

Limits come from the observed distribution of read_rows per role, not from round numbers. Each is applied first to a non-production service, with its rollback written down: DROP QUOTA or DROP SETTINGS PROFILE restores the previous behaviour immediately.

Engineering standards

How we engineer ClickHouse Cloud FinOps platforms and ClickHouse cost telemetry

Evidence and limits

How this ClickHouse Cloud FinOps engineering was tested

Related

More from ChistaDATA on ClickHouse Cloud costs

Managed ClickHouse Cloud FinOps: our desk operates this platform for you, with daily alerts and a monthly FinOps pack. ClickHouse Cloud FinOps metrics: the twelve-metric scorecard behind it. How expensive queries become a large ClickHouse Cloud bill. DBaaS optimization across clouds.

Measurements: ChistaDATA lab, 16 October 2026, ClickHouse 26.9.17.8. Billing inputs synthetic. Test every recommendation on a non-production service before applying it to production, and maintain a robust DR posture.