How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)
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 &amp; 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