BigQuery SQL Formatter

BigQuery reads strings and names differently

GoogleSQL breaks several habits carried over from other databases, and the formatter has to follow its rules before it can lay anything out:

• Backticks quote identifiers. shop-prod.sales.orders is one name, even with the hyphen in the project ID. Unquoted hyphenated paths such as my-project.dataset.table are also accepted.
• Double quotes make a string, not a column. "EU" is the same value as 'EU'. A table written as "proj.ds.orders" will format without complaint, but BigQuery will reject it, because that is text.
• Backslash escapes the quote. Write 'it\'s'. There is no doubled-quote escape: 'it''s' is read as two separate literals, and the output shows them as 'it' 's'.
• Prefixed and triple-quoted literals. r'\d+' (raw), b'...' (bytes), rb'...' and """multi-line""" strings are kept byte for byte.
• Three comment styles. #, -- and /* */ are all comments, so a # never gets mistaken for an operator.
• Parameters. Named query parameters (@start_date), positional ? and system variables like @@project_id stay as single tokens.

GoogleSQL constructs the layout understands

QUALIFY gets its own clause line, so a “latest row per customer” filter reads like WHERE and HAVING do. Window specs inside OVER (...) are broken onto separate PARTITION BY and ORDER BY lines.

Type syntax is recognised: STRUCT<name STRING, value FLOAT64> and ARRAY<INT64>[1, 2, 3] keep their angle brackets, and the type names follow your keyword-case setting. SELECT AS STRUCT, * EXCEPT (...) REPLACE (...), UNNEST, PIVOT, MERGE and ML.PREDICT(MODEL ..., TABLE ...) all format without errors. In DDL, the column list is indented and PARTITION BY and CLUSTER BY start new lines.

A wildcard table such as analytics-123.events.events_* stays intact inside its backticks, and _TABLE_SUFFIX filters are treated as an ordinary column.

Scripts, UDFs and the rough edges

Multi-statement scripts with DECLARE, BEGIN ... END, IF ... END IF and FOR ... DO loops format statement by statement. Expect a flat result: the statements inside a block are not indented under BEGIN, and END FOR comes out split across two lines. The script still runs, but you may want to re-indent block bodies by hand.

The SQL text passed to EXECUTE IMMEDIATE is a string, so it is left untouched. Calls to user-defined functions pick up a space before the parenthesis (net (revenue)), which BigQuery accepts.

The formatter checks lexical structure (strings, comments, brackets) and a few grammar slips, such as a CASE with no END. It does not know your schema, so an unknown column or a wrong type still has to be caught by a dry run in BigQuery.

Settings for long analytics queries

BigQuery code tends to be long CTE chains feeding window functions and nested UNNEST subqueries. A few settings help:

1. Keyword case: UPPER separates SELECT and OVER from your snake_case columns. As written leaves an existing house style alone.
2. Commas: Leading puts each comma at the start of the line, which makes commenting out a column a one-line change.
3. AND / OR: Start of line lines up long WHERE filters on _TABLE_SUFFIX, event names and dates.
4. A wider line width keeps short STRUCT(...) and IF(...) calls on one line instead of exploding them.

Minify (Ctrl+Shift+M) collapses whitespace and drops # and -- comments but keeps /*+ ... */ blocks and every string and backticked name as is. More conventions are collected in the SQL style guide.

Examples

select customer_id, order_id, total_usd, struct(ship_city as city, ship_country as country) as shipping from `shop-prod.sales.orders` # paid orders only
where status = 'paid' and created_at >= @start_ts qualify row_number() over (partition by customer_id order by created_at desc) = 1

SELECT
  customer_id,
  order_id,
  total_usd,
  STRUCT(ship_city AS city, ship_country AS country) AS shipping
FROM
  `shop-prod.sales.orders` # paid orders only
WHERE
  status = 'paid'
  AND created_at >= @start_ts
QUALIFY
  row_number() OVER (
    PARTITION BY
      customer_id
    ORDER BY
      created_at DESC
  ) = 1

select user_pseudo_id, (select value.string_value from unnest(event_params) where key = 'page_location') as page, count(*) as views from `analytics-123.analytics_987.events_*` where _table_suffix between '20260101' and '20260131' and event_name = 'page_view' group by 1, 2 order by views desc

SELECT
  user_pseudo_id
, (
    SELECT
      value.string_value
    FROM
      UNNEST (event_params)
    WHERE
      key = 'page_location'
  ) AS page
, count(*) AS views
FROM
  `analytics-123.analytics_987.events_*`
WHERE
  _table_suffix BETWEEN '20260101' AND '20260131'
  AND event_name = 'page_view'
GROUP BY
  1
, 2
ORDER BY
  views DESC

declare start_date date default date '2026-01-01';
create temp table recent_orders as select order_id, customer_id, total_usd from `shop-prod.sales.orders` where order_date >= start_date;
select customer_id, sum(total_usd) as revenue from recent_orders group by customer_id order by revenue desc limit 50;

DECLARE start_date date DEFAULT date '2026-01-01';

CREATE TEMP TABLE recent_orders AS
SELECT
  order_id,
  customer_id,
  total_usd
FROM
  `shop-prod.sales.orders`
WHERE
  order_date >= start_date;

SELECT
  customer_id,
  sum(total_usd) AS revenue
FROM
  recent_orders
GROUP BY
  customer_id
ORDER BY
  revenue DESC
LIMIT
  50;

create or replace table `shop-prod.web.product_views` (view_id string, product struct<sku string, price numeric>, tags array<string>, viewed_at timestamp) partition by date(viewed_at) cluster by view_id;
select regexp_extract(page_url, r'/product/(\d+)') as product_id, count(distinct session_id) as sessions from `shop-prod.web.pageviews` where page_url like '%/product/%' group by product_id

CREATE OR REPLACE TABLE `shop-prod.web.product_views` (
  view_id string,
  product STRUCT<sku STRING, price NUMERIC>,
  tags ARRAY<STRING>,
  viewed_at timestamp
)
PARTITION BY
  DATE(viewed_at)
CLUSTER BY
  view_id;

SELECT
  REGEXP_EXTRACT(page_url, r'/product/(\d+)') AS product_id,
  COUNT(DISTINCT session_id) AS sessions
FROM
  `shop-prod.web.pageviews`
WHERE
  page_url LIKE '%/product/%'
GROUP BY
  product_id

Common errors and how to fix them

Related tools

• SQL formatter
• sql formatter/snowflake
• sql formatter/redshift
• sql formatter/spark
• sql validator
• sql minifier
• sql style guide
• sql unterminated quoted string