Building an LLM Cost Dashboard in BigQuery That Actually Changes Behavior

OpenAI, Anthropic, and Bedrock all show a total. They don't answer:

• Which product feature spent the most this week?
• Which team is responsible?
• Which users are 10× the average?
• What's our cost per successful support ticket resolved?
• Which prompts are costing us in retries?

To answer those, you need your own event stream in a warehouse where you can join spend to product events.

CREATE TABLE llm_events (
  event_id STRING NOT NULL,
  ts TIMESTAMP NOT NULL,
  provider STRING NOT NULL,        -- openai, anthropic, bedrock, vertex, self
  model STRING NOT NULL,           -- gpt-4o-mini, claude-3-5-sonnet, ...
  operation STRING NOT NULL,       -- chat, embed, image, transcribe
  feature STRING NOT NULL,         -- 'support-triage', 'summary', ...
  team STRING NOT NULL,            -- 'growth', 'support', 'internal'
  user_id STRING,                  -- hashed
  session_id STRING,
  request_id STRING,               -- provider's request id
  prompt_tokens INT64,
  completion_tokens INT64,
  cached_tokens INT64,             -- if provider supports prompt caching
  latency_ms INT64,
  status STRING,                   -- ok, error, timeout, rate_limited
  error_code STRING,
  cost_usd NUMERIC,                -- computed at emit time
  metadata JSON
)
PARTITION BY DATE(ts)
CLUSTER BY feature, team, model;

Three patterns, in order of preference:

1. Direct BigQuery streaming insert from your app — simplest, works up to ~10k events/sec.
2. Pub/Sub → Dataflow → BigQuery — buffered, retryable, decouples ingestion from app.
3. Cloud Logging → BQ Log Sink — free-ish, but log-based cost quirks; good for low volume.

Wrap every LLM SDK call in a small helper that computes cost and emits the event:

def call_llm(messages, feature: str, team: str, user_id: str, **kw):
    t0 = time.perf_counter()
    try:
        resp = client.chat.completions.create(messages=messages, **kw)
        status = "ok"; err = None
        usage = resp.usage
    except APIError as e:
        status, err = "error", e.code
        usage = None; raise
    finally:
        emit_llm_event(
            provider="openai", model=kw["model"], operation="chat",
            feature=feature, team=team, user_id=hash_user(user_id),
            prompt_tokens=usage.prompt_tokens if usage else 0,
            completion_tokens=usage.completion_tokens if usage else 0,
            latency_ms=int((time.perf_counter()-t0)*1000),
            status=status, error_code=err,
            cost_usd=compute_cost(kw["model"], usage) if usage else 0,
        )
    return resp

Model prices change monthly. Keep a versioned price table in BigQuery joined by (model, effective_date). Never hardcode prices in application code — you'll get it wrong.

CREATE TABLE llm_prices (
  provider STRING, model STRING,
  effective_from DATE, effective_to DATE,
  input_per_1k NUMERIC, cached_input_per_1k NUMERIC,
  output_per_1k NUMERIC
);

-- models/marts/llm_daily.sql
{{ config(materialized='incremental', partition_by={'field':'day','data_type':'date'}) }}
SELECT
  DATE(ts) AS day,
  feature, team, model, status,
  COUNT(*) AS calls,
  SUM(prompt_tokens) AS prompt_tokens,
  SUM(completion_tokens) AS completion_tokens,
  SUM(cost_usd) AS cost_usd,
  APPROX_QUANTILES(latency_ms, 100)[OFFSET(95)] AS p95_ms
FROM {{ ref('llm_events') }}
{% if is_incremental() %}
  WHERE DATE(ts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
{% endif %}
GROUP BY 1,2,3,4,5

Full dbt patterns in dbt in anger.

Total spend divided by user count is a vanity metric. The real KPI is cost per successful business outcome.

WITH resolved AS (
  SELECT session_id, COUNT(*) AS n
  FROM support_events
  WHERE outcome = 'resolved' AND DATE(ts) = CURRENT_DATE() - 1
  GROUP BY 1
),
llm AS (
  SELECT session_id, SUM(cost_usd) AS cost
  FROM llm_events
  WHERE feature = 'support-triage' AND DATE(ts) = CURRENT_DATE() - 1
  GROUP BY 1
)
SELECT
  SUM(llm.cost) / SUM(resolved.n) AS cost_per_resolution
FROM llm JOIN resolved USING (session_id);

Build in Metabase, Looker, or Superset — five panels:

1. Daily spend, stacked by feature; 7-day and 30-day trend.
2. Cost per successful action, by feature.
3. Top 10 users by cost (helps catch abuse or investigate power users).
4. Model mix — % of spend on premium vs cheap models by feature.
5. Error rate by (model, feature) — retries silently double cost.

-- Runs hourly via scheduled query; posts to Slack via a webhook
WITH last_hour AS (
  SELECT feature, SUM(cost_usd) AS cost
  FROM llm_events
  WHERE ts BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND CURRENT_TIMESTAMP()
  GROUP BY 1
),
baseline AS (
  SELECT feature, AVG(hourly_cost) AS avg_cost, STDDEV(hourly_cost) AS sd
  FROM (
    SELECT feature, TIMESTAMP_TRUNC(ts, HOUR) AS h, SUM(cost_usd) AS hourly_cost
    FROM llm_events
    WHERE ts BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
                 AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
    GROUP BY 1,2
  )
  GROUP BY 1
)
SELECT l.feature, l.cost, b.avg_cost, (l.cost - b.avg_cost) / b.sd AS z_score
FROM last_hour l JOIN baseline b USING (feature)
WHERE (l.cost - b.avg_cost) / b.sd > 4.0

Any z-score >4 pings Slack. Beats waking up to a $2k bill.

When cost-per-action for a feature exceeds threshold, take action:

• Route non-critical calls to a cheaper model (e.g., 4o → 4o-mini for summaries).
• Add prompt caching where the same system prompt is reused.
• Trim system prompts (measure per-call token count in your dashboard).
• Consider fine-tuning — see fine-tuning vs RAG.
• Add semantic caching for repeated queries (redis + embedding similarity).

• Never log prompt/response text by default — store hashes or truncated redactions only.
• If you must store text for eval, use a separate table with stricter IAM and 30-day TTL.
• Hash user IDs at emit time; keep the mapping in a locked table.
• Set BigQuery table expiration (90 days for events, 400 for daily rollups).

Meta-observation: your observability data can itself become expensive. Partition by day, cluster by feature, alias every query to hit clustered columns. See BigQuery cost control that scales for the whole discipline.

1. Every LLM call in the system emits an event within 100ms.
2. The dashboard opens in under 2 seconds — always fresh, always cheap.
3. Every product team can self-serve their spend and cost-per-outcome.
4. Anomalies fire in Slack within an hour, not a month.
5. Cost-per-action is a first-class product metric, tracked over time.