BigQuery queries are shaped by two things other dialects do not have: table names that are three dotted parts inside a single pair of backticks, and columns that are arrays of structs rather than scalars. Both change what a formatter has to get right — the dots inside `project.dataset.table` are not operators, and an UNNEST in the FROM clause is a join even though it reads like a function call.
GoogleSQL has also added clauses that remove whole layers of nesting. QUALIFY filters on a window function without the wrapping subquery the standard would require, and SELECT * EXCEPT drops columns without listing the ones you keep. The query below, loaded into the editor above, uses both.
Before and after
This is the query loaded in the editor above. On the left is what you paste; on the right is what the formatter returns with the default options for this dialect.
select * except(payload), e.user_id, safe_cast(json_value(e.payload, '$.amount') as numeric) as amount, h.name as hit_name from `analytics-prod.events.sessions_*` e, unnest(e.hits) as h where _table_suffix between '20260101' and '20260131' and e.geo.country = 'BR' qualify row_number() over (partition by e.user_id order by e.event_timestamp desc) = 1;SELECT
* EXCEPT (payload),
e.user_id,
SAFE_CAST(JSON_VALUE(e.payload, '$.amount') AS NUMERIC) AS amount,
h.name AS hit_name
FROM
`analytics-prod.events.sessions_*` e,
UNNEST (e.hits) AS h
WHERE
_table_suffix BETWEEN '20260101' AND '20260131'
AND e.geo.country = 'BR'
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY
e.user_id
ORDER BY
e.event_timestamp DESC
) = 1;What is specific to BigQuery
Backtick-quoted qualified names
A BigQuery table reference is `project.dataset.table`, with the dots inside the quoting rather than between separately quoted parts. Project ids routinely contain hyphens — analytics-prod in the example — which is exactly why the backticks are mandatory: without them the hyphen would parse as subtraction.
The whole reference is treated as a single identifier token, so it is never split at a dot or at a hyphen.
UNNEST is a join, not a function call
When a column is an ARRAY, UNNEST in the FROM clause flattens it into rows, correlated to the row it came from. The comma before it is a CROSS JOIN, which is why the example reads FROM table e, UNNEST(e.hits) AS h — one row per hit, carrying its session.
It is placed on its own line in the FROM list, at the same level as the table it expands, which is the honest representation of what it is.
SELECT * EXCEPT and * REPLACE
Wide event tables make listing every column impractical, so GoogleSQL lets you subtract instead: * EXCEPT (payload) selects everything but that column, and * REPLACE (expr AS col) substitutes one column's value while keeping the rest. Both are parsed as modifiers of the star rather than as function calls.
QUALIFY
Filtering on a window function normally requires computing it in a subquery and filtering outside, because WHERE runs before window functions. QUALIFY does it in one level — the example keeps the most recent event per user without a wrapping SELECT.
It is a top-level clause and is placed alongside WHERE and GROUP BY. QUALIFY requires a WHERE, GROUP BY or HAVING in the same query block, or a WINDOW clause.
Wildcard tables and _TABLE_SUFFIX
A trailing * in a table name matches every table sharing that prefix, and the pseudo-column _TABLE_SUFFIX holds the part that matched — the standard way to scan a date-sharded export. Filtering on _TABLE_SUFFIX is what keeps the query from reading every shard, so it belongs in the WHERE clause rather than in a later filter.
SAFE_CAST and the SAFE. function prefix return NULL instead of raising on bad input, which matters when the data is user-supplied JSON. Both are recognised as ordinary function syntax.
Known limitations
No formatter handles every corner of a dialect. These are the cases where this one produces output you may want to correct by hand.
- The pseudo-columns _table_suffix, _partitiontime and _partitiondate are left in the case you typed them, because they are parsed as identifiers rather than keywords. BigQuery accepts any case for them.
- Scripting statements — DECLARE, SET, BEGIN ... END, EXECUTE IMMEDIATE — format much less cleanly than queries, in common with procedural code in every dialect.
Conventions worth adopting
Formatting does not change what a query costs
BigQuery bills on bytes scanned, which depends on the columns you reference and the partitions you touch — not on whitespace. Formatting a query never changes its cost. SELECT * does, which is the real argument for EXCEPT over the star when the table is wide.
GoogleSQL only
Legacy SQL, the pre-2016 dialect with [project:dataset.table] bracket syntax, is a different grammar and is not supported here. If your query uses colons and square brackets in table names, it is Legacy SQL and needs migrating rather than formatting.
BigQuery formatting FAQ
Does formatting affect how much my query costs?
No. Cost is driven by the bytes scanned — the columns referenced and the partitions read. Whitespace and keyword casing have no effect on either.
Does it support Legacy SQL?
No, only GoogleSQL (formerly Standard SQL). Legacy SQL uses a different table reference syntax and a different grammar.
Are STRUCT and ARRAY expressions handled?
Yes. Nested STRUCT constructors and ARRAY_AGG calls are parsed as ordinary expressions, and UNNEST is recognised as part of the FROM clause.
Is my query sent to Google or to any server?
No. This page runs the formatter in your browser. The query is not sent anywhere, including to BigQuery.
Other SQL dialects
Not sure which one you need, or working with more than one? The general SQL formatter lets you switch dialects without leaving the page.