AI function costs
BigQuery's estimate counts bytes only, so a query that sends every row to Gemini can show $0.00. Querylab.io says when a query's AI calls bill on top, and shows the real prompts, the number of calls and their Gemini cost before you run it.
SELECT word, AI.IF(('Is this word a verb? ', word)) AS is_verb FROM `bigquery-public-data.samples.shakespeare`
What the estimate leaves out
The cost next to a query is BigQuery’s dry run, and a dry run reports only the bytes the query reads. A function such as AI.IF or AI.GENERATE also sends a request to Gemini for every row it runs on, and Vertex AI bills those tokens separately. The query above reads 1.27 MiB, which is free, and makes 164,656 Gemini calls, which cost a few dollars.
When a query calls a function that bills outside BigQuery’s bytes, the status bar shows AI calls billed separately next to the estimate. Hover it to see each function and who bills it.
| Functions | Billed by |
|---|---|
AI.GENERATE and its typed forms, AI.IF, AI.CLASSIFY, AI.SCORE, AI.AGG, ML.GENERATE_TEXT, ML.GENERATE_TABLE | Vertex AI, Gemini tokens for each call |
AI.EMBED, AI.GENERATE_EMBEDDING, AI.SIMILARITY, ML.GENERATE_EMBEDDING | Vertex AI, embedding tokens for each row (BigQuery’s built-in embedding models run on slots instead) |
AI.RANK, and AI.SEARCH with rerank => TRUE | The ranking API |
AI.TRANSLATE, ML.TRANSLATE | Cloud Translation |
AI.PARSE_DOCUMENT, AI.CHUNK_DOCUMENT, ML.PROCESS_DOCUMENT | Document AI, per page |
ML.UNDERSTAND_TEXT, ML.ANNOTATE_IMAGE, ML.TRANSCRIBE | The Natural Language, Vision and Speech-to-Text APIs |
ML.PREDICT on a remote model | Vertex AI |
AI.COUNT_TOKENS, AI.FORECAST, AI.DETECT_ANOMALIES, AI.EVALUATE and AI.PREDICT are billed by BigQuery itself and get no note. An AI. function Querylab.io doesn’t know yet is listed as one that may bill outside BigQuery.
AI.IF, AI.CLASSIFY, AI.SCORE and AI.AGG add their own instructions to every prompt, so each call costs far more than the prompt you wrote. Measured on BigQuery, AI.IF sends about 120 input tokens a call for an 8-token prompt.
Preview the prompts
Click AI calls billed separately, then Preview prompts on sample rows. Querylab.io copies sample rows of the tables the query reads into the engine in your browser and works out, for each Gemini or AI.EMBED call in the query:
- the first prompts it would send, built from your data, with a
NULLpart marked; - how many calls the sample makes, and how many the whole query makes;
- the tokens of one call and the cost;
- ways to make it cheaper.
Nothing in the preview calls Gemini. It uses a free dry run to find the tables, free row reads to copy them, and one AI.COUNT_TOKENS query, which bills 0 bytes.
For the query above, the preview shows prompts such as Is this word a verb? augurs, 164,656 calls on the whole query (every row of the table), about 119 input and 9 output tokens a call on gemini-3.1-flash-lite, and a Gemini cost of $3.56 to $7.12.
How the calls are counted
A table small enough to copy whole is copied whole, and the sample’s count is the count, whatever the query does with it. For a bigger table:
- Every row reaches the call (one table, no filter, join,
UNNESTor grouping, or aWHEREthat is only the AI call): the count is the table’s row count, exactly. - An outer
LIMIT: BigQuery applies it before the calls, so the query makes at most that many. It is exact when every row would reach the call. - A filter on one table: the table is read at 64 places spread over it, 16 rows each, and the share of those rows that pass the filter is scaled to the table. The count says about and scaled from the sample. On
samples.shakespearewithWHERE word_count > 5, the preview gave about 25,728 calls, and the table has 23,428 such rows. - No sampled row reaches the call: the preview says the call runs on only a small share of the table, and gives the cost per 1,000 calls.
Spreading the sample matters because rows next to each other are often alike. The first 1,000 rows of samples.shakespeare all have word_count = 1.
A query that joins several tables, or groups rows, gets no count for the whole query, and the cost is given per 1,000 calls. When a filter has to be left out of the preview (an AI.IF under OR, a HAVING or QUALIFY), the counts are marked at most.
Ways to make it cheaper
Under each call the preview names what would cut the cost, with what it saves:
- Repeated prompts. When a share of the sampled prompts repeat an earlier one, calling the function once per distinct prompt (
SELECT DISTINCTin a CTE, then a join back) skips those calls. - Optimized mode. For
AI.IF,AI.CLASSIFYandAI.SCOREfrom about 3,000 calls,optimization_mode => 'MINIMIZE_COST'trains a small model on part of the rows and answers the rest with it. - A cheaper model. When another Gemini model costs at least a fifth less for the same tokens, the preview names it and how much less.
What it can’t preview
The preview works on one SELECT statement with its AI calls in the select list or in WHERE, in the main query or in a WITH clause. It says why when it can’t preview a query:
- a script, DML, or pipe syntax;
UNION,INTERSECTorEXCEPT;- a table function in
FROM, such asML.GENERATE_TEXT,AI.GENERATE_TEXT,AI.GENERATE_TABLEorML.GENERATE_EMBEDDING; - a call inside a subquery, or in
ORDER BY,GROUP BYor a join condition; - an AI call inside another AI call’s prompt;
AI.AGGand a per-row AI function in the same select list;- a
WINDOWclause.
Run on 20 rows
After a preview, Run on 20 rows in BigQuery runs your query for real on 20 of the sample rows of each table it reads. The rows are sent with the query as a query parameter, so the run reads no table and bills 0 bytes; the AI calls on those rows do bill, usually under a cent.
When it finishes, the panel shows the first result rows (your query’s real answers) and the tokens BigQuery reports the calls used, from INFORMATION_SCHEMA.JOBS_BY_USER, with their cost. BigQuery reports the tokens a few seconds after the job; if they aren’t there yet, run it again a minute later.
The run needs every table written with its dataset (dataset.table), and the tables themselves, not views. A column whose type can’t be a query parameter (GEOGRAPHY, INTERVAL, RANGE) stops it, and the panel names the column.
How the cost is worked out
AI.COUNT_TOKENScounts the tokens of up to 200 sampled prompts per call, for the model the call uses. It runs in BigQuery, bills 0 bytes, and works on a project without the Vertex AI API.- Querylab.io adds the tokens each function sends and returns on top of your prompt, measured on BigQuery:
| Function | Added input tokens per call | Output tokens per call | Default model |
|---|---|---|---|
AI.GENERATE | none | any length, not priced | gemini-3.5-flash-lite |
AI.GENERATE_BOOL | about 37 | about 8 | gemini-3.5-flash-lite |
AI.GENERATE_INT | about 36 | about 10 | gemini-3.5-flash-lite |
AI.GENERATE_DOUBLE | about 38 | about 12 | gemini-3.5-flash-lite |
AI.IF | about 112 | about 9 | gemini-3.1-flash-lite |
AI.CLASSIFY | about 155, with three categories | about 3 | gemini-3.1-flash-lite |
AI.SCORE | about 350 | about 25 | gemini-3.1-flash-lite |
AI.AGG | about 400 per group, plus every row of the group | about 20 | gemini-3.1-flash-lite |
- The tokens are priced at the model’s price from Google’s Cloud Billing Catalog. The cost is a range from the Batch price to the standard price, because which of the two BigQuery bills hasn’t been confirmed yet.
A call that names its model with endpoint => '...' is priced at that model. AI.GENERATE output is left out of the cost, and the preview says so, because the model can write any length. When the call caps it with model_params => JSON '{"generationConfig": {"maxOutputTokens": 64}}', the output is priced at that cap and the cost is marked at most. The preview shows no cost for a model without a known price, such as a tuned model.
AI.EMBED is priced by its model: text-embedding-005 and text-multilingual-embedding-002 per character (white space isn’t counted), gemini-embedding-001 per token. An embedding has no output tokens.
Where else it shows
- Hover on an AI function in the editor has a Billing line: who bills its calls and per what, and for a managed function the tokens it adds to each prompt.
- Prism AI and external AI tools: the Run query? card names the AI functions in the SQL that bill outside the dry-run bytes it shows.
Limits and budgets
Querylab.io’s cost limits count BigQuery bytes, so they don’t stop Gemini charges. A byte limit of 1 GiB lets the query above run. To cap Gemini spend, set BigQuery’s AI token quotas (GenAiInputTokensPerDay and the output quota, per project or per user) in the Google Cloud console.