Semantic aggregation over millions of rows in BigQuery with AI.AGG
Google Cloud introduces AI.AGG in BigQuery preview, enabling native semantic aggregation of millions of unstructured rows via a single SQL query, eliminating external pipelines and handling multimodal data directly in-database.
For over ten years, BigQuery has processed structured data at petabyte scale, but unstructured data like logs and images required complex external pipelines to extract themes. AI.AGG, now in preview, embeds a fully managed, multi-level semantic aggregation function directly into BigQuery GoogleSQL, allowing data teams to run natural-language instructions over millions of rows using a single SQL query. The function abstracts the engineering complexity of chunking, parallel execution, and recursive synthesis, enabling native processing of unstructured data without external orchestration.
AI.AGG operates by validating row sizes, dynamically batching rows to fit token limits, and applying the SQL prompt in parallel across batches using Vertex AI. It recursively synthesizes intermediate outputs until a single aggregated string is produced per GROUP BY clause, relying on full-weight LLMs at every stage. The function supports both text and multimodal inputs, leveraging BigQuery Object Tables and OBJ.GET_ACCESS_URL to aggregate visual insights from Cloud Storage without data movement or transcoding.
When handling nullable fields in structured inputs, AI.AGG discards entire rows if any field is NULL, requiring explicit use of IFNULL() to ensure data integrity. The function’s analytical power is amplified when combined with standard SQL GROUP BY operations, enabling DevOps teams to analyze millions of system logs and generate human-readable diagnostic summaries. Prompting techniques, such as instructing the model to describe normal operation before identifying anomalies, help reduce hallucinations in healthy log data.
AI.AGG also functions as a dynamic orchestration engine, enabling autonomous data labeling pipelines. For example, it can synthesize definitive categories from unstructured support tickets and output them as a strict JSON array, which can then be fed into row-level classifiers like AI.CLASSIFY. To ensure programmatic outputs, prompts must adhere to strict engineering disciplines, including explicit output contracts, contextual delimiters, failure state design, and few-shot anchoring to ground the model’s attention.