Repository: sqlfluff/sqlfluff
Issue category: Feature Request

Snowflake: Add support for CONNECT BY
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

`CONNECT BY` syntax is valid in [Snowflake](https://docs.snowflake.com/en/sql-reference/constructs/connect-by) and is not currently supported in sqlfluff.

### Expected Behaviour

The `CONNECT BY` construct should be parseable.

### Observed Behaviour

When using `CONNECT BY` sqlfluff returns 
> Found unparsable section: 'connect by prior foo = bar'

and 

> WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?

### How to reproduce

```sql
with tbl as (
    select 'A' as foo, 'B' as bar
    union all
    select 'B' as foo, 'C' as bar
)

select
    *,
    connect_by_root bar as connect_by_root
from tbl
connect by prior foo = bar
```


### Dialect

```
dialect = snowflake
```

### Version

```
$ sqlfluff --version
sqlfluff, version 3.0.6
```

### Configuration

.sqlfluff
```
[sqlfluff]
dialect = snowflake
templater = jinja
sql_file_exts = .sql
rules =
    capitalisation.keywords,
    capitalisation.literals,
    capitalisation.functions,
    capitalisation.types,
    capitalisation.identifiers
```


### Are you willing to work on and submit a PR to address the issue?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)



Functions to edit:
src/sqlfluff/dialects/dialect_snowflake.py:ConnectByClauseSegment

Patch:
diff --git a/src/sqlfluff/dialects/dialect_snowflake.py b/src/sqlfluff/dialects/dialect_snowflake.py
index 1cdeb9ea3ca..64acb21037b 100644
--- a/src/sqlfluff/dialects/dialect_snowflake.py
+++ b/src/sqlfluff/dialects/dialect_snowflake.py
@@ -685,6 +685,7 @@
             # Allow use of CONNECT_BY_ROOT pseudo-columns.
             # https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html#:~:text=Snowflake%20supports%20the%20CONNECT_BY_ROOT,the%20Examples%20section%20below.
             Sequence("CONNECT_BY_ROOT", Ref("ColumnReferenceSegment")),
+            Sequence("PRIOR", Ref("ColumnReferenceSegment")),
         ],
         before=Ref("LiteralGrammar"),
     ),
@@ -1165,27 +1166,13 @@ class ConnectByClauseSegment(BaseSegment):
             "CONNECT",
             "BY",
             Delimited(
-                Sequence(
-                    Ref.keyword("PRIOR", optional=True),
-                    Ref("ColumnReferenceSegment"),
-                    Ref("EqualsSegment"),
-                    Ref.keyword("PRIOR", optional=True),
-                    Ref("ColumnReferenceSegment"),
-                ),
+                OptionallyBracketed(Ref("ExpressionSegment")),
             ),
         ),
         Sequence(
             "CONNECT",
             "BY",
-            Delimited(
-                Sequence(
-                    Ref.keyword("PRIOR", optional=True),
-                    Ref("ColumnReferenceSegment"),
-                    Ref("EqualsSegment"),
-                    Ref("ColumnReferenceSegment"),
-                ),
-                delimiter="AND",
-            ),
+            OptionallyBracketed(Ref("ExpressionSegment")),
             Sequence(
                 "START",
                 "WITH",

Test patch: diff --git a/test/fixtures/dialects/snowflake/connect_by.sql b/test/fixtures/dialects/snowflake/connect_by.sql
index 579fd8600a8..8833a06dfba 100644
--- a/test/fixtures/dialects/snowflake/connect_by.sql
+++ b/test/fixtures/dialects/snowflake/connect_by.sql
@@ -42,3 +42,16 @@ select
 from components c
 connect by prior c.parent_component_id = c.component_id AND PRIOR c.component_type = c.component_type
 order by quantity;
+
+with tbl as (
+    select 'A' as foo, 'B' as bar
+    union all
+    select 'B' as foo, 'C' as bar
+)
+
+select
+    *,
+    connect_by_root bar as connect_by_root,
+    sys_connect_by_path(bar, '') as path
+from tbl
+connect by prior foo = bar and not contains(prior path, bar);
diff --git a/test/fixtures/dialects/snowflake/connect_by.yml b/test/fixtures/dialects/snowflake/connect_by.yml
index c5d1e253889..01441f35cfb 100644
--- a/test/fixtures/dialects/snowflake/connect_by.yml
+++ b/test/fixtures/dialects/snowflake/connect_by.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: f7fb309134f715fa9db2152b58edb39c7e9945c453bfd0ec7647b0c4c5127b68
+_hash: e14025f4c73fa53449dcd09a84dd1f56407ecfa362d611a04ff98af56aebcef5
 file:
 - statement:
     select_statement:
@@ -38,13 +38,14 @@ file:
               quoted_literal: "'President'"
           - keyword: connect
           - keyword: by
-          - column_reference:
-              naked_identifier: manager_id
-          - comparison_operator:
-              raw_comparison_operator: '='
-          - keyword: prior
-          - column_reference:
-              naked_identifier: employee_id
+          - expression:
+            - column_reference:
+                naked_identifier: manager_id
+            - comparison_operator:
+                raw_comparison_operator: '='
+            - keyword: prior
+            - column_reference:
+                naked_identifier: employee_id
       orderby_clause:
       - keyword: order
       - keyword: by
@@ -99,13 +100,14 @@ file:
               quoted_literal: "'President'"
           - keyword: connect
           - keyword: by
-          - column_reference:
-              naked_identifier: manager_id
-          - comparison_operator:
-              raw_comparison_operator: '='
-          - keyword: prior
-          - column_reference:
-              naked_identifier: employee_id
+          - expression:
+            - column_reference:
+                naked_identifier: manager_id
+            - comparison_operator:
+                raw_comparison_operator: '='
+            - keyword: prior
+            - column_reference:
+                naked_identifier: employee_id
       orderby_clause:
       - keyword: order
       - keyword: by
@@ -167,13 +169,14 @@ file:
               numeric_literal: '1'
           - keyword: connect
           - keyword: by
-          - column_reference:
-              naked_identifier: parent_component_id
-          - comparison_operator:
-              raw_comparison_operator: '='
-          - keyword: prior
-          - column_reference:
-              naked_identifier: component_id
+          - expression:
+            - column_reference:
+                naked_identifier: parent_component_id
+            - comparison_operator:
+                raw_comparison_operator: '='
+            - keyword: prior
+            - column_reference:
+                naked_identifier: component_id
       orderby_clause:
       - keyword: order
       - keyword: by
@@ -221,13 +224,14 @@ file:
               quoted_literal: "'President'"
           - keyword: connect
           - keyword: by
-          - column_reference:
-              naked_identifier: manager_id
-          - comparison_operator:
-              raw_comparison_operator: '='
-          - keyword: prior
-          - column_reference:
-              naked_identifier: employee_id
+          - expression:
+            - column_reference:
+                naked_identifier: manager_id
+            - comparison_operator:
+                raw_comparison_operator: '='
+            - keyword: prior
+            - column_reference:
+                naked_identifier: employee_id
       orderby_clause:
       - keyword: order
       - keyword: by
@@ -269,32 +273,143 @@ file:
           connectby_clause:
           - keyword: connect
           - keyword: by
-          - keyword: prior
-          - column_reference:
-            - naked_identifier: c
-            - dot: .
-            - naked_identifier: parent_component_id
-          - comparison_operator:
-              raw_comparison_operator: '='
-          - column_reference:
-            - naked_identifier: c
-            - dot: .
-            - naked_identifier: component_id
-          - keyword: AND
-          - keyword: PRIOR
-          - column_reference:
-            - naked_identifier: c
-            - dot: .
-            - naked_identifier: component_type
-          - comparison_operator:
-              raw_comparison_operator: '='
-          - column_reference:
-            - naked_identifier: c
-            - dot: .
-            - naked_identifier: component_type
+          - expression:
+            - keyword: prior
+            - column_reference:
+              - naked_identifier: c
+              - dot: .
+              - naked_identifier: parent_component_id
+            - comparison_operator:
+                raw_comparison_operator: '='
+            - column_reference:
+              - naked_identifier: c
+              - dot: .
+              - naked_identifier: component_id
+            - binary_operator: AND
+            - keyword: PRIOR
+            - column_reference:
+              - naked_identifier: c
+              - dot: .
+              - naked_identifier: component_type
+            - comparison_operator:
+                raw_comparison_operator: '='
+            - column_reference:
+              - naked_identifier: c
+              - dot: .
+              - naked_identifier: component_type
       orderby_clause:
       - keyword: order
       - keyword: by
       - column_reference:
           naked_identifier: quantity
 - statement_terminator: ;
+- statement:
+    with_compound_statement:
+      keyword: with
+      common_table_expression:
+        naked_identifier: tbl
+        keyword: as
+        bracketed:
+          start_bracket: (
+          set_expression:
+          - select_statement:
+              select_clause:
+              - keyword: select
+              - select_clause_element:
+                  quoted_literal: "'A'"
+                  alias_expression:
+                    keyword: as
+                    naked_identifier: foo
+              - comma: ','
+              - select_clause_element:
+                  quoted_literal: "'B'"
+                  alias_expression:
+                    keyword: as
+                    naked_identifier: bar
+          - set_operator:
+            - keyword: union
+            - keyword: all
+          - select_statement:
+              select_clause:
+              - keyword: select
+              - select_clause_element:
+                  quoted_literal: "'B'"
+                  alias_expression:
+                    keyword: as
+                    naked_identifier: foo
+              - comma: ','
+              - select_clause_element:
+                  quoted_literal: "'C'"
+                  alias_expression:
+                    keyword: as
+                    naked_identifier: bar
+          end_bracket: )
+      select_statement:
+        select_clause:
+        - keyword: select
+        - select_clause_element:
+            wildcard_expression:
+              wildcard_identifier:
+                star: '*'
+        - comma: ','
+        - select_clause_element:
+            keyword: connect_by_root
+            column_reference:
+              naked_identifier: bar
+            alias_expression:
+              keyword: as
+              naked_identifier: connect_by_root
+        - comma: ','
+        - select_clause_element:
+            function:
+              function_name:
+                function_name_identifier: sys_connect_by_path
+              function_contents:
+                bracketed:
+                - start_bracket: (
+                - expression:
+                    column_reference:
+                      naked_identifier: bar
+                - comma: ','
+                - expression:
+                    quoted_literal: "''"
+                - end_bracket: )
+            alias_expression:
+              keyword: as
+              naked_identifier: path
+        from_clause:
+          keyword: from
+          from_expression:
+            from_expression_element:
+              table_expression:
+                table_reference:
+                  naked_identifier: tbl
+            connectby_clause:
+            - keyword: connect
+            - keyword: by
+            - expression:
+              - keyword: prior
+              - column_reference:
+                  naked_identifier: foo
+              - comparison_operator:
+                  raw_comparison_operator: '='
+              - column_reference:
+                  naked_identifier: bar
+              - binary_operator: and
+              - keyword: not
+              - function:
+                  function_name:
+                    function_name_identifier: contains
+                  function_contents:
+                    bracketed:
+                    - start_bracket: (
+                    - expression:
+                        keyword: prior
+                        column_reference:
+                          naked_identifier: path
+                    - comma: ','
+                    - expression:
+                        column_reference:
+                          naked_identifier: bar
+                    - end_bracket: )
+- statement_terminator: ;