{
  "cells": [
    {
      "cell_type": "markdown",
      "id": "article-000",
      "metadata": {},
      "source": [
        "# Bring Your SQL Skills to Fabric Runtime 2.0\n",
        "\n",
        "You should not have to learn a new programming language before you can put your SQL skills to work on a lakehouse.\n",
        "\n",
        "With Apache Spark 4.1 in Microsoft Fabric Runtime 2.0, we give SQL developers familiar building blocks for more capable workflows: variables, Common Table Expressions (CTEs), recursive queries, and SQL scripting. You can reuse your SQL skills rather than switch languages just to set a parameter or choose a processing branch.\n",
        "\n",
        "That also makes Runtime 2.0 a more practical destination for existing analytical SQL and SQL-based ETL workflows. More business logic can remain in SQL while Spark handles distributed scans, joins, and aggregations. It is a migration accelerator, not drop-in T-SQL or PL/SQL compatibility: dialect differences, transaction requirements, and performance still need validation.\n",
        "\n",
        "Runtime 2.0 is generally available. Start with the self-contained examples below, then evaluate one of your own workflows.\n",
        "\n",
        "## The problem: a SQL workflow split across languages\n",
        "\n",
        "Consider a sales notebook where queries are SQL, but Python supplies parameters, branches on a batch total, or repeatedly queries a hierarchy. For SQL-centric teams, those wrappers can make the workflow harder to review and maintain.\n",
        "\n",
        "PySpark remains a good choice for Python libraries and broader application logic. The benefit here is choice: keep SQL-oriented work together when that is the clearest design.\n",
        "\n",
        "## What is new in Spark 4.1, and what builds on existing SQL?\n",
        "\n",
        "Not every feature in this tutorial was introduced in Spark 4.1. The value is the **combined SQL experience available in Runtime 2.0**.\n",
        "\n",
        "| Capability | What to know | Why it matters |\n",
        "|---|---|---|\n",
        "| Ordinary CTEs | An existing Spark SQL capability | Preserve readable, named transformation stages |\n",
        "| Session variables | Available in Runtime 2.0's Spark SQL surface | Make typed, reusable parameters explicit |\n",
        "| Dynamic SQL | `EXECUTE IMMEDIATE` is available in Runtime 2.0 | Keep metadata-driven execution in SQL |\n",
        "| Recursive CTEs | Added in Spark 4.1 | Express hierarchy traversal in SQL |\n",
        "| SQL scripting | Introduced in Spark 4.0; generally available and enabled by default in Spark 4.1 | Coordinate statements with local variables, conditions, loops, and handlers |\n",
        "\n",
        "No scripting-enablement flag is needed for the default Spark 4.1 configuration.\n",
        "\n",
        "## Choose your learning path\n",
        "\n",
        "- **Core path:** [session variables](#1-session-variables-make-your-parameters-explicit), [CTEs](#2-ctes-give-each-transformation-a-name), then [local variables and branching](#4-sql-scripting-keep-variables-and-decisions-together).\n",
        "- **Go further:** [recursive CTEs](#3-recursive-ctes-walk-a-hierarchy-without-an-application-side-loop), [loops](#5-for-loops-useful-for-control-data-not-millions-of-fact-rows), [error handling](#6-error-handling-handle-a-specific-condition-deliberately), and [dynamic SQL](#7-dynamic-sql-reuse-typed-parameters).\n",
        "\n",
        "Run the shared setup first. The core path needs only the sales view; the hierarchy section creates its own employee view.\n",
        "\n",
        "### Shared setup\n",
        "\n",
        "1. Select a **Runtime 2.0 Environment**. If you change its runtime, **Save** and **Publish** the Environment before using it. Alternatively, use the workspace's Runtime 2.0 default if there is no Environment override.\n",
        "2. Keep **PySpark** as the notebook's default language. Each code cell passes SQL text to\n",
        "   `spark.sql(...)`. No Lakehouse attachment, external data, or package installation is\n",
        "   required.\n",
        "3. Start a **fresh Spark session** and run the setup cells below. Then follow either learning path, or select **Run all** in the imported notebook for the complete tutorial.\n",
        "4. Compare outputs with the expected tables. The handler example deliberately returns `REJECTED`; that is its expected result, not a notebook failure. Stop on unexpected errors rather than skipping ahead.\n",
        "\n",
        "Optional exercises change existing cells. Restore their original values and queries, or reimport the download, before checking the baseline with **Run all** again.\n",
        "\n",
        "\n",
        "**Use the Spark SQL experience**\n",
        "\n",
        "Run these examples in a **Fabric Spark notebook**, not a Warehouse T-SQL editor or a Lakehouse SQL analytics endpoint. Those are different execution surfaces.\n",
        "\n",
        "\n",
        "Each code block is one PySpark cell. Pass either one top-level SQL statement or one\n",
        "complete `BEGIN ... END` script to `spark.sql(...)`. Keep a script together rather than\n",
        "splitting it at its internal semicolons.\n",
        "\n",
        "\n",
        "The business logic stays in SQL. `spark.sql(...)` returns a DataFrame; `.show(truncate=False)`\n",
        "displays its results without shortening values. Setup and variable-assignment cells do\n",
        "not display a result table.\n",
        "\n",
        "Check the Spark version:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-001",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "SELECT version() AS spark_version;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-002",
      "metadata": {},
      "source": [
        "The version should begin with `4.1`. Record the full value if you are validating a migration.\n",
        "\n",
        "### Create six sales rows\n",
        "\n",
        "The amounts represent order revenue, not unit prices. Explicit decimals keep the examples appropriate for currency calculations."
      ]
    },
    {
      "cell_type": "code",
      "id": "article-003",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "CREATE OR REPLACE TEMP VIEW sql_sales AS\n",
        "SELECT\n",
        "    order_id,\n",
        "    city,\n",
        "    product,\n",
        "    CAST(amount AS DECIMAL(10, 2)) AS amount\n",
        "FROM VALUES\n",
        "    (1, 'Seattle',  'Laptop',  1500),\n",
        "    (2, 'Seattle',  'Monitor',  600),\n",
        "    (3, 'New York', 'Laptop',  1800),\n",
        "    (4, 'New York', 'Monitor',  500),\n",
        "    (5, 'Chicago',  'Laptop',  1700),\n",
        "    (6, 'Chicago',  'Monitor',  700)\n",
        "AS source(order_id, city, product, amount);\n",
        "\"\"\")\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-004",
      "metadata": {},
      "source": [
        "Check the baseline:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-005",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "SELECT COUNT(*) AS order_count, SUM(amount) AS total_sales\n",
        "FROM sql_sales;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-006",
      "metadata": {},
      "source": [
        "| order_count | total_sales |\n",
        "|---:|---:|\n",
        "| 6 | 6800.00 |\n",
        "\n",
        "`sql_sales` is a **session-scoped temporary view**. It does not create a persistent table or write files. You can rerun the setup; the view is replaced. After restarting the session, run the setup again.\n",
        "\n",
        "## 1. Session variables: make your parameters explicit\n",
        "\n",
        "A threshold should be a parameter, not a string pasted into several queries.\n",
        "\n",
        "Declare a session variable in a new cell:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-007",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "DECLARE OR REPLACE VARIABLE sql_min_sales DECIMAL(10, 2) DEFAULT 1000.00;\n",
        "\"\"\")\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-008",
      "metadata": {},
      "source": [
        "Use it in a query:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-009",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "SELECT order_id, city, amount\n",
        "FROM sql_sales\n",
        "WHERE amount >= session.sql_min_sales\n",
        "ORDER BY amount DESC, order_id;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-010",
      "metadata": {},
      "source": [
        "| order_id | city | amount |\n",
        "|---:|---|---:|\n",
        "| 3 | New York | 1800.00 |\n",
        "| 5 | Chicago | 1700.00 |\n",
        "| 1 | Seattle | 1500.00 |\n",
        "\n",
        "Now change the parameter:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-011",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "SET VAR sql_min_sales = 1700.00;\n",
        "\"\"\")\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-012",
      "metadata": {},
      "source": [
        "Run the query again:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-013",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "SELECT order_id, city, amount\n",
        "FROM sql_sales\n",
        "WHERE amount >= session.sql_min_sales\n",
        "ORDER BY amount DESC, order_id;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-014",
      "metadata": {},
      "source": [
        "Only New York's `1800.00` order and Chicago's `1700.00` order remain. **The query is unchanged; the parameter is different.**\n",
        "\n",
        "The same pattern works for a processing date, region, batch ID, or watermark. It reduces the need to construct SQL strings just to change a value.\n",
        "\n",
        "Three details matter:\n",
        "\n",
        "- **Session scope:** the value survives between cells in the same Spark session, but not a session restart. Initialize parameters in every scheduled run; do not depend on a previous interactive run.\n",
        "- **Explicit assignment:** outside a script, use `SET VAR` or `SET VARIABLE` for variable values. Plain `SET` is also used for Spark configuration and is not a substitute.\n",
        "- **Name resolution:** `session.sql_min_sales` makes the session variable explicit. Avoid giving a variable the same name as an input column.\n",
        "\n",
        "`OR REPLACE` makes the declaration easy to rerun. These variables are temporary session state, not secrets storage or persistent application configuration.\n",
        "\n",
        "## 2. CTEs: give each transformation a name\n",
        "\n",
        "Ordinary CTEs are already familiar to many SQL developers. They let you express a multi-stage transformation without burying the logic inside nested subqueries.\n",
        "\n",
        "Here we calculate city totals, then keep cities with at least `2300.00` in sales:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-015",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "WITH sales_by_city AS (\n",
        "    SELECT city, SUM(amount) AS total_sales\n",
        "    FROM sql_sales\n",
        "    GROUP BY city\n",
        "),\n",
        "qualifying_cities AS (\n",
        "    SELECT city, total_sales\n",
        "    FROM sales_by_city\n",
        "    WHERE total_sales >= 2300.00\n",
        ")\n",
        "SELECT city, total_sales\n",
        "FROM qualifying_cities\n",
        "ORDER BY total_sales DESC, city;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-016",
      "metadata": {},
      "source": [
        "| city | total_sales |\n",
        "|---|---:|\n",
        "| Chicago | 2400.00 |\n",
        "| New York | 2300.00 |\n",
        "\n",
        "Seattle's `2100.00` total is filtered out. The query reads as a sequence of named steps: **aggregate, apply the business threshold, return the result**.\n",
        "\n",
        "For migration, this is useful because the transformation's structure can remain recognizable to the people who maintain it.\n",
        "\n",
        "**A CTE is scoped to one statement, not the whole notebook.** It is not a persistent table, a cache, or a guarantee that Spark computes a result only once. Spark still optimizes the query and chooses its execution plan.\n",
        "\n",
        "## 3. Recursive CTEs: walk a hierarchy without an application-side loop\n",
        "\n",
        "Organizational charts, product categories, bills of materials, and dependency trees all contain parent-child relationships. Without recursive SQL, a team may need repeated joins at a fixed depth or a loop outside SQL.\n",
        "\n",
        "Create a small employee hierarchy:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-017",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "CREATE OR REPLACE TEMP VIEW sql_employees AS\n",
        "SELECT *\n",
        "FROM VALUES\n",
        "    ('Alice', CAST(NULL AS STRING)),\n",
        "    ('Bob',   'Alice'),\n",
        "    ('Carol', 'Alice'),\n",
        "    ('David', 'Bob'),\n",
        "    ('Emma',  'Bob'),\n",
        "    ('Frank', 'Carol')\n",
        "AS source(employee, manager);\n",
        "\"\"\")\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-018",
      "metadata": {},
      "source": [
        "Alice is the root. Bob and Carol report to Alice; David and Emma report to Bob; Frank reports to Carol.\n",
        "\n",
        "Now walk the hierarchy:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-019",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "WITH RECURSIVE employee_hierarchy (employee, manager, depth) AS (\n",
        "    SELECT employee, manager, CAST(0 AS INT) AS depth\n",
        "    FROM sql_employees\n",
        "    WHERE manager IS NULL\n",
        "\n",
        "    UNION ALL\n",
        "\n",
        "    SELECT e.employee, e.manager, CAST(h.depth + 1 AS INT)\n",
        "    FROM sql_employees e\n",
        "    JOIN employee_hierarchy h\n",
        "        ON e.manager = h.employee\n",
        ")\n",
        "SELECT employee, manager, depth\n",
        "FROM employee_hierarchy\n",
        "ORDER BY depth, employee;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-020",
      "metadata": {},
      "source": [
        "| employee | manager | depth |\n",
        "|---|---|---:|\n",
        "| Alice | NULL | 0 |\n",
        "| Bob | Alice | 1 |\n",
        "| Carol | Alice | 1 |\n",
        "| David | Bob | 2 |\n",
        "| Emma | Bob | 2 |\n",
        "| Frank | Carol | 2 |\n",
        "\n",
        "Read this as two parts:\n",
        "\n",
        "1. The **anchor** selects the root employees.\n",
        "2. The **recursive step** finds the direct reports of employees discovered in the preceding iteration.\n",
        "\n",
        "`UNION ALL` combines those levels. The query stops when the recursive step produces no more rows, subject to Spark's recursion safeguards.\n",
        "\n",
        "This can move hierarchy traversal out of application code and into one SQL statement. It does **not** make every graph problem inexpensive. Each level can add work, and multiple parent paths can multiply rows.\n",
        "\n",
        "For production hierarchies, validate unique keys, missing parents, cycles, expected depth, and result size. This sample is a small, acyclic tree; it does not demonstrate general cycle detection. Do not use an arbitrary depth filter to hide incomplete results.\n",
        "\n",
        "Spark 4.1 defaults to a recursion-level limit of `100` and a recursive-row limit of `1,000,000`, controlled by `spark.sql.cteRecursionLevelLimit` and `spark.sql.cteRecursionRowLimit`. Treat a limit error as a reason to inspect the data and query before increasing a safeguard. Keep aggregates and window functions out of the recursive step; apply them to the completed result where appropriate.\n",
        "\n",
        "## 4. SQL scripting: keep variables and decisions together\n",
        "\n",
        "CTEs organize **one query**. SQL scripting coordinates **multiple statements**.\n",
        "\n",
        "A compound statement starts with `BEGIN` and ends with `END`. Inside it, you can declare\n",
        "local variables, assign query results with plain `SET`, branch, and run other SQL statements.\n",
        "\n",
        "Here is a complete script that classifies our sales batch:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-021",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "BEGIN\n",
        "    DECLARE order_count BIGINT DEFAULT 0;\n",
        "    DECLARE total_sales DECIMAL(18, 2) DEFAULT 0.00;\n",
        "    DECLARE review_threshold DECIMAL(18, 2) DEFAULT 6000.00;\n",
        "    DECLARE batch_status STRING DEFAULT 'NORMAL';\n",
        "\n",
        "    SET (order_count, total_sales) = (\n",
        "        SELECT COUNT(*), COALESCE(SUM(amount), 0.00)\n",
        "        FROM sql_sales\n",
        "    );\n",
        "\n",
        "    IF order_count = 0 THEN\n",
        "        SET batch_status = 'EMPTY';\n",
        "    ELSEIF total_sales >= review_threshold THEN\n",
        "        SET batch_status = 'REVIEW';\n",
        "    ELSE\n",
        "        SET batch_status = 'NORMAL';\n",
        "    END IF;\n",
        "\n",
        "    SELECT order_count, total_sales, review_threshold, batch_status;\n",
        "END;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-022",
      "metadata": {},
      "source": [
        "| order_count | total_sales | review_threshold | batch_status |\n",
        "|---:|---:|---:|---|\n",
        "| 6 | 6800.00 | 6000.00 | REVIEW |\n",
        "\n",
        "One aggregate query assigns the row count and revenue total to the two variables, in order. `IF / ELSEIF / ELSE` chooses a status, and the final `SELECT` returns one result.\n",
        "\n",
        "**Empty means no rows, not zero revenue.** Zero-value orders or purchases offset by refunds can produce a zero total in a nonempty batch. Use `COUNT(*)` to distinguish those cases.\n",
        "\n",
        "**Try a second branch:** change `review_threshold` to `7000.00` and rerun the whole cell. The status should become `NORMAL`.\n",
        "\n",
        "**Try the edge cases**, one at a time, keeping the original `6000.00` threshold:\n",
        "\n",
        "- For an empty batch, change `FROM sql_sales` in this script to `FROM sql_sales WHERE 1 = 0`.\n",
        "- Restore that query, then replace `SUM(amount)` with `SUM(CAST(0 AS DECIMAL(10, 2)))` to simulate six zero-value orders without changing the source view.\n",
        "\n",
        "| Scenario | order_count | total_sales | batch_status |\n",
        "|---|---:|---:|---|\n",
        "| Empty batch | 0 | 0.00 | EMPTY |\n",
        "| Six zero-value orders | 6 | 0.00 | NORMAL |\n",
        "\n",
        "Restore the original aggregate query afterward. These exercises do not modify `sql_sales` or affect the later examples.\n",
        "\n",
        "Unlike the earlier session parameter, these variables are **local to the block**. Run the complete block each time; its variables are declared and initialized again.\n",
        "\n",
        "In a real workflow, a branch might select a processing strategy or decide whether to run a write. Here we return a status instead, so the tutorial needs no audit tables, persistent writes, or special cleanup.\n",
        "\n",
        "## 5. FOR loops: useful for control data, not millions of fact rows\n",
        "\n",
        "Some workflows genuinely need iteration. SQL scripting lets you iterate over a query result without writing a Python loop.\n",
        "\n",
        "This example visits **three city summaries**, accumulates their totals, and returns one result:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-023",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "BEGIN\n",
        "    DECLARE cities_checked INT DEFAULT 0;\n",
        "    DECLARE checked_total DECIMAL(18, 2) DEFAULT 0.00;\n",
        "\n",
        "    FOR city_row AS\n",
        "        SELECT city, SUM(amount) AS city_total\n",
        "        FROM sql_sales\n",
        "        GROUP BY city\n",
        "        ORDER BY city\n",
        "    DO\n",
        "        SET cities_checked = cities_checked + 1;\n",
        "        SET checked_total = checked_total + city_row.city_total;\n",
        "    END FOR;\n",
        "\n",
        "    SELECT cities_checked, checked_total;\n",
        "END;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-024",
      "metadata": {},
      "source": [
        "| cities_checked | checked_total |\n",
        "|---:|---:|\n",
        "| 3 | 6800.00 |\n",
        "\n",
        "`city_row.city_total` accesses a column from the current loop row.\n",
        "\n",
        "**This is a syntax example, not an optimization.** For this particular calculation, use a single set-based query instead:"
      ]
    },
    {
      "cell_type": "code",
      "id": "article-025",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "SELECT\n",
        "    COUNT(DISTINCT city) AS cities_checked,\n",
        "    SUM(amount) AS checked_total\n",
        "FROM sql_sales;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-026",
      "metadata": {},
      "source": [
        "You should get the same `3` and `6800.00`.\n",
        "\n",
        "Use loops for small control lists when each item truly requires a separate operation. Do not translate a row-by-row database cursor over a fact table into a row-by-row Spark loop. A loop does not automatically distribute its iterations, and statements inside it can trigger repeated Spark work.\n",
        "\n",
        "## 6. Error handling: handle a specific condition deliberately\n",
        "\n",
        "A migration is not complete if it only preserves the happy path. SQL-centric workflows often include explicit business-rule failures and error handling.\n",
        "\n",
        "This example rejects a batch whose total exceeds a configured limit. Read it in three steps:\n",
        "\n",
        "1. **Calculate** the batch total in the outer block.\n",
        "2. **Validate** inside a small nested block, raising and handling a specific error when the total is too high.\n",
        "3. **Report** the result after leaving that nested block.\n",
        "\n",
        "Spark's `raise_error()` raises `USER_RAISED_EXCEPTION`, with SQLSTATE `P0001`. We evaluate it inside an assignment to trigger the exception without emitting an extra query result. The cast supplies the assignment's string type; the function raises an error rather than returning a status. The handler sets the rejection status and reason."
      ]
    },
    {
      "cell_type": "code",
      "id": "article-027",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "BEGIN\n",
        "    DECLARE allowed_total DECIMAL(18, 2) DEFAULT 6000.00;\n",
        "    DECLARE actual_total DECIMAL(18, 2) DEFAULT 0.00;\n",
        "    DECLARE validation_status STRING DEFAULT 'ACCEPTED';\n",
        "    DECLARE validation_reason STRING DEFAULT 'Within the configured limit';\n",
        "\n",
        "    SET actual_total = (\n",
        "        SELECT COALESCE(SUM(amount), 0.00)\n",
        "        FROM sql_sales\n",
        "    );\n",
        "\n",
        "    BEGIN\n",
        "        DECLARE EXIT HANDLER FOR SQLSTATE 'P0001'\n",
        "        BEGIN\n",
        "            SET validation_status = 'REJECTED';\n",
        "            SET validation_reason =\n",
        "                'Sales total exceeds the configured limit';\n",
        "        END;\n",
        "\n",
        "        IF actual_total > allowed_total THEN\n",
        "            SET validation_status = CAST(\n",
        "                raise_error('Sales total exceeds the configured limit')\n",
        "                AS STRING\n",
        "            );\n",
        "        END IF;\n",
        "    END;\n",
        "\n",
        "    SELECT\n",
        "        validation_status,\n",
        "        actual_total,\n",
        "        allowed_total,\n",
        "        validation_reason;\n",
        "END;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-028",
      "metadata": {},
      "source": [
        "Expected: one result with `REJECTED`, `6800.00`, `6000.00`, and the explicit rejection reason. `EXIT` leaves the inner block where the handler is declared; the outer block continues to the final `SELECT`.\n",
        "\n",
        "Change `allowed_total` to `7000.00` and rerun to see the accepted path.\n",
        "\n",
        "This handler demonstrates a controlled business outcome; **it turns that particular exception into a result**. If your production scheduler must mark the run as failed, let the condition propagate or rethrow it rather than treating a status row as job failure. Unexpected errors should still fail visibly; do not wrap everything in a broad handler that reports success.\n",
        "\n",
        "Also, `BEGIN ... END` defines a script block, **not a multi-statement transaction**. A handler does not roll back earlier successful writes. Design persistent workflows for retries, idempotency, and the actual transaction guarantees of the target tables.\n",
        "\n",
        "## 7. Dynamic SQL: reuse typed parameters\n",
        "\n",
        "Sometimes a workflow must choose a statement at runtime. For example, a metadata-driven\n",
        "job may select one approved source table from a configuration list and apply a common\n",
        "validation query. `EXECUTE IMMEDIATE` executes SQL text; typed session variables keep\n",
        "configurable values separate from that text.\n",
        "\n",
        "This example keeps the source fixed to demonstrate **session parameters** and\n",
        "**scalar-result capture** with `INTO`, not a case where dynamic SQL is required.\n",
        "Run the entire cell: its first two calls initialize the parameters, and its final call\n",
        "executes the script. `OR REPLACE` makes the declarations safe to rerun."
      ]
    },
    {
      "cell_type": "code",
      "id": "article-029",
      "metadata": {},
      "source": [
        "spark.sql(\"\"\"\n",
        "DECLARE OR REPLACE VARIABLE sql_dynamic_city STRING DEFAULT 'Seattle';\n",
        "\"\"\")\n",
        "\n",
        "spark.sql(\"\"\"\n",
        "DECLARE OR REPLACE VARIABLE sql_dynamic_minimum_order DECIMAL(10, 2) DEFAULT 1000.00;\n",
        "\"\"\")\n",
        "\n",
        "spark.sql(\"\"\"\n",
        "BEGIN\n",
        "    DECLARE qualifying_sales DECIMAL(18, 2) DEFAULT 0.00;\n",
        "\n",
        "    EXECUTE IMMEDIATE\n",
        "        'SELECT COALESCE(SUM(amount), 0.00)\n",
        "         FROM sql_sales\n",
        "         WHERE city = session.sql_dynamic_city\n",
        "           AND amount >= session.sql_dynamic_minimum_order'\n",
        "        INTO qualifying_sales;\n",
        "\n",
        "    SELECT\n",
        "        session.sql_dynamic_city AS selected_city,\n",
        "        session.sql_dynamic_minimum_order AS minimum_order,\n",
        "        qualifying_sales;\n",
        "END;\n",
        "\"\"\").show(truncate=False)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "article-030",
      "metadata": {},
      "source": [
        "| selected_city | minimum_order | qualifying_sales |\n",
        "|---|---:|---:|\n",
        "| Seattle | 1000.00 | 1500.00 |\n",
        "\n",
        "The executed SQL reads the two **session-scoped** variables by name. Block-local variables\n",
        "are not implicitly visible inside its SQL text; `INTO` transfers the returned total to\n",
        "the local `qualifying_sales` variable. Change a parameter's declaration and rerun the\n",
        "whole cell to change the filter. The SQL string stays unchanged; no values are concatenated.\n",
        "\n",
        "This is useful when migrating metadata-driven SQL workflows. But dynamic SQL is not needed for every parameterized query: **use an ordinary query with a session variable when that is enough.**\n",
        "\n",
        "Value parameters are not raw SQL fragments or a license to access arbitrary objects. For dynamic object names, use Spark's supported `IDENTIFIER` syntax where applicable, validate the allowed targets, and retain normal authorization checks. Never concatenate untrusted input into a statement.\n",
        "\n",
        "## What actually runs in Spark?\n",
        "\n",
        "It helps to distinguish the **control flow** from the **data processing**:\n",
        "\n",
        "- **Session variables** hold small parameter values; they are not distributed datasets.\n",
        "- **CTEs** describe relational query structure for Spark to analyze and optimize.\n",
        "- **Recursive CTEs** express an iterative query; depth and branching affect the amount of work.\n",
        "- **Scripts** coordinate statements and conditions. Their queries still go through Spark's planning and execution machinery.\n",
        "- **Loops** control repeated operations; they do not turn sequential business logic into parallel execution.\n",
        "\n",
        "The scale comes from how Spark executes the underlying queries across data partitions. A variable or an `IF` statement is not a performance feature by itself, and rewriting a query as a CTE does not guarantee a faster plan.\n",
        "\n",
        "Runtime 2.0 also supports Fabric's Native Execution Engine. Evaluate it for your workload, inspect supported operators and fallback behavior, and measure the resulting plans.\n",
        "\n",
        "The practical improvement here is **a richer SQL programming model on top of a distributed analytical engine**. That is valuable even before you measure any execution-time change.\n",
        "\n",
        "## Bring an existing SQL workflow across in stages\n",
        "\n",
        "Start with one analytical batch or transformation workflow, not the most transaction-heavy application in your estate.\n",
        "\n",
        "| Existing pattern | Runtime 2.0 starting point | What still needs attention |\n",
        "|---|---|---|\n",
        "| Parameters repeated in generated SQL | Session variables or bound parameters | Session initialization, types, and name resolution |\n",
        "| Nested SQL transformations | Named CTEs | Result equivalence and physical plans |\n",
        "| Hierarchy traversal in application code | Recursive CTEs | Cycles, missing parents, depth, and row growth |\n",
        "| Python wrappers around SQL decisions | SQL scripting with local variables and `IF` | Scope, errors, and retry behavior |\n",
        "| Database cursors | Set-based SQL first; small `FOR` loops only where needed | Avoid per-row Spark jobs |\n",
        "| Generated SQL strings | `EXECUTE IMMEDIATE` with typed session parameters | Safe identifiers and permitted targets |\n",
        "| Stored procedures and multi-statement transactions | Reworked SQL scripts plus Fabric orchestration | Procedure packaging, transaction boundaries, and operational contracts are not drop-in equivalents |\n",
        "\n",
        "For T-SQL or PL/SQL migrations, inventory more than syntax. Check date and string functions, null behavior, decimal precision, ANSI arithmetic and cast behavior, collation and case sensitivity, temporary objects, exception semantics, and permissions. Database-specific constructs such as `@variables`, `GO`, or a procedure body will need review rather than being pasted unchanged.\n",
        "\n",
        "For an existing Fabric Spark workload moving from Runtime 1.3, also validate Spark behavior changes, configured settings, connectors, and custom libraries. Runtime 2.0 changes major components, including Python, Java, and Scala. Use the [Fabric Runtime 2.0 migration guide](https://learn.microsoft.com/en-us/fabric/data-engineering/runtime-2-0-migration-guide), not a runtime dropdown alone, as your production migration plan.\n",
        "\n",
        "### Validate correctness first, then representative performance\n",
        "\n",
        "The six-row tutorial is a functional exercise, **not a benchmark**.\n",
        "\n",
        "For your pilot:\n",
        "\n",
        "1. Compare output schemas, row counts, duplicate handling, nulls, and monetary totals against the existing implementation.\n",
        "2. Exercise the normal, empty, nonempty zero-total, rejected, and unexpected-error paths. Confirm that retries cannot duplicate successful writes.\n",
        "3. Use production-representative data to compare end-to-end duration, capacity consumption, stage behavior, shuffle, spill, and concurrency.\n",
        "4. Keep data, capacity, cache state, and execution settings comparable. Record whether Native Execution Engine is enabled.\n",
        "5. Save the executed notebook with its full Spark version, selected Environment, runtime release channel, run timestamp, settings, and outputs. Restart the session and repeat to catch hidden state.\n",
        "\n",
        "For this tutorial, the key checkpoints are:\n",
        "\n",
        "| Checkpoint | Expected result |\n",
        "|---|---|\n",
        "| Sales baseline | 6 orders, total `6800.00` |\n",
        "| Session threshold `1000.00` | 3 orders |\n",
        "| Session threshold `1700.00` | 2 orders |\n",
        "| CTE city threshold `2300.00` | Chicago `2400.00`; New York `2300.00` |\n",
        "| Employee hierarchy | 6 employees; maximum depth 2 |\n",
        "| Script review threshold `6000.00` | `REVIEW` |\n",
        "| Optional empty-batch exercise | 0 orders, `0.00`, `EMPTY` |\n",
        "| Optional zero-value exercise | 6 orders, `0.00`, `NORMAL` |\n",
        "| City loop and set-based equivalent | 3 cities, total `6800.00` |\n",
        "| Handler limit `6000.00` | Explicit `REJECTED` result |\n",
        "| Dynamic Seattle query | `1500.00` |\n",
        "\n",
        "The only objects created here are temporary views and session variables. They remain\n",
        "available for experimentation until the Spark session ends. Stop the session when you\n",
        "are finished; it consumes Fabric capacity while active.\n",
        "\n",
        "## Your SQL skills are the starting point\n",
        "\n",
        "You do not need to abandon SQL to build more capable analytical workflows in Fabric. With Runtime 2.0 and Spark 4.1, we bring more of the surrounding workflow into the SQL development experience: parameters, hierarchy traversal, decisions, iteration, and explicit error handling.\n",
        "\n",
        "For SQL developers, that lowers the barrier to getting started. For teams migrating existing analytical SQL applications, it creates more opportunities to preserve readable business logic while adapting execution for a lakehouse and distributed processing.\n",
        "\n",
        "**Start now with one workflow in a Runtime 2.0 development notebook.** Run these examples, replace the sample views with your own data, and validate the result. Migrate in stages, with evidence, rather than keeping avoidable language-switching workarounds simply because that was how an older runtime worked.\n",
        "\n",
        "## References\n",
        "\n",
        "- [Fabric Runtime 2.0](https://learn.microsoft.com/en-us/fabric/data-engineering/runtime-2-0)\n",
        "- [Migrate from Fabric Runtime 1.3 to Runtime 2.0](https://learn.microsoft.com/en-us/fabric/data-engineering/runtime-2-0-migration-guide)\n",
        "- [Develop and execute Fabric notebooks](https://learn.microsoft.com/en-us/fabric/data-engineering/author-execute-notebook)\n",
        "- [Spark 4.1 release notes](https://spark.apache.org/releases/spark-release-4.1.0.html)\n",
        "- [Spark SQL session variables](https://spark.apache.org/docs/4.1.0/sql-ref-syntax-ddl-declare-variable.html) and [variable assignment](https://spark.apache.org/docs/4.1.0/sql-ref-syntax-aux-set-var.html)\n",
        "- [Common Table Expressions](https://spark.apache.org/docs/4.1.0/sql-ref-syntax-qry-select-cte.html), [recursive-query error conditions](https://spark.apache.org/docs/4.1.0/sql-error-conditions.html#invalid-recursive-cte), and [recursion configuration limits](https://spark.apache.org/docs/4.1.0/configuration.html)\n",
        "- [SQL scripting](https://spark.apache.org/docs/4.1.0/sql-ref-scripting.html), [compound statements and handlers](https://spark.apache.org/docs/4.1.0/control-flow/compound-stmt.html), [IF](https://spark.apache.org/docs/4.1.0/control-flow/if-stmt.html), and [FOR](https://spark.apache.org/docs/4.1.0/control-flow/for-stmt.html)"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Synapse PySpark",
      "language": "python",
      "name": "synapse_pyspark"
    },
    "language_info": {
      "name": "python"
    },
    "tutorial": {
      "source": "_posts/2026-11-17-spark-sql-enhancements-fabric-runtime-2-0.md",
      "source_sha256": "e9c81fda05e1400af8279339491abe8922522f7ef177071a05ccd28c2787822b"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}
