Sam Debruyn on 18 Jun 2023 15:38:49
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.
Xiaoyu Li (administrator) on 17 Jul 2024 18:05:29
Thanks for your feedback. We are working on adding the support of nested CTE to Fabric Warehousing.
- Comments (1)
RE: Support for nested WITH statements
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 welllSpark SQL already support this syntax.It would be great to support it— Does not work in fabric T-SQLWITH outer AS( WITH inner AS( SÉLECT * FROM t ) SELECT * FROM inner)SELECT * FROM outer