WITH expressions in BigQuery
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
So I recently discovered the WITH expression in BigQuery SQL.
Not to be confused with the WITH clause, which we use to define common table expressions (CTEs).
👉 What does a WITH expression do?
It lets you define a series of variables, scoped to a single expression. Each variable can reference previously defined ones (and table columns), and in the end, the whole expression returns a result.
📌 Where could this be useful?
Think back to the time before QUALIFY was supported. We often had to create an extra CTE just to filter with WHERE rn = 1 or for similar windowed calculations. When QUALIFY came, it saved us a bunch of boilerplate CTEs.
Well, WITH expressions have the potential to help in the same way — but for non-window calculations.
When working with complex formulas, you can’t reference (within the same SELECT) a column you just defined. The usual workaround is to push it into another CTE — which works, but feels verbose. I still opted to do it since it's important that the code stayed readable and maintainable.
Now WITH expressions give us a cleaner option and help avoid those 7-operand expressions. I, for one, plan on trying them out ASAP.
Has anyone here used them already? Any thoughts? Docs here.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.
