{
  "nbformat": 4,
  "nbformat_minor": 5,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.13.0"
    },
    "blog_metadata": {
      "topic": "Moving Off ODBC in Fabric: A Migration Playbook for Analytics Teams",
      "slug": "moving-off-odbc-in-fabric-a-migration-playbook-for-analytics",
      "generated_by": "LinkedIn Post Generator + Azure OpenAI",
      "generated_at": "2026-09-17T16:20:30.418Z"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Moving Off ODBC in Fabric: A Migration Playbook for Analytics Teams\n",
        "\n",
        "This notebook turns the migration playbook into a hands-on validation workflow. It focuses on inventorying ODBC usage, classifying migration risk, recommending Fabric-friendly targets, and simulating wave-based cutover governance so analytics teams can reduce connectivity debt without breaking valid exception scenarios."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install pandas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import json\n",
        "import re\n",
        "from pathlib import Path\n",
        "from datetime import datetime, timezone\n",
        "\n",
        "import pandas as pd"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Migration framing and sample estate\n",
        "\n",
        "The core idea is targeted modernization, not blanket replacement. In practice, that means separating legacy embedded ODBC dependencies that should move toward ADBC-aligned or Fabric-native patterns from valid ODBC-compatible Spark SQL client scenarios that may remain as governed exceptions.\n",
        "\n",
        "The sample data below creates a representative analytics estate with active, dead, documented, and undocumented connection definitions so the rest of the notebook can validate the playbook end to end."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "sample_connections = pd.DataFrame([\n",
        "    {\n",
        "        \"system_name\": \"sales-etl\",\n",
        "        \"artifact_type\": \"deployment_script\",\n",
        "        \"consuming_tool\": \"python-job\",\n",
        "        \"owner_name\": \"data-eng\",\n",
        "        \"environment_name\": \"prod\",\n",
        "        \"data_source\": \"sql01.sales\",\n",
        "        \"auth_method\": \"service_principal\",\n",
        "        \"connector_or_driver\": \"ODBC Driver 18\",\n",
        "        \"execution_path\": \"daily 05:00\",\n",
        "        \"business_criticality\": \"high\",\n",
        "        \"rollback_contact\": \"platform-oncall\",\n",
        "        \"connection_string\": \"Driver={ODBC Driver 18};Server=sql01;Database=sales;Encrypt=yes;\",\n",
        "        \"status\": \"active\"\n",
        "    },\n",
        "    {\n",
        "        \"system_name\": \"finance-report\",\n",
        "        \"artifact_type\": \"powerbi_desktop\",\n",
        "        \"consuming_tool\": \"power-query\",\n",
        "        \"owner_name\": None,\n",
        "        \"environment_name\": \"prod\",\n",
        "        \"data_source\": \"legacy_finance_dsn\",\n",
        "        \"auth_method\": \"user\",\n",
        "        \"connector_or_driver\": \"ODBC DSN\",\n",
        "        \"execution_path\": \"daily 06:00\",\n",
        "        \"business_criticality\": \"high\",\n",
        "        \"rollback_contact\": \"bi-lead\",\n",
        "        \"connection_string\": \"DSN=finance_prod;UID=finance_user;PWD=***;\",\n",
        "        \"status\": \"active\"\n",
        "    },\n",
        "    {\n",
        "        \"system_name\": \"lake-report\",\n",
        "        \"artifact_type\": \"notebook\",\n",
        "        \"consuming_tool\": \"spark-sql-client\",\n",
        "        \"owner_name\": \"analytics-platform\",\n",
        "        \"environment_name\": \"prod\",\n",
        "        \"data_source\": \"fabric_lakehouse\",\n",
        "        \"auth_method\": \"entra_id\",\n",
        "        \"connector_or_driver\": \"ODBC-compatible Spark SQL\",\n",
        "        \"execution_path\": \"hourly\",\n",
        "        \"business_criticality\": \"medium\",\n",
        "        \"rollback_contact\": \"analytics-platform\",\n",
        "        \"connection_string\": \"DSN=legacy_lakehouse;UID=user;PWD=***;\",\n",
        "        \"status\": \"active\"\n",
        "    },\n",
        "    {\n",
        "        \"system_name\": \"ad-hoc-model\",\n",
        "        \"artifact_type\": \"desktop_report\",\n",
        "        \"consuming_tool\": \"power-query\",\n",
        "        \"owner_name\": \"analyst\",\n",
        "        \"environment_name\": None,\n",
        "        \"data_source\": \"csv_bridge\",\n",
        "        \"auth_method\": \"user\",\n",
        "        \"connector_or_driver\": \"pyodbc\",\n",
        "        \"execution_path\": \"manual\",\n",
        "        \"business_criticality\": \"low\",\n",
        "        \"rollback_contact\": \"analyst\",\n",
        "        \"connection_string\": \"odbc://localhost/temp\",\n",
        "        \"status\": \"active\"\n",
        "    },\n",
        "    {\n",
        "        \"system_name\": \"dead-release-check\",\n",
        "        \"artifact_type\": \"sql_script\",\n",
        "        \"consuming_tool\": \"release-pipeline\",\n",
        "        \"owner_name\": \"platform-eng\",\n",
        "        \"environment_name\": \"test\",\n",
        "        \"data_source\": \"old_sql\",\n",
        "        \"auth_method\": \"sql_login\",\n",
        "        \"connector_or_driver\": \"System.Data.Odbc\",\n",
        "        \"execution_path\": \"on-release\",\n",
        "        \"business_criticality\": \"low\",\n",
        "        \"rollback_contact\": \"platform-eng\",\n",
        "        \"connection_string\": \"Driver={SQL Server};Server=oldsql;Database=ops;\",\n",
        "        \"status\": \"retire_candidate\"\n",
        "    }\n",
        "])\n",
        "\n",
        "sample_connections"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 1: Parse exported metadata files and normalize ODBC references into an inventory DataFrame\n",
        "\n",
        "This example reproduces the blog's metadata discovery pattern using local JSON exports. The goal is not perfect parsing on day one; it is to quickly surface hidden ODBC dependencies into a reviewable inventory with enough context to assign owners and decide migration waves."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from pathlib import Path\n",
        "import json\n",
        "import pandas as pd\n",
        "\n",
        "root = Path(\"exports\")\n",
        "root.mkdir(exist_ok=True)\n",
        "\n",
        "sample_docs = [\n",
        "    {\n",
        "        \"file\": \"sales_etl.json\",\n",
        "        \"payload\": {\n",
        "            \"name\": \"sales-etl\",\n",
        "            \"owner\": \"data-eng\",\n",
        "            \"environment\": \"prod\",\n",
        "            \"risk\": \"high\",\n",
        "            \"connectionString\": \"Driver={ODBC Driver 18};Server=sql01;Database=sales;\"\n",
        "        }\n",
        "    },\n",
        "    {\n",
        "        \"file\": \"finance_report.json\",\n",
        "        \"payload\": {\n",
        "            \"name\": \"finance-report\",\n",
        "            \"owner\": None,\n",
        "            \"environment\": \"prod\",\n",
        "            \"risk\": None,\n",
        "            \"connectionString\": \"DSN=finance_prod;UID=finance_user;PWD=***;\"\n",
        "        }\n",
        "    },\n",
        "    {\n",
        "        \"file\": \"lake_report.json\",\n",
        "        \"payload\": {\n",
        "            \"name\": \"lake-report\",\n",
        "            \"owner\": \"analytics-platform\",\n",
        "            \"environment\": \"prod\",\n",
        "            \"risk\": \"medium\",\n",
        "            \"connectionString\": \"DSN=legacy_lakehouse;UID=user;PWD=***;\"\n",
        "        }\n",
        "    },\n",
        "    {\n",
        "        \"file\": \"clean_asset.json\",\n",
        "        \"payload\": {\n",
        "            \"name\": \"clean-asset\",\n",
        "            \"owner\": \"platform-eng\",\n",
        "            \"environment\": \"dev\",\n",
        "            \"risk\": \"low\",\n",
        "            \"connectionString\": \"Server=fabric-native;Endpoint=warehouse;\"\n",
        "        }\n",
        "    }\n",
        "]\n",
        "\n",
        "for doc in sample_docs:\n",
        "    (root / doc[\"file\"]).write_text(json.dumps(doc[\"payload\"], indent=2), encoding=\"utf-8\")\n",
        "\n",
        "rows = []\n",
        "for path in root.rglob(\"*.json\"):\n",
        "    doc = json.loads(path.read_text(encoding=\"utf-8\"))\n",
        "    conn = str(doc.get(\"connectionString\", \"\"))\n",
        "    if \"odbc\" in conn.lower() or \"driver=\" in conn.lower() or \"dsn=\" in conn.lower():\n",
        "        rows.append({\n",
        "            \"source_file\": str(path),\n",
        "            \"system\": doc.get(\"name\", path.stem),\n",
        "            \"owner\": doc.get(\"owner\"),\n",
        "            \"environment\": doc.get(\"environment\"),\n",
        "            \"risk\": doc.get(\"risk\"),\n",
        "            \"connection_string\": conn\n",
        "        })\n",
        "\n",
        "inventory = pd.DataFrame(rows)\n",
        "inventory"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 2: Flag missing owner, environment, or risk classification for migration triage\n",
        "\n",
        "Once an inventory exists, the next step is to block unsafe migration behavior. Missing ownership, environment, or risk data is itself a risk signal because undocumented connections are the ones most likely to fail during refreshes, cutovers, and incident response."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import pandas as pd\n",
        "\n",
        "inventory = pd.DataFrame([\n",
        "    {\"system\": \"sales-etl\", \"owner\": \"data-eng\", \"environment\": \"prod\", \"risk\": \"high\"},\n",
        "    {\"system\": \"finance-report\", \"owner\": None, \"environment\": \"prod\", \"risk\": None},\n",
        "    {\"system\": \"ad-hoc-model\", \"owner\": \"analyst\", \"environment\": None, \"risk\": \"low\"},\n",
        "])\n",
        "\n",
        "required = [\"owner\", \"environment\", \"risk\"]\n",
        "inventory[\"missing_fields\"] = inventory[required].isna().apply(\n",
        "    lambda row: [col for col, missing in row.items() if missing], axis=1\n",
        ")\n",
        "inventory[\"needs_review\"] = inventory[\"missing_fields\"].str.len() > 0\n",
        "inventory[[\"system\", \"missing_fields\", \"needs_review\"]]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 3: Create a migration inventory table definition\n",
        "\n",
        "The original post used SQL to define a durable inventory table. Here we represent that schema in Python so the notebook remains executable while still validating the intended structure, required fields, and defaults for a governed migration register."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "inventory_table_schema = pd.DataFrame([\n",
        "    {\"column_name\": \"inventory_id\", \"data_type\": \"BIGINT\", \"nullable\": False, \"default\": \"IDENTITY(1,1)\", \"notes\": \"Primary key\"},\n",
        "    {\"column_name\": \"system_name\", \"data_type\": \"NVARCHAR(200)\", \"nullable\": False, \"default\": None, \"notes\": \"System or artifact name\"},\n",
        "    {\"column_name\": \"source_file\", \"data_type\": \"NVARCHAR(500)\", \"nullable\": False, \"default\": None, \"notes\": \"Discovery source path\"},\n",
        "    {\"column_name\": \"owner_name\", \"data_type\": \"NVARCHAR(200)\", \"nullable\": True, \"default\": None, \"notes\": \"Named decision owner\"},\n",
        "    {\"column_name\": \"environment_name\", \"data_type\": \"NVARCHAR(50)\", \"nullable\": True, \"default\": None, \"notes\": \"dev/test/prod\"},\n",
        "    {\"column_name\": \"risk_classification\", \"data_type\": \"NVARCHAR(50)\", \"nullable\": True, \"default\": None, \"notes\": \"high/medium/low\"},\n",
        "    {\"column_name\": \"connection_string\", \"data_type\": \"NVARCHAR(2000)\", \"nullable\": False, \"default\": None, \"notes\": \"Discovered connection string\"},\n",
        "    {\"column_name\": \"discovered_at\", \"data_type\": \"DATETIME2\", \"nullable\": False, \"default\": \"SYSUTCDATETIME()\", \"notes\": \"UTC discovery timestamp\"},\n",
        "    {\"column_name\": \"status\", \"data_type\": \"NVARCHAR(50)\", \"nullable\": False, \"default\": \"discovered\", \"notes\": \"Lifecycle state\"}\n",
        "])\n",
        "\n",
        "inventory_table_schema"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 4: Simulate repository scanning for ODBC markers\n",
        "\n",
        "The blog used PowerShell to scan repos and deployment folders for markers like `Driver=`, `DSN=`, `pyodbc`, and `System.Data.Odbc`. This Python version performs the same discovery step so teams can validate the pattern in a notebook environment and export findings for review."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "scan_roots = [Path(\"repo\"), Path(\"deploy\")]\n",
        "for p in scan_roots:\n",
        "    p.mkdir(exist_ok=True)\n",
        "\n",
        "(repo_file := Path(\"repo\") / \"job.py\").write_text(\n",
        "    \"import pyodbc\\nconn='Driver={ODBC Driver 18};Server=sql01;Database=sales;'\\n\",\n",
        "    encoding=\"utf-8\"\n",
        ")\n",
        "(deploy_file := Path(\"deploy\") / \"release.cs\").write_text(\n",
        "    \"using System.Data.Odbc;\\nvar c = new OdbcConnection(\\\"DSN=finance_prod;\\\");\\n\",\n",
        "    encoding=\"utf-8\"\n",
        ")\n",
        "(clean_file := Path(\"repo\") / \"clean.txt\").write_text(\n",
        "    \"no connector markers here\\n\",\n",
        "    encoding=\"utf-8\"\n",
        ")\n",
        "\n",
        "patterns = [\"Driver=\", \"DSN=\", \"odbc:\", \"System.Data.Odbc\", \"pyodbc\", \"OdbcConnection\"]\n",
        "results = []\n",
        "for root in scan_roots:\n",
        "    for path in root.rglob(\"*\"):\n",
        "        if path.is_file():\n",
        "            try:\n",
        "                lines = path.read_text(encoding=\"utf-8\", errors=\"ignore\").splitlines()\n",
        "            except Exception:\n",
        "                continue\n",
        "            for line_number, line in enumerate(lines, start=1):\n",
        "                matches = [pat for pat in patterns if pat.lower() in line.lower()]\n",
        "                if matches:\n",
        "                    results.append({\n",
        "                        \"FilePath\": str(path),\n",
        "                        \"LineNumber\": line_number,\n",
        "                        \"Match\": \";\".join(matches),\n",
        "                        \"LineText\": line.strip()\n",
        "                    })\n",
        "\n",
        "scan_results = pd.DataFrame(results)\n",
        "scan_results"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 5: Make discovery repeatable with timestamped output\n",
        "\n",
        "Discovery should be repeatable, not a one-time cleanup. This cell writes a timestamped CSV report from stable scan roots so teams can rerun the inventory process during migration waves and compare findings over time."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "output_dir = Path(\"reports\")\n",
        "output_dir.mkdir(exist_ok=True)\n",
        "stamp = datetime.now(timezone.utc).strftime(\"%Y%m%d-%H%M%S\")\n",
        "csv_path = output_dir / f\"odbc-discovery-{stamp}.csv\"\n",
        "\n",
        "markers = [\"Driver=\", \"DSN=\", \"pyodbc\", \"OdbcConnection\"]\n",
        "findings = []\n",
        "for root in [Path(\"repo\"), Path(\"deploy\")]:\n",
        "    for path in root.rglob(\"*\"):\n",
        "        if path.is_file():\n",
        "            text = path.read_text(encoding=\"utf-8\", errors=\"ignore\")\n",
        "            for line_number, line in enumerate(text.splitlines(), start=1):\n",
        "                if any(marker.lower() in line.lower() for marker in markers):\n",
        "                    findings.append({\n",
        "                        \"Path\": str(path),\n",
        "                        \"LineNumber\": line_number,\n",
        "                        \"Line\": line.strip()\n",
        "                    })\n",
        "\n",
        "findings_df = pd.DataFrame(findings)\n",
        "findings_df.to_csv(csv_path, index=False)\n",
        "print(f\"Discovery report written to {csv_path}\")\n",
        "findings_df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 6: Map legacy ODBC patterns to Fabric-friendly connection targets\n",
        "\n",
        "Migration planning works better when teams sort workloads into likely targets before changing anything. This example applies simple heuristics to recommend whether a connection looks more like a Fabric Warehouse SQL endpoint, a Lakehouse SQL analytics endpoint, or a manual-review case."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import pandas as pd\n",
        "\n",
        "inventory = pd.DataFrame([\n",
        "    {\"system\": \"sales-etl\", \"connection_string\": \"Driver={ODBC Driver 18};Server=sql01;Database=sales;\"},\n",
        "    {\"system\": \"lake-report\", \"connection_string\": \"DSN=legacy_lakehouse;UID=user;PWD=secret;\"},\n",
        "    {\"system\": \"unknown-tool\", \"connection_string\": \"Driver={SomethingElse};Server=misc;\"},\n",
        "])\n",
        "\n",
        "def recommend_target(conn: str) -> str:\n",
        "    text = conn.lower()\n",
        "    if \"database=sales\" in text:\n",
        "        return \"Fabric Warehouse SQL endpoint\"\n",
        "    if \"lakehouse\" in text or \"dsn=legacy_lakehouse\" in text:\n",
        "        return \"Fabric Lakehouse SQL analytics endpoint\"\n",
        "    return \"Review manually\"\n",
        "\n",
        "inventory[\"recommended_target\"] = inventory[\"connection_string\"].apply(recommend_target)\n",
        "inventory[[\"system\", \"recommended_target\"]]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 7: Prioritize migration work by metadata gaps and risk level\n",
        "\n",
        "The blog included a SQL prioritization query. This Python version applies the same logic: metadata gaps come first because undocumented connections are unsafe, then high-risk workloads, then standard items. This helps define migration waves with evidence instead of optimism."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "priority_df = pd.DataFrame([\n",
        "    {\n",
        "        \"system_name\": \"sales-etl\",\n",
        "        \"owner_name\": \"data-eng\",\n",
        "        \"environment_name\": \"prod\",\n",
        "        \"risk_classification\": \"high\"\n",
        "    },\n",
        "    {\n",
        "        \"system_name\": \"finance-report\",\n",
        "        \"owner_name\": None,\n",
        "        \"environment_name\": \"prod\",\n",
        "        \"risk_classification\": None\n",
        "    },\n",
        "    {\n",
        "        \"system_name\": \"lake-report\",\n",
        "        \"owner_name\": \"analytics-platform\",\n",
        "        \"environment_name\": \"prod\",\n",
        "        \"risk_classification\": \"medium\"\n",
        "    },\n",
        "    {\n",
        "        \"system_name\": \"ad-hoc-model\",\n",
        "        \"owner_name\": \"analyst\",\n",
        "        \"environment_name\": \"dev\",\n",
        "        \"risk_classification\": \"low\"\n",
        "    }\n",
        "])\n",
        "\n",
        "def migration_priority(row):\n",
        "    if pd.isna(row[\"owner_name\"]) or pd.isna(row[\"environment_name\"]) or pd.isna(row[\"risk_classification\"]):\n",
        "        return \"metadata-gap\"\n",
        "    if row[\"risk_classification\"] == \"high\":\n",
        "        return \"migrate-first\"\n",
        "    return \"standard\"\n",
        "\n",
        "priority_df[\"migration_priority\"] = priority_df.apply(migration_priority, axis=1)\n",
        "priority_order = {\"metadata-gap\": 0, \"migrate-first\": 1, \"standard\": 2}\n",
        "priority_df = priority_df.sort_values(\n",
        "    by=[\"migration_priority\", \"system_name\"],\n",
        "    key=lambda s: s.map(priority_order) if s.name == \"migration_priority\" else s\n",
        ").reset_index(drop=True)\n",
        "priority_df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 8: Separate transition candidates from approved exceptions and retirements\n",
        "\n",
        "A key lesson from the playbook is category discipline. Not all ODBC usage is the same problem. This example classifies discovered items into migration wave 1, migrate after remediation, approved exception, or retire entirely based on workload shape, ownership, and status."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "estate = sample_connections.copy()\n",
        "\n",
        "def classify_outcome(row):\n",
        "    conn = str(row[\"connection_string\"]).lower()\n",
        "    tool = str(row[\"consuming_tool\"]).lower()\n",
        "    status = str(row[\"status\"]).lower()\n",
        "    owner_missing = pd.isna(row[\"owner_name\"])\n",
        "    env_missing = pd.isna(row[\"environment_name\"])\n",
        "\n",
        "    if status == \"retire_candidate\":\n",
        "        return \"retire entirely\"\n",
        "    if \"spark-sql-client\" in tool or \"lakehouse\" in conn:\n",
        "        return \"retain as approved exception\"\n",
        "    if owner_missing or env_missing:\n",
        "        return \"migrate after remediation\"\n",
        "    if row[\"business_criticality\"] in {\"low\", \"medium\"}:\n",
        "        return \"migrate in wave 1\"\n",
        "    return \"migrate after remediation\"\n",
        "\n",
        "estate[\"recommended_outcome\"] = estate.apply(classify_outcome, axis=1)\n",
        "estate[[\"system_name\", \"consuming_tool\", \"business_criticality\", \"status\", \"recommended_outcome\"]]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 9: Validate wave results and compare operational metrics\n",
        "\n",
        "Migration success is not just connection establishment. Teams should compare refresh success, execution duration, incident triage time, and rollback events before declaring victory. This example simulates first-wave outcomes similar to the ones described in the playbook."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "wave_results = pd.DataFrame([\n",
        "    {\"system\": \"sales-etl\", \"cutover_completed\": True, \"rollback_used\": False, \"refresh_success_before\": 0.91, \"refresh_success_after\": 0.98, \"triage_minutes_before\": 95, \"triage_minutes_after\": 28, \"duration_change_pct\": -5},\n",
        "    {\"system\": \"finance-report\", \"cutover_completed\": False, \"rollback_used\": False, \"refresh_success_before\": 0.88, \"refresh_success_after\": 0.88, \"triage_minutes_before\": 110, \"triage_minutes_after\": 110, \"duration_change_pct\": 12},\n",
        "    {\"system\": \"lake-report\", \"cutover_completed\": False, \"rollback_used\": False, \"refresh_success_before\": 0.96, \"refresh_success_after\": 0.96, \"triage_minutes_before\": 60, \"triage_minutes_after\": 35, \"duration_change_pct\": 0},\n",
        "    {\"system\": \"ad-hoc-model\", \"cutover_completed\": True, \"rollback_used\": False, \"refresh_success_before\": 0.90, \"refresh_success_after\": 0.99, \"triage_minutes_before\": 40, \"triage_minutes_after\": 15, \"duration_change_pct\": -2}\n",
        "])\n",
        "\n",
        "summary = {\n",
        "    \"completed_cutovers\": int(wave_results[\"cutover_completed\"].sum()),\n",
        "    \"total_workloads\": int(len(wave_results)),\n",
        "    \"rollback_events\": int(wave_results[\"rollback_used\"].sum()),\n",
        "    \"avg_refresh_success_before\": round(wave_results[\"refresh_success_before\"].mean(), 3),\n",
        "    \"avg_refresh_success_after\": round(wave_results[\"refresh_success_after\"].mean(), 3),\n",
        "    \"median_triage_before\": float(wave_results[\"triage_minutes_before\"].median()),\n",
        "    \"median_triage_after\": float(wave_results[\"triage_minutes_after\"].median())\n",
        "}\n",
        "\n",
        "print(summary)\n",
        "wave_results"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Example 10: Build a lightweight connector register for ongoing governance\n",
        "\n",
        "A migration wave is temporary; connectivity hygiene is ongoing. This final validation step creates a connector register with connection type, supported pattern, owner, environments, exception status, and review dates so the estate does not drift back into undocumented sprawl."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "connector_register = pd.DataFrame([\n",
        "    {\n",
        "        \"connection_type\": \"Power Query legacy embedded ODBC\",\n",
        "        \"supported_pattern\": \"Transition toward ADBC-aligned or Fabric-native supported path\",\n",
        "        \"owner\": \"BI Platform\",\n",
        "        \"environments_used\": \"dev,test,prod\",\n",
        "        \"exception_status\": \"not preferred\",\n",
        "        \"last_review_date\": \"2026-09-01\"\n",
        "    },\n",
        "    {\n",
        "        \"connection_type\": \"Spark SQL ODBC-compatible client\",\n",
        "        \"supported_pattern\": \"Allowed by exception with workload validation\",\n",
        "        \"owner\": \"Analytics Platform\",\n",
        "        \"environments_used\": \"prod\",\n",
        "        \"exception_status\": \"approved exception\",\n",
        "        \"last_review_date\": \"2026-09-01\"\n",
        "    },\n",
        "    {\n",
        "        \"connection_type\": \"Fabric Warehouse SQL endpoint\",\n",
        "        \"supported_pattern\": \"preferred lane\",\n",
        "        \"owner\": \"Data Engineering\",\n",
        "        \"environments_used\": \"dev,test,prod\",\n",
        "        \"exception_status\": \"standard\",\n",
        "        \"last_review_date\": \"2026-09-01\"\n",
        "    },\n",
        "    {\n",
        "        \"connection_type\": \"Fabric Lakehouse SQL analytics endpoint\",\n",
        "        \"supported_pattern\": \"preferred lane where workload fits\",\n",
        "        \"owner\": \"Analytics Platform\",\n",
        "        \"environments_used\": \"dev,test,prod\",\n",
        "        \"exception_status\": \"standard\",\n",
        "        \"last_review_date\": \"2026-09-01\"\n",
        "    }\n",
        "])\n",
        "\n",
        "connector_register"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Process diagrams from the playbook\n",
        "\n",
        "The original article included Mermaid diagrams to show the migration flow and validation sequence. Since notebook code cells must remain valid Python here, the diagrams are preserved as plain text for reference.\n",
        "\n",
        "Flow:\n",
        "- Export configs and metadata\n",
        "- Scan for ODBC markers\n",
        "- Normalize into inventory\n",
        "- Classify owner, environment, risk\n",
        "- Prioritize migrations\n",
        "- Refactor to Fabric-native access\n",
        "- Validate queries and refresh jobs\n",
        "- Cut over and monitor\n",
        "\n",
        "Sequence:\n",
        "- Repo/Configs -> Discovery Scan: export files and deployment configs\n",
        "- Discovery Scan -> Inventory Table: insert normalized ODBC findings\n",
        "- Inventory Table -> Analytics Team: highlight owner/environment/risk gaps\n",
        "- Analytics Team -> Fabric Target: refactor to native endpoint\n",
        "- Fabric Target -> Analytics Team: validate queries and refresh"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Summary\n",
        "\n",
        "This notebook validated the main migration playbook steps: inventory every connection, separate embedded ODBC transition candidates from valid ODBC-compatible exceptions, classify risk before touching production paths, recommend Fabric-friendly targets, and measure outcomes with rollback discipline already defined.\n",
        "\n",
        "## Next Steps\n",
        "\n",
        "1. Replace the sample `exports`, `repo`, and `deploy` folders with real exported metadata and repository roots.\n",
        "2. Extend the discovery markers for your stack, including gateway configs, notebooks, CI/CD assets, and scheduled jobs.\n",
        "3. Persist the inventory and connector register to your governed data store.\n",
        "4. Add owner enforcement, review dates, and exception retirement triggers.\n",
        "5. Run a low-risk migration wave first, then compare parity, duration, and incident metrics before scaling."
      ]
    }
  ]
}