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.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store