# Triage classifications by confidence


> Split prompt_jev results into rows you can auto-accept and rows that need a second look.

Every `prompt_jev` answer comes with calibrated probabilities. Use this guide to split a classified table into three groups: rows confident enough to act on, rows worth a second pass with a language model, and rows a person should read. You're done when each row has a disposition and you know what fraction of the table each bucket holds.

:::info[Preview]
`prompt_jev` is in [preview](/about-motherduck/feature-stages/). The function name, parameters, and return types may change.
:::

## Before you start

- A table with a `prompt_jev` result column. See [Classify text with prompt_jev](/key-tasks/ai-and-motherduck/classify-text-with-prompt-jev/), which builds the `my_db.job_postings` table these examples use and fills its `role_family` column from the [job postings dataset](/getting-started/sample-data-queries/job-postings/).
- A rough sense of what an error costs you. That's what sets the threshold, not a default number.

## Step 1: Know which number to read

The three question types expose certainty differently.

| **Type** | **Read** | **Meaning** |
|---|---|---|
| `noul` | The returned `DOUBLE` | Probability the statement is true. Values near 0.5 are the uncertain ones. |
| `choice` | `.confidence`, and `.probabilities` for the runner-up | How certain the model is about the winning label. |
| `score` | `.confidence`, and `.score` for the position | How certain the model is about where on the scale the row sits. |

For `choice`, the gap between the top two probabilities often separates cases better than `confidence` alone. A row at 0.45 against 0.43 is a genuine tie; a row at 0.55 against 0.12 is not.

#### Read the confidence and runner-up

Database: `my_db`

```sql
SELECT
    title,
    role_family.choice AS role_family,
    role_family.confidence,
    list_sort(
        list_transform(role_family.probabilities, p -> p.probability),
        'desc'
    ) AS ranked
FROM my_db.job_postings
LIMIT 10;
```

An analytics engineering posting is the classic tie: it splits between `analytics` and `data_engineering` and lands around 0.5 on both. A posting titled "Staff Machine Learning Engineer" does not.

## Step 2: Look at the distribution before picking a threshold

Bucket the confidence values and see where the mass sits. Pick the threshold from this, not from a round number.

Over 200 postings classified into five role families, the mass sits at the top and the tail is thin but real:

#### Bucket the confidence values

Database: `my_db`

```sql
SELECT
    floor(role_family.confidence * 10) / 10 AS confidence_bucket,
    count(*) AS rows_in_bucket
FROM my_db.job_postings
GROUP BY confidence_bucket
ORDER BY confidence_bucket;
```

Two thirds of the postings land at 0.9 or above, and 30 sit below 0.6. A cut at 0.85 sends 136 rows straight through, 34 to a second model pass, and 30 to a person.

If you have labelled rows, measure accuracy per bucket instead. The threshold you want is the lowest bucket where accuracy is still acceptable.

#### Measure accuracy per bucket

Database: `my_db`

```sql
SELECT
    floor(role_family.confidence * 10) / 10 AS confidence_bucket,
    count(*) AS rows_in_bucket,
    round(avg((role_family.choice = human_label)::INT), 3) AS accuracy
FROM my_db.job_postings
WHERE human_label IS NOT NULL
GROUP BY confidence_bucket
ORDER BY confidence_bucket;
```

## Step 3: Assign a disposition

Write the buckets as a column so downstream queries agree on the split.

#### Assign a disposition

Database: `my_db`

```sql
CREATE OR REPLACE TABLE my_db.role_family_triage AS
SELECT
    job_id,
    title,
    description,
    role_family.choice AS role_family,
    role_family.confidence,
    CASE
        WHEN role_family IS NULL THEN 'failed'
        WHEN role_family.confidence >= 0.85 THEN 'auto'
        WHEN role_family.confidence >= 0.60 THEN 'escalate'
        ELSE 'review'
    END AS disposition
FROM my_db.job_postings;

SELECT disposition, count(*) AS postings
FROM my_db.role_family_triage
GROUP BY disposition
ORDER BY postings DESC;
```

Handle `NULL` explicitly. A failed request and a low-confidence answer need different follow-ups: the first is a retry, the second is a judgement call.

## Step 4: Send the uncertain rows to a language model

`prompt_jev` is cheap per row, `prompt` is not. Running the cheap pass over everything and the expensive pass over the remainder keeps the cost close to the cheap one.

#### Send the uncertain rows to a language model

Database: `my_db`

```sql
CREATE TABLE my_db.escalated_role_family AS
SELECT
    job_id,
    title,
    prompt(
        'Which kind of data role does this job posting describe? Reply with the label only.' ||
        E'

Posting: ' || description,
        struct := {role_family: 'VARCHAR', reasoning: 'VARCHAR'},
        struct_descr := {
            role_family: 'one of: analytics, data_engineering, data_science, machine_learning, other',
            reasoning: 'one sentence explaining the choice'
        }
    ) AS second_pass
FROM my_db.role_family_triage
WHERE disposition = 'escalate';
```

Merge the two passes into one column:

#### Merge the two passes

Database: `my_db`

```sql
SELECT
    t.job_id,
    coalesce(e.second_pass.role_family, t.role_family) AS final_role_family,
    CASE WHEN e.job_id IS NULL THEN 'jev' ELSE 'llm' END AS decided_by
FROM my_db.role_family_triage t
LEFT JOIN my_db.escalated_role_family e USING (job_id)
WHERE t.disposition IN ('auto', 'escalate');
```

The escalation pass is also where a `reasoning` field earns its place: `prompt_jev` gives you a number, not an explanation, so the rows a person reviews are the ones worth spending tokens on an explanation for.

## Step 5: Queue the rest for a person

#### Queue the rest for a person

Database: `my_db`

```sql
SELECT job_id, title, role_family AS suggested_role_family, confidence
FROM my_db.role_family_triage
WHERE disposition = 'review'
ORDER BY confidence ASC;
```

Sorting ascending puts the least certain rows first, so a reviewer working top-down clears the highest-value items first.

## Step 6: Retry the failures

`NULL` results are requests that failed after retries or ran past the AI function timeout. Rerun them against the source table.

#### Retry the failures

Database: `my_db`

```sql
UPDATE my_db.job_postings
SET role_family = prompt_jev(
    description,
    'Which kind of data role does this job posting describe?',
    choice := ['analytics', 'data_engineering', 'data_science', 'machine_learning', 'other']
)
WHERE role_family IS NULL AND description IS NOT NULL;
```

## Tuning the split

- **A large `review` bucket** points at the criteria, not the threshold. Overlapping options or a missing `other` option force the model to spread probability across several labels. Fix the criteria and rerun.
- **High confidence and wrong answers** means the question is under-specified. The model is certain about a question that isn't the one you meant.
- **Thresholds are per question**, not per table. A role-family question and a seniority question on the same postings will sit at different cut-offs.
- **Recheck after any wording change.** Editing the instructions or criteria shifts the distribution, so the old threshold no longer means what it meant.

## Related tasks

- [Classify text with prompt_jev](/key-tasks/ai-and-motherduck/classify-text-with-prompt-jev/)
- [Job postings dataset](/getting-started/sample-data-queries/job-postings/) — the data these examples run against
- [`prompt_jev` SQL reference](/sql-reference/motherduck-sql-reference/ai-functions/prompt-jev/)
- [`prompt` SQL reference](/sql-reference/motherduck-sql-reference/ai-functions/prompt/)


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fai-and-motherduck%2Ftriage-classifications-by-confidence%2F&page_title=Triage%20classifications%20by%20confidence&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.