Recursion Strategy

Hi   at the moment there is no support for recursion in CTEs in the roadmap.  This was something that was requested in Synapse Serverless (which the lakehouse sql endpoint and warehouse is based on) but never happened.  What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

In terms of what you can do for recursion, I write a query in which I join the same table back on itself.  E.G here's an example of basic recursion in an employees table.  This example can be used in the Warehouse, but can also be used in the lakehouse sql endpoint if the tables existed (eg created by spark).

create table employees
(
    empid int,
    empname varchar(50),
    managerid int
);

insert into employees
values (1,'andy',null),(2,'dave',1),(3,'alice',1),(4,'glenda',3)

select
    a.empname as EmployeeName,
    b.empname as ManagerName
from employees a
left join employees b on b.empid = a.managerid 

The reason recursion is supported in Datamart is that its backended by Azure SQL Database while the Lakehouse SQL Endpoint and Warehouse are a new MPP engine (started with Synapse Serverless).  With Fabric, MS have built a SQL engine from scratch with fundamentally different architecture to SQL Server/Azure SQL Database so not everything we expect will be there right now, if at all.