Semantic aggregation over millions of rows in BigQuery with AI.AGG

Google has introduced AI.AGG, a fully managed semantic aggregation function in preview within BigQuery GoogleSQL that lets data teams run natural-language instructions over millions of rows in a single SQL query. AI.AGG handles chunking and parallel execution natively, validating rows (dropping or erroring on payloads over 10 MiB), batching inputs to fit the token limits of a chosen Gemini endpoint, running parallel inference through Vertex AI, and recursively synthesizing intermediate summaries until one aggregated string is produced per GROUP BY clause. The function supports text and multimodal inputs via BigQuery Object Tables and OBJ.GET_ACCESS_URL, and relies on full-weight LLMs at every stage rather than the distilled CPU proxy models used by row-level functions like AI.CLASSIFY.

For more than a decade, BigQuery has helped set standards for structured data aggregation, calculating financial sums, counting active users, and computing historical averages at a petabyte scale in seconds. But as modern enterprises become inundated with unstructured data — application logs, customer service transcripts, product imagery, and diagnostic reports — a critical gap has emerged. Historically, relational SQL engines have lacked the native ability to aggregate, synthesize, and extract overarching semantic themes from millions of rows of text and multimodal data. While BigQuery recently introduced powerful row-level AI functions like AI.GENERATE and AI.CLASSIFY , analyzing massive datasets holistically presents a unique architectural challenge: the large language model LLM context window. Even with advanced foundation models like Gemini, you cannot pass millions of rows into a single prompt without triggering severe truncation or hard execution failures. Traditionally, solving this required data engineering teams to construct complex external map-reduce pipelines, manage rate-limiting backoffs, and recursively stitch chunked summaries together. Today, we are thrilled to deep-dive into AI.AGG — currently in preview — a fully managed, multi-level semantic aggregation function natively embedded within BigQuery GoogleSQL. By conceptualizing semantic aggregation as a distributed map-reduce problem, AI.AGG enables data teams to run natural-language instructions over millions of rows of data using a single SQL query. Here is a look under the hood at how it works, transformative enterprise use cases, and best practices for deploying it in production. AI.AGG abstracts the immense engineering complexity of chunking and parallel execution. When you execute an AI.AGG query, the BigQuery engine automatically orchestrates a sequence natively within the database: Row-level validation: The engine helps verify no single row exceeds the maximum context window dropping or returning an error on payloads 10 MiB . Context-safe batching: The engine dynamically groups input rows into smaller batches that safely fit within the token limits of your chosen Gemini endpoint. Parallel inference: Your natural-language SQL prompt is applied to each batch in parallel, generating intermediate semantic summaries via Vertex AI. Recursive synthesis: Finally, the system batches these intermediate outputs together and passes them back to the LLM with the exact same instruction. This reduction repeats recursively until a single, final aggregated string is produced per SQL GROUP BY clause. Because AI.AGG requires deep reasoning to synthesize concepts across disparate batches, it relies entirely on full-weight LLMs for every stage of its pipeline — differentiating it from row-level functions like AI.CLASSIFY , which can leverage distilled CPU proxy models. AI.AGG natively supports both text and multimodal inputs. By leveraging BigQuery Object Tables and the OBJ.GET ACCESS URL function, analytics teams can aggregate visual insights across thousands of images stored directly in Cloud Storage with no data movement or transcoding required. Data engineering pro-tip: The NULL exclusivity rule When evaluating multiple columns, users often pass a STRUCT into the function. However, if even a single field within that composite structure evaluates to NULL, the entire row is treated as NULL and silently discarded. Always explicitly wrap nullable fields with IFNULL e.g., IFNULL description, 'No data provided' to help verify your data safely enters the pipeline. The true analytical power of AI.AGG emerges when combined with standard SQL GROUP BY operations. System logs generated by globally distributed microservices often hide subtle degradations like memory thrashing or systemic clock drift that never throw explicit fatal errors. Using AI.AGG , DevOps teams can group millions of logs by system component and generate targeted, human-readable diagnostic summaries. Prompting Tip: Instruct the model to explicitly describe the "normal operation" of the component before hunting for anomalies. This helps suppress the LLM's tendency to hallucinate errors on completely healthy logs. AI.AGG isn't just an analytical reporting tool; it’s a dynamic orchestration engine. Imagine acquiring a massive repository of legacy support tickets with no clear categorization. You can deploy AI.AGG to read the raw text, synthesize a definitive list of categories, and output them as a strict JSON array. By unpacking this array via JSON VALUE ARRAY and feeding those dynamic labels directly into row-level classifiers like AI.CLASSIFY , you can orchestrate an end-to-end, autonomous data labeling pipeline natively in SQL — reducing the need for external Python scripts. When integrating generative models into highly deterministic SQL pipelines, standard conversational prompts will often fail. Prompting for programmatic SQL outputs is a strict engineering discipline: Explicit output contracts: Treat the prompt as an API contract. Append commands like: "Return exactly one valid JSON array of strings. Do not include markdown code blocks or conversational text." Contextual delimiters: Use string concatenation with explicit labels e.g., '- Description: ' to anchor the LLM and prevent it from conflating fields across dense batches. Failure state design: Explicitly instruct the model to return strict null values for missing information rather than guessing, preventing schema hallucinations downstream. Few-shot anchoring: Provide a literal example of your expected structure within the prompt to heavily ground the model's attention mechanism. Transitioning semantic workloads to production requires operational oversight. Because of the recursive batching architecture, the total tokens sent to Gemini will be greater than the token count of your source data. To mitigate costs, mandate strict WHERE and LIMIT pre-filtering. Furthermore, for workflows cross-referencing massive, static datasets, BigQuery supports Gemini Context Caching, which can reduce input token costs and execution latency. Use the AI.COUNT TOKENS utility to estimate query costs prior to execution. Administrators can operate knowing AI.AGG works strictly within BigQuery's governance framework, supporting VPC Service Controls VPC-SC to prevent data exfiltration. Furthermore, if a transient error occurs during a batch process, the function degrades gracefully — skipping the rejected rows rather than crashing the entire query. Preview guardrails to keep in mind: The final output string per SQL group is strictly capped at 10,000 tokens per group. Ask the model for concise output Individual payload rows must not exceed 10 MiB. When processing multimodal arrays, limit image assets to fewer than 10 images per row to avoid silent skipping. For optimal stability, we recommend processing a maximum of 20 million rows and outputting a maximum of 1,000 distinct SQL groups per query. To help you get started, here are examples of how AI.AGG is used to synthesize insights alongside traditional SQL metrics. Summarize what specific top authors talk about, turning thousands of their comments into a concise narrative Instead of counting standard HTTP error codes, use AI.AGG to read thousands of raw stack traces and log messages, grouping them by the microservice name to find the qualitative root cause of systemic failures. With AI.AGG , Google Cloud is democratizing access to semantic analysis at petabyte scale. By marrying the deterministic grouping power of BigQuery’s relational engine with the macroscopic reasoning capabilities of Vertex AI Gemini, we are transforming the enterprise data warehouse from a passive repository of historical metrics into an active, semantic runtime capable of deep, holistic intelligence. Ready to unlock the semantic value of your enterprise data? Head over to the Google Cloud console, explore the BigQuery documentation, and run your first semantic aggregation today.