So for me, the example query in pipe syntax (up to the enrich:
FROM orders
|> WHERE order_date >= '2024-01-01'
|> AGGREGATE SUM(order_amount) AS total_spent GROUP BY customer_id
|> WHERE total_spent > 1000
|> INNER JOIN customers USING(customer_id)
|> CALL ENRICH.APOLLO(EMAIL > customers.email)
|> AGGREGATE COUNT(*) high_value_customer GROUP BY company.country
is easier to parse (for me) as something like:
WITH
customer_order_totals AS (
SELECT customer_id, SUM(order_amount) AS total_spent
FROM orders
GROUP BY 1
)
,enriched_customers AS (
SELECT cus.customer_id, ENRICH.APOLLO(EMAIL > cus.email) enrichment
FROM customer cus
)
SELECT ec.country, COUNT(*) high_value_customer
FROM customer_order_totals cot
JOIN enriched_customers ec
ON ec.customer_id = cot.customer_id
WHERE cot.total_spent > 1000
GROUP BY ec.country
largely because I can understand the components in isolation first, before bringing together. yes, it's more verbose, but I am of the opinion that the brevity comes at the expense of having to keep more information in my working memory.
That’s what I mean by CTE being variable-naming. There’s nothing stopping you from using CTE’s and you’d probably make the same decision under this syntax, using CTE’s to give “names” to the partial solution
And like function-call chaining, you would presumably keep it longer where the items being manipulated are “obvious” — such that giving names is more noise than it’s worth — and CTE/variables where it becomes complicated.
That is, there’s not really a conflict of features, pipe syntax just lets you keep extending the SELECT. How much you stuff into a single unbroken pipe chain is a matter of taste, just as much as stuffing a normal SELECT with a series of subselects. You break it down the same way — with CTEs.
It only competes with CTEs in that you’re more likely to find scenarios where you could reasonably get away without it, and still be sufficiently clear and obvious, if not more so
Pipe syntax just lets you go further without having to breakdown simply due to the amount of syntax noise
WITH
customer_order_totals AS (
FROM orders
AGGREGATE customer_id, SUM(order_amount) as total_spent
GROUP BY 1
)
,enriched_customers AS (
FROM customer cus
SELECT cus.customer_id, ENRICH.APOLLO(EMAIL > cus.email) enrichment
)
FROM customer_order_totals cot
JOIN enriched_customers ec
ON ec.customer_id = cot.customer_id
WHERE cot.total_spent > 1000
AGGREGATE ec.country, COUNT(*) high_value_customer
GROUP BY etc.country
