
TL;DR: Measures in BigQuery Graph are now Generally Available (GA). By embedding grain-locked aggregations, derived MEASURE() ratios, and rich metadata directly into your Property Graph, you can query multi-fact models with GRAPH_EXPAND and AGG() without join fan-out double counting. Even better, your semantic definitions automatically sync with Knowledge Catalog so SQL queries, BI dashboards, and BigQuery Data Agents all evaluate against the exact same governed metrics.
If you have ever built executive dashboards or connected an AI agent to a real-world warehouse, you have almost certainly been burned by join fan-out. In a normalized schema, joining a parent dimension table to a child fact table repeats parent attributes on every matching row. When someone runs a simple SUM() on a budget or target stored at the parent grain, the numbers silently multiply by the number of child rows.
The problem gets dramatically worse when you enter the classic “chasm trap” by joining two independent fact tables (such as purchases and refunds) through a shared dimension table (such as marketing campaigns). Suddenly, a single SELECT query creates a Cartesian fan-out across both fact tables, inflating ad spend, gross revenue, and refund totals all at once. Historically, analytics teams avoided this by maintaining separate semantic layers in BI tools, engineering pre-aggregated flat tables, or hoping analysts and LLMs remembered to write multi-CTE SQL workarounds every single time.
With Measures in BigQuery Graph, you no longer have to maintain separate worlds for governed BI metrics and graph relationships. You can lock business metrics directly to the primary key of any node or edge table inside a BigQuery Property Graph, compose reusable derived measures, inspect schemas at zero scan cost, and expose a single governed source of truth to SQL, BI dashboards, and BigQuery Data Agents.
In this hands-on walkthrough, we will build a multi-fact e-commerce marketing graph, reproduce both the fan-out trap and the chasm trap, and solve them cleanly with MEASURE(), GRAPH_EXPAND, and BigQuery Data Agents.
The Scenario: E-Commerce Marketing ROAS & True Net Profit
To see why grain-locking matters in production, let us model a realistic startup commerce topology with two shared dimension tables and two independent fact tables:
- Brands (brands): Top-level product brands (Apex Gear and Lumina Home).
- Campaigns (campaigns): Paid marketing campaigns linked to each brand. Crucially, each campaign owns a fixed ad_budget metric at the campaign grain ($50,000, $30,000, and $40,000, totaling $80,000 for Apex Gear and $40,000 for Lumina Home).
- Purchases (purchases): Fact table #1 recording completed customer orders (revenue, cost, and is_new_customer) attributed to a campaign.
- Refunds (refunds): Fact table #2 recording customer refunds (refund_amount and reason) attributed to a campaign.
Notice the trap hidden inside this perfectly normal schema. Apex Gear has $80,000 in total ad budget across two campaigns, $160,000 in gross revenue across five purchases, and $12,000 in refunds across three refund events. Any analyst or AI agent asked to calculate Return on Ad Spend (ROAS) or net profit per brand needs metrics from all three grains at once without inflating a single dollar.
Step 1: The Relational Foundation
Let us start by creating the four base tables with explicit PRIMARY KEY and FOREIGN KEY constraints (NOT ENFORCED) and loading clean sample rows. Replace your_project.your_dataset with your actual BigQuery project and dataset names.
-- 1. Dimension Table: Brands
CREATE OR REPLACE TABLE `your_project.your_dataset.brands` (
brand_id STRING PRIMARY KEY NOT ENFORCED,
brand_name STRING,
category STRING
);
-- 2. Dimension Table: Campaigns (owns the grain-locked ad_budget metric)
CREATE OR REPLACE TABLE `your_project.your_dataset.campaigns` (
campaign_id STRING PRIMARY KEY NOT ENFORCED,
campaign_name STRING,
brand_id STRING,
channel STRING,
ad_budget FLOAT64,
FOREIGN KEY (brand_id) REFERENCES `your_project.your_dataset.brands`(brand_id) NOT ENFORCED
);
-- 3. Fact Table 1: Purchases
CREATE OR REPLACE TABLE `your_project.your_dataset.purchases` (
purchase_id STRING PRIMARY KEY NOT ENFORCED,
campaign_id STRING,
purchase_date DATE,
revenue FLOAT64,
cost FLOAT64,
is_new_customer BOOL,
FOREIGN KEY (campaign_id) REFERENCES `your_project.your_dataset.campaigns`(campaign_id) NOT ENFORCED
);
-- 4. Fact Table 2: Refunds
CREATE OR REPLACE TABLE `your_project.your_dataset.refunds` (
refund_id STRING PRIMARY KEY NOT ENFORCED,
campaign_id STRING,
refund_date DATE,
refund_amount FLOAT64,
reason STRING,
FOREIGN KEY (campaign_id) REFERENCES `your_project.your_dataset.campaigns`(campaign_id) NOT ENFORCED
);
-- Populate Seed Data
INSERT INTO `your_project.your_dataset.brands` (brand_id, brand_name, category)
VALUES
('B1', 'Apex Gear', 'Outdoor'),
('B2', 'Lumina Home', 'Smart Home');
INSERT INTO `your_project.your_dataset.campaigns` (campaign_id, campaign_name, brand_id, channel, ad_budget)
VALUES
('C1', 'Summer Trail Launch', 'B1', 'Search', 50000.0),
('C2', 'Fall Hiking Push', 'B1', 'Social', 30000.0),
('C3', 'Smart Lighting Promo', 'B2', 'Video', 40000.0);
INSERT INTO `your_project.your_dataset.purchases` (purchase_id, campaign_id, purchase_date, revenue, cost, is_new_customer)
VALUES
('P1', 'C1', DATE '2026-10-02', 45000.0, 18000.0, TRUE),
('P2', 'C1', DATE '2026-10-08', 35000.0, 14000.0, FALSE),
('P3', 'C1', DATE '2026-10-15', 20000.0, 8000.0, TRUE),
('P4', 'C2', DATE '2026-10-18', 40000.0, 18000.0, TRUE),
('P5', 'C2', DATE '2026-10-24', 20000.0, 9000.0, FALSE),
('P6', 'C3', DATE '2026-10-20', 80000.0, 32000.0, TRUE);
INSERT INTO `your_project.your_dataset.refunds` (refund_id, campaign_id, refund_date, refund_amount, reason)
VALUES
('R1', 'C1', DATE '2026-10-12', 5000.0, 'Sizing'),
('R2', 'C1', DATE '2026-10-20', 3000.0, 'Damaged'),
('R3', 'C2', DATE '2026-10-26', 4000.0, 'Late delivery'),
('R4', 'C3', DATE '2026-10-28', 6000.0, 'Defective');
Before we introduce BigQuery Graph Measures, let us see what happens if a standard SQL query joins all four tables together to build a brand summary. The syntax looks completely innocent, and BigQuery executes it without throwing a single warning.
-- The Classic Multi-Fact Chasm Trap in Standard SQL
SELECT
b.brand_name AS brand,
SUM(c.ad_budget) AS exploded_ad_budget,
SUM(p.revenue) AS exploded_revenue,
SUM(r.refund_amount) AS exploded_refunds
FROM `your_project.your_dataset.brands` AS b
JOIN `your_project.your_dataset.campaigns` AS c ON b.brand_id = c.brand_id
JOIN `your_project.your_dataset.purchases` AS p ON c.campaign_id = p.campaign_id
JOIN `your_project.your_dataset.refunds` AS r ON c.campaign_id = r.campaign_id
GROUP BY b.brand_name
ORDER BY b.brand_name;
Look at the distorted numbers returned for Apex Gear:
+-------------+--------------------+------------------+------------------+
| brand | exploded_ad_budget | exploded_revenue | exploded_refunds |
+-------------+--------------------+------------------+------------------+
| Apex Gear | 360000.0 | 260000.0 | 32000.0 |
| Lumina Home | 40000.0 | 80000.0 | 6000.0 |
+-------------+--------------------+------------------+------------------+
Every single metric for Apex Gear is wrong. Its true $80,000 ad budget exploded to $360,000 (4.5x overcounted), its $160,000 revenue inflated to $260,000, and its $12,000 in refunds nearly tripled to $32,000. That is the silent danger of multi-fact joins in analytics.
Step 2: Defining the Semantic Graph with Measures and Derived Metrics
Now let us build CommerceGraph using CREATE OR REPLACE PROPERTY GRAPH. Notice several capabilities and modeling best practices working together in this single DDL statement:
- Grain-Locked MEASURE() Definitions: total_ad_budget is locked to Campaign's KEY (campaign_id), while revenue, cost, order counts, and refund totals are locked to Purchase and Refund keys. Any base column referenced inside a MEASURE() must also appear as a dimension property in the same PROPERTIES(…) list.
- Derived Measures with AGG(): gross_profit and gross_margin_pct compose existing measures on the same node using AGG() and SAFE_DIVIDE(). BigQuery validates dependencies at graph compilation time and blocks circular references automatically.
- Conditional Distinct Counts: new_customer_orders demonstrates conditional counting with COUNT(DISTINCT CASE WHEN … THEN purchase_id ELSE NULL END) inside a measure.
- Rich Metadata OPTIONS(): Attaching description and key-value extensions (such as unit and tier) documents your metrics directly inside BigQuery and automatically catalogs them in Knowledge Catalog for downstream discovery and AI agents.
- Inlined Edge Tables (NO PROPERTIES): Reusing the child tables (campaigns, purchases, refunds) as edges with NO PROPERTIES satisfies BigQuery's edge-inlining best practice, avoiding unnecessary self-joins during GRAPH_EXPAND.
CREATE OR REPLACE PROPERTY GRAPH `your_project.your_dataset.CommerceGraph`
NODE TABLES (
`your_project.your_dataset.brands` AS Brand
KEY (brand_id)
DEFAULT LABEL OPTIONS(description = "Product brands sold across marketing campaigns")
PROPERTIES (
brand_id,
brand_name OPTIONS(description = "Official brand display name"),
category
),
`your_project.your_dataset.campaigns` AS Campaign
KEY (campaign_id)
DEFAULT LABEL OPTIONS(description = "Paid marketing campaigns linked to a brand")
PROPERTIES (
campaign_id,
campaign_name,
brand_id,
channel,
ad_budget OPTIONS(description = "Total campaign ad spend in USD"),
MEASURE(SUM(ad_budget)) AS total_ad_budget OPTIONS(
description = "Grain-locked campaign ad budget in USD (counted once per campaign)",
extensions = [("unit", "USD"), ("tier", "marketing")]
)
),
`your_project.your_dataset.purchases` AS Purchase
KEY (purchase_id)
DEFAULT LABEL OPTIONS(description = "Completed customer purchase transactions attributed to a campaign")
PROPERTIES (
purchase_id,
campaign_id,
purchase_date,
revenue,
cost,
is_new_customer,
MEASURE(SUM(revenue)) AS total_revenue OPTIONS(
description = "Total gross order revenue in USD",
extensions = [("unit", "USD"), ("tier", "finance")]
),
MEASURE(SUM(cost)) AS total_cost OPTIONS(
description = "Total cost of goods sold in USD",
extensions = [("unit", "USD"), ("tier", "finance")]
),
MEASURE(COUNT(purchase_id)) AS order_count OPTIONS(
description = "Total count of completed orders"
),
MEASURE(COUNT(DISTINCT CASE WHEN is_new_customer THEN purchase_id ELSE NULL END)) AS new_customer_orders OPTIONS(
description = "Count of orders placed by first-time buyers"
),
MEASURE(AGG(total_revenue) - AGG(total_cost)) AS gross_profit OPTIONS(
description = "Derived gross profit (total revenue minus total cost) in USD",
extensions = [("unit", "USD"), ("tier", "finance")]
),
MEASURE(SAFE_DIVIDE(AGG(total_revenue) - AGG(total_cost), NULLIF(AGG(total_revenue), 0))) AS gross_margin_pct OPTIONS(
description = "Derived gross margin ratio (gross profit divided by total revenue)",
extensions = [("unit", "ratio"), ("tier", "finance")]
)
),
`your_project.your_dataset.refunds` AS Refund
KEY (refund_id)
DEFAULT LABEL OPTIONS(description = "Customer refunds attributed to a campaign")
PROPERTIES (
refund_id,
campaign_id,
refund_date,
refund_amount,
reason,
MEASURE(SUM(refund_amount)) AS total_refunds OPTIONS(
description = "Total refunded amount in USD",
extensions = [("unit", "USD"), ("tier", "finance")]
),
MEASURE(COUNT(refund_id)) AS refund_count OPTIONS(
description = "Total count of refund events"
)
)
)
EDGE TABLES (
`your_project.your_dataset.campaigns` AS BrandCampaign
SOURCE KEY (brand_id) REFERENCES Brand (brand_id)
DESTINATION KEY (campaign_id) REFERENCES Campaign (campaign_id)
NO PROPERTIES,
`your_project.your_dataset.purchases` AS CampaignPurchase
SOURCE KEY (campaign_id) REFERENCES Campaign (campaign_id)
DESTINATION KEY (purchase_id) REFERENCES Purchase (purchase_id)
NO PROPERTIES,
`your_project.your_dataset.refunds` AS CampaignRefund
SOURCE KEY (campaign_id) REFERENCES Campaign (campaign_id)
DESTINATION KEY (refund_id) REFERENCES Refund (refund_id)
NO PROPERTIES
);
Adding measures does not stop you from running standard GQL traversals on the same graph, as long as your GQL query only references regular properties (measures are queried via GRAPH_EXPAND). Running a quick MATCH query and opening the Graph tab in BigQuery Studio lets you visualize the actual data connections and see the fan-out directly, where each pink Campaign node branches into multiple teal Purchases and orange Refunds.
GRAPH `your_project.your_dataset.CommerceGraph`
MATCH p = (b:Brand)-[:BrandCampaign]->(c:Campaign)-[:CampaignPurchase|CampaignRefund]->(f:Purchase|Refund)
RETURN TO_JSON(p) AS commerce_path
LIMIT 50;

Step 3: Querying Measures with GRAPH_EXPAND and Multi-Fact Joins
Because measures are grain-locked to node keys, you query them through the GRAPH_EXPAND table-valued function (TVF) and wrap measure columns in AGG(). Since CommerceGraph has two root fact tables (Purchase and Refund), we pass the explicit start_node argument to select which subgraph to expand.
First, let us compare naive SQL SUM(Campaign_ad_budget) directly alongside grain-locked AGG(Campaign_total_ad_budget) and our derived measures (gross_profit and gross_margin_pct) in one query:
-- Side-by-side: Naive SQL Fan-Out vs. Grain-Locked Measures
SELECT
Brand_brand_name AS brand,
SUM(Campaign_ad_budget) AS naive_sql_ad_budget,
AGG(Campaign_total_ad_budget) AS true_ad_budget,
AGG(Purchase_total_revenue) AS total_revenue,
AGG(Purchase_gross_profit) AS gross_profit,
ROUND(AGG(Purchase_gross_margin_pct) * 100, 1) AS gross_margin_pct,
AGG(Purchase_order_count) AS total_orders,
AGG(Purchase_new_customer_orders) AS new_customer_orders,
ROUND(SAFE_DIVIDE(AGG(Purchase_total_revenue), SUM(Campaign_ad_budget)), 2) AS naive_roas,
ROUND(SAFE_DIVIDE(AGG(Purchase_total_revenue), AGG(Campaign_total_ad_budget)), 2) AS true_roas
FROM GRAPH_EXPAND(
'your_project.your_dataset.CommerceGraph',
start_node => 'Purchase'
)
GROUP BY Brand_brand_name
ORDER BY total_revenue DESC;
Look at what comes back in BigQuery Studio:
+-------------+---------------------+----------------+---------------+--------------+------------------+--------------+---------------------+------------+-----------+
| brand | naive_sql_ad_budget | true_ad_budget | total_revenue | gross_profit | gross_margin_pct | total_orders | new_customer_orders | naive_roas | true_roas |
+-------------+---------------------+----------------+---------------+--------------+------------------+--------------+---------------------+------------+-----------+
| Apex Gear | 210000.0 | 80000.0 | 160000.0 | 93000.0 | 58.1 | 5 | 3 | 0.76 | 2.0 |
| Lumina Home | 40000.0 | 40000.0 | 80000.0 | 48000.0 | 60.0 | 1 | 1 | 2.0 | 2.0 |
+-------------+---------------------+----------------+---------------+--------------+------------------+--------------+---------------------+------------+-----------+
Notice how stark that difference is. Naive SQL SUM() turns a healthy 2.0x ROAS brand into a false 0.76x money-losing crisis ($210,000 ad spend instead of $80,000). Wrapping the measure in AGG() counts each campaign's budget exactly once per campaign_id, while our derived measures compute exact gross profit ($93,000) and gross margin (58.1%) directly from the graph definition.

Now let us solve the multi-fact chasm trap we triggered in Step 1. To combine metrics from both Purchase and Refund across shared campaigns, we expand each fact subgraph with GRAPH_EXPAND and join them on Campaign_campaign_id in standard SQL:
-- Multi-Fact GRAPH_EXPAND: Purchases + Refunds + Ad Budget with Zero Double Counting
SELECT
P.Brand_brand_name AS brand,
AGG(P.Campaign_total_ad_budget) AS ad_budget,
AGG(P.Purchase_total_revenue) AS gross_revenue,
AGG(R.Refund_total_refunds) AS total_refunds,
AGG(P.Purchase_total_revenue) - AGG(R.Refund_total_refunds) AS net_revenue,
AGG(P.Purchase_gross_profit) AS gross_profit,
ROUND(AGG(P.Purchase_gross_margin_pct) * 100, 1) AS gross_margin_pct,
ROUND(
SAFE_DIVIDE(
AGG(P.Purchase_total_revenue) - AGG(R.Refund_total_refunds),
AGG(P.Campaign_total_ad_budget)
),
2
) AS net_roas
FROM GRAPH_EXPAND(
'your_project.your_dataset.CommerceGraph',
start_node => 'Purchase'
) AS P
INNER JOIN GRAPH_EXPAND(
'your_project.your_dataset.CommerceGraph',
start_node => 'Refund'
) AS R
ON P.Campaign_campaign_id = R.Campaign_campaign_id
GROUP BY P.Brand_brand_name
ORDER BY net_revenue DESC;
Every single number locks to its true grain, even across the multi-fact join:
+-------------+-----------+---------------+---------------+-------------+--------------+------------------+----------+
| brand | ad_budget | gross_revenue | total_refunds | net_revenue | gross_profit | gross_margin_pct | net_roas |
+-------------+-----------+---------------+---------------+-------------+--------------+------------------+----------+
| Apex Gear | 80000.0 | 160000.0 | 12000.0 | 148000.0 | 93000.0 | 58.1 | 1.85 |
| Lumina Home | 40000.0 | 80000.0 | 6000.0 | 74000.0 | 48000.0 | 60.0 | 1.85 |
+-------------+-----------+---------------+---------------+-------------+--------------+------------------+----------+

Pro Tips for Production:
- Hierarchical Struct Output: Prefer dot-notation over prefixed column names? Pass flatten_results => FALSE to GRAPH_EXPAND, and reference nested parent properties cleanly as Campaign.Brand.brand_name and AGG(Campaign.total_ad_budget).
- Disable Query Cache While Iterating: Updating an underlying graph definition does not invalidate BigQuery’s cached results for previous GRAPH_EXPAND calls, so uncheck Use cached results (or pass –nouse_cache via CLI) while iterating on your graph schema.
Step 4: Powering BigQuery Data Agents with Governed Measures
Defining measures once in your property graph pays an immediate dividend in BigQuery Conversational Analytics (Data Agents). Because CommerceGraph exposes both entity relationships and governed MEASURE() definitions with descriptions, you can connect it directly as your agent's knowledge source:
- In Google Cloud Console, open BigQuery and navigate to Agents Hub (or open Conversational Analytics inside BigQuery Studio).
- Click Create new agent, give it a clear display name (for example, Commerce Graph Measures Agent), and select CommerceGraph under Property Graph as the data source.
- Save and publish the agent, then open a chat session to test natural language financial questions.

Let us ask the agent a question that routinely trips up ungrounded text-to-SQL systems:
Prompt: “What is the total ad budget, gross revenue, gross profit, and gross margin percentage by brand? Do not double-count ad budget.”
Instead of guessing raw table joins and running a risky SUM(ad_budget), the Data Agent reads the graph's semantic schema and automatically generates this exact query:
SELECT Brand_brand_id,
Brand_brand_name,
AGG(Campaign_total_ad_budget) AS total_ad_budget,
AGG(Purchase_total_revenue) AS gross_revenue,
AGG(Purchase_gross_profit) AS gross_profit,
AGG(Purchase_gross_margin_pct) * 100 AS gross_margin_percentage
FROM GRAPH_EXPAND("your_project.your_dataset.CommerceGraph", start_node => "Purchase")
GROUP BY Brand_brand_id,
Brand_brand_name
ORDER BY gross_revenue DESC;
It returns the exact governed answer in just over two seconds:
- Apex Gear (B1): $80,000 ad budget, $160,000 gross revenue, $93,000 gross profit, and 58.13% gross margin.
- Lumina Home (B2): $40,000 ad budget, $80,000 gross revenue, $48,000 gross profit, and 60.00% gross margin.
Now let us push the agent even further with a multi-fact prompt spanning purchases, refunds, and campaign budgets all at once:
Prompt: “What is the gross revenue, total refunds, net revenue after refunds, ad budget, and net ROAS for each brand?”
Instead of falling into the chasm trap, the Data Agent expands each fact subgraph independently (start_node => "Purchase", start_node => "Refund", and start_node => "Campaign"), aggregates each governed measure via AGG(), and combines the three results cleanly:
- Apex Gear: $160,000 gross revenue, $12,000 refunds, $148,000 net revenue, $80,000 ad budget, and 1.85x net ROAS.
- Lumina Home: $80,000 gross revenue, $6,000 refunds, $74,000 net revenue, $40,000 ad budget, and 1.85x net ROAS.
Instead of guessing joins or reinventing metric formulas on every prompt, the agent grounds its SQL directly in your graph’s governed measures and metadata.
The Verdict
Bringing BI metrics and knowledge graphs together inside BigQuery eliminates an entire class of silent data bugs for both humans and AI agents:
- Deterministic, Grain-Locked Metrics: By tying MEASURE() definitions directly to node keys, join fan-out and multi-fact chasm traps disappear. Analysts and BI tools always get the exact same number regardless of how they slice or join the model.
- Dramatically Higher AI Agent Accuracy: Large Language Models should not have to reinvent business formulas like gross margin or remember anti-fan-out CTE tricks on every prompt. Pre-defined measures, derived ratios, and OPTIONS descriptions give Data Agents a deterministic math engine right inside the warehouse.
- One Governed Semantic Layer Everywhere: Because your graph metadata and measures catalog automatically into Knowledge Catalog and expose a standard SQL surface via GRAPH_EXPAND, the exact same definitions power ad-hoc SQL, BI dashboards, and conversational AI agents with zero duplicated logic.
Ready to stop debugging double-counted KPIs? Copy the SQL above into BigQuery Studio, build your first semantic graph with MEASURE(), and let your Data Agents answer complex business questions with complete mathematical confidence.
No More Double Counting: Trusted Metrics for SQL and AI with BigQuery Graph Measures was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/no-more-double-counting-trusted-metrics-for-sql-and-ai-with-bigquery-graph-measures-5106d6868195?source=rss—-e52cf94d98af—4
