I have some Employee Data in which I am creating wanting to create a Hierarchy for. Each employee record has their direct report listed. I would like to be able to rank Employees from top to bottom based on how many superiors are ahead of them.
I have figured out how to do this in the SQL Component but wondering if there is a way to do this through a Matillion ETL workflow
Hi MK,
The best way currently is (as you have already found) using a SQL Component. It allows you to write your own complete SQL SELECT statement inside a Transformation Job.
We have not yet added a new transformation feature for recursive CTEs. Partly because they are still a relatively new capability for CDWs, and partly because we are not sure how often it would be useful.
I am keen to hear more potential use cases for recursive CTEs. Please reply on this thread if you are interested in developments in that area.
Best regards,
Ian
I am interested. Thanks for your response Ian!
Curious if any work has been done to make recursive ctes compatible with transformations. I'm trying to get a job set up to populate a table and the query fueling it relies on a recursive cte
Still requires the SQL Component but had a lot of success using connect by clause instead of recursive CTE for hierarchical queries: https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html