EHRSQL: A Practical Text-to-SQL Benchmark for Electronic Health Records

In plain words

Hospital staff ask questions that need facts from a patient database, and the existing question sets do not match what they actually ask. The goal of this paper is a benchmark that does.

The authors asked hospital staff what they would want to know. They turned those questions into templates and wrote the matching database queries by hand, for two open hospital databases. Many questions use time expressions such as "last year". Some questions cannot be answered from the database at all, so a good system must also be able to say that it cannot answer, instead of guessing.

The paper's baseline is a general sequence-to-sequence model. It writes a query, and if its uncertainty during writing is high, it does not run the query. The benchmark scores both the answers that come back and the refusals.

TL;DR

A text-to-SQL benchmark for two EHR databases (MIMIC-III and eICU), built from questions that hospital staff submitted. It adds time expressions and unanswerable questions, and defines a "trustworthy semantic parsing" task that scores answers only when the query is executed and correct. A T5-base baseline with an entropy threshold reaches an author-reported 89.9 test F1 on MIMIC-III (percentile threshold). Use it to test refusal behaviour on structured EHR data, after checking the data version (see Pitfalls).

Problem

Existing EHR question-answering sets are either generated from rules and filtered by crowd workers (MIMICSQL, limited to five tables) or derived from a clinical reading-comprehension set (emrKBQA, not publicly released when the paper was written) §2. The authors' poll at a university hospital found that these sets do not cover what staff actually ask §1.

Three gaps follow. First, questions use many time expressions, which matter in healthcare §1 tab. 3. Second, typical text-to-SQL sets assume every input is answerable: none of the compared datasets contains unanswerable questions tab. 4. Third, the Spider grammar parsers cannot produce the time-related operators (strftime, datetime, NULL) that these questions need §4.3.

Method

Data collection and filtering §3

A poll collected questions as if the respondent were talking to an AI speaker. Negative examples were shown too (questions that need outside knowledge, ambiguous ones, or requests for clinical reasons). The poll gave 1,742 utterances from 222 valid respondents §3 fig. 1.

Utterances were filtered for ambiguity, outside-knowledge needs and out-of-schema content. Remaining ones were grouped by patient scope: one patient, a group of patients, or no patient §3.1.1. Near-duplicate phrasings were merged into one template. The authors also added simple variants by hand and linked two medical event types for longitudinal questions §3.1.1. The result is 230 question templates: 174 answerable and 56 unanswerable §3.1.1 tab. 2.

Time templates §3.1.2

A time filter is one of three types: a global filter, a "within" filter that bounds events inside another event, or an exact point ("first", "last", or a timestamp). Each filter has an expression type (absolute, relative or mixed), a unit (standard calendar unit or an event such as a hospital visit), an interval type (since, until or in), and an optional choice of one event §3.1.2 tab. 3. Each time template has a natural-language expression and a SQL pattern. Templates with "this" are not combined with "since" or "until", and "N units ago" is a fixed point in time app. B.2 fig. 5.

SQL annotation §3.1.3

Four graduate students labelled the SQL over five months. One person wrote each query and a reviewer checked it for each database §3.1.3. The annotators were asked not to use JOIN and to write nested queries that follow the schema hierarchy, because the large log-style tables (about 330 million rows in MIMIC-III chartevents) make joins slow §3.1.3 fig. 2 app. C.3 fig. 6.

Template combination §3.1.4

A question is generated in three stages from a question template fig. 3:

1. operation values are sampled from fixed lists (for example maximum, five years, two or more times) §3.1.4 app. B.3;
2. time templates allowed by the question template are sampled §3.1.4;
3. condition values (drug, diagnosis, year) are sampled from the database, and the query must return at least one valid answer §3.1.4.

The fully filled question and SQL form one pair.

# Build one question-SQL pair from a template  [§3.1.4] [fig. 3]
def generate_pair(question_template, sql_template, database):
    # Stage 1: schema-independent operation values  [§3.1.4]
    operations = sample_operation_values(question_template)
    # Stage 2: time templates the question allows  [§3.1.4]
    times = sample_time_templates(question_template)
    # Stage 3: condition values drawn from the database  [§3.1.4]
    values = sample_condition_values(question_template, database)
    question = fill(question_template, operations, times, values)
    sql = fill(sql_template, operations, times, values)
    # Keep the pair only if the SQL has at least one valid answer  [§3.1.4]
    if not has_valid_answer(sql, database):
        return None
    return question, sql

Database preprocessing §3.2 app. D

• A cost table was added to both databases, with event costs that follow a Poisson draw (mean 10) for four event types and Gaussian values around it §3.2.
• Times were shifted: admissions were moved into 2100 to 2105 and the current time was set to 2105-12-31 23:59:00. Records after that time were removed, and patients without a discharge time count as current. About 10% of the sampled patients are current §3.2 app. D.2.
• 1,000 patients were sampled (MIMICSQL used 100). Ages 11 to 89 are kept; eICU's ">89" becomes 90. Where one value has several units, only the most common unit is kept. Records are lower-cased app. D.1.
• De-identification: diagnosis, procedure, lab, prescription, chart, input, output, microbiology, care-unit and ward records were shuffled across patients, keeping their times §3.2 app. D.3.

Paraphrasing §3.3 app. E

Templates are first stripped of domain terms, then paraphrased in five steps: human paraphrases (about 21 per template); machine paraphrases from T5 models and back-translation; a RoBERTa-large duplicate-question filter; ranking by GPT-Neo 1.3B perplexity with Levenshtein de-duplication; and a crowd review in which only unanimous passes are kept. About 47 paraphrases per template remain on average §3.3. Slot values are swapped back in after paraphrasing §3.3.

Splits §4.1 app. F

All question templates appear in every split. Paraphrases do not overlap between splits. Unanswerable questions are only in validation and test, where they make up 33% of each split §4.1. The number of pairs per template depends on its slot count: training gets 30 to 60 pairs for templates with fewer than three slots, 40 to 80 for three or four, and 50 to 100 for five or more. Validation gets 4 to 5 per template. Test follows the same rule but weights templates by physician-rated importance (3, 2 or 1) app. F.

The released counts were taken from the repository files (see Pitfalls for the release date):

Counts from the repository's JSON files valid.json test.json valid.json test.json.

The paper gives 9.3K, 1.1K and 1.8K pairs per database and 24,411 in total §4.1. The task description says the test set is hidden; the repository now ships test files with labels README.md.

Task: trustworthy semantic parsing §4.1 fig. 4

A model must (1) write SQL for each question, (2) decide from its own confidence whether the question is answerable, and (3) send the query to the database only when it is confident. Confidence comes from the model's own output distribution, not from training data §4.1.

Baseline model §4.3 app. G

The baseline is T5-base with a plain question-to-SQL input, or with a schema variant that appends the table and column names to the question. The paper chose general sequence-to-sequence models because standard grammar-based parsers reject time operators such as strftime, datetime and NULL §4.3. Training uses teacher-forced cross-entropy trainer_t5.py. The loader drops unanswerable items unless asked to keep them [code: utils/dataset.py#L53-L54].

Refusal by entropy §4.3

During decoding, the model's maximum per-token entropy is recorded. If it is above a threshold, the model refuses. Two heuristics set the threshold on the validation set:

# Max-entropy threshold from the validation set  [§4.3]
def threshold_kmeans(max_entropies):
    # Two-cluster K-means (k = 2); take the boundary between the clusters
    # [§4.3] [code: t5_threshold.ipynb#L83-L95]
    clusters = kmeans(max_entropies, num_clusters=2)
    return midpoint_between_clusters(max_entropies, clusters)

def threshold_percentile(max_entropies):
    # 67th percentile, since about 33% of validation items are
    # unanswerable [§4.3] [code: t5_threshold.ipynb#L166]
    return percentile(max_entropies, 67)

# Refusal rule at inference  [§4.3] [code: T5/abstain_with_entropy.py#L27-L31]
def answer_or_refuse(predicted_sql, step_entropies, threshold):
    # Maximum entropy over the decoded steps
    if max(step_entropies) <= threshold:
        return predicted_sql
    return "null"

Evaluation §4.2 evaluate.py

The first family (F1_ans) asks whether the model answered the answerable questions and refused the rest. The second (F1_exe) also requires the executed result to match the gold result. The denominators are the same in both; only the numerators differ §4.2.

# gold_answerable[i]: gold SQL is not "null"        [§4.2]
# answered[i]: the model did not refuse               [§4.2]
# correct[i]: gold and predicted results are equal   [§4.2]
def f1_scores(gold_answerable, answered, correct):
    # Precision over questions the model answered  [§4.2]
    p_ans = count_both(gold_answerable, answered) / count(answered)
    # Recall over questions that are answerable  [§4.2]
    r_ans = count_both(gold_answerable, answered) / count(gold_answerable)
    # Execution precision and recall count only correct results  [§4.2]
    p_exe = count_correct_answered / count(answered)
    r_exe = count_correct_answerable / count(gold_answerable)
    return harmonic(p_ans, r_ans), harmonic(p_exe, r_exe)

Hyperparameters

Values from the paper's training appendix app. G and the official code.

The paper gives no threshold values, and the repository gives none for eICU. The values above come from the repository's notebook t5_threshold.ipynb.

Results

Author-reported. Setup: T5-base, trained on each database separately. Values are percentages from tab. 5. Refusal thresholds were set on validation and then applied to test.

MIMIC-III and eICU, validation and test:

tab. 5

Zero-shot transfer of a Spider-trained parser (GAP), execution accuracy on answerable validation questions, by Spider hardness. Numbers in brackets are correct over answerable queries tab. 6:

tab. 6 Only about 7% (107 of 1,515) of answerable validation questions could be parsed by the Spider grammar §4.4.

Other author findings: the percentile threshold does best on both splits because the validation and test splits have the same answerable ratio §4.4. The entropy distributions for answerable and unanswerable questions differ significantly (Mann-Whitney, p < 0.001) app. H.4 (the text cites this figure as Figure 7; in source order it is Figure 8).

Paper vs code

The official repository is github.com/glee4810/EHRSQL, owned by an author. Its LICENSE file is CC BY 4.0 LICENSE. Checked at code_commit 01241a3c.

• Weight decay: the paper lists 0.1 app. G. The default optimizer is Adam, whose branch never passes the weight-decay value; only the AdamW branch does optim.py config.py [code: T5/config.py#L20]. With the default settings, the 0.1 is not applied.
• Training step unit: the paper says 100,000 steps. The code counts each micro-batch as a step and steps the optimizer once every eight micro-batches trainer_t5.py trainer_t5.py. So 100,000 steps is about 12,500 optimizer updates at effective batch 32.
• Model selection: the paper does not say how the checkpoint is chosen. The code keeps the checkpoint with the lowest validation loss [code: T5/trainer_t5.py#L88-L89] trainer_t5.py. That validation set omits unanswerable items main.py [code: utils/dataset.py#L53-L54].
• Refusal rule: the paper's rule (refuse above a threshold on maximum entropy) matches the code, which answers when max entropy <= threshold abstain_with_entropy.py.
• Percentile and K-means thresholds: the code's notebook follows the paper's two heuristics t5_threshold.ipynb t5_threshold.ipynb. The repository gives values only for MIMIC-III; eICU values are not in the repository.
• Tokenizer: the code adds four tokens (<, <=, <>, <96) to the T5 tokenizer model.py model.py. The paper does not mention this step.
• Schema input: the code appends each table name and its column names, in their original schema names, after the question. The whole schema string is lower-cased, and shuffling is off dataset.py [code: utils/dataset.py#L85-L105].
• Evaluation: the paper's six scores match the code's definitions evaluate.py §4.2. The code also rewrites the query before comparison: it replaces the current time and fixes the ranges of some vital signs evaluate.py evaluate.py. The paper mentions these post-processing steps app. C.1.
• Data release: the paper's counts come from the NeurIPS version. The repository's CHANGELOG records later SQL fixes and question rewrites CHANGELOG.md CHANGELOG.md. The repository's total is 24,405 pairs, not the paper's 24,411.
• Unanswerable placeholder: the README example shows "nan" for unanswerable queries README.md, but the shipped validation file stores "null" (362 rows), which is the value the evaluation skips evaluate.py.

Pitfalls

• Step counts: the 100,000 steps are micro-batches. Eight micro-batches make one optimizer update, so this is about 12,500 updates (see Paper vs code). Validation every 5,000 micro-batches is about every 625 updates.
• Padding in the loss: the collator pads labels with the padding token and does not replace it with an ignored index dataset.py. The model is then trained with the padding positions in the loss trainer_t5.py. Check this before reusing the loss.
• Entropy source: the code takes the entropy from one beam row per example (every num_beams / num_samples-th score row) at each step [code: T5/generate.py#L49-L53]. The returned SQL may come from a different beam, so the refusal score and the answer may not match. This follows from the slicing as written; it was not checked against the beam-search internals.
• Entropy and length: the threshold uses the maximum over all steps up to EOS generate.py. Longer SQL has more steps and so more chances for a high value. The paper does not discuss this effect.
• Threshold reuse: thresholds are tied to one model and one split. The schema and no-schema thresholds differ (0.149 vs 0.099). Do not reuse them for another model or database without refitting.
• Percentile rule: the 67th percentile assumes you know the share of unanswerable questions (33% on validation). The paper expects the percentile method to win for this reason §4.4. In deployment that share is unknown.
• Refusal is not trained: unanswerable questions are never in training dataset.py. Refusal comes only from the threshold applied after decoding.
• Input and output length: inputs and labels are truncated silently at 512 tokens dataset.py. A truncated SQL cannot run.
• Evaluation details: only the first 100 result rows are compared, after sorting their string forms evaluate.py. A 60-second timeout applies evaluate.py. Errors and timeouts count as wrong answers evaluate.py. The vital-sign constants are part of the gold query's meaning; copy them exactly.
• Data version: the paper's numbers come from the NeurIPS release. The current repository has later SQL fixes and question rewrites [code: dataset/ehrsql/CHANGELOG.md#L3-L5]. Expect different numbers unless you use the same release. The fetched arXiv source is the one recorded in raw/arxiv (version v6).
• Test labels: the paper says the test set is hidden, but the repository now ships labelled test files README.md. Scores on the public test set are not the paper's hidden-test scores.
• Database values: the two databases are time-shifted app. D.2 and de-identified by shuffling app. D.3. The README says they are for SQL evaluation, not clinical analysis README.md.
• Figure numbering: the text cites Figure 7 for the entropy distribution. In source order that figure is Figure 8, and Figure 7 is the paraphrase pipeline app. H.4 fig. 8.
• Access: rebuilding the databases from raw MIMIC-III and eICU needs PhysioNet credentialed access. The README also offers preprocessed SQLite files README.md.

Lineage

• Builds on: the T5 text-to-text model (arXiv:1910.10683, not compiled here), which the paper uses unchanged apart from the tokenizer additions above §4.3. Entropy-based uncertainty for autoregressive output follows Malinin and Gales (arXiv:2002.07650, not compiled here) §4.3.
• Compared against: MIMICSQL, a rule-generated EHR text-to-SQL set §2; Spider, KaggleDBQA and SEDE for general text-to-SQL tab. 4; and GAP, a Spider-trained parser, for zero-shot transfer §4.4 tab. 6.
• Follow-up: the repository README links an EHRSQL version built on MIMIC-IV and a 2024 shared-task overview. Neither is compiled here README.md.