# Pipe syntax in BigQuery

> BigQuery pipe syntax writes a query as a chain of |> steps in execution order, starting with FROM. WHERE replaces HAVING and QUALIFY, AGGREGATE holds the GROUP BY, and every prefix of the query is itself a valid query.

Source: https://docs.querylab.io/bigquery/pipe-syntax/

Updated: 2026-10-14

Pipe syntax lets you write a BigQuery query as a sequence of steps, each starting with `|>`. The query starts with `FROM`, and every step takes the table produced by the step before it. You read it top to bottom in the order it runs, instead of `SELECT` first and `FROM` second. It is part of GoogleSQL, so it works in views and table functions as well as plain queries, and you can mix it with standard syntax.

## The same query, both ways

We want customers with more than 10 in paid orders, largest first. In pipe syntax:

**filter, aggregate, filter, sort**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> WHERE status = 'paid'
|> AGGREGATE SUM(amount) AS total GROUP BY customer
|> WHERE total > 10
|> ORDER BY total DESC
```

Result in BigQuery (2 rows):

| customer | total |
| --- | --- |
| c3 | 20 |
| c1 | 15 |

And in standard syntax:

**the same query with SELECT, WHERE, GROUP BY, HAVING**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 10
ORDER BY total DESC
```

Result in BigQuery (2 rows):

| customer | total |
| --- | --- |
| c3 | 20 |
| c1 | 15 |

The results are the same. The pipe version runs in the order you read it: filter rows, aggregate, filter the totals, sort. Standard syntax has the filter on totals in a different clause (`HAVING`) than the filter on rows (`WHERE`), and the `SELECT` list at the top refers to things that are only defined further down. Querylab.io converts a query from one form to the other with a keyboard shortcut ([pipe syntax](https://docs.querylab.io/writing-sql/pipe-syntax/#converting-standard-and-pipe-syntax)).

The cost is the same. A dry run of a pipe query and its standard equivalent over a public table report the same bytes:

**pipe query on samples.shakespeare**

```sql
FROM `bigquery-public-data.samples.shakespeare`
|> WHERE corpus = 'hamlet'
|> AGGREGATE SUM(word_count) AS words
```

BigQuery dry run: this query processes 3.6 MB.

**the standard equivalent**

```sql
SELECT SUM(word_count) AS words
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = 'hamlet'
```

BigQuery dry run: this query processes 3.6 MB.

## Why people use it

Every prefix of a pipe query, up to any `|>`, is a valid query. When a long query gives a wrong answer, delete steps from the end and see what each stage produced. In standard syntax you have to restructure the query with subqueries to look inside. In Querylab.io every `|>` line has a gutter icon: click it to dry-run the query up to that step, or Shift-click to run it and see its rows ([partial execution](https://docs.querylab.io/writing-sql/partial-execution/)).

A query can be just `FROM`, which is `SELECT *`:

**FROM with a LIMIT**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> LIMIT 2
```

Result in BigQuery (2 rows):

| customer | amount | status |
| --- | --- | --- |
| c1 | 10 | paid |
| c1 | 5 | paid |

## The operators

The main operators are `SELECT`, `EXTEND`, `SET`, `DROP`, `RENAME`, `AS`, `WHERE`, `AGGREGATE`, `DISTINCT`, `JOIN`, `CALL`, `ORDER BY`, `LIMIT`, `UNION`, `INTERSECT`, `EXCEPT`, `TABLESAMPLE`, `WITH`, `PIVOT`, `UNPIVOT` and `MATCH_RECOGNIZE`. Most match the standard clause of the same name. The ones with no direct counterpart are the interesting ones.

### WHERE does the job of HAVING and QUALIFY

There is one filter operator, and it can go anywhere. After `AGGREGATE` it filters on totals, like `HAVING`. It also accepts window functions, so the usual [QUALIFY](https://docs.querylab.io/bigquery/qualify/) pattern needs no extra clause:

**largest order per customer with a window function in WHERE**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> WHERE ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) = 1
|> ORDER BY customer
```

Result in BigQuery (3 rows):

| customer | amount | status |
| --- | --- | --- |
| c1 | 10 | paid |
| c2 | 7 | refunded |
| c3 | 20 | paid |

An aggregate function in `WHERE` is an error. Aggregation belongs to `AGGREGATE`.

### EXTEND adds columns and keeps the rest

`SELECT` lists columns and drops everything else. `EXTEND` keeps all columns and adds new ones. `SET` replaces a column's value, `DROP` removes columns and `RENAME` renames them:

**SET, RENAME and DROP**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> SET amount = amount * 100
|> RENAME amount AS cents
|> DROP status
|> ORDER BY cents
```

Result in BigQuery (4 rows):

| customer | cents |
| --- | --- |
| c1 | 500 |
| c2 | 700 |
| c1 | 1000 |
| c3 | 2000 |

New columns from one `EXTEND` can't refer to each other. The second expression here fails:

**using doubled in the same EXTEND**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> EXTEND amount * 2 AS doubled, doubled + 1 AS plus_one
```

BigQuery rejects this query:

```text
Unrecognized name: doubled at [8:34]
```

Use a second `EXTEND`, which sees the first one's result:

**two EXTEND steps**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> EXTEND amount * 2 AS doubled
|> EXTEND doubled + 1 AS plus_one
|> SELECT customer, amount, doubled, plus_one
|> ORDER BY amount
```

Result in BigQuery (4 rows):

| customer | amount | doubled | plus_one |
| --- | --- | --- | --- |
| c1 | 5 | 10 | 11 |
| c2 | 7 | 14 | 15 |
| c1 | 10 | 20 | 21 |
| c3 | 20 | 40 | 41 |

### AGGREGATE holds the GROUP BY

`AGGREGATE` takes the aggregate expressions and, optionally, a `GROUP BY`. The grouping columns appear in the output first, then the aggregates, and you don't repeat them in a select list. A `GROUP BY` item can have an alias:

**GROUP BY with an alias**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> AGGREGATE SUM(amount) AS total GROUP BY UPPER(status) AS state
|> ORDER BY state
```

Result in BigQuery (2 rows):

| state | total |
| --- | --- |
| PAID | 35 |
| REFUNDED | 7 |

`GROUP AND ORDER BY` groups and sorts by the grouping columns in one go, and each item can take `ASC` or `DESC`:

**GROUP AND ORDER BY status DESC**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> AGGREGATE SUM(amount) AS total, COUNT(*) AS orders
   GROUP AND ORDER BY status DESC
```

Result in BigQuery (2 rows):

| status | total | orders |
| --- | --- | --- |
| refunded | 7 | 1 |
| paid | 35 | 3 |

There is no standalone `GROUP BY` step. A `|> GROUP BY` fails with a message that tells you where it goes:

**a GROUP BY step**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> AGGREGATE SUM(amount) AS total
|> GROUP BY customer
```

BigQuery rejects this query:

```text
Syntax error: GROUP BY should be part of a pipe AGGREGATE operator, without a leading pipe symbol at [9:4]
```

`SELECT` can't aggregate either:

**SUM in a SELECT step**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> SELECT customer, SUM(amount) AS total
```

BigQuery rejects this query:

```text
Aggregate function SUM not allowed in pipe SELECT at [8:21]
```

### Each step sees only the step before it

After `|> SELECT customer`, the `amount` column no longer exists. In standard syntax `WHERE` runs before `SELECT`, so it can use any column. In a pipe each step starts from the previous result:

**filter on a column dropped by SELECT**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> SELECT customer
|> WHERE amount > 5
```

BigQuery rejects this query:

```text
Unrecognized name: amount at [9:10]
```

So order the steps with that in mind: filter first, then select. Use `EXTEND` instead of `SELECT` when you want to keep the other columns.

### JOIN and AS

`JOIN` takes the pipe's table as its left side and the table in the step as its right side. Use `AS` before the pipe or in the `FROM` to name the left side so the `ON` clause can refer to it:

**join, then aggregate by a joined column**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders AS o
|> JOIN (SELECT 'c1' AS customer, 'Ana' AS name UNION ALL SELECT 'c3', 'Cy') AS c
   ON o.customer = c.customer
|> AGGREGATE SUM(o.amount) AS total GROUP BY c.name
|> ORDER BY name
```

Result in BigQuery (2 rows):

| name | total |
| --- | --- |
| Ana | 15 |
| Cy | 20 |

### Set operators and PIVOT

`UNION`, `INTERSECT` and `EXCEPT` take the pipe's table as the left side and the queries in parentheses as the right. See [set operations](https://docs.querylab.io/bigquery/set-operations/) for the `ALL` / `DISTINCT` rules, which are the same:

**UNION ALL as a pipe step**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> WHERE status = 'refunded'
|> SELECT customer, amount
|> UNION ALL (SELECT 'c9', 99)
|> ORDER BY customer
```

Result in BigQuery (2 rows):

| customer | amount |
| --- | --- |
| c2 | 7 |
| c9 | 99 |

`PIVOT` and `UNPIVOT` are steps as well. Columns that aren't pivoted become the grouping, as in [standard PIVOT](https://docs.querylab.io/bigquery/pivot-and-unpivot/):

**PIVOT as a step**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> PIVOT(SUM(amount) FOR status IN ('paid', 'refunded'))
|> ORDER BY customer
```

Result in BigQuery (3 rows):

| customer | paid | refunded |
| --- | --- | --- |
| c1 | 15 | NULL |
| c2 | NULL | 7 |
| c3 | 20 | NULL |

### TABLESAMPLE

`TABLESAMPLE` exists as a step, but BigQuery only samples base tables. A step on a CTE or inline data fails the same way as in standard syntax:

**TABLESAMPLE on a CTE**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> TABLESAMPLE SYSTEM (50 PERCENT)
```

BigQuery rejects this query:

```text
TABLESAMPLE SYSTEM can only be applied directly to base tables.
```

See [table sampling](https://docs.querylab.io/bigquery/table-sampling/) for what can be sampled.

## Mixing with standard syntax

A pipe query works anywhere a query does, and standard queries can contain pipe queries. A subquery in the `FROM` of a standard query can be a pipe chain:

**a pipe subquery inside a standard query**

```sql
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
SELECT customer, total
FROM (
  FROM orders
  |> AGGREGATE SUM(amount) AS total GROUP BY customer
)
WHERE total > 10
ORDER BY customer
```

Result in BigQuery (2 rows):

| customer | total |
| --- | --- |
| c1 | 15 |
| c3 | 20 |

## Limits

Google's guide lists one: you can't put a differential privacy clause in a `SELECT` after a pipe operator. Write the clause in standard syntax and add the pipe steps after the query.

A pipe query can stand wherever a query does. We ran `CREATE TABLE ... AS FROM ... |> SELECT ...`, `INSERT INTO ... FROM ... |> SELECT ...` and a pipe query inside parentheses as a subquery, and all three worked.

Read the same query written as a chain of pipe steps. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Pipe query syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax)

- [Work with pipe syntax](https://cloud.google.com/bigquery/docs/pipe-syntax-guide)