AI functions in BigQuery (AI.GENERATE, AI.IF, AI.CLASSIFY)

BigQuery calls Gemini from SQL with AI.GENERATE, AI.IF, AI.CLASSIFY, AI.SCORE and others. Every row is a request to Vertex AI, billed there. The BigQuery dry run shows only the bytes of your table, so it can't warn you what a query will cost.

BigQuery’s AI functions send each row’s prompt to a Gemini model on Vertex AI (which Google now calls Gemini Enterprise Agent Platform) and return the answer as a SQL value. AI.GENERATE returns free text or a typed struct, AI.IF returns a BOOL you can put in a WHERE, AI.CLASSIFY returns a category, AI.SCORE a number. The model call is billed by Vertex AI, per token, on top of the BigQuery query. The dry run doesn’t include it: a query that asks Gemini for a summary of 2,225 articles dry-runs at 5 MB, the size of the text column, which is a fraction of a cent. We did not run any of the LLM calls on this page, so the examples are dry runs and the return types come from dry-run schemas.

The functions, and what each returns

Google groups them into general-purpose functions, where you pick the model, prompt and parameters, and managed functions, where BigQuery chooses the model and prompt wording. We read the return types from the schema of a dry run:

The table-valued versions work on a table with a remote model: AI.GENERATE_TEXT (the older name ML.GENERATE_TEXT also exists), AI.GENERATE_TABLE for a custom output schema, and AI.GENERATE_EMBEDDING. Both names resolved in our test. Separate task-specific functions call Cloud AI APIs for translation, document processing, transcription, natural language and vision.

The general-purpose functions return a struct on purpose. status holds the error for that row, and full_response has the model’s complete response, including safety ratings. The managed functions return a bare scalar and no status. Row-level failures, such as exceeding quota, come back as NULL, and the query still succeeds. For AI.IF, AI.SCORE and AI.CLASSIFY, max_error_ratio sets the share of failed rows the query tolerates. The default is 1.0, which tolerates failures. Lower it (we saw 0.2 accepted) so that a quota problem fails the query instead of returning a partial answer.

Passing max_error_ratio to AI.GENERATE is rejected: “No matching signature”. AI.IF also takes an optimization_mode argument, MINIMIZE_COST or MAXIMIZE_QUALITY, for the optimized mode described below. max_error_ratio isn’t supported together with MINIMIZE_COST.

The prompt is built from columns

A prompt is a string, or a struct of strings and columns that BigQuery concatenates:

SELECT
  title,
  AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result AS summary
FROM mydataset.articles;

SELECT AI.GENERATE(('Extract the name and age: ', description),
                   output_schema => 'name STRING, age INT64') AS person
FROM mydataset.patients;

SELECT title FROM mydataset.articles
WHERE AI.IF(('Is this article about football? ', body));

SELECT AI.CLASSIFY(ticket, ['billing', 'shipping', 'other']) AS topic, COUNT(*)
FROM mydataset.tickets
GROUP BY topic;

With output_schema, the result is a struct with the field names and types you give, and the schema is checked: our dry run of the name STRING, age INT64 schema returned age INT64 and name STRING in the output schema. Types follow the schema, so you can use the result in WHERE and GROUP BY without a CAST.

What the dry run shows

The first example reads only the body and title columns of a public table of 2,225 BBC articles, and BigQuery reports the bytes of those two columns:

SELECT title, AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result AS summary
FROM `bigquery-public-data.bbc_news.fulltext`

Adding LIMIT 3 doesn’t change the estimate:

SELECT title, AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result AS summary
FROM `bigquery-public-data.bbc_news.fulltext`
LIMIT 3

Both say 5,045,835 bytes. Google’s reference says that to limit Vertex AI charges when you use LIMIT, you should write the selected rows to a table first and call the function on that table, not on a subquery with LIMIT:

CREATE TABLE mydataset.sample AS SELECT title, body FROM mydataset.articles LIMIT 10;

SELECT title, AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result FROM mydataset.sample;

AI.IF in a WHERE clause is the same. The function runs on each row of the table to decide whether it passes:

SELECT title
FROM `bigquery-public-data.bbc_news.fulltext`
WHERE AI.IF(('Is this article about football? ', body))

And a query with no table at all reports nothing:

SELECT AI.GENERATE('Write a haiku about nulls').result AS poem

0 bytes, one request to Gemini. The estimate in the editor, in the console and from bq --dry_run is the BigQuery side only. To estimate the model side, call AI.COUNT_TOKENS on a sample of your prompts and multiply by the model’s per-token price from the Vertex AI pricing page. After the fact, the job’s details show input, output, thought and cache token counts.

Cost, connections and quotas

BigQuery bills the query as usual. Vertex AI bills the model calls to the project that runs the query. Charges can take up to 24 hours to appear. To find them in Cloud Billing, filter the service Vertex AI and the label bigquery_job_id_prefix set to the job’s ID (the first 63 characters, in lower case). You can also attach your own query label to find a group of jobs.

With no connection_id, the function runs with your own credentials, which need roles/bigquery.jobUser and roles/aiplatform.user on the project. Google recommends a BigQuery connection, which runs as a service account, for jobs expected to run 48 hours or longer. A remote model for AI.GENERATE_TEXT and the other table functions always uses a connection:

CREATE MODEL mydataset.gemini
REMOTE WITH CONNECTION `myproject.us.myconnection`
OPTIONS (endpoint = 'gemini-2.5-flash');

An endpoint that BigQuery doesn’t know is an error at compile time (“Unsupported remote model endpoint”), and a connection that doesn’t exist fails the same way, in a dry run, before any request is made. Requests use dynamic shared quota by default. request_type can force Provisioned Throughput (DEDICATED) or shared quota (SHARED). The model and region combinations Google lists change often, so read the current list before you hard-code an endpoint.

Google’s overview adds an optimized mode for AI.IF and AI.CLASSIFY (Preview) that trains a small model on a sample of your rows and uses it for the rest. It avoids an LLM call for most rows, which cuts tokens and latency on large tables.

Store the answers

Running the query again calls the model again and bills again. Write the answers to a table once and query the table.