# Semantic aggregation over millions of rows in BigQuery with AI.AGG > Source: > Published: 2026-11-05 17:00:00+00:00 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.