BigQuery SQL Formatter

BigQuery queries get wide before they get deep. A fully-qualified table reference is `project.dataset.table` before you have selected anything, nested and repeated fields add dotted paths, and every UNNEST adds another entry to the FROM list. Two joins in and the query is a single line nobody can review.

With the BigQuery dialect selected, the formatter reads GoogleSQL properly: backticks around a whole table path rather than a single identifier, STRUCT and ARRAY constructors, UNNEST in the FROM clause, and aggregate functions carrying their own ORDER BY and LIMIT inside the call.

GoogleSQL constructs handled here

• Backtick-quoted table paths. `acme-prod.sales.orders` is one token, not three. The backticks are mandatory whenever the project id contains a hyphen — which it usually does, since Google project ids commonly do — and the formatter never splits the path on its dots.
• UNNEST. Flattening a repeated field is a join against UNNEST(o.line_items), and it is printed in the FROM list with the other sources so the fan-out is visible. This is the single most useful thing formatting does to a BigQuery query: an accidental cross join with an unnested array is obvious on separate lines and invisible on one.
• STRUCT and ARRAY. Constructors and their field aliases are preserved, and dotted access into a struct column such as o.shipping.country is kept intact.
• Aggregates with internal clauses. ARRAY_AGG(x ORDER BY y LIMIT 5) has an ORDER BY and a LIMIT inside the function call. Those get indented within the parentheses rather than promoted to top-level clauses, which is what makes the difference between the two readable.
• Pseudo-columns. _PARTITIONTIME and _PARTITIONDATE are treated as ordinary column references.

One layout quirk to expect: UNNEST(items) WITH OFFSET AS pos splits the WITH OFFSET across lines, because WITH and OFFSET are both clause keywords elsewhere. The query is unchanged, only its layout.

Standard SQL, not legacy SQL

BigQuery has two dialects of its own. Legacy SQL — the original BigQuery dialect — quoted tables as [project:dataset.table], used FLATTEN for repeated fields, and treated a comma in the FROM clause as UNION ALL. GoogleSQL, the standard-compliant dialect that has been the default for years, uses backticks, UNNEST, and treats that same comma as a CROSS JOIN.

That comma is not a cosmetic difference; the same query text means two different things in the two dialects. This formatter targets GoogleSQL. If you have legacy SQL with bracketed table references, convert it before formatting rather than after.

Why layout matters when you pay per byte scanned

BigQuery on-demand pricing bills the bytes a query reads, so the two most consequential lines in most queries are the column list and the partition filter. Both are the first things a clause-per-line layout makes checkable: a SELECT * against a wide table is obvious, and a missing or non-constant filter on the partitioning column stands out in a stack of AND predicates.

Be clear about the mechanism, though. Formatting does not reduce cost. Whitespace is stripped by the parser and the bytes billed are identical either way. What formatting buys you is a query whose cost you can review before you run it — and a diff in code review where somebody else can too. Use the console dry-run estimate for the actual number.