Text-to-SQL over BigQuery
Explore how to build accurate text-to-SQL queries over BigQuery by understanding data grain and join effects like fanout. Learn to validate SQL with dry-run cost controls, enforce security boundaries, and ensure precise revenue calculations. This lesson helps you trace aggregation errors, apply business rules correctly, and write testable regressions to defend query correctness in real scenarios.
We'll cover the following...
The analytics agent reports $300 of East paid-order revenue for September, although the two qualifying order headers each contain $100. Money is stored in integer cents: 10000 cents = $100. The SQL ran successfully within the scan budget. Trace the source rows to find what those checks missed.
Tracing the reported revenue
The independent example uses two tables:
orders:order_id, region, business_date, status, revenue_cents.order_lines:line_id, order_id, product_code.
Here are all six order headers, with money stored as integer cents:
O0 East 2026-08-31 paid 6000O1 East 2026-09-01 paid 10000O2 East 2026-09-30 paid 10000O3 West 2026-09-15 paid 8000O4 East 2026-09-20 cancelled 5000O5 East 2026-10-01 paid 7000
O1 has lines L11 and L12, both product P. O2 has L21, product Q. Every other order has one product-Q line. Consequently, this dataset does not yet test orders without lines.
The generated query is:
SELECT o.region, SUM(o.revenue_cents) AS revenue_centsFROM orders oJOIN order_lines l ON o.order_id = l.order_idWHERE o.region = 'East' AND o.status = 'paid'AND o.business_date >= '2026-09-01'AND o.business_date < '2026-10-01'GROUP BY o.region;
Line 1: Select the region and total the header amounts, naming the output
revenue_cents.Lines 2–3: Match each order with every line bearing its order ID.
Lines 4–6: Retain East paid orders within the September interval.
Line 7: Produce one aggregate group per region.
Before tracing it, predict which source fact explains the extra $100.
Inspect the intermediate rows to check which facts enter the total.
Reading the joined rows
An alias is a short name: o means orders, and l means order_lines. A qualified column, such as o.order_id, identifies which table supplies the value.
A table’s grain states what one row represents. The order table has one row per order; the line table has one row per order line. Read this query conceptually in the following order; the database optimizer can choose a different physical execution plan.
FROMandJOIN: Combine matching rows. O1 matches two lines, so its header amount appears twice.WHERE: Filter that combined collection by region, status, and date.GROUP BY: Put the surviving rows into region groups.SUM: Add the amounts within each group.SELECT: Emit the requested output columns.
The surviving joined rows are:
order_id line_id header revenue_centsO1 L11 10000O1 L12 10000O2 L21 10000
The sum is 10000 + 10000 + 10000 = 30000 cents. This repetition of a parent fact across child rows is called join fanout.
Here, COUNT(*) counts three joined rows, while COUNT(DISTINCT o.order_id) counts two orders. An inner join also removes an order with no matching line; the supplied dataset cannot expose that omission ...
