For more than a decade, BigQuery has been the standard for structured data aggregation — financial sums, active-user counts, historical averages at petabyte scale. The gap is unstructured data: application logs, customer-service transcripts, product imagery, and diagnostic reports. Relational SQL engines have lacked native ability to aggregate, synthesize, and extract semantic themes from millions of rows of text and multimodal data. Row-level functions like AI.GENERATE and AI.CLASSIFY exist, but holistic analysis collides with the LLM context window; you cannot pass millions of rows into a single prompt. Previously this required external map-reduce pipelines with rate-limiting backoffs and recursive chunked summaries.

AI.AGG, currently in preview, is a fully managed, multi-level semantic aggregation function natively embedded in BigQuery GoogleSQL. It treats semantic aggregation as a distributed map-reduce problem, allowing natural-language instructions over millions of rows in one SQL query. Under the hood the engine orchestrates four stages: row-level validation (no single row exceeds the maximum context window, with payloads over 10 MiB dropped or errored), context-safe batching (dynamic grouping that fits the chosen Gemini endpoint’s token limits), parallel inference (the prompt is applied to each batch in parallel, producing intermediate semantic summaries via Vertex AI), and recursive synthesis (intermediate outputs are re-batched and passed back to the LLM with the same instruction until a single final aggregated string is produced per SQL GROUP BY clause). Unlike row-level functions such as AI.CLASSIFY, which can leverage distilled CPU proxy models, AI.AGG uses full-weight LLMs at every stage because it requires deep reasoning across batches.

AI.AGG supports text and multimodal inputs: using BigQuery Object Tables and OBJ.GET_ACCESS_URL, teams can aggregate visual insights across thousands of images in Cloud Storage without data movement or transcoding. One data-engineering trap: when passing a STRUCT of multiple columns, if any single field evaluates to NULL, the entire row is treated as NULL and silently discarded. Google advises explicitly wrapping nullable fields with IFNULL() (for example, IFNULL(description, ‘No data provided’)).

Two enterprise use cases stand out. For high-volume system logs from globally distributed microservices, DevOps teams can group millions of logs by component and generate targeted diagnostic summaries that expose subtle degradations like memory thrashing or clock drift that never throw fatal errors. The prompting tip: instruct the model to describe the component’s “normal operation” before hunting anomalies, which suppresses hallucinated errors on healthy logs. The second is an “agentic data cloud”: AI.AGG can read a large repository of legacy support tickets, synthesize a definitive list of categories, and output them as a strict JSON array. Unpack that array with JSON_VALUE_ARRAY, feed the labels into row-level classifiers like AI.CLASSIFY, and you have an end-to-end autonomous labeling pipeline natively in SQL.

Google frames prompt engineering for deterministic SQL pipelines as a strict engineering discipline: treat the prompt as an API contract (for example, “Return exactly one valid JSON array of strings. Do not include markdown code blocks or conversational text”), use contextual delimiters like ‘- Description: ‘ to prevent field conflation, instruct the model to return strict nulls for missing information rather than guess, and provide few-shot examples of the expected structure. Operationally, recursive batching means total tokens sent to Gemini exceed the source-data token count, so strict WHERE and LIMIT pre-filtering is recommended. Gemini Context Caching can cut input-token costs and latency for massive static datasets, and AI.COUNT_TOKENS estimates cost before execution. AI.AGG operates inside BigQuery‘s governance framework with VPC Service Controls (VPC-SC) to prevent data exfiltration, and transient batch errors degrade gracefully by skipping rejected rows instead of crashing the query.

Preview guardrails: final output is capped at 10,000 tokens per SQL group, individual payload rows must stay under 10 MiB, multimodal arrays should have fewer than 10 images per row to avoid silent skipping, and Google recommends processing at most 20 million rows and outputting at most 1,000 distinct SQL groups per query. Examples include macro-level topic discovery (summarizing what specific top authors discuss across thousands of comments) and autonomous DevOps diagnostic reporting that reads raw stack traces and log messages grouped by microservice to find qualitative root causes. The broader claim: AI.AGG marries BigQuery‘s deterministic grouping with Vertex AI Gemini reasoning, turning the enterprise data warehouse from a passive repository of historical metrics into an active semantic runtime.