Deep CTE in Snowflake

CTE is a technique to abstract complex queries or to reuse their common parts. Is it possible to use them to query hierarchies? Yes, and I will show you some ways I am doing it.

Table structure

Recursive approach

It works for fixed number of hierarchies pretty well. The following query succeeded with 1 billion of rows and hierarchy depth 8, but failed with warehouse size when the path to the root was around 84. It has also built-in limitation of 100 recursive calls.

Power of 4

This approach handles all amount of data and depth up to 4k links (easy to extend), but with 1 billion rows and 84 links takes around 15 minutes (warehouse XS). I tested it with 4 million rows and 900 links and it took only 1 minute.