Recursive CTE cyles in Snowflake

Question

I have found this example to handle cycles in Recursive CTE: Recursive CTE stop condition for loops

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=dfe8858352afad6411609d157d3fe85e

I would like to do the same in snowflake, how can I do that? I have tried to "port" the example, but it is not clear to me how the array part should be converted to snowflake?

Answer 1

I have below, which returns the same result as your example from Postgres:

WITH RECURSIVE paths AS (
  -- For simplicity assume node 1 is the start
  -- we'll have two starting nodes for data = 1 and 2
  SELECT DISTINCT
    src           as node
    , data        as data
    , 0           as depth
    , src::text   as path
    , false       as is_cycle
    , ARRAY_CONSTRUCT(src)  as path_array
  FROM edges
  WHERE src IN ( 1,2)
  UNION ALL
  SELECT 
    edges.dst
    , edges.data
    , depth + 1
    , paths.path || '->' || edges.dst::text
    , ARRAY_CONTAINS(dst::variant, path_array)
    , ARRAY_APPEND(path_array, dst)
  FROM paths
  JOIN edges 
    ON edges.src = paths.node 
    AND edges.data = paths.data
    AND NOT is_cycle
)
SELECT * FROM paths;

However, I have to remove the DISTINCT in the recursive part, as it is not allowed in Snowflake:

SQL compilation error: DISTINCT is not allowed in a CTEs recursive term.