BigQuery AI.AGG() Deep Dive: Summarize Unstructured Data at Scale with SQL

The article introduces BigQuery‘s AI.AGG() function, now in preview, which lets you summarize or synthesize unstructured or multimodal data across millions of rows using natural-language instructions in a single SQL line. The core tension is that existing AI functions in BigQuery work well on individual rows, but analyzing massive volumes of logs, product descriptions, or images requires a fundamentally different approach—manual investigation doesn’t scale, and standard SQL can’t extract thematic insights like “top feature requests from negative reviews” from free text. AI.AGG() directly addresses this gap by embedding LLM-powered aggregation into the data warehouse query path.

Technically, AI.AGG() works by automatically splitting input rows into batches, aggregating each batch with an LLM, then aggregating those intermediate results into a final answer—managing context windows transparently. The post demonstrates concrete patterns: analyzing Apache Spark logs for hidden anomalies (with prompts that explicitly allow the model to say “everything is fine”), extracting product categories from text and images, and chaining AI.AGG() with AI.CLASSIFY() to produce structured JSON labels for downstream SQL. Best practices include pre-filtering rows to reduce token usage, using IFNULL() to avoid silent NULL drops from STRUCT concatenation, and specifying model endpoints explicitly for production pipelines.

The practical takeaway is that AI.AGG() transforms BigQuery from a row-level analysis engine into one that can answer nuanced, aggregate questions over unstructured data without leaving SQL. For builders, the key insight is designing prompts that yield parseable output formats (JSON, Markdown) and chaining outputs into traditional aggregations or other AI functions. The function is non-deterministic, so production pipelines should accommodate slight variations in results. This is a meaningful shift for anyone operating at scale on logs, support tickets, or multimodal catalogs—reducing what was a labor-intensive investigation workflow to a single query.