Repository: sqlfluff/sqlfluff
Issue category: Bug Report

Databricks Notebook Parsing: Parser fails on leading Markdown cell 
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

SQL Fluff won't parse a Databricks SQL Notebook that starts with a Markdown Magic cell (interpreted as comments), when exported from databricks / committed to a git repo. The default format of a leading markdown cell adds an empty line before the first actual cell. 


![grafik](https://github.com/user-attachments/assets/e14dc4a8-4fa5-408c-857f-25a8c7a5dd72)

![grafik](https://github.com/user-attachments/assets/78701d0a-c21e-4113-94af-61f93b488763)

Edit:
Just tested: for any type of leading cells, this behavior persists. -> Any non-SQL cell as first cell will produce this issue. Format for R, Python, Shell Cells all are saved with a trailing empty line before the Command-Terminator.


### Expected Behaviour

Notebooks gets parsed and linted.

### Observed Behaviour

SQL Fluff fails on parsing and refuses to parse the rest of the file.
![grafik](https://github.com/user-attachments/assets/340f648b-1d74-45a8-bb5a-8884c19b7c0e)
![grafik](https://github.com/user-attachments/assets/8cf73e88-f4a1-4a68-b410-6600d03679cf)


when removing the empty line before the first -- COMMAND -------- , the file gets parsed no problem.
adding any keyword, statement, etc also results in a succesfully parsed file, although any contents will then be rendered as part of the mark down cell and not interpreted / executed later. So basically this is an invalid notebook 

### How to reproduce

Parsable, but invalid SQL Notebook.

```sql
-- Databricks notebook source
-- MAGIC %md
-- MAGIC # MarkDown Cell
-- COMMAND ----------

-- MAGIC %md
-- MAGIC # Test

-- COMMAND ----------

-- DBTITLE 1,Create Widget
CREATE  WIDGET TEXT     base_ts_txt       DEFAULT "2024-05-29 18:00:00";
```

Not parsable:
```sql
-- Databricks notebook source
-- MAGIC %md
-- MAGIC # MarkDown Cell

-- COMMAND ----------

-- MAGIC %md
-- MAGIC # Test

-- COMMAND ----------

-- DBTITLE 1,Create Widget
CREATE  WIDGET TEXT     base_ts_txt       DEFAULT "2024-05-29 18:00:00";
```

### Dialect

Databricks

### Version

3.2.5

### Configuration

[tool.sqlfluff.core]
dialect = "databricks"
templater = "jinja"
sql_file_exts = ".sql"
verbose = 2
exclude_rules = ["RF04", "ST05", "ST06","ST09",  "LT05", "AL01", "ST01", "RF02", "RF03","AL09"]
ignore_files = []

[tool.sqlfluff.indentation]
indent_unit = "space"
tab_space_size = 4
allow_implicit_indents = "True"
 
[tool.sqlfluff.layout.type.groupby_clause]
line_position = "alone:strict"

[tool.sqlfluff.layout.type.statement_terminator]
spacing_before = "touch"
line_position = "trailing"

[tool.sqlfluff.layout.type.comma]
spacing_before = "touch"
line_position = "leading"

[tool.sqlfluff.layout.type.end_of_file]
spacing_before = "touch"

[tool.sqlfluff.rules.capitalisation.keywords]
capitalisation_policy = "upper"

[tool.sqlfluff.rules.convention.quoted_literals]
preferred_quoted_literal_style = "double_quotes"

[tool.sqlfluff.templater.python.context]
catalog = "xxx"
current_catalog = "xxx"
target_schema = "xxx"
source_schema = "xxx"
pos_ts = "2024-01-01 00:00:00"
pfc_ts = "2024-01-01 00:00:00"
delivery_begin_incl = "2024-01-01 00:00:00"
delivery_end_excl = "2024-01-01 00:00:00"
future_begin_incl = "2024-01-01 00:00:00"
base_ts_txt = "2024-01-01 00:00:00"
pid = "<pid>"
proc_id = "<pid>"

### 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)



Functions to edit:
src/sqlfluff/dialects/dialect_databricks.py:StatementSegment
Functions added: src/sqlfluff/dialects/dialect_databricks.py:MagicCellStatementSegment

Patch:
diff --git a/src/sqlfluff/dialects/dialect_databricks.py b/src/sqlfluff/dialects/dialect_databricks.py
index d03479f5697..23d27635bd3 100644
--- a/src/sqlfluff/dialects/dialect_databricks.py
+++ b/src/sqlfluff/dialects/dialect_databricks.py
@@ -12,6 +12,7 @@
     BaseSegment,
     Bracketed,
     CodeSegment,
+    CommentSegment,
     Dedent,
     Delimited,
     IdentifierSegment,
@@ -61,6 +62,7 @@
     before="equals",
 )
 
+
 databricks_dialect.insert_lexer_matchers(
     # Notebook Cell Delimiter:
     # https://learn.microsoft.com/en-us/azure/databricks/notebooks/notebook-export-import#sql-1
@@ -70,6 +72,22 @@
     before="newline",
 )
 
+databricks_dialect.insert_lexer_matchers(
+    # Databricks Notebook Start:
+    # needed to insert "so early" to avoid magic + notebook
+    # start to be interpreted as inline comments
+    # https://learn.microsoft.com/en-us/azure/databricks/notebooks/notebooks-code#language-magic
+    [
+        RegexLexer(
+            "notebook_start", r"-- Databricks notebook source(\r?\n){1}", CommentSegment
+        ),
+        RegexLexer("magic_line", r"(-- MAGIC)( [^%]{1})([^\n]*)", CodeSegment),
+        RegexLexer("magic_start", r"(-- MAGIC %)([^\n]{2,})(\r?\n)", CodeSegment),
+    ],
+    before="inline_comment",
+)
+
+
 databricks_dialect.add(
     CommandCellSegment=TypedParser("command", CodeSegment, type="statement_terminator"),
     DoubleQuotedUDFBody=TypedParser(
@@ -215,6 +233,9 @@
             optional=True,
         ),
     ),
+    NotebookStart=TypedParser("notebook_start", CommentSegment, type="notebook_start"),
+    MagicLineGrammar=TypedParser("magic_line", CodeSegment, type="magic_line"),
+    MagicStartGrammar=TypedParser("magic_start", CodeSegment, type="magic_start"),
 )
 
 databricks_dialect.replace(
@@ -1042,6 +1063,8 @@ class StatementSegment(sparksql.StatementSegment):
             Ref("FunctionParameterListGrammarWithComments"),
             Ref("DeclareOrReplaceVariableStatementSegment"),
             Ref("CommentOnStatementSegment"),
+            # Notebook grammar
+            Ref("MagicCellStatementSegment"),
         ]
     )
 
@@ -1511,3 +1534,22 @@ class CommentOnStatementSegment(BaseSegment):
         "IS",
         OneOf(Ref("QuotedLiteralSegment"), "NULL"),
     )
+
+
+class MagicCellStatementSegment(BaseSegment):
+    """Treat -- MAGIC %md/py/sh/... Cells as their own segments.
+
+    N.B. This is a workaround, to make databricks notebooks
+    with leading parsable by sqlfluff.
+
+    https://learn.microsoft.com/en-us/azure/databricks/notebooks/notebooks-code#language-magic
+    """
+
+    type = "magic_cell_segment"
+    match_grammar = Sequence(
+        Ref("NotebookStart", optional=True),
+        Ref("MagicStartGrammar"),
+        AnyNumberOf(Ref("MagicLineGrammar"), optional=True),
+        terminators=[Ref("CommandCellSegment", optional=True)],
+        reset_terminators=True,
+    )

Test patch: diff --git a/test/fixtures/dialects/databricks/command_terminator.sql b/test/fixtures/dialects/databricks/command_terminator.sql
index bf680a9392f..ad61852856a 100644
--- a/test/fixtures/dialects/databricks/command_terminator.sql
+++ b/test/fixtures/dialects/databricks/command_terminator.sql
@@ -8,4 +8,5 @@ SELECT COL2 FROM TABLE2
 
 -- COMMAND ----------
 
-SELECT COL3 FROM TABLE3
+SELECT COL3 FROM TABLE3;
+SELECT COL4 FROM TABLE4;
diff --git a/test/fixtures/dialects/databricks/command_terminator.yml b/test/fixtures/dialects/databricks/command_terminator.yml
index 518d7d4c2c6..d3355bc1f8b 100644
--- a/test/fixtures/dialects/databricks/command_terminator.yml
+++ b/test/fixtures/dialects/databricks/command_terminator.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: 07c4373056abf54fa047c7c873623bea8ed31ae8ed78c50faa325a9e13daa7a2
+_hash: f1cafde889d19e15076ec4ac9f91422fe0d24ff3d6125ad98fd5ad0137ad0f01
 file:
 - statement:
     select_statement:
@@ -49,3 +49,19 @@ file:
             table_expression:
               table_reference:
                 naked_identifier: TABLE3
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          column_reference:
+            naked_identifier: COL4
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: TABLE4
+- statement_terminator: ;
diff --git a/test/fixtures/dialects/databricks/magic_line.sql b/test/fixtures/dialects/databricks/magic_line.sql
new file mode 100644
index 00000000000..9fafcadb075
--- /dev/null
+++ b/test/fixtures/dialects/databricks/magic_line.sql
@@ -0,0 +1,24 @@
+-- Databricks notebook source
+-- MAGIC %md
+-- MAGIC # Dummy Notebook
+
+-- COMMAND ----------
+
+-- DBTITLE 1,Select Data
+
+SELECT x FROM y
+
+-- COMMAND ----------
+
+-- MAGIC %python
+-- MAGIC foo = 'bar'
+-- MAGIC print(foo)
+
+-- COMMAND ----------
+
+SELECT a FROM b;
+
+-- COMMAND ----------
+
+-- MAGIC %sh
+-- MAGIC echo heloworld
diff --git a/test/fixtures/dialects/databricks/magic_line.yml b/test/fixtures/dialects/databricks/magic_line.yml
new file mode 100644
index 00000000000..205d2dfbc3e
--- /dev/null
+++ b/test/fixtures/dialects/databricks/magic_line.yml
@@ -0,0 +1,53 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# 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: e2d50f62c11ce3de4f424c3fe61da8f64a44f9fd0c0928468ebfa3b4462e04cd
+file:
+- statement:
+    magic_cell_segment:
+      magic_start: "-- MAGIC %md\n"
+      magic_line: '-- MAGIC # Dummy Notebook'
+- statement_terminator: "\n\n-- COMMAND ----------\n"
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          column_reference:
+            naked_identifier: x
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: y
+- statement_terminator: "\n\n-- COMMAND ----------\n"
+- statement:
+    magic_cell_segment:
+    - magic_start: "-- MAGIC %python\n"
+    - magic_line: "-- MAGIC foo = 'bar'"
+    - magic_line: -- MAGIC print(foo)
+- statement_terminator: "\n\n-- COMMAND ----------\n"
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          column_reference:
+            naked_identifier: a
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: b
+- statement_terminator: ;
+- statement_terminator: "\n\n-- COMMAND ----------\n"
+- statement:
+    magic_cell_segment:
+      magic_start: "-- MAGIC %sh\n"
+      magic_line: -- MAGIC echo heloworld