Skip to the page
Tecyfy

Spark SQL Pipe Syntax (|>): A Practical Guide with Examples

Spark 4.0 added SQL pipe syntax: a query written as steps joined by |>, read top to bottom. Every step with a query we ran, the three mistakes that cause errors, and whether it changes performance.

Tecyfy · Published · Updated · 8 min read

SQL pipe syntax, added in Apache Spark 4.0, lets you write a query as a list of steps joined by |>, starting from a table and reading top to bottom. Each step takes the rows from the step above and does one thing to them: filter, add a column, aggregate, join, sort. Because steps can come in any order, any number of times, it needs no separate HAVING or QUALIFY, and many queries that would need a nested subquery become a list of steps.

Pipe syntax works alongside existing SQL: you can write any query in ordinary SQL, pipe syntax or a mix of the two. Every query below was run on Spark 4.2 (the syntax used is the one documented for Spark 4.0), with these two small tables:

CREATE OR REPLACE TEMP VIEW orders AS SELECT * FROM VALUES
  (1, 'ana',  'Electronics', 'Laptop', 1200, 1, DATE'2026-01-15'),
  (2, 'ben',  'Electronics', 'Phone',   800, 1, DATE'2026-01-16'),
  (3, 'ana',  'Clothing',    'Shirt',    50, 3, DATE'2026-01-17'),
  (4, 'chen', 'Clothing',    'Jeans',    80, 2, DATE'2026-02-02'),
  (5, 'ben',  'Books',       'Novel',    25, 4, DATE'2026-02-03'),
  (6, 'chen', 'Electronics', 'Headset', 150, 2, DATE'2026-02-05')
AS t(order_id, customer, category, product, price, quantity, order_date);

CREATE OR REPLACE TEMP VIEW customers AS SELECT * FROM VALUES
  ('ana', 'gold'), ('ben', 'standard'), ('chen', 'gold')
AS t(customer, tier);

Why write SQL this way

An ordinary SQL query is written in one order and runs in another. You write SELECT first, but Spark reads FROM first, then WHERE, then GROUP BY, then HAVING, and only then SELECT. When a query needs more steps than that fixed order allows, such as ranking after aggregating and then filtering on the rank, you wrap it in a subquery, and the logic ends up nested inside out.

Here is "the two categories with the most revenue this year" written both ways. Both return Electronics with 2300 and Clothing with 310.

-- Ordinary SQL: read from the inside out
SELECT category, revenue FROM (
  SELECT category, revenue, ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rk FROM (
    SELECT category, SUM(price * quantity) AS revenue
    FROM orders
    WHERE order_date >= DATE'2026-01-01'
    GROUP BY category
  ) AS by_category
) AS ranked
WHERE rk <= 2;

-- Pipe syntax: read from the top down
FROM orders
|> WHERE order_date >= DATE'2026-01-01'
|> AGGREGATE SUM(price * quantity) AS revenue GROUP BY category
|> EXTEND ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rk
|> WHERE rk <= 2
|> SELECT category, revenue;

The pipe version also has a practical advantage while you write it: you can stop after any line and run what you have, because every prefix of a pipe query is a valid query.

The steps you will use most

FROM, WHERE and SELECT

A pipe query can start with FROM table, which on its own returns the whole table. WHERE keeps the rows that match, and SELECT chooses the columns:

FROM orders
|> WHERE category = 'Electronics'
|> SELECT product, price;
-- Laptop 1200, Phone 800, Headset 150

You can also add pipe steps after an ordinary query. This is handy when you have an existing query and want one more step:

SELECT category, SUM(price * quantity) AS revenue FROM orders GROUP BY category
|> WHERE revenue > 300;

EXTEND, SET and DROP

EXTEND adds columns and keeps all the existing ones. A column it adds can be used from the next step on:

FROM orders
|> EXTEND price * quantity AS revenue, revenue > 500 AS big_order
|> SELECT product, revenue, big_order;
-- Laptop 1200 true, Phone 800 true, Shirt 150 false, ...

SET replaces the value of an existing column, and DROP removes columns. Neither needs you to list the columns you keep:

FROM orders
|> SET price = price * 0.9
|> DROP order_date, customer
|> LIMIT 2;

AGGREGATE

AGGREGATE does the work of GROUP BY and the aggregate part of SELECT in one step. Without GROUP BY it returns one row:

FROM orders
|> AGGREGATE SUM(price * quantity) AS revenue, COUNT(*) AS orders;
-- 2710, 6

With GROUP BY, the grouping columns come first in the result without being repeated, and a WHERE after it does what HAVING used to:

FROM orders
|> EXTEND price * quantity AS revenue
|> AGGREGATE SUM(revenue) AS revenue, COUNT(*) AS orders GROUP BY category
|> WHERE revenue > 300
|> ORDER BY revenue DESC;
-- Electronics 2300 3, Clothing 310 2

AS and JOIN

The rows coming down the pipe are the left side of a join. AS gives them a name so you can tell their columns apart from the other table's:

FROM orders
|> AS o
|> JOIN customers AS c ON o.customer = c.customer
|> WHERE c.tier = 'gold'
|> AGGREGATE SUM(o.price * o.quantity) AS gold_revenue GROUP BY c.customer
|> ORDER BY gold_revenue DESC;
-- ana 1350, chen 460

Window functions

A window function goes in SELECT, and a WHERE after it filters on the result, which is what QUALIFY does in other databases. The best-selling product in each category:

FROM orders
|> EXTEND price * quantity AS revenue
|> SELECT category, product, revenue,
     ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_in_category
|> WHERE rank_in_category = 1
|> ORDER BY revenue DESC;
-- Electronics Laptop 1200, Clothing Jeans 160, Books Novel 100

ORDER BY, LIMIT and OFFSET work as steps too, and the Spark documentation lists the rest, including UNION, PIVOT and TABLESAMPLE.

Three mistakes and the errors they give

Using a column before the step that makes it. Steps run in order, so a filter on revenue must come after the EXTEND that creates it:

FROM orders
|> WHERE revenue > 500
|> EXTEND price * quantity AS revenue;
-- [UNRESOLVED_COLUMN.WITH_SUGGESTION] ... `revenue` cannot be resolved

Repeating an aggregate after AGGREGATE. After an AGGREGATE step, the original columns are gone. |> WHERE SUM(quantity) > 2 fails because there is no quantity any more. Name the aggregate and filter on the name:

FROM orders
|> AGGREGATE SUM(quantity) AS units GROUP BY category
|> WHERE units > 3;
-- Electronics 4, Clothing 5, Books 4

Putting a plain column in AGGREGATE. Every expression in AGGREGATE must contain an aggregate function. A column you want to keep goes in GROUP BY; writing AGGREGATE SUM(price) AS total, product GROUP BY category returns an error.

Does it change performance?

No. Spark's optimizer works on the plan, not the text. Write steps in the order that reads best.

When to use it

Pipe syntax is worth it for long analytical queries with several stages, for queries you build up step by step while exploring data, and anywhere you would otherwise reach for a nested subquery or a common table expression just to filter on a computed value. For a short SELECT ... FROM ... WHERE, ordinary SQL is just as clear and more familiar to the next reader.

The two forms mix freely, so you can adopt it one query at a time. The idea is not unique to Spark: Google's BigQuery uses the same |> syntax.

Tecyfy Takeaway

The pipe operator lets a SQL query read in the order it runs: start from the table, then filter, add, aggregate, join and rank, one line each. It needs no separate HAVING or QUALIFY, and mixes with ordinary SQL. The rule to remember is that each step only sees what the step above produced.

Questions

What is the SQL pipe operator in Spark?
Pipe syntax, added in Apache Spark 4.0, writes a query as steps joined by |>, starting from a table and reading top to bottom. Each step takes the rows from the step above and filters, adds columns, aggregates, joins or sorts them.
Is pipe syntax faster than ordinary SQL in Spark?
No. The choice is about readability, not speed.
How do I replace HAVING and QUALIFY with pipe syntax?
Put a WHERE step after the AGGREGATE or after the step that computes the window function. Any step can follow any other, so filtering on an aggregate or a rank needs no subquery.
What does AGGREGATE do in Spark pipe syntax?
It does the work of GROUP BY and the aggregate part of SELECT in one step. Every expression in it must contain an aggregate function; columns to keep go in GROUP BY, and the original columns are gone in the steps after it.
Can I mix pipe syntax with ordinary SQL?
Yes. You can add pipe steps after an ordinary query, and every prefix of a pipe query is itself a valid query, so you can adopt it one query at a time. BigQuery uses the same |> syntax.

Sources

Where each fact comes from

  1. 1SQL Pipe Syntax
  2. 2Spark Release 4.0.0
  3. 3Pipe query syntax