TL;DR: nested WITH statements are supported in all modern data warehouses, lakehouses, and database engines except for Fabric & T-SQL products. This makes the usage of SQL templating engines unnecessarily complex.
Fabric supports regular WITH statements like the following:
with customers as ( select * from lakehouseone.dbo.customers ), orders as ( select * from lakehousetwo.dbo.orders ) select * from orders o join customers c on o.customer_id = c.id
However, the following nested version is currently not supported:
with customers_with_addresses as ( customers as ( select * from lakehouseone.dbo.customers ), addresses as ( select * from lakehousetwo.dbo.addresses ), final as ( select c.id, c.name, a.line_1, a.zip, a.country from customers c join addresses a on c.address_id = a.id ) select * from final ), orders as ( select * from lakehousetwo.dbo.orders ) select * from orders o join customers_with_addresses c on o.customer_id = c.id
You can see how working with CTEs allows for a very clean approach to SQL and enables the use of modular blocks of SQL.
This is especially popular in SQL templating engines like dbt.
Nested WITH statements allow for easy query injection. Wrapping a SELECT statement and inserting it into a query as a CTE only works if that inserted statement doesn't contain any CTEs.
More users have complained about the lack of nested WITH statements here and here.
3 Comments
Thanks for your feedback. We are working on adding the support of nested CTE to Fabric Warehousing.
Frameworks like DBT using jinja templating used nested WITH but Fabric Warehouse don’t support
this syntax and it prevents ephemeral materialization with DBT. Most of concurrent warehouses as welll
Spark SQL already support this syntax.
It would be great to support it
— Does not work in fabric T-SQL
WITH outer AS(
WITH inner AS(
SÉLECT * FROM t
)
SELECT * FROM inner
)
SELECT * FROM outer
- Status added:Planned
