In a typical data architecture, data from source systems is ingested into the bronze layer, where it is stored as-is. Subsequent transformations are then applied in the silver layer.

One of these transformations could involve calculating the EmpLevel column. Since recursive CTEs are not supported in Databricks SQL, you have the following options:

1. Use pyspark: Use pyspark, as in the example below, and persist the calculated EmpLevel column. This is flexible and you can have as many levels as you need. The table is updated on some basis, so you can consider EmpLevel calculation as one of the steps of this update.

2. Hardcode Levels for a Limited Hierarchy: If the hierarchy depth is known and limited, you can manually define levels using a series of unions. This is for sure not as elegant as recursive CTE or pyspark solution, but it will work as well.