Repository: sqlfluff/sqlfluff
Issue category: Bug Report

Issue:
Incorrect references.special_chars when using pivoted column
### Search before asking

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


### What Happened

When linting valid Snowflake code, following violation is raised incorrectly.

### Expected Behaviour

Since we only select from invalid named column, not creating invalid named column, violation shouldn't be raised.

### Observed Behaviour

```
== [bug2.sql] FAIL                                                                                                                                                                                                           
L:   4 | P:   5 | RF05 | Do not use special characters in identifiers.
                       | [references.special_chars]
L:   5 | P:   5 | RF05 | Do not use special characters in identifiers.
                       | [references.special_chars]
```

### How to reproduce

```sql
SELECT
    COL1,
    COL2,
    "'PIVOTED1'" AS PIVOTED1,
    "'PIVOTED2'" AS PIVOTED2
FROM TBL
PIVOT (
    MAX(VAL)
    FOR COL IN ('PIVOTED1', 'PIVOTED2')
)

```

### Dialect

snowflake

### Version

sqlfluff, version 2.3.4
python 3.10.12

### Configuration

N/A

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

- [X] 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)



Hints:
#5358 and #5359 are likely same underlying root cause.

Patch:
diff --git a/src/sqlfluff/rules/references/RF05.py b/src/sqlfluff/rules/references/RF05.py
index 6ea99e91555..2b12a69555b 100644
--- a/src/sqlfluff/rules/references/RF05.py
+++ b/src/sqlfluff/rules/references/RF05.py
@@ -63,6 +63,9 @@ def _get_additional_allowed_characters(self, dialect_name: str) -> str:
         if dialect_name == "bigquery":
             # In BigQuery, also allow hyphens.
             result.update("-")
+        if dialect_name == "snowflake":
+            # In Snowflake, external stage metadata uses $.
+            result.update("$")
         return "".join(result)
 
     def _eval(self, context: RuleContext) -> Optional[LintResult]:
@@ -101,6 +104,10 @@ def _eval(self, context: RuleContext) -> Optional[LintResult]:
         ):
             return LintResult(memory=context.memory)
 
+        if self._is_aliased_select_clause_element(context):
+            # If selects are aliased, ignore unaliased column reference
+            return None
+
         # Do some extra processing for quoted identifiers.
         if context.segment.is_type("quoted_identifier"):
             # Update the default policy to quoted
@@ -215,3 +222,13 @@ def _init_ignore_words_list(self) -> List[str]:
             self.ignore_words_list = []
 
         return self.ignore_words_list
+
+    @staticmethod
+    def _is_aliased_select_clause_element(context: RuleContext) -> bool:
+        for seg in reversed(context.parent_stack):
+            if seg.is_type("alias_expression"):
+                return False
+            if seg.is_type("select_clause_element"):
+                return seg.get_child("alias_expression") is not None
+
+        return False


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/RF05.yml b/test/fixtures/rules/std_rule_cases/RF05.yml
index cb2976ef844..9d654ff0730 100644
--- a/test/fixtures/rules/std_rule_cases/RF05.yml
+++ b/test/fixtures/rules/std_rule_cases/RF05.yml
@@ -444,3 +444,49 @@ test_fail_table_aliases:
     rules:
       references.special_chars:
         quoted_identifiers_policy: table_aliases
+
+test_pass_special_chars_snowflake_stage_metadata:
+  # https://docs.snowflake.com/en/user-guide/querying-metadata
+  pass_str: |
+    SELECT
+      METADATA$FILENAME
+      , METADATA$FILE_ROW_NUMBER
+      , METADATA$FILE_CONTENT_KEY
+      , METADATA$FILE_LAST_MODIFIED
+      , METADATA$START_SCAN_TIME
+    FROM @ext_stage;
+  configs:
+    core:
+      dialect: snowflake
+
+test_pass_pivot_snowflake:
+  pass_str: |
+    SELECT
+      COL1,
+      COL2,
+      "'PIVOTED1'" AS PIVOTED1,
+      "'PIVOTED2'" AS PIVOTED2
+    FROM TBL
+    PIVOT (
+      MAX(VAL)
+      FOR COL IN ('PIVOTED1', 'PIVOTED2')
+    )
+  configs:
+    core:
+      dialect: snowflake
+
+test_pass_pivot_snowflake_complex:
+  pass_str: |
+    SELECT
+      COL1,
+      COL2,
+      CAST("'PIVOTED1'" AS bigint) AS PIVOTED1,
+      "'PIVOTED2'" AS PIVOTED2
+    FROM TBL
+    PIVOT (
+      MAX(VAL)
+      FOR COL IN ('PIVOTED1', 'PIVOTED2')
+    )
+  configs:
+    core:
+      dialect: snowflake