4. Querying LLM logs & evals¶
Intermediate · 13 min read
Every LLM call your app makes should be logged as a row: which feature, which model, which prompt version, how many tokens, how long it took, whether it failed. Eval runs are rows too. Once they're in a table, SQL answers the questions that matter every week: what does this cost, what's slow, what broke, and is the new prompt better?
4.1 The tables¶
import random
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE llm_calls (
id INTEGER PRIMARY KEY,
ts TEXT, -- ISO timestamp
user_id TEXT,
feature TEXT, -- 'support_chat', 'summarise', 'extract'
model TEXT,
prompt_version TEXT,
input_tokens INTEGER,
output_tokens INTEGER,
latency_ms INTEGER,
status TEXT -- 'ok', 'error', 'timeout'
);
CREATE TABLE model_prices ( -- USD per 1M tokens (illustrative)
model TEXT PRIMARY KEY, input_price REAL, output_price REAL
);
INSERT INTO model_prices VALUES ('small', 0.15, 0.60), ('large', 3.00, 15.00);
""")
rng = random.Random(42)
FEATURES = {"support_chat": ("small", 2500, 300), "summarise": ("large", 6000, 500), "extract": ("small", 1200, 150)}
rows = []
for i in range(1, 601):
feature = rng.choice(list(FEATURES))
model, avg_in, avg_out = FEATURES[feature]
day = f"2026-10-{rng.randint(1, 7):02d}"
status = rng.choices(["ok", "error", "timeout"], weights=[95, 3, 2])[0]
latency = int(rng.lognormvariate(7.0 if model == "small" else 7.8, 0.4))
rows.append((i, f"{day}T{rng.randint(8, 22):02d}:{rng.randint(0, 59):02d}:00", f"u{rng.randint(1, 40)}",
feature, model, rng.choice(["v2", "v3"]) if feature == "support_chat" else "v1",
int(rng.gauss(avg_in, avg_in * 0.2)), int(rng.gauss(avg_out, avg_out * 0.3)), latency, status))
con.executemany("INSERT INTO llm_calls VALUES (?,?,?,?,?,?,?,?,?,?)", rows)
def show(sql, params=()):
cur = con.execute(sql, params)
cols = [d[0] for d in cur.description]
rows = cur.fetchall()
widths = [max(len(str(x)) for x in [c, *[r[i] for r in rows]]) for i, c in enumerate(cols)]
print(" ".join(c.ljust(w) for c, w in zip(cols, widths)).rstrip())
for r in rows:
print(" ".join(str(x).ljust(w) for x, w in zip(r, widths)).rstrip())
show("SELECT COUNT(*) AS calls, MIN(ts) AS first, MAX(ts) AS last FROM llm_calls")
In production this table is written by the LLM client wrapper on every call (or exported from Langfuse, LangSmith or your provider's usage API).
4.2 Cost per feature¶
Join to the price table and compute cost per call:
show("""
SELECT c.feature, c.model,
COUNT(*) AS calls,
ROUND(SUM(c.input_tokens * p.input_price
+ c.output_tokens * p.output_price) / 1e6, 2) AS cost_usd,
ROUND(AVG(c.input_tokens * p.input_price
+ c.output_tokens * p.output_price) / 1e6, 5) AS usd_per_call
FROM llm_calls AS c
JOIN model_prices AS p ON p.model = c.model
GROUP BY c.feature, c.model
ORDER BY cost_usd DESC
""")
feature model calls cost_usd usd_per_call
summarise large 178 4.5 0.02529
support_chat small 195 0.11 0.00054
extract small 227 0.06 0.00027
summarise makes up about a third of the calls but almost all of the cost — the first place to look for savings
(a smaller model? shorter inputs? caching?).
4.3 Daily trend and top users¶
show("""
SELECT substr(c.ts, 1, 10) AS day,
COUNT(*) AS calls,
ROUND(SUM(c.input_tokens * p.input_price + c.output_tokens * p.output_price) / 1e6, 2) AS cost_usd
FROM llm_calls AS c JOIN model_prices AS p USING (model)
GROUP BY day ORDER BY day
""")
show("""
SELECT user_id, COUNT(*) AS calls, SUM(input_tokens + output_tokens) AS tokens
FROM llm_calls
GROUP BY user_id
ORDER BY tokens DESC
LIMIT 3
""")
day calls cost_usd
2026-10-01 92 0.64
2026-10-02 89 0.71
2026-10-03 83 0.61
2026-10-04 78 0.6
2026-10-05 82 0.66
2026-10-06 89 0.73
2026-10-07 87 0.72
user_id calls tokens
u20 26 86343
u38 26 77533
u6 18 72831
Per-user totals are how you set quotas, catch abuse (a script hammering your API) and price plans.
4.4 p95 latency without a percentile function¶
Averages hide the slow requests users complain about; track p50 and p95. PostgreSQL has
percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms); SQLite doesn't, so number the rows with a window function
and pick the row at the 95% position:
show("""
WITH ranked AS (
SELECT model, latency_ms,
ROW_NUMBER() OVER (PARTITION BY model ORDER BY latency_ms) AS rn,
COUNT(*) OVER (PARTITION BY model) AS n
FROM llm_calls
WHERE status = 'ok'
)
SELECT model,
MAX(CASE WHEN rn = CAST(0.50 * n AS INTEGER) THEN latency_ms END) AS p50_ms,
MAX(CASE WHEN rn = CAST(0.95 * n AS INTEGER) THEN latency_ms END) AS p95_ms,
MAX(latency_ms) AS max_ms
FROM ranked
GROUP BY model
""")
4.5 Error rates¶
show("""
SELECT feature,
COUNT(*) AS calls,
SUM(status != 'ok') AS failed,
ROUND(100.0 * AVG(CASE WHEN status != 'ok' THEN 1 ELSE 0 END), 1) AS fail_pct,
SUM(status = 'timeout') AS timeouts
FROM llm_calls
GROUP BY feature
ORDER BY fail_pct DESC
""")
feature calls failed fail_pct timeouts
extract 227 15 6.6 7
support_chat 195 9 4.6 5
summarise 178 5 2.8 1
In SQLite a comparison like status != 'ok' is 1 or 0, so SUM(...) counts matches (in PostgreSQL write
COUNT(*) FILTER (WHERE status != 'ok')). Alert when the fail rate jumps above its normal level.
4.6 Comparing prompt versions in production¶
support_chat ran two prompt versions side by side (an A/B test). Compare them on the numbers you log:
show("""
SELECT prompt_version,
COUNT(*) AS calls,
ROUND(AVG(input_tokens)) AS avg_in,
ROUND(AVG(output_tokens)) AS avg_out,
ROUND(AVG(latency_ms)) AS avg_ms,
ROUND(100.0 * AVG(status != 'ok'), 1) AS fail_pct
FROM llm_calls
WHERE feature = 'support_chat'
GROUP BY prompt_version
""")
prompt_version calls avg_in avg_out avg_ms fail_pct
v2 96 2424.0 294.0 1151.0 5.2
v3 99 2453.0 296.0 1200.0 4.0
Cost and speed are only half the story — whether answers got better comes from evals.
4.7 Eval results: pass rates and regressions¶
An eval run stores one row per (test case, prompt version):
con.executescript("""
CREATE TABLE eval_results (case_id TEXT, category TEXT, prompt_version TEXT, passed INTEGER, score REAL);
""")
cases = [("refund-1", "refund"), ("refund-2", "refund"), ("refund-3", "refund"), ("ship-1", "shipping"),
("ship-2", "shipping"), ("acct-1", "account"), ("acct-2", "account"), ("acct-3", "account")]
v2 = {"refund-1": 1, "refund-2": 0, "refund-3": 1, "ship-1": 1, "ship-2": 1, "acct-1": 1, "acct-2": 0, "acct-3": 1}
v3 = {"refund-1": 1, "refund-2": 1, "refund-3": 1, "ship-1": 1, "ship-2": 0, "acct-1": 1, "acct-2": 1, "acct-3": 1}
con.executemany("INSERT INTO eval_results VALUES (?,?,?,?,?)",
[(c, cat, v, res[c], 0.9 if res[c] else 0.3) for v, res in (("v2", v2), ("v3", v3)) for c, cat in cases])
show("""
SELECT category,
ROUND(100.0 * AVG(CASE WHEN prompt_version = 'v2' THEN passed END)) AS v2_pass_pct,
ROUND(100.0 * AVG(CASE WHEN prompt_version = 'v3' THEN passed END)) AS v3_pass_pct
FROM eval_results
GROUP BY category
UNION ALL
SELECT 'ALL',
ROUND(100.0 * AVG(CASE WHEN prompt_version = 'v2' THEN passed END)),
ROUND(100.0 * AVG(CASE WHEN prompt_version = 'v3' THEN passed END))
FROM eval_results
""")
category v2_pass_pct v3_pass_pct
account 67.0 100.0
refund 67.0 100.0
shipping 100.0 50.0
ALL 75.0 88.0
v3 wins overall — but shipping got worse. Find exactly which cases regressed with a self-join:
show("""
SELECT a.case_id, a.category
FROM eval_results AS a
JOIN eval_results AS b ON b.case_id = a.case_id
WHERE a.prompt_version = 'v2' AND a.passed = 1
AND b.prompt_version = 'v3' AND b.passed = 0
""")
That's the case to read before shipping v3. An overall score that goes up can still hide a regression in the one category your users care about most.
4.8 From SQL to pandas¶
For charts and further analysis, load any query into a DataFrame:
import pandas as pd
df = pd.read_sql("""
SELECT substr(ts, 1, 10) AS day, feature, COUNT(*) AS calls
FROM llm_calls GROUP BY day, feature
""", con)
print(df.pivot(index="day", columns="feature", values="calls").tail(3))
feature extract summarise support_chat
day
2026-10-05 34 26 22
2026-10-06 39 28 22
2026-10-07 27 27 33
See Pandas for GenAI for more on eval reports in pandas.
Interview questions¶
What would you log for every LLM call, and what would you monitor?
Timestamp, user/tenant, feature, model and version, prompt version, input/output tokens, latency, status/error, and a trace id (plus the prompt and response in secure storage). Monitor cost per feature and per user, p50/p95 latency, error and timeout rates, and quality signals (eval scores, user feedback) — with alerts on changes.
How do you compare two prompt versions?
Offline: run both on the same eval set and compare pass rates overall and per category, then look at individual regressions. Online: A/B test with a prompt_version column and compare cost, latency, error rate and user feedback. Ship only if quality improves without regressions in important categories.
Practice¶
- Find the hour of day with the highest p95 latency for the
largemodel. - Add a
cost_usdcolumn tollm_callswith anUPDATE … FROM-style query (or a view) and recompute 4.2 without the join.
Next: Text-to-SQL — let users ask these questions in plain English.