For decades, a SQL aggregate had a non-negotiable rule: it could take millions of rows in, but the output had to be deterministic. COUNT doesn’t improvise. SUM doesn’t suddenly count two extra dollars just because the prompt was pretty.
Google has now put a probabilistic model in that exact position.
AI.AGG looks like a normal aggregate: it sits after GROUP BY, analyses a group of text or images, and returns one STRING per group. What it actually does is hand the rows off to Gemini on Vertex AI in batches, then aggregate the batch results again, until the whole group has been collapsed into one natural-language conclusion. The warehouse no longer only answers how many — it starts answering what these records, taken together, mean.
That is more than another LLM hook in BigQuery. It changes the boundary of the analysis chain: work that used to live in the application layer — exporting data, chunking context, orchestrating model calls, merging outputs — can now be pushed into a single SQL statement. Being able to write it as a single SQL statement, however, is not the same as suddenly inheriting the determinism, the cost model, and the auditability of SUM.
AI.AGG is not generation, it is compression
BigQuery already shipped AI.GENERATE and AI.GENERATE_TEXT. They are good at row-level work: classify each review, summarise each ticket, identify each image. As many inputs go in, that many model results usually come out.
The pattern gets awkward the moment the question is global.
Suppose the table has 50,000 product reviews and we want to know the recurring advantages, the recurring complaints, and the overall attitude towards price and quality. Row-by-row generation gives us 50,000 local judgements. We still have to solve three problems ourselves: how to chunk the data into a model’s context, how to merge the batch conclusions, and how to stop the intermediate outputs from re-exceeding the context window.
AI.AGG is built for exactly that reduction layer. The official docs make the contract explicit: it automatically performs multi-level aggregation — batching the raw data first, then aggregating the batch results. The data being analysed can therefore exceed a single Gemini context window, without us having to hand-write tree-style summarisation or MapReduce-style merges.
Seen this way, it is more like an LLM-driven REDUCE than a more convenient text-generation function.
| Function | Granularity | Typical output | Best fit |
|---|---|---|---|
AI.GENERATE | once per row | one result per row | classification, extraction, rewrite |
AI.GENERATE_TEXT | once per row | result table with model metadata | row-level generation with call-level visibility |
AI.AGG | once per group | one string per group | summarisation, induction, cross-record patterns |
The real shift is not that “SQL can write words”, but that the database is now natively taking over the context orchestration that used to live in the application layer.
What actually happens behind one SQL statement
The syntax is not complicated:
AI.AGG(
[ DISTINCT ] INPUT,
INSTRUCTION
[, connection_id => 'CONNECTION']
[, endpoint => 'ENDPOINT']
)
INPUT can be a string, or a STRUCT of strings, Cloud Storage object references, and arrays of them. Object references are produced by OBJ.GET_ACCESS_URL, so the same aggregate can consume reviews, logs, images, or documents. INSTRUCTION is the aggregation prompt. connection_id is how BigQuery talks to Vertex AI and Cloud Storage. endpoint selects the Gemini model. If you omit the connection, BigQuery falls back to end-user credentials; if you omit the endpoint, BigQuery picks a model for you.
A minimal review-analysis query looks like this:
SELECT
product_id,
AI.AGG(
review,
'''
Summarize the overall sentiment for this product.
List the top three positive themes and top three complaints.
Separate observations about price, quality, and usability.
'''
) AS review_summary
FROM `analytics.product_reviews`
WHERE review IS NOT NULL
GROUP BY product_id;
On the surface, this is not so different from STRING_AGG. The execution semantics are not.
Think of the internal process as a reduction tree:
- BigQuery collects the input rows belonging to one group.
- The engine splits a large group into several batches that fit a single inference call.
- Gemini produces a batch-level summary for each one.
- Those intermediate summaries are batched and summarised again.
- The whole group finally collapses into one string.
This is why it can step around the single-call context limit. That “stepping around”, however, is not free capacity. The official docs spell it out: because multi-level batching and aggregation keep resending intermediate results to Gemini, the total tokens sent to the model are higher than the tokens in the source data.
In other words, AI.AGG removes the hand-written orchestration code but does not remove the orchestration cost — it just hides it inside the query plan.
The four jobs it is best suited for
The original Medium piece lists reviews, images, logs, and agent audits. For developers, a more useful question is not “which business vertical is this data from?” but “does the question satisfy two conditions at once — it has to span many records, and the answer cannot be expressed by a deterministic aggregate?”
Reviews and tickets: from counts to themes
Classic SQL can count the one-star reviews, and it can run keyword frequency. It still cannot easily recognise a cross-sentence, cross-record judgement like “users feel the price is fair, but the install flow is universally frustrating”.
AI.AGG can group by product, version, or channel and compress large amounts of free-form feedback into themes, sentiment, and example quotes. It is good for exploratory induction, not for replacing raw metrics. A safer query keeps review counts, average rating, and negative-ratio next to the natural-language conclusion, so the prose never stands without a deterministic anchor.
Log analysis: behaviour patterns, not error codes
The most interesting question in application logs is rarely “how many 500 errors did we see?” but “what were the last few things a user did before they churned?” The first should go to COUNTIF and a time series; the second needs a behavioural pattern reconstructed across many events.
AI.AGG fits this kind of analysis, but only after the SQL layer has already done the filtering, sessionisation, and field trimming. Pushing a raw log table at the model not only blows the cost up, it also drowns the real signal in noise.
Multimodal data: object tables join the aggregate
Through BigQuery object tables and OBJ.GET_ACCESS_URL, the input can reference images sitting in Cloud Storage. The official example asks Gemini to summarise the main categories across a folder of product photos; the same shape can answer “which animals appear most in this camera-trap dataset” or “which brands show up most in user-uploaded pictures”.
The docs also list hard limits: rows that contain ten or more images may be skipped, single rows over 10 MiB may fail with an internal error, and some images created via OBJ.GET_ACCESS_URL may not be processable. Multimodal support has arrived inside SQL, but it is not yet “throw the whole image bucket in and walk away”.
Agent operation audits: turn interaction logs into improvements
If the agent platform records task type, tool calls, human takeover, and completion, AI.AGG can group by agent version or task family and answer which tasks finish fastest, which steps most often need a human in the loop, and which common patterns appear right before failure.
That is a richer explanation than a success rate, but it also carries a risk: models turn correlation into causation very confidently. AI conclusions should generate investigation hypotheses, not directly trigger agent rollbacks or production configuration changes.
“20 million rows is fine” does not mean “20 million rows is the right thing to run”
Google’s published ceilings are generous: up to 20 million rows per query, and up to 1,000 distinct groups. Go above these numbers and queries may time out. Each group’s output is also capped at 10,000 tokens.
These numbers say the system is aimed at warehouse-scale aggregation, not at client-side prose generation. They are not cost commitments, and they are not quality guarantees.
The cost guidance in the docs is worth taking seriously: for complex queries with JOIN or ORDER BY ... LIMIT, the number of rows actually processed by the model can diverge from what the developer expected. To control inference size strictly, write the query’s result into a separate materialised table first, and only then run Gemini against it.
That is how I split a production query into two steps:
CREATE OR REPLACE TABLE `analytics.ai_agg_input` AS
SELECT
product_id,
review
FROM `raw.product_reviews`
WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND review IS NOT NULL
QUALIFY ROW_NUMBER() OVER (
PARTITION BY product_id
ORDER BY created_at DESC
) <= 5000;
Then run the inference:
SELECT
product_id,
COUNT(*) AS sampled_reviews,
AI.AGG(
review,
'Summarize recurring themes. Do not infer facts absent from the reviews.'
) AS summary
FROM `analytics.ai_agg_input`
GROUP BY product_id;
At minimum, this lets us answer three questions before any model call: how many rows, how many groups, and what sampling rule was applied. Pair that with AI.COUNT_TOKENS for text input estimation, and the cost stops being a black box.
The name “aggregate function” is the most dangerous part
AI.AGG is shaped like SUM or AVG with prose, and that is exactly the trap. Three differences matter.
First, results are not guaranteed stable. Model, endpoint, prompt, and the batch path can all change the wording, and even the order of themes. If the result feeds a report, persist the prompt, the model endpoint, the query version, and the generation time — do not re-derive the prose every time someone opens the page.
Second, failure semantics are not all-or-nothing. The docs are explicit: if some Vertex AI calls fail, the function may return partial results; only when every call fails, or every input row is invalid, does it return NULL. “A string came back” is not the same as “the full input was processed”. Today the function only returns one string — there is no per-batch status table to audit.
Third, it is not the right tool for precise computation. Order amounts, risk thresholds, SLA breaches, user counts — all of these should keep living in deterministic SQL. AI.AGG is for open-ended questions, or for producing candidate conclusions for a human reviewer.
A practical rule of thumb: if a wrong answer can automatically charge a card, ban a user, fire an alert, or flip a production flag, do not let AI.AGG be the final decision-maker on its own.
Four guardrails I would add before going to production
Pin the input set
Materialise first, infer second. Filtering, sampling, deduplication, and access control stay in plain SQL, so every AI call can be traced back to a known data snapshot.
Pin the output contract
Even though the function returns only a string, the prompt can demand a stable format, such as JSON. JSON returned by the model must still be checked with SAFE.PARSE_JSON or an application-level schema; parse failures should land in an error queue, not be silently swallowed.
AI.AGG(
TO_JSON_STRING(STRUCT(review_id, rating, review)),
'''
Return valid JSON with keys:
overall_sentiment, positive_themes, negative_themes, uncertainties.
Do not include markdown fences.
'''
)
Build a small evaluation set
Take a fixed sample of real data that covers the normal case, the long tail, conflicting cases, and dirty data; tag it with human ground truth or acceptance criteria. After any change to prompt, model, or pre-processing, run this sample first before expanding the surface area.
Keep conclusions and evidence separate
Save the natural-language summary together with the raw data snapshot, the deterministic metrics, the query text, the model endpoint, and the generation time. When the model says “price complaints dominate”, the analyst must be able to walk back to the underlying reviews and statistical distribution, not just trust a fluent paragraph.
Limits you should not ignore yet
The official Google page still labels AI.AGG as Preview, applies the Pre-GA terms, and warns that support may be limited. This conflicts with the Medium piece’s claim of general availability. For anyone planning to adopt it, treat the current official docs and the project-level behaviour you can actually verify as the source of truth, not a second-hand article.
Endpoint choice is also not fully free: only Gemini models that do not require a thinking budget are usable; for Gemini 3 the Agent Platform does not support regional endpoints and you need global, us, or eu. Cross-region selection can also affect data-governance and compliance review.
The known issues include: some image objects may fail to process, long-running queries on Workforce Identity Federation may fail unexpectedly, and certain combinations of DISTINCT aggregates can hit an internal error. All of that points to a feature that is best suited to controlled experiments and supporting analysis, not to replacing a mature data-product pipeline.
The data warehouse is becoming an inference runtime
The most important thing about AI.AGG is not that Google added another AI function to BigQuery. The signal is that data no longer has to leave the warehouse before model induction enters the picture. Permissions, filtering, grouping, and inference are starting to live in the same SQL.
That removes a layer of application glue code, and it pushes new responsibilities back onto data engineering: how to estimate token costs, how to version probabilistic outputs, how to spot partial results, how to audit prompts, and how to keep a model upgrade from quietly shifting historical reports.
For decades, we trusted aggregates because the same input always produced the same answer. Now, something that looks like a GROUP BY may be running multi-level Gemini inference behind the scenes and handing back a string that is shaped like an answer.
When the database starts to “understand” our data, the skill that becomes scarce may no longer be wiring models into SQL. It may be knowing which parts still have to stay in the hands of deterministic computation and human judgement.