AI functions

Use AI functions to filter and join documents, score answers, assign labels, extract text, write text answers, and read sparse autoencoder features. The examples use BigQuery syntax. See the SQL reference for dialect selection and a first query.

Functions

Select a function to jump to its arguments, examples, and limits. Square brackets mark optional arguments.

PROMPT

PROMPT combines a question template with document column references. The placeholders {0}, {1}, and so on identify the columns in argument order.

For a filter, the prompt references one column:

PROMPT('Does this review discuss the ending of the movie?\n\n{0}', r.body)

For a join, the prompt references one column from each table:

PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
       r.body, a.aspect)

Here {0} is the review and {1} is the aspect.

A prompt argument can also read a struct field or apply a text function to a column. See Column expressions.

AI.IF

AI.IF takes a PROMPT and evaluates it on each row or each pair of rows. The model returns TRUE or FALSE.

Filtering

Use AI.IF in a WHERE clause to filter rows:

SELECT r.id
FROM reviews r
WHERE AI.IF(PROMPT('Does this review mention a positive aspect?\n\n{0}', r.body))
  AND AI.IF(PROMPT('Does this review discuss the ending?\n\n{0}', r.body))

Use AND to combine filters. All AI filters on one table must read the same document column.

Joining

Use AI.IF to join two tables with a natural language condition:

SELECT r.id, a.aspect
FROM reviews r, aspects a
WHERE AI.IF(PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
                   r.body, a.aspect))

Add column comparisons in ON to restrict the pairs the model evaluates. For example, imagine that each claim has a wiki_url identifying its source passage in an evidence table:

SELECT c.id, e.id
FROM claims c
JOIN evidence e
  ON c.wiki_url = e.id
 AND AI.IF(PROMPT('Does the passage support the claim?\n\nClaim: {0}\nPassage: {1}',
                  c.claim, e.text))

The model evaluates only pairs where c.wiki_url = e.id.

EXISTS and NOT EXISTS

EXISTS keeps a row if the subquery returns at least one match. NOT EXISTS keeps it if the subquery returns no matches.

The following query finds reviews that discuss at least one aspect:

SELECT r.id
FROM reviews r
WHERE EXISTS (
    SELECT 1 FROM aspects a
    WHERE AI.IF(PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
                       r.body, a.aspect)))

Use NOT EXISTS to find reviews that discuss none of the listed aspects:

SELECT r.id
FROM reviews r
WHERE NOT EXISTS (
    SELECT 1 FROM aspects a
    WHERE AI.IF(PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
                       r.body, a.aspect)))

The subquery must be SELECT 1 FROM <table> WHERE AI.IF(...).

Options

Both AI.IF and AI_FILTER accept an optional second argument with planning hints:

AI.IF(PROMPT(...), {'selectivity': 0.3, 'anchor': 'r'})
AI_FILTER(PROMPT(...), {'selectivity': 0.3, 'anchor': 'r'})

• selectivity estimates the fraction of rows or pairs that pass. Quail uses it to choose filter and join order. The default is 0.2, meaning 20%. The hint does not change model answers.
• anchor (joins only) sets the table alias whose document KV is reused across pairs. This side is the anchor. If omitted, Quail selects the anchor using estimated query time. See Joins.

AI.SCORE

Unlike AI.IF, which returns only TRUE or FALSE, AI.SCORE(PROMPT(...)) returns a score between 0 and 1 as a FLOAT64. Higher scores indicate a stronger model preference for a positive answer to your question.

Return scores

For example, imagine a documents table where each row is a customer support request. Its body column contains the request text. The following query returns the score for whether each customer requests a refund:

SELECT d.id,
       AI.SCORE(PROMPT('Does the customer request a refund? {0}', d.body))
           AS refund_score
FROM documents d

A score in SELECT requires a column alias with AS.

Filter rows

Compare the score with <, <=, >, or >=. For the same support requests, the following query keeps rows with a refund score of at least 0.75:

SELECT d.id
FROM documents d
WHERE AI.SCORE(
    PROMPT('Does the customer request a refund? {0}', d.body),
    {'selectivity': 0.1}
) >= 0.75

Choose the threshold using labeled examples from your task. A score of 0.75 does not establish that the model is correct 75% of the time.

The optional selectivity hint estimates the fraction of rows that pass the comparison. It defaults to 0.2 and does not change the scores. See Options.

Join tables

For example, imagine a queries table of search queries and a documents table of articles. The following query pairs each search query with articles whose relevance score is at least 0.8:

SELECT q.id, d.id
FROM queries q
JOIN documents d ON AI.SCORE(
    PROMPT('Is {1} relevant to {0}?', q.text, d.body)
) >= 0.8

For a prompt that reads two documents, the template must contain {0} once. Refer to that document in words if you need to mention it again.

To return a score for every pair without filtering, use CROSS JOIN and put AI.SCORE in SELECT.

Model behavior

All supported models can run AI.SCORE:

• Qwen3 rerankers are trained to score a document's relevance to a query. For a prompt with two documents, the first is the query and the second is the candidate document.
• Decision 2.0 Kai returns its probability of "Yes" when choosing between "Yes" and "No".
• Qwen3 generative models and DiffusionGemma compare their scores for TRUE and FALSE answers. Quail converts that comparison to a value between 0 and 1.

Quail computes a repeated score once when it appears in SELECT and a condition, or in several comparisons. A query cannot combine AI.SCORE with AI.IF.

AI.CLASSIFY

AI.CLASSIFY returns one of your supplied categories as a STRING. For example, imagine a reviews table with review text in body. The following query returns a sentiment label for each review:

SELECT r.id,
       AI.CLASSIFY(r.body, ARRAY['positive', 'negative', 'mixed']) AS sentiment
FROM reviews r;

A classification in SELECT requires a column alias with AS. The call must be a separate item, rather than part of a larger expression.

Arguments

AI.CLASSIFY(input, categories[, options])

Each category can have a description of up to 25 words. For example, ARRAY[('refund', 'asks for money back'), ('praise', 'expresses approval')]. If labels have the same score, Quail returns the first in the list.

ARRAY[{'label': 'refund', 'description': 'asks for money back'}, {'label': 'praise'}]

Optional parameters

Pass an object as the third argument, such as {'probabilities': true}.

The probabilities sum to 1. They are available for individual documents; classifications of joined rows return labels only.

Query examples

Filter by category

Use the classification's AS name in WHERE to filter by its label. For example, the following query keeps negative or mixed reviews from the reviews table:

SELECT r.id,
       AI.CLASSIFY(PROMPT('What sentiment does this review express? {0}', r.body),
                   ARRAY['positive', 'negative', 'mixed']) AS sentiment
FROM reviews r
WHERE sentiment IN ('negative', 'mixed');

• = keeps one category. Other comparisons raise CompileError.
• A category that is not in the list matches no rows.
• You can also repeat the AI.CLASSIFY call in WHERE. Quail evaluates the classification once and uses its label for both the filter and the result.

Classify joined rows

Use a PROMPT that refers to both documents to classify joined rows. For example, imagine a reviews table of movie reviews and an aspects table of subjects such as acting or plot. The following query finds the aspects each review discusses and classifies its sentiment about each matched aspect:

SELECT r.id, a.aspect,
       AI.CLASSIFY(
           PROMPT('What is the opinion of the review about the aspect? {0} {1}',
                  r.body, a.aspect),
           ARRAY['positive', 'negative', 'mixed']) AS opinion
FROM reviews r
JOIN aspects a ON AI.IF(
    PROMPT('Does the review discuss this aspect? {0} {1}', r.body, a.aspect));

These limits apply:

• The classification and its AI.IF join run on one GPU.
• A WHERE condition cannot test the label of a joined row.
• A query cannot classify joined rows and also use AI.SCORE.

For labels computed before later filters, see Classification answers. For the scoring methods, see Label scoring.

AI.EXTRACT

AI.EXTRACT returns the answer to each of your questions, copied from a document. For example, imagine a contracts table with contract text in text. The following query returns the end date and the signer of each lease:

SELECT c.id,
       AI.EXTRACT(c.text,
                  {'end_date': 'When does the lease end?',
                   'signer': 'Who signed the lease?'}) AS lease
FROM contracts c;

The result is a struct with one STRING field for each question. A field is null when the document does not answer its question. For a contract with the text Lease ends on March 3, 2027. Signed by Ana Ruiz., lease is {"end_date": "March 3, 2027", "signer": "Ana Ruiz"}.

An extraction in SELECT requires a column alias with AS. The call must be a separate item, rather than part of a larger expression.

Arguments

AI.EXTRACT(input, questions)

Answer positions

Quail also returns the position of each answer in a second struct. Its name is the alias followed by _span, and it has the same fields. Each field holds the start and end character positions of its answer. For the contract above, lease_span is {"end_date": {"start": 14, "end": 27}, "signer": {"start": 39, "end": 47}}. In Python, text[start:end] returns the answer.

Each question is a separate extraction. On the Quail backend, the questions about one document read the same document KV.

Filter on answers

Use the extraction's AS name to filter by an answer field in WHERE. For example, with the same contracts table, the following query keeps leases whose extracted end date contains "2027":

SELECT c.id,
       AI.EXTRACT(c.text, {'end_date': 'When does the lease end?'}) AS lease
FROM contracts c
WHERE lease.end_date LIKE '%2027%';

Quail extracts the answer once and uses it for both the filter and the result. Documents with no answer are excluded. Use lease.end_date IS NULL to keep documents where no end date was found.

Answer fields support the conditions listed in WHERE and ON. You can also test an answer's position. For example, lease_span.end_date.start < 500 keeps answers that start within the document's first 500 characters.

Quail may reorder a table's AI filters, classifications, and extractions to reduce estimated work. A condition on an answer runs after the extraction that produces it. Use a planning hint to set the expected fraction of documents that pass, or pass order="as_written" to session.sql() to preserve the written order.

Limits

• Use a model that supports AI.EXTRACT. See Supported models and GPUs.
• Filters on answers run on the Quail backend only.
• ORDER BY, DISTINCT, and GROUP BY cannot use the result of an extraction.

AI.MAP

AI.MAP writes a text answer for each row, following your instruction. For example, imagine a reviews table with review text in body. The following query writes a one-sentence summary of each review:

SELECT r.id,
       AI.MAP(r.body, 'Summarize the review in one sentence.') AS summary
FROM reviews r;

The result is a STRING column with one answer for each row. The model always picks its most likely next token, so the same document and instruction give the same answer. An answer ends when the model finishes or when it reaches the answer limit.

A map in SELECT requires a column alias with AS, and the call must be a separate item. To change the answer, map in a CTE, then change it in the outer query:

WITH m AS (
    SELECT r.id,
           AI.MAP(r.body, 'Summarize the review in one sentence.') AS summary
    FROM reviews r
)
SELECT m.id, UPPER(m.summary) AS loud_summary
FROM m;

Arguments

AI.MAP(input, instruction[, max_tokens])

Several maps and other functions

Each map in a query writes its own answer. On the Quail backend, the maps about one document read the same document KV. Maps also work with AI.IF, AI.CLASSIFY, and AI.EXTRACT on the same table. The following query summarizes only the negative reviews:

SELECT r.id,
       AI.MAP(r.body, 'Summarize the review in one sentence.') AS summary
FROM reviews r
WHERE AI.IF(PROMPT('Is this review negative?\n\n{0}', r.body));

Quail writes answers only for the rows that pass the filter.

Use answers in the query

You can use a map's answer in WHERE, ORDER BY, GROUP BY, and SELECT DISTINCT, like any text column. The following query keeps reviews whose complaints mention the ending:

SELECT r.id,
       AI.MAP(r.body, 'List what the review complains about.') AS complaints
FROM reviews r
WHERE complaints ILIKE '%ending%';

Quail writes each answer once and uses it for both the condition and the result. Use a planning hint to set the expected fraction of rows that pass.

AI.SAE_FEATURES

AI.SAE_FEATURES returns the sparse autoencoder (SAE) features of a prompt. An SAE changes the model's hidden state for one token into many values, and most of these values are zero. Each nonzero value is a feature, with an ID and a strength. A feature ID has no name. To find what a feature means, read the documents where its strength is largest. The function does not generate text.

For example, imagine a tickets table with the first message of each support conversation in request. The following query returns the features of each request:

SELECT t.id,
       AI.SAE_FEATURES(PROMPT('{0}\n\nWhat is the customer asking for?',
                              t.request),
                       pooling => 'last') AS intent
FROM tickets t;

The result is a list with one struct for each nonzero feature, from the strongest to the weakest. For the request Hi, I need to cancel one of my flights booked on May 17., intent starts with [{"feature": 7850, "strength": 2.01}, {"feature": 20512, "strength": 1.53}, ...].

Strengths are nonnegative activation values, not probabilities. They can exceed 1 and are not normalized to sum to 1. A filter such as strength > 1 uses an arbitrary cutoff; 1 has no special meaning.

An AI.SAE_FEATURES call in SELECT requires a column alias with AS. The call must be a separate item, rather than part of a larger expression.

Arguments

AI.SAE_FEATURES(prompt, pooling => 'last' | 'max')

Pooling

• 'last' uses the last token of the prompt. The question follows the document, so the question changes these features. Use 'last' to read features about the question, e.g., the intent of a request. The list has at most 100 features.
• 'max' uses the largest strength of each feature over all tokens of the prompt. Most of these tokens are document tokens, so the features describe the content of the document. The list can have thousands of features.

Unnest the features

Put the call in UNNEST in FROM to get one row for each feature. The rows have the columns f.feature and f.strength, where f is the AS name. The other columns repeat for each feature. Use these columns in SELECT, WHERE, GROUP BY, ORDER BY, and aggregates. To give a field another name in the result, write f.feature AS feature_id in SELECT. WHERE uses the original name.

For example, with the same tickets table, the following query counts how many requests return each feature:

SELECT f.feature, COUNT(*) AS requests
FROM tickets t,
     UNNEST(AI.SAE_FEATURES(PROMPT('{0}\n\nWhat is the customer asking for?',
                                   t.request),
                            pooling => 'last')) AS f
GROUP BY f.feature
ORDER BY requests DESC;

To read two lists, use one of these forms:

• UNNEST(a, b) AS u(f, g) puts the lists side by side. Row i has element i of each list. If one list is shorter, its fields are null in the rows after its end.
• UNNEST(a) AS f, UNNEST(b) AS g gives each pair of elements, one from each list.

A WHERE test of a feature field compares the field with literals. Use a comma or CROSS JOIN before UNNEST. A row with no features then gives no rows. To keep that row with null fields, write LEFT JOIN UNNEST(...) AS f ON TRUE. Other joins, other ON conditions, USING, and WITH ORDINALITY are not supported.

Limits

• Use the qwen3.5-2b-sae-bf16 model. See Supported models and GPUs.
• A query with AI.SAE_FEATURES cannot also use AI.IF, AI.SCORE, AI.CLASSIFY, or AI.EXTRACT.
• ORDER BY, DISTINCT, and GROUP BY cannot use the feature list. Use the fields of an UNNEST instead.
• UNNEST reads AI.SAE_FEATURES calls only.
• Calls over the same documents share their KV. If any call uses 'max', Quail also keeps the document features for the later calls. It reads each question in full and does not share prefixes between different documents, because KV does not contain the residual values that 'max' needs.

For an application, see Find customer intents in support conversations.

Planning hints

Use HINT(condition, {'selectivity': fraction}) on a classification label, extracted answer, or map answer to estimate the fraction of documents that pass. Quail uses the estimate to choose the order of AI functions.

For example, in the lease query above, replace the WHERE clause with:

WHERE HINT(lease.end_date LIKE '%2027%', {'selectivity': 0.4})

The hint estimates that 40% of documents pass. The default is 20%. A hint affects planning only; it does not change which rows pass.