Pipe syntax

BigQuery's pipe syntax writes a query as a top-to-bottom chain of steps joined by |>, in the order they run. Querylab.io converts standard SQL to pipe and back with one shortcut, and runs any prefix of the chain to show its rows.

FROM `ace-analytics.warehouse.orders`
|> WHERE order_date >= '2024-01-01' AND status = 'completed'
|> AGGREGATE COUNT(*) AS orders, SUM(amount) AS revenue
 GROUP BY user_id
|> ORDER BY revenue DESC
|> LIMIT 20

The same query in standard SQL puts the SELECT first and the FROM after it, which is the reverse of what BigQuery does with it. Both forms run identically and cost the same.

SELECT user_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM `ace-analytics.warehouse.orders`
WHERE order_date >= '2024-01-01' AND status = 'completed'
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 20

Converting between standard and pipe syntax

Standard to pipe and pipe to standard are both one action. They work on the whole document or, if you have a selection, on the selection alone.

The same two commands are in the editor’s right-click menu, and the ⋮ menu at the top right of the editor shows the one that applies to the query in the tab:

Convert to Pipe is a faithful translation: every CTE and subquery stays where it was. Convert to Pipe (Optimized) rewrites the query the way you’d write it by hand: subqueries flattened, single-use CTEs inlined into the chain, and SELECT steps that only re-list existing columns dropped.

WITH completed AS (
SELECT user_id, amount
FROM `ace-analytics.warehouse.orders`
WHERE status = 'completed'
)
SELECT user_id, SUM(amount) AS revenue
FROM completed
GROUP BY user_id
HAVING SUM(amount) > 1000

FROM `ace-analytics.warehouse.orders`
|> WHERE status = 'completed'
|> AGGREGATE SUM(amount) AS revenue
 GROUP BY user_id
|> WHERE revenue > 1000

HAVING has no pipe operator: it becomes a WHERE after AGGREGATE, which can use the aggregate’s alias.

• The statement has a FROM. SELECT 1 + 1 has nothing to pipe from and is left unchanged.

• For a large script, select one statement and convert just that.

The same converter runs without an account at querylab.io/tools/pipe-to-sql.

Running part of the chain

Every |> gets an icon in the gutter. Hover it for the SQL up to that step; click to dry-run that prefix, Shift-click to run it and see its rows in the result grid. That is the fastest way to find which step lost the rows you expected. See partial execution.

FROM `ace-analytics.warehouse.orders`
|> WHERE status = 'completed'
|> AGGREGATE COUNT(*) AS orders GROUP BY user_id
|> ORDER BY orders DESC

Every operator and its standard equivalent

All BigQuery pipe operators are supported. Where standard SQL has an equivalent it’s in the first column.

Column operators without a standard form

EXTEND, SET, DROP and RENAME change the column list without restating it. Converting to standard SQL expands them into a SELECT with the full list.

FROM `ace-analytics.warehouse.users`
|> EXTEND UPPER(country) AS country_code
|> SET email = LOWER(email)
|> DROP password_hash, salt
|> RENAME created_at AS signup_at

For the operators themselves, see BigQuery’s pipe syntax reference.

Mixing pipe and standard syntax

Pipe operators can follow a standard query, and a pipe query can be a CTE or a subquery in a standard one. The converter handles both directions of that, and the editor works the same inside either part.

WITH cleaned AS (
FROM `ace-analytics.warehouse.events`
|> WHERE event_date >= '2024-01-01'
|> SELECT user_id, event_type
)
SELECT user_id, COUNT(*) AS events
FROM cleaned
GROUP BY user_id
|> WHERE events > 10

Formatting a pipe query

The formatter keeps the chain one step per line. Settings → Editor → SQL Formatting → Pipe Operator Style chooses between Standard (pipes indented with the content) and Left-Aligned (|> in column 0, keywords indented). See SQL formatting.