prompt_jev is in preview. Requests from every cloud region are processed by TypeSafe's servers in the United States. The function name, parameters, and return types may change. Avoid workloads with regulated or sensitive data while in Preview.Classify text with prompt_jev
Use this guide to turn a column of free text — job descriptions, support messages, closure notes, reviews, transcripts — into a column you can group, filter, and sort on. You're done when the text column has a typed companion column stored in a table.
Before you start
- A MotherDuck organization on the Lite or Business plan, in any region. In
eu-central-1andeu-west-1, an Admin must turn it on first. - A table with a
VARCHARcolumn holding the text. - Write access to a database where you can store the results.
The examples run against the job postings dataset: 200,000 postings for data roles, each with the full description text. Load a slice of it into your own database to follow along:
CREATE OR REPLACE TABLE my_db.job_postings AS SELECT * FROM 'https://us.data.motherduck.com/job_postings/parquet/year=2026/month=03/jobs.parquet' ORDER BY listed_date DESC, job_id LIMIT 200;
Two hundred rows is enough to work with and small enough that a full pass costs little. Every measured number on this page comes from that slice.
Step 1: Pick the question type
prompt_jev answers one of three question shapes. Pick the one that matches the decision you need.
| You need | Type | Returns |
|---|---|---|
| One label out of a fixed list | choice | The winning label, per-label probabilities, and a confidence |
| A rating on an ordered scale | score | A weighted position on the scale, per-level probabilities, and a confidence |
| Whether a statement is true | noul | A probability between 0 and 1 |
"Which role family is this?" is a choice. "How senior is this role?" is a score, because the levels have an order. "Does this posting state a salary range?" is a noul.
If you can't enumerate the possible answers, this isn't the right function. Use prompt with a struct schema instead.
To put several of these questions to the same row in one call, see Ask many questions in one pass.
Step 2: Write the instructions and criteria
Write the instructions as a question about one row, not as a task description for the model. Keep criteria short, mutually exclusive, and phrased in the same register.
-- Good: one question, disjoint options
SELECT
prompt_jev (
description,
'Which kind of data role does this job posting describe?',
choice := [
'analytics',
'data_engineering',
'data_science',
'machine_learning',
'other'
]
)
FROM
my_db.job_postings
LIMIT
20;Rules the binder enforces, so you'll see these as errors before any row runs:
choiceandscoreneed at least two labels, and they must be unique.choice,score, andnoulare mutually exclusive. Pick one.instructions,choice,score, andnoulmust be constants. They can't reference a column.
For score, order the levels from lowest to highest. The order defines the scale, and the returned score is a weighted position on it.
SELECT
prompt_jev (
description,
'How senior is the role this posting describes?',
score := [
'intern',
'junior',
'mid',
'senior',
'staff or above'
]
)
FROM
my_db.job_postings
LIMIT
20;Step 3: Test on a sample
Run against a small slice first and read the answers before spending a full pass over the table. Twenty rows is enough to catch instructions that are ambiguous or criteria that overlap.
SELECT
title,
prompt_jev (
description,
'Which kind of data role does this job posting describe?',
choice := [
'analytics',
'data_engineering',
'data_science',
'machine_learning',
'other'
]
) AS role_family
FROM
my_db.job_postings
USING SAMPLE
20 ROWS;Look at role_family.confidence across the sample. A run where most rows come back below roughly 0.6 usually means the criteria overlap or a needed option is missing — add an other or unclear option and run again. Job postings are a good illustration: analytics engineering roles split their probability between analytics and data_engineering until one of the two labels carries a description that claims them.
Step 4: Combine several fields into the input
prompt_jev takes one text value per row. When the decision depends on more than one column, concatenate them with labels so the model can tell the parts apart.
SELECT
prompt_jev (
'Title: ' || title || E'
' || 'Location: ' || location || E'
' || 'Description: ' || description,
'Does this role require working from an office at least part of the week?'
) AS onsite_required
FROM
my_db.job_postings
LIMIT
20;Leave out columns the question doesn't depend on. They add tokens and dilute the signal. A question about the work rarely needs the posting date, and a date in the input invites the model to reason about it.
Step 5: Materialize the results
The function runs once per row per query, so store the answers rather than recomputing them.
CREATE TABLE my_db.posting_role_family AS
SELECT
job_id,
title,
prompt_jev (
description,
'Which kind of data role does this job posting describe?',
choice := [
'analytics',
'data_engineering',
'data_science',
'machine_learning',
'other'
]
) AS role_family
FROM
my_db.job_postings;Keying the results table on job_id rather than adding a column to the source means a new question, threshold, or model produces a new table without rebuilding the postings.
To add the column to an existing table instead, declare the exact return type:
ALTER TABLE my_db.job_postings
ADD COLUMN role_family STRUCT(
choice VARCHAR,
probabilities STRUCT(value VARCHAR, probability DOUBLE) [],
confidence DOUBLE
);
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;Step 6: Verify and backfill
A row returns NULL when its input was NULL or when the request failed after retries. The query itself doesn't fail, so check for gaps:
SELECT count(*) AS missing FROM my_db.job_postings WHERE role_family IS NULL AND description IS NOT NULL;
Rerun the UPDATE from step 5 to fill them in. The WHERE role_family IS NULL clause means only the missing rows are sent.
Step 7: Query the results
The classification is a plain column now.
SELECT role_family.choice AS role_family, count(*) AS postings, round(avg(role_family.confidence), 2) AS mean_confidence FROM my_db.job_postings GROUP BY role_family ORDER BY postings DESC;
That run took about three seconds over 200 postings. analytics taking half the corpus is the title filter showing through, not a claim about the job market.
For a score question, sort or bucket on the numeric value:
SELECT
date_trunc('month', listed_date) AS month,
round(avg(seniority.score), 2) AS mean_seniority
FROM
my_db.job_postings
GROUP BY
month
ORDER BY
month;Ask many questions in one pass
One call can answer a whole list of questions about the same row. Pass questions instead of instructions, with one named entry per question. Each entry carries its own type and instructions.
Twenty noul questions, one per language, turn a description into twenty probabilities:
CREATE OR REPLACE TABLE my_db.posting_languages AS
SELECT job_id, prompt_jev(description, questions := {
sql: {type: 'noul', instructions: 'Does this posting ask for SQL?'},
python: {type: 'noul', instructions: 'Does this posting ask for Python?'},
r: {type: 'noul', instructions: 'Does this posting ask for R, the language?'},
scala: {type: 'noul', instructions: 'Does this posting ask for Scala?'},
java: {type: 'noul', instructions: 'Does this posting ask for Java, not JavaScript?'},
go: {type: 'noul', instructions: 'Does this posting ask for Go, not the verb?'},
rust: {type: 'noul', instructions: 'Does this posting ask for Rust?'},
c: {type: 'noul', instructions: 'Does this posting ask for C, not C++ or C#?'},
cpp: {type: 'noul', instructions: 'Does this posting ask for C++?'},
csharp: {type: 'noul', instructions: 'Does this posting ask for C#?'},
javascript: {type: 'noul', instructions: 'Does this posting ask for JavaScript?'},
typescript: {type: 'noul', instructions: 'Does this posting ask for TypeScript?'},
julia: {type: 'noul', instructions: 'Does this posting ask for Julia, the language?'},
kotlin: {type: 'noul', instructions: 'Does this posting ask for Kotlin?'},
ruby: {type: 'noul', instructions: 'Does this posting ask for Ruby?'},
php: {type: 'noul', instructions: 'Does this posting ask for PHP?'},
swift: {type: 'noul', instructions: 'Does this posting ask for Swift?'},
matlab: {type: 'noul', instructions: 'Does this posting ask for MATLAB?'},
bash: {type: 'noul', instructions: 'Does this posting ask for Bash scripting?'},
perl: {type: 'noul', instructions: 'Does this posting ask for Perl?'}
}) AS languages
FROM my_db.job_postings;The answer is a STRUCT with one DOUBLE field per question, named after the key you gave it, so languages.rust is the probability that the posting asks for Rust.
Say what a question excludes whenever two answers read alike. C matches C++, C#, and "C-level" until the question rules them out, and Go matches the verb.
Twenty questions in one call send the description once. Twenty separate noul queries send it twenty times, and usage is metered on input tokens.
What you give up is throughput. batch_size only applies to single-question calls, so a questions call sends one request per row instead of packing 32 rows into one. Over a large table a wide question set runs slower than a single question does.
Cast the struct to a MAP to read the answers as rows instead of twenty columns, so adding a language later doesn't change the shape of every query downstream:
SELECT e.key AS language, count(*) FILTER(e.value >= 0.5) AS postings, round(avg(e.value), 2) AS mean_probability FROM my_db.posting_languages, unnest(map_entries(languages::MAP(VARCHAR, DOUBLE))) AS t (e) GROUP BY language ORDER BY postings DESC;
Troubleshooting
prompt_jev requires at least two criteria for type "choice" — add a second option, or switch to noul if the question is a yes/no.
prompt_jev "choice" parameter must be a constant value — the label list references a column. Move the values into the query text, or run one query per label set.
prompt_jev "choice", "score", and "noul" cannot be combined — drop the extra argument. Pick one question type per call, or ask many questions in one pass.
AI functions are not available for your organization. — the organization is on the Free plan, or an Admin has asked support to disable prompt_jev. See the TypeSafe integration page.
Many rows come back NULL — the query hit the AI function timeout. Split the table into batches with LIMIT and OFFSET, or filter down to the rows that still need an answer and rerun.
Related tasks
- Triage classifications by confidence — decide which rows to trust and which to escalate
- Job postings dataset — the data these examples run against
prompt_jevSQL referencepromptSQL reference — for generated text and open-ended extraction