{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# DP-700 Episode 7 \u2013 Batch Transformation with PySpark (Live Demo)\n\n**DashWiseBI \u00b7 Microsoft Fabric User Group Pune**\nCompanion notebook for the blog post: [DP-700 Episode 7: Batch Transformation with PySpark, SQL & KQL](https://www.dashwisebi.com/blog/dp-700-episode-7-batch-transformation-pyspark-sql-kql)\n\n### What this demo covers\n| # | Section | Blog section / exam skill |\n|---|---------|---------------------------|\n| 0 | Setup | \u2013 |\n| 1 | Generate landing data (dirty orders CSV, nested JSON, normalized source tables) | Demo step 1 |\n| 2 | Bronze ingest + lazy evaluation | 2 \u00b7 Transform data |\n| 3 | Core transformations: `select`, `where`, `withColumn`, `cast`, `when/otherwise`, `union` vs `unionByName` | 2 \u00b7 Transform data |\n| 4 | Duplicates: detect, `distinct`, `dropDuplicates` trap, `row_number` latest-wins | 5 \u00b7 Duplicates |\n| 5 | Missing data: profile, standardize placeholders, `fillna`, flags, fill-forward | 5 \u00b7 Missing data |\n| 6 | Silver with Delta `MERGE`: guarded upsert, failing MERGE, idempotency check | 5 \u00b7 Upserts |\n| 7 | Denormalize: flatten snowflake \u2192 `dim_product`, Unknown member, row explosion, `broadcast` | 3 \u00b7 Denormalize |\n| 8 | Group & aggregate: grain, `agg`, `rollup`, `cube`, `pivot`, window functions | 4 \u00b7 Aggregate |\n| 9 | Late-arriving data: batch 2, watermark vs event-date load, late dimension, `replaceWhere`, `mergeSchema` | 5 \u00b7 Late data |\n| 10 | Flatten nested JSON: dot notation, `explode` vs `explode_outer` | Demo step 4 |\n| 11 | Same ideas in Spark SQL (`%%sql`): CTE dedup, `MERGE INTO`, `ROLLUP`, time travel | 2 \u00b7 Spark SQL |\n| 12 | Exam tips recap + cleanup | Exam tips |\n\n### Before you run\n1. Create (or open) a **Lakehouse** in a Fabric workspace (trial capacity is fine).\n2. Attach it to this notebook as the **default lakehouse** (Explorer pane \u2192 *Add lakehouse*).\n3. Use **Fabric Runtime 1.3** (Spark 3.5, Delta 3.2) or later. Run the cells top to bottom.\n\nThe notebook creates all its own data, so nothing needs to be uploaded.",
   "id": "cell-00"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 0 \u00b7 Setup\n`RESET_DEMO = True` drops the demo tables so the session always starts clean. All tables are prefixed with `ep7_` so they don't clash with anything else in your lakehouse.",
   "id": "cell-01"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "from pyspark.sql import functions as F\nfrom pyspark.sql import Window\nfrom pyspark.sql.types import (StructType, StructField, StringType, IntegerType,\n                               LongType, DecimalType, DateType)\nfrom delta.tables import DeltaTable\nimport os, json\n\n# Two views of the same folder in the default lakehouse\nLANDING_FS    = \"/lakehouse/default/Files/ep7_landing\"   # local file API (mounted default lakehouse)\nLANDING_SPARK = \"Files/ep7_landing\"                      # Spark relative path to the same folder\n\nRESET_DEMO = True\nP = \"ep7_\"   # table prefix\nTABLES = [\"bronze_orders\", \"silver_orders\", \"src_category\", \"src_subcategory\", \"src_product\",\n          \"src_customer\", \"dim_product\", \"dim_customer\", \"gold_sales_wide\",\n          \"gold_monthly_sales\", \"gold_daily_sales\", \"scratch_merge_demo\"]\n\nspark.conf.set(\"spark.sql.session.timeZone\", \"UTC\")\n\nif RESET_DEMO:\n    for t in TABLES:\n        spark.sql(f\"DROP TABLE IF EXISTS {P}{t}\")\n    print(\"Demo tables dropped.\")\n\nprint(\"Spark version:\", spark.version)",
   "id": "cell-02"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 1 \u00b7 Generate the landing data\nWe write a deliberately **dirty** orders file into `Files/ep7_landing/orders/`. Everything the blog warns about is planted here:\n\n| Problem | Where |\n|---|---|\n| Exact duplicate row | `OrderID 1005` appears twice, identical |\n| Two versions of one key | `OrderID 1002`: *Pending* (older) first, *Shipped* (newer) at the **end** of the file |\n| Missing numeric | `OrderID 1003` has no `Qty` |\n| Placeholder / dirty text | `Country` = `N/A`, empty, `\"  india \"`, `india` |\n| Missing dimension (never arrives) | `CustomerID C999`, `ProductID P99` |\n| Late dimension (arrives in batch 2) | `CustomerID C005` |",
   "id": "cell-03"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "orders_batch_01 = \"\"\"OrderID,CustomerID,ProductID,OrderDate,Qty,UnitPrice,Country,Status,ModifiedAt\n1001,C001,P01,2026-10-01,2,25.00,India,Shipped,2026-10-01 09:00:00\n1002,C002,P02,2026-10-01,1,120.00,India,Pending,2026-10-01 10:00:00\n1003,C003,P03,2026-10-02,,15.50,USA,Shipped,2026-10-02 11:00:00\n1004,C001,P01,2026-10-02,3,25.00,N/A,Shipped,2026-10-02 12:00:00\n1005,C004,P04,2026-10-03,5,8.75,UK,Shipped,2026-10-03 09:30:00\n1005,C004,P04,2026-10-03,5,8.75,UK,Shipped,2026-10-03 09:30:00\n1006,C999,P02,2026-10-04,2,120.00,  india ,Pending,2026-10-04 10:00:00\n1007,C002,P99,2026-10-05,1,60.00,USA,Shipped,2026-10-05 15:00:00\n1008,C003,P03,2026-10-06,4,15.50,,Shipped,2026-10-06 16:00:00\n1009,C005,P05,2026-10-15,2,45.00,Germany,Shipped,2026-10-15 10:00:00\n1011,C001,P02,2026-10-20,1,120.00,india,Shipped,2026-10-20 13:00:00\n1012,C004,P05,2026-10-28,3,45.00,UK,Cancelled,2026-10-28 09:00:00\n1013,C002,P01,2026-11-02,6,25.00,India,Shipped,2026-11-02 10:00:00\n1014,C003,P04,2026-11-05,10,8.75,USA,Shipped,2026-11-05 11:00:00\n1002,C002,P02,2026-10-01,1,120.00,India,Shipped,2026-10-02 08:00:00\n\"\"\"\n\n# Nested JSON (one order per line) for the flattening demo in section 10\norders_json = [\n    {\"OrderID\": 2001, \"OrderDate\": \"2026-11-01\",\n     \"Customer\": {\"CustomerID\": \"C001\", \"City\": \"Pune\"},\n     \"Items\": [{\"ProductID\": \"P01\", \"Qty\": 2, \"UnitPrice\": 25.0},\n               {\"ProductID\": \"P03\", \"Qty\": 1, \"UnitPrice\": 15.5}]},\n    {\"OrderID\": 2002, \"OrderDate\": \"2026-11-02\",\n     \"Customer\": {\"CustomerID\": \"C002\", \"City\": \"Mumbai\"},\n     \"Items\": [{\"ProductID\": \"P02\", \"Qty\": 1, \"UnitPrice\": 120.0}]},\n    {\"OrderID\": 2003, \"OrderDate\": \"2026-11-03\",\n     \"Customer\": {\"CustomerID\": \"C003\", \"City\": \"Nagpur\"},\n     \"Items\": []},   # order with no lines: explode drops it, explode_outer keeps it\n]\n\ndef write_text(path, text):\n    os.makedirs(os.path.dirname(path), exist_ok=True)\n    with open(path, \"w\") as f:\n        f.write(text)\n\nwrite_text(f\"{LANDING_FS}/orders/orders_batch_01.csv\", orders_batch_01)\nwrite_text(f\"{LANDING_FS}/orders_json/orders_2026_11.json\",\n           \"\\n\".join(json.dumps(o) for o in orders_json))\nprint(\"Landing files written:\", os.listdir(f\"{LANDING_FS}/orders\"), os.listdir(f\"{LANDING_FS}/orders_json\"))",
   "id": "cell-04"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Normalized **source** tables (Category \u2192 Subcategory \u2192 Product snowflake, plus Customer). Section 7 flattens these into dimensions.\nNote that customer `C005` is **not** here yet; it arrives late in section 9.",
   "id": "cell-05"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "spark.createDataFrame(\n    [(1, \"Electronics\"), (2, \"Home\"), (3, \"Office\")],\n    \"CategoryKey INT, CategoryName STRING\"\n).write.mode(\"overwrite\").saveAsTable(f\"{P}src_category\")\n\nspark.createDataFrame(\n    [(10, \"Audio\", 1), (11, \"Accessories\", 1), (20, \"Kitchen\", 2), (30, \"Stationery\", 3)],\n    \"SubcategoryKey INT, SubcategoryName STRING, CategoryKey INT\"\n).write.mode(\"overwrite\").saveAsTable(f\"{P}src_subcategory\")\n\nspark.createDataFrame(\n    [(1, \"P01\", \"USB-C Cable\",        11, 25.00),\n     (2, \"P02\", \"Bluetooth Speaker\",  10, 120.00),\n     (3, \"P03\", \"Notebook A5\",        30, 15.50),\n     (4, \"P04\", \"Coffee Mug\",         20, 8.75),\n     (5, \"P05\", \"Desk Lamp\",          30, 45.00)],\n    \"ProductKey INT, ProductID STRING, ProductName STRING, SubcategoryKey INT, ListPrice DOUBLE\"\n).write.mode(\"overwrite\").saveAsTable(f\"{P}src_product\")\n\nspark.createDataFrame(\n    [(1, \"C001\", \"Asha Traders\",   \"Retail\",    \"Pune\"),\n     (2, \"C002\", \"Bright Office\",  \"Corporate\", \"Mumbai\"),\n     (3, \"C003\", \"Campus Store\",   \"Education\", \"Nagpur\"),\n     (4, \"C004\", \"Delta Supplies\", \"Retail\",    \"London\")],\n    \"CustomerKey INT, CustomerID STRING, CustomerName STRING, Segment STRING, City STRING\"\n).write.mode(\"overwrite\").saveAsTable(f\"{P}src_customer\")\n\nprint(\"Source tables created.\")",
   "id": "cell-06"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 2 \u00b7 Bronze: land the raw file as-is\nBronze keeps the data **exactly as received** (all strings) plus lineage columns. We read without `inferSchema` on purpose; typing happens in silver.\n\n**Lazy evaluation:** `spark.read`, `withColumn`, `select` and `where` only build a plan. Nothing runs until an **action** (`count`, `show`, `display`, `collect`, `write`).",
   "id": "cell-07"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "bronze_b1 = (\n    spark.read\n         .option(\"header\", True)\n         .csv(f\"{LANDING_SPARK}/orders/orders_batch_01.csv\")\n         .withColumn(\"_source_file\", F.col(\"_metadata.file_path\"))\n         .withColumn(\"_ingested_at\", F.current_timestamp())\n)\n\n# Still nothing has executed. Look at the plan:\nbronze_b1.explain()",
   "id": "cell-08"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "# An action triggers the job\nprint(\"Rows in batch 1:\", bronze_b1.count())\n\nbronze_b1.write.mode(\"overwrite\").saveAsTable(f\"{P}bronze_orders\")\ndisplay(spark.table(f\"{P}bronze_orders\"))",
   "id": "cell-09"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 3 \u00b7 Core transformations\n`select` + `cast` to type the columns, `withColumn` for derived columns, `F.when().otherwise()` for conditional logic, `where`/`filter` to subset.",
   "id": "cell-10"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "orders_typed = (\n    spark.table(f\"{P}bronze_orders\")\n         .select(\n             F.col(\"OrderID\").cast(\"int\").alias(\"OrderID\"),\n             F.trim(\"CustomerID\").alias(\"CustomerID\"),\n             F.trim(\"ProductID\").alias(\"ProductID\"),\n             F.to_date(\"OrderDate\", \"yyyy-MM-dd\").alias(\"OrderDate\"),\n             F.col(\"Qty\").cast(\"int\").alias(\"Qty\"),\n             F.col(\"UnitPrice\").cast(\"decimal(10,2)\").alias(\"UnitPrice\"),\n             F.col(\"Country\"),\n             F.col(\"Status\"),\n             F.to_timestamp(\"ModifiedAt\").alias(\"ModifiedAt\"),\n         )\n         .withColumn(\"LineAmount\", F.col(\"Qty\") * F.col(\"UnitPrice\"))\n         .withColumn(\"OrderSize\",\n                     F.when(F.col(\"Qty\") >= 5, \"Large\")\n                      .when(F.col(\"Qty\") >= 2, \"Medium\")\n                      .when(F.col(\"Qty\").isNotNull(), \"Small\")\n                      .otherwise(\"Unknown\"))\n)\n\norders_typed.printSchema()\ndisplay(orders_typed.where(F.col(\"Status\") != \"Cancelled\").orderBy(\"OrderID\"))",
   "id": "cell-11"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### `union` vs `unionByName`\n`union` matches columns **by position**, so two frames with the same columns in a different order are silently corrupted. `unionByName` matches **by name**, and `allowMissingColumns=True` fills columns missing on one side with nulls.",
   "id": "cell-12"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "a = spark.createDataFrame([(1, \"India\")], \"OrderID INT, Country STRING\")\nb = spark.createDataFrame([(\"UK\", 2)],    \"Country STRING, OrderID INT\")   # same columns, different order\nc = spark.createDataFrame([(3, \"USA\", \"Web\")], \"OrderID INT, Country STRING, Channel STRING\")\n\nprint(\"union (positional): values land in the wrong columns\")\na.union(b).show()\n\nprint(\"unionByName: matched by name\")\na.unionByName(b).show()\n\nprint(\"unionByName(allowMissingColumns=True): Channel filled with null\")\na.unionByName(c, allowMissingColumns=True).show()",
   "id": "cell-13"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 4 \u00b7 Duplicates\nThree kinds: **exact copies**, **multiple versions of one key**, and **repeated deliveries** of the same event (we'll see the last one in batch 2).\n\n### 4.1 Detect: group by the key and keep groups with more than one row",
   "id": "cell-14"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "display(\n    orders_typed.groupBy(\"OrderID\")\n                .agg(F.count(\"*\").alias(\"Copies\"),\n                     F.countDistinct(\"ModifiedAt\").alias(\"Versions\"))\n                .where(\"Copies > 1\")\n)",
   "id": "cell-15"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 4.2 `distinct()` only removes exact copies\n`distinct()` compares **all** columns: 1005 collapses to one row, but both versions of 1002 survive.",
   "id": "cell-16"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "print(\"Rows:\", orders_typed.count(), \"| after distinct():\", orders_typed.distinct().count())\ndisplay(orders_typed.distinct().where(\"OrderID IN (1002, 1005)\").orderBy(\"OrderID\", \"ModifiedAt\"))",
   "id": "cell-17"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 4.3 The `dropDuplicates(key)` trap\n`dropDuplicates([\"OrderID\"])` keeps **an arbitrary row** per key. Nothing guarantees it is the newest one. Here it usually keeps the first row it meets, the stale *Pending* version of 1002. Even when it happens to pick the right one, that is luck, not logic.",
   "id": "cell-18"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "display(orders_typed.dropDuplicates([\"OrderID\"]).where(\"OrderID = 1002\")\n                    .select(\"OrderID\", \"Status\", \"ModifiedAt\"))",
   "id": "cell-19"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 4.4 Latest wins: `row_number()` over a window\nPartition by the business key, order by `ModifiedAt DESC`, keep `rn = 1`. This is deterministic, and it is the answer to the exam question *\"the wrong version survives deduplication\"*.",
   "id": "cell-20"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "w_latest = Window.partitionBy(\"OrderID\").orderBy(F.col(\"ModifiedAt\").desc())\n\norders_dedup = (orders_typed\n                .withColumn(\"rn\", F.row_number().over(w_latest))\n                .where(\"rn = 1\")\n                .drop(\"rn\"))\n\nprint(\"Rows after latest-wins dedup:\", orders_dedup.count())\ndisplay(orders_dedup.where(\"OrderID IN (1002, 1005)\").select(\"OrderID\", \"Status\", \"ModifiedAt\"))",
   "id": "cell-21"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 5 \u00b7 Missing data\nOrder of operations: **profile \u2192 standardize placeholders \u2192 decide (drop / replace / impute / flag)**.\n\n### 5.1 Profile nulls per column",
   "id": "cell-22"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "display(orders_dedup.select([F.sum(F.col(c).isNull().cast(\"int\")).alias(c)\n                             for c in orders_dedup.columns]))",
   "id": "cell-23"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Only one `Country` shows as null, but `N/A`, `\"  india \"` and `india` are also problems. **Standardize placeholders first**, or the null count lies.\n\n### 5.2 Standardize placeholders and casing",
   "id": "cell-24"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "PLACEHOLDERS = [\"\", \"N/A\", \"NA\", \"NULL\", \"-\", \"UNKNOWN\"]\n\norders_std = orders_dedup.withColumn(\n    \"Country\",\n    F.when(F.upper(F.trim(\"Country\")).isin(PLACEHOLDERS), F.lit(None))\n     .when(F.length(F.trim(\"Country\")) <= 3, F.upper(F.trim(\"Country\")))   # codes: UK, USA\n     .otherwise(F.initcap(F.trim(\"Country\")))                              # names: India\n)\n\ndisplay(orders_std.groupBy(\"Country\").count().orderBy(\"Country\"))",
   "id": "cell-25"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 5.3 `fillna` is type-aware, and filling with 0 distorts averages\n`fillna({...})` with a dict fills each column with a value of the matching type. Watch what happens to the **average quantity** when the missing `Qty` becomes `0`.",
   "id": "cell-26"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "filled_zero = orders_std.fillna({\"Country\": \"Unknown\", \"Qty\": 0})\n\ndisplay(\n    orders_std.agg(F.avg(\"Qty\").alias(\"AvgQty_nulls_ignored\"))\n    .crossJoin(filled_zero.agg(F.avg(\"Qty\").alias(\"AvgQty_after_fill_0\")))\n)",
   "id": "cell-27"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**Decision for silver:** fill `Country` with `\"Unknown\"`, keep `Qty` as **null**, and add an indicator column so the gap stays visible and explainable.",
   "id": "cell-28"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "orders_clean = (orders_std\n                .withColumn(\"QtyMissing\", F.col(\"Qty\").isNull())\n                .fillna({\"Country\": \"Unknown\"}))\n\ndisplay(orders_clean.orderBy(\"OrderID\"))",
   "id": "cell-29"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 5.4 Fill forward with `last(..., ignorenulls=True)` over a window\nTypical for prices, rates or sensor readings: carry the last known value forward within each key.",
   "id": "cell-30"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "prices = spark.createDataFrame(\n    [(\"P01\", \"2026-11-01\", 25.0), (\"P01\", \"2026-11-02\", None), (\"P01\", \"2026-11-03\", None),\n     (\"P01\", \"2026-11-04\", 26.0), (\"P02\", \"2026-11-01\", 120.0), (\"P02\", \"2026-11-02\", None)],\n    \"ProductID STRING, PriceDate STRING, Price DOUBLE\"\n).withColumn(\"PriceDate\", F.to_date(\"PriceDate\"))\n\nw_ffill = (Window.partitionBy(\"ProductID\").orderBy(\"PriceDate\")\n                 .rowsBetween(Window.unboundedPreceding, Window.currentRow))\n\ndisplay(prices.withColumn(\"PriceFilled\", F.last(\"Price\", ignorenulls=True).over(w_ffill))\n              .orderBy(\"ProductID\", \"PriceDate\"))",
   "id": "cell-31"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 6 \u00b7 Silver with Delta `MERGE`\nWe package sections 3 to 5 into one reusable function, `to_silver()`, then **upsert** into `silver_orders`.\n\nThe MERGE is guarded with `s.ModifiedAt > t.ModifiedAt`, so an older or repeated row can never overwrite a newer one. That guard is what makes the load **idempotent**.",
   "id": "cell-32"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "def to_silver(bronze_df):\n    \"\"\"Type, standardize, dedup (latest wins) and flag missing values.\"\"\"\n    w = Window.partitionBy(\"OrderID\").orderBy(F.col(\"ModifiedAt\").desc())\n    return (\n        bronze_df.select(\n            F.col(\"OrderID\").cast(\"int\").alias(\"OrderID\"),\n            F.trim(\"CustomerID\").alias(\"CustomerID\"),\n            F.trim(\"ProductID\").alias(\"ProductID\"),\n            F.to_date(\"OrderDate\", \"yyyy-MM-dd\").alias(\"OrderDate\"),\n            F.col(\"Qty\").cast(\"int\").alias(\"Qty\"),\n            F.col(\"UnitPrice\").cast(\"decimal(10,2)\").alias(\"UnitPrice\"),\n            F.when(F.upper(F.trim(\"Country\")).isin(PLACEHOLDERS), F.lit(None))\n             .when(F.length(F.trim(\"Country\")) <= 3, F.upper(F.trim(\"Country\")))\n             .otherwise(F.initcap(F.trim(\"Country\"))).alias(\"Country\"),\n            F.trim(\"Status\").alias(\"Status\"),\n            F.to_timestamp(\"ModifiedAt\").alias(\"ModifiedAt\"),\n        )\n        .withColumn(\"rn\", F.row_number().over(w)).where(\"rn = 1\").drop(\"rn\")\n        .withColumn(\"LineAmount\", F.col(\"Qty\") * F.col(\"UnitPrice\"))\n        .withColumn(\"QtyMissing\", F.col(\"Qty\").isNull())\n        .fillna({\"Country\": \"Unknown\"})\n    )\n\n\ndef upsert_silver(source_df):\n    target = DeltaTable.forName(spark, f\"{P}silver_orders\")\n    (target.alias(\"t\")\n           .merge(source_df.alias(\"s\"), \"t.OrderID = s.OrderID\")\n           .whenMatchedUpdateAll(condition=\"s.ModifiedAt > t.ModifiedAt\")\n           .whenNotMatchedInsertAll()\n           .execute())\n\n\ndef fingerprint(table):\n    \"\"\"Row count + order-independent checksum, used to prove idempotency.\"\"\"\n    df = spark.table(table)\n    return df.select(F.count(\"*\").alias(\"rows\"),\n                     F.sum(F.xxhash64(*df.columns).cast(\"decimal(38,0)\")).alias(\"checksum\")).first()\n\n\ndef last_merge_metrics(table):\n    return (DeltaTable.forName(spark, table).history(1)\n            .select(\"version\", \"operation\", \"operationMetrics.numSourceRows\",\n                    \"operationMetrics.numTargetRowsInserted\", \"operationMetrics.numTargetRowsUpdated\"))",
   "id": "cell-33"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "silver_b1 = to_silver(spark.table(f\"{P}bronze_orders\"))\n\n# Create the empty silver table with the right schema, then load it via MERGE\nif not spark.catalog.tableExists(f\"{P}silver_orders\"):\n    silver_b1.limit(0).write.saveAsTable(f\"{P}silver_orders\")\n\nupsert_silver(silver_b1)\n\ndisplay(last_merge_metrics(f\"{P}silver_orders\"))\ndisplay(spark.table(f\"{P}silver_orders\").orderBy(\"OrderID\"))",
   "id": "cell-34"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 6.1 Why MERGE fails on a dirty source\nA MERGE fails when **several source rows match one target row** and would update it. Here we merge the *un-deduplicated* typed rows (two 1002s, two 1005s) with an unguarded update into a scratch copy of silver.\n**Fix:** deduplicate the source to one row per key first, which `to_silver()` already does.",
   "id": "cell-35"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "spark.table(f\"{P}silver_orders\").write.mode(\"overwrite\").saveAsTable(f\"{P}scratch_merge_demo\")\n\ndirty_source = orders_typed.withColumn(\"QtyMissing\", F.col(\"Qty\").isNull()).drop(\"OrderSize\")\n\ntry:\n    (DeltaTable.forName(spark, f\"{P}scratch_merge_demo\").alias(\"t\")\n        .merge(dirty_source.alias(\"s\"), \"t.OrderID = s.OrderID\")\n        .whenMatchedUpdateAll()          # no guard, no dedup\n        .whenNotMatchedInsertAll()\n        .execute())\n    print(\"MERGE succeeded (unexpected)\")\nexcept Exception as e:\n    print(\"MERGE failed as expected:\\n\", str(e).splitlines()[0][:300])",
   "id": "cell-36"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 6.2 Prove idempotency: run the same load twice\nSame input twice \u2192 same table. The second MERGE inserts 0 and updates 0 rows.",
   "id": "cell-37"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "before = fingerprint(f\"{P}silver_orders\")\nupsert_silver(to_silver(spark.table(f\"{P}bronze_orders\")))   # rerun the same batch\nafter = fingerprint(f\"{P}silver_orders\")\n\nprint(\"Before:\", before)\nprint(\"After: \", after)\nprint(\"Idempotent:\", before == after)\ndisplay(last_merge_metrics(f\"{P}silver_orders\"))",
   "id": "cell-38"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 7 \u00b7 Denormalize toward gold\n**Normalize at the source, denormalize toward gold.** First we flatten the Product \u2192 Subcategory \u2192 Category snowflake into a single `dim_product`, and add an **Unknown member (-1)** to each dimension.",
   "id": "cell-39"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "def build_dim_product():\n    dim = (spark.table(f\"{P}src_product\")\n           .join(spark.table(f\"{P}src_subcategory\"), \"SubcategoryKey\", \"left\")\n           .join(spark.table(f\"{P}src_category\"), \"CategoryKey\", \"left\")\n           .select(\"ProductKey\", \"ProductID\", \"ProductName\", \"SubcategoryName\", \"CategoryName\", \"ListPrice\"))\n    unknown = spark.createDataFrame([(-1, \"N/A\", \"Unknown\", \"Unknown\", \"Unknown\", None)], dim.schema)\n    dim.unionByName(unknown).write.mode(\"overwrite\").saveAsTable(f\"{P}dim_product\")\n\n\ndef build_dim_customer():\n    dim = spark.table(f\"{P}src_customer\")\n    unknown = spark.createDataFrame([(-1, \"N/A\", \"Unknown\", \"Unknown\", \"Unknown\")], dim.schema)\n    dim.unionByName(unknown).write.mode(\"overwrite\").saveAsTable(f\"{P}dim_customer\")\n\n\nbuild_dim_product()\nbuild_dim_customer()\ndisplay(spark.table(f\"{P}dim_product\").orderBy(\"ProductKey\"))",
   "id": "cell-40"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 7.1 The row-explosion trap\nIf the dimension key is **not unique**, the join multiplies fact rows and inflates every total. Always check uniqueness before you join, because Fabric keys are *NOT ENFORCED*.",
   "id": "cell-41"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "bad_dim = spark.table(f\"{P}dim_product\").unionByName(\n    spark.table(f\"{P}dim_product\").where(\"ProductID = 'P01'\"))   # P01 now appears twice\n\nsilver = spark.table(f\"{P}silver_orders\")\nprint(\"Fact rows:\", silver.count(),\n      \"| after join to bad dim:\", silver.join(bad_dim, \"ProductID\", \"left\").count())\n\ndef assert_unique(df, key):\n    dupes = df.groupBy(key).count().where(\"count > 1\")\n    n = dupes.count()\n    if n:\n        display(dupes)\n        raise ValueError(f\"{n} duplicate value(s) in key '{key}'\")\n    print(f\"OK: '{key}' is unique\")\n\nassert_unique(spark.table(f\"{P}dim_product\"), \"ProductID\")\ntry:\n    assert_unique(bad_dim, \"ProductID\")\nexcept ValueError as e:\n    print(\"Caught:\", e)",
   "id": "cell-42"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 7.2 Build the wide gold table\n**Left join from the fact** so no sale is lost, map unmatched keys to **-1**, and `broadcast` the small dimensions to avoid a shuffle.",
   "id": "cell-43"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "def build_gold_sales_wide():\n    f = spark.table(f\"{P}silver_orders\").alias(\"f\")\n    p = spark.table(f\"{P}dim_product\").alias(\"p\")\n    c = spark.table(f\"{P}dim_customer\").alias(\"c\")\n    wide = (\n        f.join(F.broadcast(p), F.col(\"f.ProductID\") == F.col(\"p.ProductID\"), \"left\")\n         .join(F.broadcast(c), F.col(\"f.CustomerID\") == F.col(\"c.CustomerID\"), \"left\")\n         .select(\n             \"f.OrderID\", \"f.OrderDate\", \"f.Status\", \"f.Country\", \"f.Qty\", \"f.UnitPrice\", \"f.LineAmount\",\n             F.coalesce(\"p.ProductKey\", F.lit(-1)).alias(\"ProductKey\"),\n             F.coalesce(\"p.ProductName\", F.lit(\"Unknown\")).alias(\"ProductName\"),\n             F.coalesce(\"p.CategoryName\", F.lit(\"Unknown\")).alias(\"CategoryName\"),\n             F.coalesce(\"c.CustomerKey\", F.lit(-1)).alias(\"CustomerKey\"),\n             F.coalesce(\"c.CustomerName\", F.lit(\"Unknown\")).alias(\"CustomerName\"),\n             F.coalesce(\"c.Segment\", F.lit(\"Unknown\")).alias(\"Segment\"),\n             F.col(\"f.CustomerID\").alias(\"SourceCustomerID\"),\n             F.col(\"f.ProductID\").alias(\"SourceProductID\"),\n         )\n    )\n    wide.write.mode(\"overwrite\").option(\"overwriteSchema\", True).saveAsTable(f\"{P}gold_sales_wide\")\n\n\nbuild_gold_sales_wide()\ngold = spark.table(f\"{P}gold_sales_wide\")\nprint(\"Silver rows:\", spark.table(f\"{P}silver_orders\").count(), \"| Gold rows:\", gold.count())\n\nprint(\"Rows mapped to the Unknown member:\")\ndisplay(gold.where(\"ProductKey = -1 OR CustomerKey = -1\")\n            .select(\"OrderID\", \"SourceCustomerID\", \"CustomerKey\", \"SourceProductID\", \"ProductKey\"))",
   "id": "cell-44"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 8 \u00b7 Group and aggregate\n**Pick the grain first.** Here: one row per **month \u00d7 category**, excluding cancelled orders.",
   "id": "cell-45"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "sales = (spark.table(f\"{P}gold_sales_wide\")\n              .where(\"Status <> 'Cancelled'\")\n              .withColumn(\"Month\", F.trunc(\"OrderDate\", \"month\")))\n\nmonthly = (sales.groupBy(\"Month\", \"CategoryName\")\n                .agg(F.sum(\"LineAmount\").alias(\"Revenue\"),\n                     F.count(\"*\").alias(\"Rows\"),\n                     F.count(\"Qty\").alias(\"RowsWithQty\"),           # count(col) skips nulls\n                     F.countDistinct(\"OrderID\").alias(\"Orders\"),\n                     F.approx_count_distinct(\"CustomerKey\").alias(\"ApproxCustomers\"),\n                     F.avg(\"LineAmount\").alias(\"AvgLine\"),\n                     F.min(\"OrderDate\").alias(\"FirstOrder\"),\n                     F.max(\"OrderDate\").alias(\"LastOrder\"),\n                     F.collect_set(\"ProductName\").alias(\"Products\"))\n                .orderBy(\"Month\", \"CategoryName\"))\n\ndisplay(monthly)",
   "id": "cell-46"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 8.1 `rollup`, `cube` and `pivot`\n`rollup` adds hierarchical subtotals (month total, then grand total). `cube` adds **every** combination. `pivot` turns category values into columns. `grouping_id()` tells subtotal rows apart from real null values.",
   "id": "cell-47"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "display(sales.rollup(\"Month\", \"CategoryName\")\n             .agg(F.sum(\"LineAmount\").alias(\"Revenue\"), F.grouping_id().alias(\"GroupingLevel\"))\n             .orderBy(F.col(\"Month\").asc_nulls_last(), F.col(\"CategoryName\").asc_nulls_last()))",
   "id": "cell-48"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "display(sales.cube(\"Month\", \"Segment\")\n             .agg(F.sum(\"LineAmount\").alias(\"Revenue\"))\n             .orderBy(F.col(\"Month\").asc_nulls_last(), F.col(\"Segment\").asc_nulls_last()))",
   "id": "cell-49"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "categories = [\"Electronics\", \"Home\", \"Office\", \"Unknown\"]   # listing values avoids an extra scan\ndisplay(sales.groupBy(\"Month\").pivot(\"CategoryName\", categories).agg(F.sum(\"LineAmount\")).orderBy(\"Month\"))",
   "id": "cell-50"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 8.2 Window functions: running total, rank, moving average\nWindows keep every row and add a calculation over a partition, unlike `groupBy`, which collapses rows.",
   "id": "cell-51"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "daily = sales.groupBy(\"OrderDate\").agg(F.sum(\"LineAmount\").alias(\"Revenue\"))\n\nw_running = Window.orderBy(\"OrderDate\").rowsBetween(Window.unboundedPreceding, Window.currentRow)\nw_moving3 = Window.orderBy(\"OrderDate\").rowsBetween(-2, Window.currentRow)\n\ndisplay(daily.withColumn(\"RunningRevenue\", F.sum(\"Revenue\").over(w_running))\n             .withColumn(\"Moving3DayAvg\", F.round(F.avg(\"Revenue\").over(w_moving3), 2))\n             .orderBy(\"OrderDate\"))",
   "id": "cell-52"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "w_rank = Window.partitionBy(\"CategoryName\").orderBy(F.col(\"Revenue\").desc())\n\ndisplay(sales.groupBy(\"CategoryName\", \"ProductName\").agg(F.sum(\"LineAmount\").alias(\"Revenue\"))\n             .withColumn(\"RankInCategory\", F.dense_rank().over(w_rank))\n             .orderBy(\"CategoryName\", \"RankInCategory\"))",
   "id": "cell-53"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 8.3 Pitfall: aggregates over empty input\nWithout `groupBy` you get **one row** (with null). With `groupBy` you get **zero rows**. This matters when a downstream step expects a row.",
   "id": "cell-54"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "empty = sales.where(\"1 = 0\")\nprint(\"agg without groupBy:\", empty.agg(F.sum(\"LineAmount\")).collect())\nprint(\"agg with groupBy:   \", empty.groupBy(\"Month\").agg(F.sum(\"LineAmount\")).collect())",
   "id": "cell-55"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "# Persist the gold aggregates\nmonthly.drop(\"Products\").write.mode(\"overwrite\").saveAsTable(f\"{P}gold_monthly_sales\")\ndaily.write.mode(\"overwrite\").saveAsTable(f\"{P}gold_daily_sales\")\nprint(\"gold_monthly_sales and gold_daily_sales written.\")",
   "id": "cell-56"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 9 \u00b7 Late-arriving data (batch 2)\nBatch 2 arrives on **2026-11-10** and contains:\n\n| Row | What it is |\n|---|---|\n| `1002` Delivered | An **update** to an existing order |\n| `1010`, `OrderDate 2026-10-03` | A **late fact**: an old event that arrives today |\n| `1015` from `C005` | A new order |\n| `1013` (same version as batch 1) | A **repeated delivery** |\n| New column `Channel` | **Schema drift** |\n\nAt the same time, customer **C005** finally lands in the source system (a **late dimension**).",
   "id": "cell-57"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "orders_batch_02 = \"\"\"OrderID,CustomerID,ProductID,OrderDate,Qty,UnitPrice,Country,Status,ModifiedAt,Channel\n1002,C002,P02,2026-10-01,1,120.00,India,Delivered,2026-11-10 09:00:00,Web\n1010,C001,P03,2026-10-03,2,15.50,India,Shipped,2026-11-10 09:05:00,Store\n1015,C005,P01,2026-11-09,2,25.00,Germany,Shipped,2026-11-10 09:10:00,Web\n1013,C002,P01,2026-11-02,6,25.00,India,Shipped,2026-11-02 10:00:00,Web\n\"\"\"\nwrite_text(f\"{LANDING_FS}/orders/orders_batch_02.csv\", orders_batch_02)\n\nbronze_b2 = (spark.read.option(\"header\", True)\n                  .csv(f\"{LANDING_SPARK}/orders/orders_batch_02.csv\")\n                  .withColumn(\"_source_file\", F.col(\"_metadata.file_path\"))\n                  .withColumn(\"_ingested_at\", F.current_timestamp()))\n\n# Schema drift: append the new Channel column with mergeSchema (old rows get null)\n(bronze_b2.write.mode(\"append\").option(\"mergeSchema\", True).saveAsTable(f\"{P}bronze_orders\"))\n\nspark.table(f\"{P}bronze_orders\").printSchema()\ndisplay(spark.table(f\"{P}bronze_orders\").groupBy(\"_source_file\").count())",
   "id": "cell-58"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 9.1 Event-date load vs. watermark on modified time\n\u274c **Naive:** \"load everything with `OrderDate` after the last loaded date\" misses the late order 1010 *and* the update to 1002.\n\u2705 **Correct:** filter on **when the row changed** (`ModifiedAt`) using a watermark taken from silver. The repeated 1013 is filtered out because its `ModifiedAt` is not newer.",
   "id": "cell-59"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "silver_now = spark.table(f\"{P}silver_orders\")\nlast_event_date = silver_now.agg(F.max(\"OrderDate\")).first()[0]\nwatermark       = silver_now.agg(F.max(\"ModifiedAt\")).first()[0]\nprint(\"Last OrderDate in silver:\", last_event_date, \"| Watermark (max ModifiedAt):\", watermark)\n\nbronze_typed = to_silver(spark.table(f\"{P}bronze_orders\"))\n\nnaive = bronze_typed.where(F.col(\"OrderDate\") > F.lit(last_event_date))\nincremental = bronze_typed.where(F.col(\"ModifiedAt\") > F.lit(watermark))\n\nprint(\"Naive event-date load picks up:\", [r.OrderID for r in naive.collect()])\nprint(\"Watermark load picks up:       \", sorted(r.OrderID for r in incremental.collect()))",
   "id": "cell-60"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "A safer variant is a **lookback window**: reprocess everything modified in the last *N* days. It overlaps previous loads on purpose, and the guarded MERGE makes the overlap harmless.",
   "id": "cell-61"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "LOOKBACK_DAYS = 14\nlookback = bronze_typed.where(F.col(\"ModifiedAt\") >= F.lit(watermark) - F.expr(f\"INTERVAL {LOOKBACK_DAYS} DAYS\"))\nprint(\"Lookback picks up:\", sorted(r.OrderID for r in lookback.collect()))\n\nupsert_silver(lookback)\ndisplay(last_merge_metrics(f\"{P}silver_orders\"))\ndisplay(spark.table(f\"{P}silver_orders\").where(\"OrderID IN (1002, 1010, 1013, 1015)\").orderBy(\"OrderID\"))",
   "id": "cell-62"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "# Run batch 2 again: nothing should change\nbefore = fingerprint(f\"{P}silver_orders\")\nupsert_silver(lookback)\nprint(\"Idempotent on rerun:\", before == fingerprint(f\"{P}silver_orders\"))",
   "id": "cell-63"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 9.2 Late dimension: C005 arrives, so rebuild gold\nOrder 1009 (from batch 1) and 1015 are attached to C005. Before the customer existed they mapped to the **Unknown member**. Because gold is **rebuilt from silver**, adding the dimension row and rebuilding repairs them. We never dropped those fact rows, so there is nothing to backfill.",
   "id": "cell-64"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "print(\"Before: rows on Unknown customer\")\ndisplay(spark.table(f\"{P}gold_sales_wide\").where(\"CustomerKey = -1\").select(\"OrderID\", \"SourceCustomerID\", \"CustomerKey\"))\n\nspark.createDataFrame([(5, \"C005\", \"Eagle GmbH\", \"Corporate\", \"Berlin\")],\n                      spark.table(f\"{P}src_customer\").schema) \\\n     .write.mode(\"append\").saveAsTable(f\"{P}src_customer\")\n\nbuild_dim_customer()\nbuild_gold_sales_wide()\n\nprint(\"After: only the customer that never existed (C999) is still Unknown\")\ndisplay(spark.table(f\"{P}gold_sales_wide\").where(\"CustomerKey = -1 OR SourceCustomerID = 'C005'\")\n                                          .select(\"OrderID\", \"SourceCustomerID\", \"CustomerKey\", \"CustomerName\"))",
   "id": "cell-65"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### 9.3 Restate only the affected dates with `replaceWhere`\nRather than overwriting the entire daily table, recompute **only the date range touched by this batch** and replace just that slice. This is idempotent and much cheaper on large tables.",
   "id": "cell-66"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "affected = lookback.agg(F.min(\"OrderDate\").alias(\"lo\"), F.max(\"OrderDate\").alias(\"hi\")).first()\npredicate = f\"OrderDate >= '{affected.lo}' AND OrderDate <= '{affected.hi}'\"\nprint(\"Replacing slice:\", predicate)\n\nrestated = (spark.table(f\"{P}gold_sales_wide\")\n                 .where(\"Status <> 'Cancelled'\")\n                 .where(predicate)\n                 .groupBy(\"OrderDate\").agg(F.sum(\"LineAmount\").alias(\"Revenue\")))\n\n(restated.write.mode(\"overwrite\")\n         .option(\"replaceWhere\", predicate)\n         .saveAsTable(f\"{P}gold_daily_sales\"))\n\ndisplay(spark.table(f\"{P}gold_daily_sales\").orderBy(\"OrderDate\"))",
   "id": "cell-67"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 10 \u00b7 Flatten nested JSON\nUse **dot notation** for structs and **`explode`** for arrays (one output row per array element).\n`explode` **drops** rows whose array is empty or null, while `explode_outer` keeps them with nulls. Order 2003 shows the difference.",
   "id": "cell-68"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "orders_j = spark.read.json(f\"{LANDING_SPARK}/orders_json/\")\norders_j.printSchema()\n\nflat = (orders_j\n        .select(\"OrderID\",\n                F.to_date(\"OrderDate\").alias(\"OrderDate\"),\n                F.col(\"Customer.CustomerID\").alias(\"CustomerID\"),     # dot notation into a struct\n                F.col(\"Customer.City\").alias(\"City\"),\n                F.explode(\"Items\").alias(\"item\"))                     # one row per line item\n        .select(\"*\", \"item.*\").drop(\"item\")\n        .withColumn(\"LineAmount\", F.col(\"Qty\") * F.col(\"UnitPrice\")))\n\ndisplay(flat)\n\nprint(\"explode rows:      \", orders_j.select(F.explode(\"Items\")).count())\nprint(\"explode_outer rows:\", orders_j.select(F.explode_outer(\"Items\")).count(), \"(keeps order 2003)\")",
   "id": "cell-69"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 11 \u00b7 The same ideas in Spark SQL\n`%%sql` cells and `spark.sql()` run on the same engine and tables. Temp views created in PySpark are visible to SQL cells in the same session.",
   "id": "cell-70"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "# Expose a clean batch to SQL\nto_silver(spark.table(f\"{P}bronze_orders\")).createOrReplaceTempView(\"v_silver_candidates\")\n\n# spark.sql() from Python, e.g. to parameterise with f-strings\ndisplay(spark.sql(f\"SELECT Status, COUNT(*) AS Orders FROM {P}silver_orders GROUP BY Status ORDER BY Status\"))",
   "id": "cell-71"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**Latest-wins dedup with a CTE + `ROW_NUMBER()`** (in a Fabric Warehouse you would write this with `QUALIFY`):",
   "id": "cell-72"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "sparksql",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "%%sql\nWITH ranked AS (\n    SELECT CAST(OrderID AS INT) AS OrderID, Status, ModifiedAt, _source_file,\n           ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY CAST(ModifiedAt AS TIMESTAMP) DESC) AS rn\n    FROM ep7_bronze_orders\n)\nSELECT * FROM ranked\nWHERE OrderID IN (1002, 1013)\nORDER BY OrderID, rn",
   "id": "cell-73"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**`MERGE INTO` in SQL:** the same guarded upsert. Re-running it changes nothing (check the metrics columns in the output).",
   "id": "cell-74"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "sparksql",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "%%sql\nMERGE INTO ep7_silver_orders AS t\nUSING v_silver_candidates AS s\n   ON t.OrderID = s.OrderID\nWHEN MATCHED AND s.ModifiedAt > t.ModifiedAt THEN UPDATE SET *\nWHEN NOT MATCHED THEN INSERT *",
   "id": "cell-75"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**Subtotals with `GROUP BY ROLLUP`:**",
   "id": "cell-76"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "sparksql",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "%%sql\nSELECT DATE_TRUNC('MONTH', OrderDate) AS Month,\n       CategoryName,\n       SUM(LineAmount)                AS Revenue,\n       GROUPING_ID()                  AS GroupingLevel\nFROM ep7_gold_sales_wide\nWHERE Status <> 'Cancelled'\nGROUP BY ROLLUP (DATE_TRUNC('MONTH', OrderDate), CategoryName)\nORDER BY Month NULLS LAST, CategoryName NULLS LAST",
   "id": "cell-77"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**Delta history and time travel:** every MERGE is a version. Look at order 1002 before and after batch 2.",
   "id": "cell-78"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "sparksql",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "%%sql\nDESCRIBE HISTORY ep7_silver_orders",
   "id": "cell-79"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "first_merge_version = (spark.sql(f\"DESCRIBE HISTORY {P}silver_orders\")\n                            .where(\"operation = 'MERGE'\").agg(F.min(\"version\")).first()[0])\n\ndisplay(spark.sql(f\"\"\"\n    SELECT 'v{first_merge_version} (after batch 1)' AS Snapshot, OrderID, Status, ModifiedAt\n    FROM {P}silver_orders VERSION AS OF {first_merge_version} WHERE OrderID = 1002\n    UNION ALL\n    SELECT 'current', OrderID, Status, ModifiedAt\n    FROM {P}silver_orders WHERE OrderID = 1002\n\"\"\"))",
   "id": "cell-80"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 12 \u00b7 Exam tips recap\n| Trap | What we showed |\n|---|---|\n| `dropDuplicates(key)` keeps an **arbitrary** row | 4.3, fixed with `row_number()` ordered by `ModifiedAt` in 4.4 |\n| MERGE fails when several source rows match one target row | 6.1, so deduplicate the source first |\n| Keys are *NOT ENFORCED* in Fabric | 7.1, so check uniqueness in your ETL (`assert_unique`) |\n| Don't drop fact rows with missing dimensions | 7.2, left join + Unknown member (-1), repaired in 9.2 |\n| Filling nulls with 0 distorts averages | 5.3 |\n| `count(*)` vs `count(col)`; approximate distinct counts | 8 |\n| Loading by **event date** misses late rows | 9.1, so use a watermark on modified time or a lookback window |\n| Make loads **idempotent** | 6.2 and 9.1, guarded MERGE + fingerprint check; `replaceWhere` in 9.3 |\n| `union` is positional | 3, so prefer `unionByName` |\n| The SQL analytics endpoint is **read-only** | Write with Spark (this notebook), pipelines, dataflows or a Warehouse |\n\n**Next in the session:** rebuild the same clean-up in a **Warehouse** (CTAS, `QUALIFY`, T-SQL `MERGE`) and in an **Eventhouse** (`take_any` materialized view with lookback, `mv-expand`, `make-series`).",
   "id": "cell-81"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Cleanup (optional)\nSet `CLEANUP = True` to drop all demo tables and landing files.",
   "id": "cell-82"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "source": "CLEANUP = False\n\nif CLEANUP:\n    for t in TABLES:\n        spark.sql(f\"DROP TABLE IF EXISTS {P}{t}\")\n    import shutil\n    shutil.rmtree(LANDING_FS, ignore_errors=True)\n    print(\"Demo tables and landing files removed.\")",
   "id": "cell-83"
  }
 ],
 "metadata": {
  "kernel_info": {
   "name": "synapse_pyspark"
  },
  "kernelspec": {
   "name": "synapse_pyspark",
   "display_name": "Synapse PySpark",
   "language": "Python"
  },
  "language_info": {
   "name": "python"
  },
  "microsoft": {
   "language": "python",
   "language_group": "synapse_pyspark",
   "ms_spell_check": {
    "ms_spell_check_language": "en"
   }
  },
  "nteract": {
   "version": "nteract-front-end@1.0.0"
  },
  "spark_compute": {
   "compute_id": "/trident/default"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}