Claude API Telemetry on ClickHouse: A Production Guide to Cost, Latency, and Error Analytics

Ingestion Pipeline — Safely Writing From Cloudflare Workers to ClickHouse Cloud

Assuming you are calling the Claude API from Cloudflare Workers, here is how to instrument and ship telemetry from the Worker side.

Posting directly to ClickHouse's HTTP interface is the simplest path. But never write synchronously — if a write failure can affect the API response, your user experience is at the mercy of your telemetry stack. The right pattern is to push the work into the background with ctx.waitUntil.

// src/lib/telemetry.ts
import Anthropic from "@anthropic-ai/sdk";
 
interface Env {
  ANTHROPIC_API_KEY: string;
  CLICKHOUSE_URL: string;
  CLICKHOUSE_USER: string;
  CLICKHOUSE_PASSWORD: string;
  TELEMETRY_FALLBACK: KVNamespace;
}
 
interface TelemetryEvent {
  event_id: string;
  request_id: string;
  user_id: string;
  workspace_id: string;
  ts: string; // ISO8601
  model: string;
  api_version: string;
  input_tokens: number;
  output_tokens: number;
  cache_read_tokens: number;
  cache_creation_tokens: number;
  stop_reason: string;
  latency_ms: number;
  ttft_ms: number;
  streaming: number;
  success: number;
  error_type: string;
  http_status: number;
  feature_id: string;
  prompt_template_id: string;
  prompt_version: number;
  cost_usd: number;
}
 
const PRICES_PER_MTOK: Record<string, { input: number; output: number; cacheRead: number; cacheCreation: number }> = {
  "claude-sonnet-4-6": { input: 3, output: 15, cacheRead: 0.3, cacheCreation: 3.75 },
  "claude-opus-4-6":   { input: 15, output: 75, cacheRead: 1.5, cacheCreation: 18.75 },
  "claude-haiku-4-5":  { input: 0.8, output: 4, cacheRead: 0.08, cacheCreation: 1 },
};
 
function calcCost(model: string, ev: TelemetryEvent): number {
  const p = PRICES_PER_MTOK[model];
  if (!p) return 0;
  const m = 1_000_000;
  return (
    (ev.input_tokens * p.input +
     ev.output_tokens * p.output +
     ev.cache_read_tokens * p.cacheRead +
     ev.cache_creation_tokens * p.cacheCreation) / m
  );
}
 
export async function sendTelemetry(env: Env, event: TelemetryEvent): Promise<void> {
  // JSONEachRow is the most efficient format for ClickHouse ingestion
  const body = JSON.stringify(event);
  const url = `${env.CLICKHOUSE_URL}/?query=INSERT+INTO+claude_api_events+FORMAT+JSONEachRow`;
 
  try {
    const res = await fetch(url, {
      method: "POST",
      headers: {
        "Authorization": `Basic ${btoa(`${env.CLICKHOUSE_USER}:${env.CLICKHOUSE_PASSWORD}`)}`,
        "Content-Type": "application/x-ndjson",
      },
      body,
    });
    if (!res.ok) {
      const text = await res.text();
      throw new Error(`ClickHouse insert failed: ${res.status} ${text}`);
    }
  } catch (err) {
    // Fallback: queue to KV and resend via cron
    console.error("Telemetry insert failed, queuing to KV:", err);
    await env.TELEMETRY_FALLBACK.put(
      `event:${event.event_id}`,
      body,
      { expirationTtl: 7 * 24 * 60 * 60 }
    );
  }
}

And here is what instrumenting a request handler looks like.

// src/handler.ts
export async function handleClaudeRequest(
  req: Request,
  env: Env,
  ctx: ExecutionContext
): Promise<Response> {
  const t0 = Date.now();
  const eventId = crypto.randomUUID();
  const userId = req.headers.get("x-user-id") ?? "anonymous";
  const workspaceId = req.headers.get("x-workspace-id") ?? "default";
  const featureId = "summarize";
  const promptTemplateId = "summary-v3";
  let ttftMs = 0;
  let firstChunkSeen = false;
 
  const anthropic = new Anthropic({ apiKey: env.ANTHROPIC_API_KEY });
 
  try {
    const stream = anthropic.messages.stream({
      model: "claude-sonnet-4-6",
      max_tokens: 1024,
      messages: [{ role: "user", content: "Summarize the latest deployment notes." }],
    });
 
    for await (const _chunk of stream) {
      if (!firstChunkSeen) {
        ttftMs = Date.now() - t0;
        firstChunkSeen = true;
      }
    }
    const final = await stream.finalMessage();
 
    const event: TelemetryEvent = {
      event_id: eventId,
      request_id: (stream as { request_id?: string }).request_id ?? "",
      user_id: userId,
      workspace_id: workspaceId,
      ts: new Date().toISOString(),
      model: "claude-sonnet-4-6",
      api_version: "2023-06-01",
      input_tokens: final.usage.input_tokens,
      output_tokens: final.usage.output_tokens,
      cache_read_tokens: final.usage.cache_read_input_tokens ?? 0,
      cache_creation_tokens: final.usage.cache_creation_input_tokens ?? 0,
      stop_reason: final.stop_reason ?? "",
      latency_ms: Date.now() - t0,
      ttft_ms: ttftMs,
      streaming: 1,
      success: 1,
      error_type: "",
      http_status: 200,
      feature_id: featureId,
      prompt_template_id: promptTemplateId,
      prompt_version: 3,
      cost_usd: 0,
    };
    event.cost_usd = calcCost(event.model, event);
 
    // Critical: send in the background. Never block the response on telemetry.
    ctx.waitUntil(sendTelemetry(env, event));
 
    return new Response("ok");
  } catch (err) {
    const e = err as { status?: number; constructor: { name: string } };
    const event: TelemetryEvent = {
      event_id: eventId,
      request_id: "",
      user_id: userId,
      workspace_id: workspaceId,
      ts: new Date().toISOString(),
      model: "claude-sonnet-4-6",
      api_version: "2023-06-01",
      input_tokens: 0,
      output_tokens: 0,
      cache_read_tokens: 0,
      cache_creation_tokens: 0,
      stop_reason: "",
      latency_ms: Date.now() - t0,
      ttft_ms: ttftMs,
      streaming: 1,
      success: 0,
      error_type: e?.constructor?.name ?? "UnknownError",
      http_status: e?.status ?? 500,
      feature_id: featureId,
      prompt_template_id: promptTemplateId,
      prompt_version: 3,
      cost_usd: 0,
    };
    ctx.waitUntil(sendTelemetry(env, event));
    return new Response("error", { status: 500 });
  }
}

A common question: why pull usage once at the end of the stream rather than incrementing as chunks arrive? Streamed responses include the final usage totals on the closing message_delta event. Fetching them via finalMessage() is the only way to be sure your numbers match what Anthropic billed. If the connection drops mid-stream you record an error event instead, so there is no double-counting risk.

Always wire up a fallback path. ClickHouse Cloud advertises 99.9 percent SLA, but transient network failures still happen — that is reality, not pessimism. Queueing failed events to Cloudflare KV and replaying them via a scheduled Worker keeps your data loss rate above 99.99 percent without any heroics.

Aggregation and Visualization — Sub-Second Dashboards With Materialized Views

A dashboard that runs SELECT model, sum(cost_usd) FROM claude_api_events WHERE ts > now() - INTERVAL 1 DAY GROUP BY model every second behind the scenes will start to feel slow as data grows. The ClickHouse-native answer is to precompute aggregates with materialized views.

-- Per-minute pre-aggregation table
CREATE TABLE claude_minute_metrics (
    minute DateTime,
    workspace_id LowCardinality(String),
    feature_id LowCardinality(String),
    model LowCardinality(String),
 
    request_count UInt32,
    success_count UInt32,
    error_count UInt32,
    total_input_tokens UInt64,
    total_output_tokens UInt64,
    total_cache_read_tokens UInt64,
    total_cost_usd Decimal64(6),
 
    p50_latency_ms AggregateFunction(quantile(0.5), UInt32),
    p95_latency_ms AggregateFunction(quantile(0.95), UInt32),
    p99_latency_ms AggregateFunction(quantile(0.99), UInt32)
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(minute)
ORDER BY (workspace_id, feature_id, model, minute);
 
-- Materialized view: every INSERT into the source table propagates here
CREATE MATERIALIZED VIEW claude_minute_metrics_mv
TO claude_minute_metrics
AS SELECT
    toStartOfMinute(ts) AS minute,
    workspace_id,
    feature_id,
    model,
    count() AS request_count,
    sumIf(1, success = 1) AS success_count,
    sumIf(1, success = 0) AS error_count,
    sum(input_tokens) AS total_input_tokens,
    sum(output_tokens) AS total_output_tokens,
    sum(cache_read_tokens) AS total_cache_read_tokens,
    sum(cost_usd) AS total_cost_usd,
    quantileState(0.5)(latency_ms) AS p50_latency_ms,
    quantileState(0.95)(latency_ms) AS p95_latency_ms,
    quantileState(0.99)(latency_ms) AS p99_latency_ms
FROM claude_api_events
GROUP BY minute, workspace_id, feature_id, model;

Querying the rollup is dramatically faster.

-- Dashboard query — typically returns in tens of milliseconds
SELECT
    minute,
    sum(total_cost_usd) AS cost_usd,
    sum(request_count) AS requests,
    quantileMerge(0.95)(p95_latency_ms) AS p95_ms
FROM claude_minute_metrics
WHERE workspace_id = 'tenant_abc'
  AND minute >= now() - INTERVAL 24 HOUR
GROUP BY minute
ORDER BY minute;

Why bother with AggregateFunction(quantile(0.95), UInt32)? Percentiles are normally not roll-uppable — you cannot average two p95 values and get the right answer. ClickHouse solves this by letting you store an intermediate aggregation state with quantileState. Even after grouping inside a materialized view, you can recover an accurate final value with quantileMerge later. This is one of the things ClickHouse does that very few other databases match.

Cost-Analysis SQL Recipes — By Model, User, and Prompt

A small library of queries for slicing a cost spike to its root cause in under five minutes.

Daily cost trend by model.

SELECT
    toStartOfDay(ts) AS day,
    model,
    sum(cost_usd) AS cost_usd,
    sum(input_tokens + output_tokens) AS total_tokens
FROM claude_api_events
WHERE ts >= now() - INTERVAL 30 DAY
GROUP BY day, model
ORDER BY day, cost_usd DESC;

Cost efficiency by prompt template.

SELECT
    prompt_template_id,
    count() AS requests,
    sum(cost_usd) AS total_cost,
    avg(cost_usd) AS avg_cost,
    avg(output_tokens) AS avg_output_tokens,
    quantile(0.95)(latency_ms) AS p95_latency_ms
FROM claude_api_events
WHERE ts >= now() - INTERVAL 7 DAY
  AND success = 1
GROUP BY prompt_template_id
ORDER BY total_cost DESC
LIMIT 20;

Once you spot the templates that consume the most tokens on average, prompt compression — fewer few-shot examples, tighter system instructions — can deliver large savings. On one project, optimizing just the top three offenders cut our monthly Claude API bill by 22 percent.

Cache hit rate visibility.

SELECT
    feature_id,
    sum(cache_read_tokens) AS cache_read,
    sum(input_tokens) AS total_input,
    round(sum(cache_read_tokens) / nullif(sum(input_tokens), 0) * 100, 2) AS cache_hit_pct
FROM claude_api_events
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY feature_id
ORDER BY total_input DESC;

A feature with prompt caching enabled but a low hit rate usually points to prompts that mutate too often — perhaps you are interpolating dynamic content into the cached prefix. That is your cue to stabilize the template structure or rethink the cache-key boundaries.

Detecting Failure Patterns — Anomaly, Loop, and Hallucination Signals

These are the queries that catch silent failures — issues that never appear on your invoice but ruin user experience. Wire them into a dashboard or scheduled alerts and you will know about regressions before users complain.

Retry-loop detection. A single user hammering one feature for a few minutes usually means a runaway retry loop somewhere.

SELECT
    user_id,
    feature_id,
    count() AS requests_in_window,
    sum(cost_usd) AS cost_usd,
    sum(input_tokens) AS input_tokens
FROM claude_api_events
WHERE ts >= now() - INTERVAL 5 MINUTE
GROUP BY user_id, feature_id
HAVING requests_in_window > 50
ORDER BY requests_in_window DESC;

Hallucination outliers. Outputs that are far shorter or longer than the expected range are a strong "something broke" signal — the prompt format may have drifted, or the model may be cutting off unexpectedly.

SELECT
    feature_id,
    prompt_template_id,
    count() AS suspicious_count,
    avg(output_tokens) AS avg_output,
    quantile(0.99)(output_tokens) AS p99_output
FROM claude_api_events
WHERE ts >= now() - INTERVAL 1 HOUR
  AND success = 1
  AND (output_tokens < 10 OR output_tokens > 3000)
GROUP BY feature_id, prompt_template_id
HAVING suspicious_count > 5;

Latency regressions, scored against a recent baseline.

WITH baseline AS (
    SELECT
        feature_id,
        avg(latency_ms) AS baseline_avg,
        stddevPop(latency_ms) AS baseline_std
    FROM claude_api_events
    WHERE ts BETWEEN now() - INTERVAL 1 DAY AND now() - INTERVAL 1 HOUR
      AND success = 1
    GROUP BY feature_id
)
SELECT
    e.feature_id,
    avg(e.latency_ms) AS recent_avg,
    b.baseline_avg,
    (avg(e.latency_ms) - b.baseline_avg) / nullif(b.baseline_std, 0) AS z_score
FROM claude_api_events e
JOIN baseline b USING (feature_id)
WHERE e.ts >= now() - INTERVAL 5 MINUTE
GROUP BY e.feature_id, b.baseline_avg, b.baseline_std
HAVING z_score > 3;

Features with a z-score above 3 have drifted significantly from their baseline — usually the upstream signal is a model change, prompt edit, or transient network hiccup. A z=3 alert threshold has, in my experience, been the sweet spot for low false-positive rates while still catching real incidents.

Multi-Tenant Cost Allocation — Chargeback and Per-Customer Margin

If you are running a SaaS, per-tenant cost allocation is a survival issue, not just a nice-to-have. The schema choice of putting workspace_id at the front of ORDER BY pays off here.

-- Monthly cost ranking by tenant
SELECT
    workspace_id,
    sum(cost_usd) AS api_cost_usd,
    sum(input_tokens + output_tokens) AS total_tokens,
    countDistinct(user_id) AS active_users,
    sum(cost_usd) / nullif(countDistinct(user_id), 0) AS cost_per_user
FROM claude_api_events
WHERE ts >= toStartOfMonth(now())
GROUP BY workspace_id
ORDER BY api_cost_usd DESC
LIMIT 50;

A tenant whose cost_per_user is dramatically higher than the average usually signals a mismatch between your pricing tier and actual usage. We share this report with the finance team every quarter to inform pricing-plan revisions. On one occasion the query surfaced two enterprise tenants on an "unlimited" plan whose Claude API consumption exceeded what we charged them by more than 3x — renegotiating those contracts saved us roughly $32,000 per year.

Defining "unprofitable tenant" is business-specific, but a useful starting heuristic is "monthly subscription revenue < API cost × 1.5." Continuously monitoring margin in SQL puts pricing decisions on a data-driven footing instead of guesswork.

Reconciling Against the Anthropic Invoice — Keeping Drift Under One Percent

If the totals in your ClickHouse database disagree with the totals on the Anthropic console, you will lose the trust of the finance team and the board very quickly. Always run a monthly reconciliation query.

-- Monthly aggregation by model — compare with Anthropic console
SELECT
    toStartOfMonth(ts) AS month,
    model,
    sum(input_tokens) AS input_tokens,
    sum(output_tokens) AS output_tokens,
    sum(cache_read_tokens) AS cache_read_tokens,
    sum(cache_creation_tokens) AS cache_creation_tokens,
    round(sum(cost_usd), 2) AS estimated_cost_usd
FROM claude_api_events
WHERE ts >= now() - INTERVAL 3 MONTH
GROUP BY month, model
ORDER BY month, model;

In my experience, three causes account for nearly all reconciliation drift. First, replay gaps from the KV fallback queue — a cron failure can leave thousands of events un-shipped. Second, local-instrumentation misses on network timeouts — Anthropic billed the request, but the Worker never received the response and never recorded an event. Third, pricing-table staleness — Anthropic updates pricing and the PRICES_PER_MTOK constant in your codebase falls behind, a classic human error.

A practical rule: alert when reconciliation drift exceeds 5 percent. That threshold catches real data-quality regressions early without flooding you with false positives.

An Operational Cookbook — Weekly and Monthly Routines

Building a dashboard is not the goal. Looking at the same dashboard regularly is. Here is the routine that worked for our team.

Every Monday morning, we post a week-over-week comparison to Slack. The query:

-- This week vs. last week, by feature_id
WITH last_week AS (
    SELECT feature_id, sum(cost_usd) AS cost
    FROM claude_api_events
    WHERE ts BETWEEN now() - INTERVAL 14 DAY AND now() - INTERVAL 7 DAY
    GROUP BY feature_id
),
this_week AS (
    SELECT feature_id, sum(cost_usd) AS cost
    FROM claude_api_events
    WHERE ts >= now() - INTERVAL 7 DAY
    GROUP BY feature_id
)
SELECT
    coalesce(t.feature_id, l.feature_id) AS feature_id,
    l.cost AS last_week_cost,
    t.cost AS this_week_cost,
    (t.cost - l.cost) / nullif(l.cost, 0) * 100 AS pct_change
FROM this_week t
FULL OUTER JOIN last_week l USING (feature_id)
ORDER BY this_week_cost DESC;

At the start of each month we hold a thirty-minute review meeting that places last month's total cost beside the git log of the top ten cost-driving features. Lining cost trends up against commit history surfaces patterns like "the summarize feature's cost is 1.8x higher since the v3.2 release on the 14th." Catching regressions through that lens has been remarkably effective.

Each quarter we audit the features whose prompt-cache hit rate has dropped below 40 percent. Sometimes the cache key is fragmenting because we are interpolating dynamic content into the system prompt; sometimes the boundary between system prompt and conversation history has drifted. Setting aside time deliberately to investigate these patterns keeps the system from quietly slipping into worse cost efficiency over time.

What I Got Wrong in Production

Five places I tripped, so you can avoid them.

First, missing the "Idle" billing on ClickHouse Cloud. Cloud will scale down when there are no active connections, but materialized views run periodic background work that prevents true idle state. Tightening SETTINGS background_pool_size and batching nightly ingestion into a cron job dropped our monthly bill by about 28 percent.

Second, single-row inserts fragment partitions. Sending one INSERT per request from Workers triggers frequent background merges in ClickHouse, eating CPU. Putting a Workers Queue in front and batching inserts every one to five seconds is the production-quality default. My standard configuration is a queue batch size of 100 with a five-second flush timeout.

Third, overusing LowCardinality blows up memory. If you decorate every string column, certain queries will hit MEMORY_LIMIT_EXCEEDED. Reserve LowCardinality for columns whose distinct value count stays under a few hundred — feature_id, model, error_type. Applying it to high-cardinality columns like user_id actually hurts performance.

Fourth, timestamp precision mismatches break deduplication. If you use ReplacingMergeTree keyed by event_id but your timestamps disagree at the millisecond level across replays, you end up with duplicate rows. Always use DateTime64(3) and verify the precision when converting from ISO strings.

Fifth, never, ever store user content. Keep prompt_template_id, omit the prompt text. Add a filter on the Worker side before serializing your event. I once stored "first 50 characters of user input" thinking it was harmless, then had to delete every row in the table during a GDPR review. Once was enough.

A Real Incident, Start to Finish — Telemetry vs No Telemetry

Concrete examples often land better than abstractions, so let me walk through a cost-spike incident I actually responded to, with the timeline.

One Monday morning a Slack alert popped: "Weekend costs are 2.3x the forecast." Because we had telemetry in place, the response went like this.

At 9:05 AM, the daily-cost-trend query showed that claude-opus-4-6 usage had been climbing since Friday night. At 9:15, breaking down by feature_id pointed to a single feature, report-generator, as the outlier. At 9:30, looking at prompt_template_id revealed that a new template version, report-v4, had been deployed the previous Friday. At 9:45, checking the git log for v4's release commit showed that the system prompt had grown by 800 tokens of new examples. By 10:00 we had a hot-fix deployed that rolled the prompt back to v3, and by 10:30 costs were back to normal.

Total time: one hour and twenty-five minutes. In the pre-telemetry era, the same kind of incident would have taken a full two days just to triage to the right feature. The ROI on the time we spent building the pipeline was already clear after this one event.

The real lesson here is that with the right data, SQL hands you the answer — you do not have to glare at a dashboard waiting for inspiration. Defining a repeatable drill-down path with a small number of queries means anyone on the team can run the investigation and reach the same root cause without depending on individual intuition.

What to Do Next

Start with ClickHouse Cloud's free tier — 30 days and $300 in credits — provision the schema above, and use ctx.waitUntil to stream telemetry from your Workers for one week. With seven days of real data the queries above will, almost certainly, expose at least one feature that is more expensive than expected and at least one prompt template where caching is not landing the way you assumed. Fixing one or two of these usually saves hundreds of dollars per month — sometimes much more.

For the dashboard layer, Grafana's ClickHouse plugin is the fastest path; for an internal-only audience, Metabase produces perfectly respectable visualizations with very little setup. If you want to deepen your understanding of ClickHouse's design philosophy and operational patterns, the deeper material on MergeTree internals and replication quirks pays off when you start tuning at scale.

One more practical tip before you start: when you provision your ClickHouse Cloud cluster, set up a separate database (or at least a separate user) for telemetry ingestion. The Worker only needs INSERT permission on the events table, nothing else. Keeping read-only dashboarding credentials and write-only ingestion credentials separate is a small thing that pays back the first time you have to rotate a leaked secret or audit who can run a DROP TABLE statement.

If you operate across multiple environments — staging and production, or a self-hosted dev cluster alongside ClickHouse Cloud — adding a deployment column with values like prod, staging, or dev to the schema lets you reuse all the same queries with a simple WHERE deployment = 'prod' filter. Resist the urge to create separate tables per environment; it makes cross-environment comparisons painful and turns schema migrations into multi-table coordination exercises.

Finally, treat your telemetry pipeline like any other production system. The KV fallback queue should have its own monitoring (alert when depth grows). The pricing constants should live in version control with a code review checklist that flags pricing-table edits. The reconciliation query should run on a schedule and Slack you the drift number every month. Telemetry that is itself unobserved tends to drift into uselessness, and the moment you stop trusting the numbers is the moment they stop being useful.

For related implementation patterns, see the companion guides on production-ready cost optimization for the Claude API and building an OpenTelemetry observability foundation for the Claude API. Reading them alongside this one closes the loop on measure → analyze → optimize.