[Claude Code] Medicare Part D × Text-to-SQL Part 4: Evaluation and Operations

1. Introduction

In Part 3, I moved the promises written in documents into hooks and skills, so checks run even when no one remembers to run them. Part 4, the final part, is about evaluation.

All the tools so far were for “not making mistakes during the work.” Evaluation measures whether the finished app answers questions correctly, by comparing it against correct answers. Once you can measure, you know where to fix, and you also know what a fix broke.

In this part, I follow this flow in the order I built it.

1. Build an evaluation set with correct answers first
2. Read the first results and classify the failures
3. Fix according to the classification and re-evaluate (the improvement loop)
4. Turn the evaluation and fix procedures into skills
5. Keep records, and keep things from breaking in operation

2. Building the Evaluation Set First

2-1. 30 Questions, 3 Levels

The evaluation set is 30 questions in eval/questions.yaml. I split them into three levels by difficulty.

Level	Content	Questions	Example
L1	Simple aggregation	8	How many physicians prescribed semaglutide in 2024?
L2	Joins or multiple conditions	12	Top 20 internal medicine physicians in Florida by opioid prescription count in 2024
L3	Unsolvable without the glossary	10	Annual trend of national SGLT2 inhibitor prescription volume (30-day equivalents)

L3 questions can’t be answered without mapping drug classes (SGLT2 inhibitors, DOACs, and so on) or brand names (Ozempic) to columns and values in the data. This is where I measure whether the glossary built in Part 2 is working.

2-2. Correct Answers Held as SQL, Verified by Running Them

Each question has a correct SQL query (expected_sql). I didn’t trust the correct SQL just because I wrote it; I ran it in BigQuery and checked the results. Running run_eval.py --validate re-checks at any time that all 30 queries execute.

Building the evaluation set is a human’s job. Deciding what counts as correct is a decision about what the app should answer; leave it to the agent and you risk getting “correct answers fitted to what it built.”

2-3. Judging by Matching Results

The judging does not compare SQL strings, because many different SQL queries produce the same answer. Both the generated SQL and the correct SQL are run, and the result tables are compared.

Metric	Condition for correct
Substantive accuracy	The rows and values of the correct answer are contained in the generated result. Column names, column order, and row order don’t matter. Numbers may differ by up to 1% relative error. Extra auxiliary columns are allowed
Exact match	Substantively correct, and the number of rows and columns also match

I used two metrics because a single one gets misread. At first I measured only exact match, and the result was 4/30 (13.3%). Most of the failures were cases where the SQL was correct but had one extra column. Measure only exact match, and a correct answer that merely adds a brand-name column counts as wrong. Measure only substantive accuracy, and you can’t see breakdowns in the shape of the answer, like returning a 50-row breakdown to “what is the total?”

Results are saved per question. Even if a run times out partway, --rejudge redoes just the judging. It doesn’t call Claude, so there are no API charges.

3. First Results and Failure Classification

3-1. Run 1

In the record of the first run (run 1), substantive accuracy was 25/30. The record groups the causes of the 5 failed questions into three.

Cause	Questions	What happened
The unit of “drug”	q01, q07, q10	Aggregated by brand name × generic name. Levothyroxine prescription count was about 10% too low (48,632,351 → 43,237,856)
Two definitions of opioid	q14	The value counted by the drug flag (5,031,547) and the value from the per-physician column (4,885,026) differ by about 3%. Ambiguity on the evaluation set’s side
Aggregation granularity	q22	Asked for per-year class totals (3 rows), it returned 38 rows of year × drug

One of the three was a problem not with the app but with the evaluation set. Building correct answers is a human’s job, but correct answers built by humans can be wrong too.

3-2. Sorting Failures into Seven Categories

I read the generated SQL for each failed question and sorted them by cause. Deciding the categories up front determines where to fix.

Category	Symptom	Where to fix
Missing glossary entry	Read a term as the wrong column. Couldn’t resolve a paraphrase of a drug class	Glossary glossary.yaml
Wrong table choice	The column exists, but in the wrong table	table_hints in the glossary
Aggregation error	Right column, but the wrong choice between SUM and COUNT DISTINCT, or weighted and simple average	Note in the glossary → if that doesn’t fix it, one few-shot example
Guard rejection	The guard rejected valid SQL	guards.py, with tests first
Too many columns / format	The answer is right but has too many columns, or one number split into 50 rows	Rules in system.md (keep exceptions narrow)
Question ambiguity	Both the correct SQL’s and the generated SQL’s interpretations are valid	The evaluation set’s question or correct SQL
Variability	Passes or fails under the same conditions	Don’t fix. Run multiple times to judge

When a failure spans two categories, I try the lightest fix first, in the order glossary → example → rule. Rules affect a wide range and tend to break other questions (Section 4).

3-3. The Glossary Was Teaching an Error

While classifying, I found that the glossary itself was wrong. It was q04, “How many physicians prescribed semaglutide in 2024?”

The glossary said, “For prescriber count, use tot_prscrbrs as is.” But semaglutide has multiple brands, and summing the per-brand rows counts physicians who prescribed both twice.

Aggregation method	Prescriber count
SUM of per-brand tot_prscrbrs	453,256
COUNT(DISTINCT prscrbr_npi) per physician	174,885

That is a 2.6x overcount. I rewrote the glossary to “use tot_prscrbrs only when a single row is determined; when combining, use COUNT(DISTINCT prscrbr_npi),” and left the numbers backing this up in a note.

The agent was writing SQL exactly as the glossary said. What was wrong was the instruction side, and it was found only by comparing against the evaluation’s correct answers (values checked against real data).

3-4. What the Human Decided / What Execution Found

What the human decided	What execution found
The 30 questions and their correct answers	An aggregation error the glossary was teaching (q04)
Judging by matching results, with two metrics	The gap between substantive accuracy and exact match (29 vs. 11 in run 6)
Failure categories and where to fix each	Real examples for each category (Section 4)

4. The Improvement Loop

4-1. Five Fixed, Five Broken

For the first fix, I added rules to the prompt. Substantive accuracy in run 2 was 25/30, unchanged.

Looking inside, 5 questions were fixed and a different 5 broke. Net zero. Add one rule, and questions that rule didn’t anticipate break.

An example is q26, “What is the total national drug cost for statins in 2024?” After I added the rule “when asked about cost, include brand names too,” a question asking for a single total started returning 50 rows by brand. I limited the rule to “when asked for a ranking or breakdown.”

From this, I set one principle for fixes: one diff, in one place, as small as possible.

4-2. Check Every Question of the Same Shape

Another failure was a missed fix. In q15 (specialties with the highest average patient risk score), it was ambiguous whether to take a weighted or simple average, so I fixed the question. But I overlooked q18 (specialties with the highest average beneficiary age), which had the same kind of ambiguity.

When you fix one question, check every question of the same shape. I added this to the procedure too.

4-3. A Bigger Model Doesn’t Fix It

In run 3, I also ran the same evaluation set on Opus 5. The result was the same number correct as Sonnet 5, and the same 3 questions failed. It took 1.5 times as long, at 2.5 times the unit price.

The cause was not the model’s capability but the glossary, rules, and questions. I kept Sonnet 5 as the default model.

After that, through a glossary and few-shot revision (run 4), a fix for a timeout on the 30th evaluation question (run 5), and a rule plus few-shot examples for recognizing format-only instructions (run 6), substantive accuracy reached 29/30.

Run	Change	Substantive accuracy
1	First version	25/30
2	Prompt fix	25/30
3	Resolved question ambiguity (“aggregate by generic name” for q01 and q07), compared with Opus 5	27/30
4	Glossary and few-shot revision	28/29
5	Fixed the timeout on the 30th evaluation question	29/30
6	Rule for format-only instructions and 2 few-shot examples	29/30
7	Alternative correct-answer shape in scoring (q17, q21)	30/30

The denominator in run 4 is 29 because the run stopped on an API read timeout after finishing question 29. Since results were saved per question, nothing up to that point was lost. Run 5 is where I fixed the exception handling.

4-4. Run 6 Results and q21

The results of run 6 were as follows.

• Substantive accuracy 29/30 (96.7%), exact match 11/30 (36.7%), 8.0 minutes
• By level: L1 8/8, L2 12/12, L3 9/10

Exact match doesn’t reach 40%. The 18-question gap from substantive accuracy consists of answers that contain the correct rows and values but have extra columns or rows. I used two metrics so as not to hide this gap.

The only incorrect answer was q21, “How much did the number of Ozempic prescribers increase from 2022 to 2024?” The correct SQL returns 2 rows, one per year, and the generated SQL returned 1 row with the difference. The numbers are right; only the shape of the answer differs.

4-5. Fixing q21 (Run 7)

I actually fixed this last question for this post. The human did three things: typed /guard-glossary-update q21, approved the approach, and started the re-evaluation.

Classification. The agent read the records of runs 4 to 6 and the scoring code, and classified the cause as “question ambiguity.” “How much did it increase” can be correctly answered either with 2 rows, one per year, or with 1 row of the difference. But the scoring marked a result as wrong before even looking at values if it had fewer rows than the correct answer. The shape of q21’s answer varied from run to run, and in run 6 it happened to answer with 1 row.

Same shape. Searching the questions, it found, without being prompted, that q17, “How much did total national drug cost increase from 2022 to 2024?”, had the same shape. q17 passed in run 6, but in a run where it answers with a 1-row difference, it fails for the same reason. This avoids repeating the missed fix from 4-2.

Proposal, and rejected options. The proposal was to fix only the evaluation side and not touch the prompt.

Option	Decision
Add an alternative correct-answer shape to the question (accept_sql, 1-row difference) and count it substantively correct if either is satisfied	Adopted
Fix the answer shape in system.md	Rejected. It would constrain the app to fit the test, the same path as q26
Rewrite the correct SQL to the 1-row difference	Rejected. Then the 2-row per-year answer would fail

Real data. The values were checked by running them on BigQuery via MCP. The national Ozempic rows are one per year, with no duplication like in q04. 2022 was 179,831 and 2024 was 286,517, a difference of 106,686. The q17 difference also matched the result of the correct SQL.

Partway through, the BigQuery MCP lost its connection (expired authentication). The agent didn’t write any values; it showed the SQL for checking and stopped. After a person re-authenticated, it continued.

Checks and stopping. It passed the 50 pytest cases, ruff, and the execution check of 32 correct SQL queries (the 30 questions plus 2 accept_sql), committed, requested “Please re-evaluate on all 30 questions,” and stopped. It included an explanation: --limit only runs the first N questions, so checking q17 and q21 near the end means running them all anyway.

Run 7. A person re-evaluated with /eval.

	Substantive accuracy	Exact match	Time
Run 6	29/30	11/30	8.0 min
Run 7	30/30	10/30	7.6 min

Substantive accuracy reached 30/30 for the first time. But the agent’s report began like this: q21 passed, but not because of this scoring change. In run 7, q21 answered in the per-year shape (4 columns) and passed in the shape of the original correct SQL. The added accept_sql was never used. It passed this time because of variation in the answer’s shape, and since the prompt was unchanged from run 6, the difference is within the range of variability and should not be concluded from a single run, it wrote.

accept_sql is a provision for scoring correctly the next time it answers with a 1-row difference. Whether it works won’t be known until that run comes.

4-6. Not Drawing Conclusions from Variability

From Sonnet 5 on, temperature can’t be specified (sending it returns a 400 error). Even for the same question, the generated SQL can change from run to run.

So I don’t conclude a fix worked based only on one question getting fixed or breaking. If it hasn’t been run multiple times under the same conditions, I record “may be variability.” The 30/30 in run 7 is an example of this promise actually at work. The more a number goes up, the more I check what actually caused it.

5. Turning Evaluation and Improvement into Skills

5-1. With Only Documents

Without skills, evaluation and fixes depended on human memory.

• For evaluation, a person ran run_eval.py and appended the result headline, changes, and the diff from the previous run to docs/EVAL.md
• How to fix failed questions was scattered across retrospective notes in EVAL.md. The next time something failed, a person reread them and decided

I turned these two into two skills: /eval and /guard-glossary-update.

5-2. /eval: Started Only by a Human

/eval is the procedure for running the 30 questions, comparing with the previous run, and recording the results.

Step	Content
1. What to measure	First write down the prompts, glossary entries, and questions that changed since last time. If nothing changed, write “measuring variability”
2. Run number	Add 1 to the highest number in the existing records. When running only some questions, don’t assign a number
3. Run	Mention that it takes time and incurs charges, then run it
4. Compare with last time	List the IDs that were fixed, broke, and are still failing, and classify the causes of the ones that broke
5. Record	Append to docs/EVAL_run<N>.md and to the history in docs/EVAL.md

I wrote three rules to follow: don’t fix prompts or the glossary inside the skill (whether to fix is decided by the person who read the results), always report accuracy with both metrics, and don’t draw conclusions from variability.

For /eval, I wrote disable-model-invocation: true in the frontmatter, making it a skill only a human can start. Running the 30 questions incurs Anthropic API charges and takes 8 to 9 minutes.

5-3. /guard-glossary-update: Stops Just Before Re-evaluation

/guard-glossary-update is the procedure that starts from a failed question, decides where to fix, and applies the smallest diff. Both a human and Claude can start this one.

1. Read the generated SQL and assign it to one of the seven categories from Section 3
2. Get the correct answer from real data via the BigQuery MCP. Only write values into the glossary that you ran and saw. In a session where MCP isn’t available, stop without writing values
3. Apply a diff in one place only
4. Pass the yaml syntax check and pytest
5. Ask a human for re-evaluation and stop

At the end of the SKILL.md, I included the real cases from Section 4 (q04, q26, q18, and the fact that Opus 5 didn’t fix things) as “real cases we ran into.” The next time something fails, instead of a person rereading the retrospective notes in EVAL.md, the agent reads these cases.

Its actual behavior (q21) is as described in 4-5. It went through classification, finding the same-shape question, checking against real data, the diff, and the tests, and stopped just before re-evaluation.

5-4. The Billing Boundary Is the Boundary of Who Starts It

The two skills split just before the operation that incurs charges.

Operation	Charges	Who starts it
Classification, checking real data, diff, tests	BigQuery reads only (with MCP limits)	Human and Claude (/guard-glossary-update)
Re-evaluating 30 questions	Anthropic API	Human only (/eval)

The fixing skill stops when re-evaluation becomes necessary and asks the human: “Please re-evaluate on all 30 questions. What to look at is whether q21 is fixed and whether other questions in the same category broke.” The human reads the request and decides whether to start /eval.

5-5. Trust a Skill Only After Running It Once

I verified that the two SKILL.md files worked as written by having subagents with no conversation history read them.

• /eval --limit 2: both questions correct, 0.4 minutes. Since it was a partial run, it didn’t touch docs/EVAL.md
• /guard-glossary-update q04: classified as “aggregation error,” with the fix location being terms in glossary.yaml. Since the session had no MCP, it stopped without writing values and showed the SQL for checking

This surfaced discrepancies between the SKILL.md and the actual behavior: a partial run with --limit doesn’t update the record files, run 4 and earlier have no individual record files, and so on. Even for a procedure the agent wrote by reading the code, the argument details don’t line up until you run it. As in Parts 2 and 3, I trust a skill only after running it once.

6. Records and Operations

6-1. DECISIONS.md

Design decisions are kept in docs/DECISIONS.md, dated, in a “what, and why” form. The URLs of the official docs I checked go there too. Decisions made through evaluation (the reasoning for making Sonnet 5 the default model, the glossary rewrite) also go here.

EVAL.md records “what happened”; DECISIONS.md records “why I did it.” In the instruction sheet for long tasks, I have the agent read DECISIONS.md first (Part 3).

6-2. Structured Logs

Every time the app finishes processing a question, it logs an event called question_done. Looking at its sql_rerun field, you can check that the SQL wasn’t rerun for a format-only instruction like “make it a line chart” (the effect of the few-shot examples in Part 3).

The evaluation can only measure the 30 prepared questions. Logs are how you later check that the app behaved as intended on real questions not in the evaluation. On the BigQuery side, the job history lets you check that no queries failed in the same time window.

6-3. Table Expiration

When I ran the data verification skill (/verify-data), I found that all 5 tables in BigQuery had an expiration date of 2026-11-06. It was the dataset default, a setting that deletes tables 60 days after loading. Left alone, the app would have silently broken in November.

I added a step to /verify-data to check expiration dates. The operation that removes expirations (bq update) is set to ask in the Part 1 settings, so a human runs it.

Before publishing Part 4, I removed the dataset default (60 days) and the expirations on the 5 tables with bq update. Since bq commands stop at ask, at first a person checked the content of each one and approved them one by one, and partway through, on the person’s instruction, the rest proceeded without approval. After that, I confirmed with the /verify-data procedure that 0 tables had an expiration date.

The same check also found that the total row count in the docs had an addition error (84,819,407 → correctly 85,167,407). Both are the kind of error the 30 evaluation questions can’t catch.

6-4. Deploying to Cloud Run

The app can be put on Cloud Run. It isn’t public; only accounts that have been granted permission can open it. The deploy procedure is also a skill, /deploy, and pushing to production (--run) always stops for human confirmation via ask in settings.

The procedure and the failures I ran into while deploying (such as the health check passing while only questions failed) will be covered in detail in a separate bonus post.

7. The First Project’s Harness

7-1. This Part’s Harness

Tool	What it is in this repository	What a human did when there were only documents
Evaluation set	eval/questions.yaml (30 questions, 3 levels, correct SQL verified by running it)	Ask a few questions by hand and check the answers by eye
Judging	eval/run_eval.py (matching results, substantive accuracy and exact match)	Read the SQL and judge whether it’s right
Skill	/eval (human only), /guard-glossary-update	Run run_eval.py and append to EVAL.md. Reread retrospective notes to decide how to fix
Records	docs/EVAL.md, docs/DECISIONS.md, question_done logs	Remember what was changed and what happened
Permissions and stop conditions	/eval is human only; removing table expirations is ask	Remember which operations incur charges and which settings break things

7-2. Overview of the First Project

Here is the harness built from Part 0 through Part 4, in one table.

Element	What it is	Part
Project documents	CLAUDE.md, design doc, data dictionary, DECISIONS.md, HARNESS.md	Part 2
Permissions and stop conditions	allow / ask / deny in .claude/settings.json	Part 1
MCP	BigQuery (MCP Toolbox, read-only, 2 GiB limit per query, dedicated service account)	Part 1
Skill	cms-csv-to-bigquery, verify-data, display-check, eval, guard-glossary-update, deploy (plus sync-public, only in the working repository)	Parts 2–4
Hook	Automatic guard tests, ruff and tsc, billing gate	Part 3
Evaluation	30-question accuracy evaluation, 50 guard unit test cases	Parts 3 and 4

What was left to the human was judgment where ask stopped things, and reading the evaluation results. What counts as correct, which fix to adopt, and when to run the re-evaluation that incurs charges were decided by a person to the end.

7-3. What Carries Over to the Second Project

In the second project, I change the subject and use the same pattern.

What carries over	How it’s used
eval and guard-glossary-update	The pattern “evaluate → classify failures → where to fix → re-evaluation is human” stays as is. Remove subject-specific values (column names, real examples of categories) to make them general
Permission design	ask for operations with charges or external impact, allow for read-only ones, deny for keys and destructive operations
Hook setup	Post-edit checks, and a gate before charges

cms-csv-to-bigquery, which loads CSV into BigQuery, isn’t used in the second project. The second project’s subject is not querying a database but the work of reading literature and making judgments.

8. Summary

• Build the evaluation set first, by a human. Deciding what counts as correct is a decision about what the app should answer, and correct SQL is verified by running it
• Judge by results, with two metrics. Look at substantive accuracy of 29/30 and exact match of 11/30 side by side
• Classify failures before fixing. The category determines where to fix. One diff in one place, and check every question of the same shape. A bigger model doesn’t fix errors on the instruction side
• Stop at the billing boundary. The fixing skill stops just before re-evaluation, and only a human can start the re-evaluation skill

Evaluation is the most direct form this series has given to “making things verifiable in advance.” Because there were correct answers for 30 questions, the glossary error was found, I could see what a fix broke, and I could decide how much to let the agent fix.

How much you can hand off is determined by how much you made verifiable in advance.

Next Steps

The first project ends with this part. The second project takes on screening for systematic literature reviews. I rebuild the method of a paper (TrialMind) that used LLMs for literature search, screening, and data extraction, using subagents with a separate role for each stage, and compare it with the original paper using the same metrics. The evaluation and fix pattern from the first project carries over as is.