How to break cycles in Snowflake when running recursive queries

Question

I am running a hierarchical query in snowflake using the CONNECT BY clause in the form of

SELECT
       CONNECT_BY_ROOT field_a
     , field_a
     , SYS_CONNECT_BY_PATH (field_b, '/') AS path_
FROM my_table
START WITH field_a 
CONNECT BY  PRIOR  field_b =  field_a;

The problem is my table has some loops and snowflake is stopping after 100 iterations or never finishing the query. Is there anyway to break cycles like Oracle does with the NOCYCLE clause?

user_12178980 · Answer

The Snowflake documentation is typically very good. I recommend looking there, first. This specific topic is covered in the CONNECT BY function documentation here:

https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html#usage-notes

Unfortunately, it looks like the limit of iterations is the only way to assist you with this issue, as NOCYCLE is not currently supported.

How to break cycles in Snowflake when running recursive queries

1 Answers