How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

Question

I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or recursive SQL statements.

As far as I can tell Snowflake does not support these operations. Are there any other queries that can be made to mimic this or work around this?

"Currently, Snowflake does not fully support common table expressions (CTE) in DDL operations."

Snowflake documentation: https://docs.snowflake.net/manuals/sql-reference/constructs/with.html

see: https://www.postgresql.org/docs/current/static/queries-with.html

Answer 1

Snowflake currently supports recursive CTEs.

I just build an sql to parse an account tree to consolidate amounts at the branch level.

From the source account tree data, main is used to build each branch & immediate sub branch/leaves combination

with main as (
  select a.scode as src , b.scode as tgt  
  from accttree a 
    join accttree b on (a.totalinto=b.scode)
), tree as (
  select src, NULL as bridge, tgt  
  from main 
  --where hprop = 15365 
  union all 
  select m.src, m.tgt as bridge, t.tgt as tgt 
  from main m 
    join tree t on (m.tgt = t.src)
)
--Tree gives the branch and leaf relation for the entire set of branches
Select * 
from tree