BQBrains | BigQuery Pipe Syntax: Cleaner Analytics SQL

BigQuery's pipe syntax, generally available since 2025, adds a |> operator that lets you write a query as a linear sequence of steps — FROM, then filter, then aggregate, then sort — in the order the engine conceptually executes them, instead of the inside-out nesting standard SQL forces on you. It is not a new language: every pipe query compiles to the same plan as its classic equivalent, costs the same, and can be mixed freely with ordinary SQL. What changes is readability and the cost of maintenance. This tutorial rewrites three queries of the kind we see in client codebases every week and then draws the line on when to keep classic SQL.

The basics in thirty seconds

A pipe query starts with a FROM clause and then applies operators, each beginning with |>:

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> WHERE start_time >= '2024-01-01'
|> AGGREGATE COUNT(*) AS trips, AVG(duration_minutes) AS avg_minutes
   GROUP BY subscriber_type
|> ORDER BY trips DESC
|> LIMIT 5;

The main operators are WHERE, SELECT, EXTEND (add columns without listing existing ones), SET (replace a column), DROP, RENAME, AGGREGATE ... GROUP BY, JOIN, ORDER BY, LIMIT, UNION/INTERSECT/EXCEPT, PIVOT/UNPIVOT, TABLESAMPLE, and AS (alias the intermediate result). Each operator sees the columns produced by the previous one, so there is no HAVING — you just WHERE after AGGREGATE.

Rewrite 1: nested aggregation with a filter on the aggregate

The classic version needs a subquery or a HAVING, and the column the reader cares about is buried at the bottom:

SELECT subscriber_type, trips
FROM (
  SELECT subscriber_type, COUNT(*) AS trips
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  WHERE start_time >= '2024-01-01'
  GROUP BY subscriber_type
)
WHERE trips > 10000
ORDER BY trips DESC;

Pipe version:

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> WHERE start_time >= '2024-01-01'
|> AGGREGATE COUNT(*) AS trips GROUP BY subscriber_type
|> WHERE trips > 10000
|> ORDER BY trips DESC;

Same plan, same bytes. The filter-after-aggregate reads as exactly what it is.

Rewrite 2: a three-level CTE chain

A typical "daily active users with a 7-day rolling average" query:

WITH daily AS (
  SELECT DATE(event_ts) AS d, COUNT(DISTINCT user_id) AS dau
  FROM `analytics.events`
  WHERE event_ts >= TIMESTAMP('2026-07-01')
  GROUP BY d
),
rolling AS (
  SELECT d, dau,
    AVG(dau) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d
  FROM daily
),
flagged AS (
  SELECT *, dau < 0.8 * dau_7d AS is_dip
  FROM rolling
)
SELECT d, dau, ROUND(dau_7d) AS dau_7d, is_dip
FROM flagged
WHERE is_dip
ORDER BY d;

Pipe version:

FROM `analytics.events`
|> WHERE event_ts >= TIMESTAMP('2026-07-01')
|> AGGREGATE COUNT(DISTINCT user_id) AS dau GROUP BY DATE(event_ts) AS d
|> EXTEND AVG(dau) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d
|> EXTEND dau < 0.8 * dau_7d AS is_dip
|> WHERE is_dip
|> SELECT d, dau, ROUND(dau_7d) AS dau_7d, is_dip
|> ORDER BY d;

EXTEND is the operator that earns its keep here: each derived column is one line, and nobody has to invent a CTE name for a single expression. Window functions work inside EXTEND exactly as they do in SELECT.

Rewrite 3: the exploratory query that grows by accretion

The queries that hurt most are the ones analysts build incrementally — add a join, add a filter, pivot, add another filter — until the nesting is four deep. Pipe syntax turns that into appending lines:

FROM `sales.orders` AS o
|> JOIN `sales.customers` AS c USING (customer_id)
|> WHERE o.order_date BETWEEN '2026-01-01' AND '2026-06-30'
|> WHERE c.segment IN ('enterprise', 'mid-market')
|> AGGREGATE SUM(o.net_amount) AS revenue
   GROUP BY c.segment, FORMAT_DATE('%Y-%m', o.order_date) AS month
|> PIVOT (SUM(revenue) FOR segment IN ('enterprise', 'mid-market'))
|> ORDER BY month;

Two WHERE steps in a row are fine; the optimiser merges them. Readers can comment out any single line to see the intermediate result — something a nested subquery never allows.

Readability and maintenance wins

Execution order is reading order. New team members stop asking "which part runs first?"
Incremental debugging. Delete everything after a |> and run it; you are looking at that step's output.
Smaller diffs. Adding a filter is one added line, not a re-indented subquery.
Fewer throwaway names. EXTEND removes most single-purpose CTEs.

When to keep classic SQL

Shared views and dbt/Dataform models consumed by other tools. Anything that parses SQL — linters, lineage tools, some BI semantic layers — may not understand pipe syntax yet. Check your toolchain before converting shared models.
Correlated subqueries and EXISTS. Pipe syntax can embed classic subqueries in expressions, but the clearest form of a correlated predicate is still the classic one.
Teams that move between engines. Pipe syntax is BigQuery (and ZetaSQL) specific. If the same query must run on Snowflake, Postgres, or Spark SQL, classic SQL travels.
Tiny queries. A two-line SELECT ... FROM ... WHERE gains nothing.

Mixing the two

You can start with a classic SELECT and pipe from it, or wrap a pipe query as a subquery:

SELECT month, enterprise / NULLIF(mid_market, 0) AS ratio
FROM (
  FROM `sales.monthly_by_segment`
  |> WHERE month >= '2026-01'
  |> PIVOT (SUM(revenue) FOR segment IN ('enterprise', 'mid-market'))
);

Pipe syntax is supported in BigQuery Studio, scheduled queries, the bq CLI, client libraries, and Dataform — the feature is a property of the SQL dialect, not the client. Our recommendation: adopt it first for ad-hoc and exploratory work, then for new pipeline models once your lineage and linting tools confirm support, and leave stable shared views alone until you have a reason to touch them. If you would like help setting SQL standards for a growing team, contact us.