How to Query Hierarchical Data in Snowflake

Querying hierarchical data is well documented for Snowflake. However, here is an extensive in-depth coverage of a very typical and frequent hierarchy, using self-joins on the same table. It is the parent-child hierarchy for fathers and sons, Facebook friends, employees and their managers etc. Each entity has one single identifier and a friendlier display name. And the hierarchy is given by another optional ID to its unique and optional parent.

Typical Scenario

The CSV file below — that you can easily save and upload in a Snowflake table — presents a hierarchy on the EMPNO-MGR pair, with ENAME used as display name for both an employee and the manager:

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7839,KING,PRESIDENT,,1981-11-17,5000,,10
7698,BLAKE,MANAGER,7839,1981-05-01,2850,,30
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300.5,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7900,JAMES,CLERK,7698,1981-12-03,950,,30
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7782,CLARK,MANAGER,7839,1981-06-09,2450.5,,10
7934,MILLER,CLERK,7782,1982-01-23,1300,,10
7566,JONES,MANAGER,7839,1981-04-02,2975.8,,20
7788,SCOTT,ANALYST,7566,1982-12-09,3000,,20
7876,ADAMS,CLERK,7788,1983-01-12,1100,,20
7902,FORD,ANALYST,7566,1981-12-03,3000,,20
7369,SMITH,CLERK,7902,1980-12-17,800,,20