I have just learnt that recursive CTE's are not supported in Databricks SQL, however, we are looking to shift the complex aggregations into Databricks instead of relying on Azure SQL DB.

We are using Azure SQL DB with CDC enabled in combination with ADF to populate the DLT's in Databricks. From here we are trying to aggregate in two separate dimensions, which are by hierarchy (using a parent child table for reference) and then by period (day, week, month, year etc.). Our data engineering team have a stronger background in SQL and more of a DBA related past, so Python and Spark are newer conepts for us.

From my understanding it appears that PySpark may be the only option to achieve the rolling up of figures through a hierarchy. Is this correct? If so, does anyone know of any supporting material to be able to achieve this?

Primarily we will use two source tables, which are a team hierarchy with parent and child relationships then a transactional table with a team Id, date, and the values we are looking to aggregate up through the hierarchy.