BigQuery's AI.AGG summarizes millions of rows in one SQL query. Count the rows it quietly leaves out

Ask an analyst to read 50,000 customer complaints and tell you the five main themes, and you will get an answer in a week. Ask a model, and you hit a different wall: the complaints do not fit in its context window. Google’s new BigQuery function is built for that wall.

AI.AGG, in preview, runs a natural-language instruction over millions of rows in a single SQL query and returns one aggregated answer per group. “Summarize the main failure causes in these logs, by service” becomes a GROUP BY.

How it works

Google treats semantic aggregation as a distributed map-reduce problem, with Gemini at every stage:

1. Validate rows
2. Batch to fit context
3. Infer in parallel
4. Synthesize recursively

It accepts text and images: it can aggregate visual findings across images in Cloud Storage without moving them. It works within BigQuery’s governance, including VPC Service Controls. Google’s suggested uses include diagnosing high-volume system logs and generating category lists that then feed AI.CLASSIFY, the row-level function.

The limits are the article

Google’s post is precise about limits, and several matter more than they look:

• Rows can disappear silently. If any field in a STRUCT passed to the function is NULL, the whole row is treated as NULL and discarded. Google’s advice is to wrap nullable fields with IFNULL. Transient batch errors also skip the rejected rows rather than failing the query.
• Scale has recommended ceilings. About 20 million rows and 1,000 groups per query, at most 10 MiB per row, fewer than ten images per row, and a final answer capped at 10,000 tokens per group.
• It costs more tokens than the data contains. Recursive batching means total tokens sent to Gemini exceed the token count of the source data. Unlike AI.CLASSIFY, which can use small distilled models on CPUs, AI.AGG uses full-weight models at every stage.

The first point deserves a pause. In ordinary SQL, a dropped row is a bug someone finds. Here, it is designed behavior. A summary of “all complaints this quarter” that silently excluded every complaint with a missing region field is not a summary of all complaints. The answer will read as complete either way.

Using it safely

Count what went in. Run a plain COUNT(*) per group with the same filters and the same IFNULL handling, and compare it with what the aggregation actually covered. If the function or your pipeline cannot report rows processed, build that check yourself before trusting the output in a report.

Ask for abstention. Google’s own prompt guidance says to design failure states, returning nulls rather than guessing, and to define an explicit output contract. Do both. An aggregate that says “insufficient evidence” is more useful than a confident paragraph built on a fraction of the rows. It is the abstention property we listed in the specification for agent context.

Estimate before running. Google points to AI.COUNT_TOKENS for cost estimates, strict WHERE and LIMIT filters, and context caching for static datasets. Make the estimate a required step for any query over a few hundred thousand rows.

Keep it for discovery. AI.AGG is excellent for finding themes nobody has named yet. Once a theme is named, move to row-level classification, which can be counted, audited and run with cheaper models. Discovery with the expensive tool, measurement with the cheap one.