BigQuery Graph measures explained (Why your AI agents get metrics wrong and how measures fix it)

Summary

AI engineer Annie Wang demonstrates why AI agents and SQL queries frequently return incorrect numerical totals when querying data spread across multiple relational tables, and how BigQuery Graph measures resolve the issue. In a sample music dataset, a record label named Cymbal Records owns four songs with individual stream counts of 2 billion, 1.5 billion, 1.2 billion, and 400 million, amounting to an actual total of 5.1 billion streams. A separate table stores playlist information. When an AI agent is asked to calculate total streams per label and list their playlist counts, it writes a join query across the songs, labels, and playlists tables.

The resulting SQL join duplicates rows for songs assigned to multiple playlists. The track Neon Summer appears on four playlists, causing its 2 billion streams to be listed four times, while Paper Crown appears on two playlists and is listed twice. When the query executes a standard SUM on the joined stream column, Neon Summer contributes 8 billion streams and Paper Crown contributes 3 billion streams. Cymbal Records' stream count is returned as 12.6 billion instead of 5.1 billion, representing an error of nearly two and a half times the true figure. While this error is obvious on a five-row sample dataset, such inflation silently passes undetected in production tables containing millions of rows.

BigQuery Graph addresses this by embedding calculation rules directly into a property graph data model. A property graph defines how existing BigQuery tables connect as nodes and edges without duplicating or moving the underlying storage. Within the graph definition, developers add a measure to the songs node using the MEASURE keyword, defining the calculation as the sum of streams. Because the measure is bound to the song entity, BigQuery uses the unique song identifier to ensure each song's streams are counted only once within any aggregation group.

To query the measure, analysts write SQL using the AGG function over the defined measure name within a GRAPH_EXPAND clause, replacing the raw SUM function. AGG enforces the deduplication rules configured in the graph, returning the correct 5.1 billion streams for Cymbal Records. The same measure can be reused smoothly when changing query groupings, such as grouping streams by playlist rather than by label, without altering the aggregation logic.

In addition to fixing arithmetic aggregations, the graph retains the topological relationships across nodes. Tracing Cymbal Records through artists and songs to playlists reveals that 4.7 billion of its 5.1 billion streams, or 92 percent of its catalog volume, connect to the Hot Right Now playlist. This topological analysis demonstrates how cutting that single node removes three of eight catalog placements and leaves one song without any playlist coverage, uncovering structural risks invisible in traditional flat summaries. Property graphs and measures can be constructed manually in SQL, visually in the BigQuery console, or with the Data Agent Kit, and they publish to Knowledge Catalog where BigQuery conversational analytics agents can query them directly.