← All articles
DP-700FabricPySparkT-SQLKQLData Transformation

DP-700 Episode 7: Batch Transformation with PySpark, SQL & KQL

potphodepraful

PowerBI consultant ·

Title card for DP-700 Episode 7, Batch Transformation with PySpark, SQL and KQL in Microsoft Fabric, with four tiles: Transform, Denormalize, Aggregate and Clean

Welcome to Episode 7 of our DP-700 series. In Episode 1 we looked at workspace settings, in Episode 2 at lifecycle management, in Episode 3 at security and governance, in Episode 4 at orchestration, in Episode 5 at loading patterns, and in Episode 6 at how batch data gets in with shortcuts, mirroring and pipelines. Now the data is in. This episode is about what you do to it. These are the four topics from the session outline:

  • Transform data by using PySpark, SQL, and KQL
  • Denormalize data
  • Group and aggregate data
  • Handle duplicate, missing, and late-arriving data

The theme is that there is one set of ideas and three syntaxes. A filter, a join, a group-by and a "latest row per key" exist in PySpark, in T-SQL and in KQL, and the exam expects you to recognize each one in all three. It also expects you to know where each engine lives, what it can and cannot write, and which traps each one has.

List of the nine bullets in the DP-700 group Ingest and transform batch data: five done in Episodes 5 and 6 and four marked This episode

A note on wording. The study guide Microsoft updated on 5 November 2026 lists these as "Transform data by using PySpark, SQL, and KQL", "Denormalize data", "Group and aggregate data" and "Handle duplicate, missing, and late-arriving data", in the group Ingest and transform batch data. With this episode the whole batch group is covered. The streaming group (choosing a streaming engine, eventstreams, Spark structured streaming, KQL and windowing functions) comes next.


The companion demo: one dataset, three engines

This episode comes with three demo files. They all use the same small orders dataset, so you can watch one problem being solved three ways and compare the results row for row. Every code block in this post is taken from them. Download them from the table below (click a file name).

FileWhere it runsWhat you need
DP700_Ep7_Batch_Transformation_PySpark_Demo.ipynbA Fabric notebookA lakehouse attached as the default lakehouse, and Fabric Runtime 1.3 (Spark 3.5, Delta 3.2) or later. It creates all its own data, so there is nothing to upload.
DP700_Ep7_Warehouse_TSQL_Demo.sqlA Warehouse, in a New SQL queryA Warehouse in the same workspace. Everything lives in the schema ep7, so it cannot clash with your own objects.
DP700_Ep7_Eventhouse_KQL_Demo.kqlA KQL queryset on an EventhouseAn Eventhouse (its KQL database is created with it). Put the cursor inside a block and press Shift+Enter. A block is the lines between blank lines.

Run each file from top to bottom, one section at a time. In the SQL file, select a block and then run it. In the notebook, run the cells in order. A few blocks fail on purpose and are marked EXPECTED ERROR. Read the error message, because it is part of the lesson. The notebook tables are prefixed ep7_ and the SQL tables live in ep7, so you can clean up afterwards by dropping them.

What is planted in the data

The orders file is deliberately dirty. Every problem the exam asks about is in it once, so you can find it, fix it and check the fix:

ProblemWhere it isWhich section handles it
Exact duplicate rowOrder 1005 appears twice, identical4 Duplicates
Two versions of one keyOrder 1002: Pending first, Shipped (newer) at the end of the file4 Duplicates, upsert
Missing numberOrder 1003 has no Qty1 typing traps, 4 Missing data
Placeholder and dirty textCountry is N/A, empty, " india " or india4 Missing data
Dimension that never arrivesCustomer C999 and product P992 Unknown member
Dimension that arrives lateCustomer C005 (orders 1009 and 1015)4 Late data
Batch 2, arriving on 2026-11-101002 becomes Delivered (an update). 1010 has an old order date (a late fact). 1015 is new. 1013 is delivered again unchanged (a repeat). A new Channel column appears (schema drift).4 Late data

Where each part of the post lives in the demo files

Post sectionNotebookT-SQLKQL
1 TransformSections 2, 3, 11Sections 1, 2, 10Sections 1, 2, 6
2 DenormalizeSections 7, 10Sections 6, 9Sections 1, 6, 10
3 Group and aggregateSection 8Section 7Section 7
4 Duplicates, missing, lateSections 4, 5, 6, 9Sections 3, 4, 5, 8Sections 3, 4, 5, 8, 9

The big picture: three engines, three homes

In Episode 5 you learned to pick the tool that lives next to your data. Here is the same idea as a map. Each engine works on a different kind of store and has different powers.

Three columns describing PySpark and Spark SQL, T-SQL and KQL: where each runs, where the data is, whether it can write, what it is best at and what to watch for

  • PySpark and Spark SQL run in a notebook or a Spark job definition. They work mostly on lakehouse Delta tables and files, and can write back with append, overwrite and MERGE. Fabric's lakehouse uses Delta Lake as its standard table format, so tables you write with Spark are readable by every other Fabric engine.
  • T-SQL runs in the Warehouse, where you can create tables and insert, update, delete and merge. It also runs on the lakehouse SQL analytics endpoint, but there it is read-only: you can create views, functions and procedures on top of the lakehouse tables, but you cannot create tables or change data.
  • KQL runs in a KQL database in an Eventhouse. It is built for logs, telemetry and time series. It can read, and it can write with commands such as .set-or-append, and update policies transform data as it arrives.

1. Transform data by using PySpark, SQL and KQL

The first job is to get comfortable in all three languages. Start with this table. Every row is a task you do in almost every pipeline, written three ways.

Table showing the same twelve data jobs in PySpark, T-SQL and KQL: filter, select columns, add a column, if else, cast, join, union, group, top, distinct, latest row per key and save

PySpark in a notebook

A DataFrame is a distributed table. You describe a chain of transformations, and Spark runs them when you ask for a result, such as writing a table or showing rows. This is called lazy evaluation: withColumn, where and join build a plan, while write, show, count and collect are the actions that run it. The spark session is already created in a Fabric notebook.

from pyspark.sql import functions as F

raw = spark.read.format("csv").option("header", "true").load("Files/landing/orders/")

silver = (raw
    .withColumn("OrderDate", F.to_date("OrderDate", "yyyy-MM-dd"))
    .withColumn("Amount", F.col("Amount").cast("decimal(18,2)"))
    .withColumn("Country", F.upper(F.trim("Country")))
    .withColumn("Size", F.when(F.col("Amount") >= 1000, "Large").otherwise("Standard"))
    .where(F.col("OrderId").isNotNull()))

silver.write.mode("append").format("delta").saveAsTable("silver_orders")

The pieces to know:

  • Read: spark.read for files, spark.table for a lakehouse table. Read a shortcut with a relative path, as you saw in Episode 6.
  • Shape: select, where (or filter), withColumn, drop, cast, and F.when(...).otherwise(...) for if-else.
  • Combine: join with a type such as "inner" or "left". unionByName stacks two DataFrames by column name, which is safer than union, which stacks by position. A small dimension can be broadcast so Spark sends it to every worker instead of shuffling the big table: fact.join(F.broadcast(dim), "ProductKey", "left").
  • Write: mode("append") adds rows, mode("overwrite") replaces the table, and saveAsTable registers it in the lakehouse. Use MERGE (section 4) for upserts. If a write adds new columns, set mergeSchema so the table schema can grow.

From the demo: bronze, lazy evaluation and typing

The notebook lands the raw file into a bronze table exactly as it arrived. Nothing is typed or cleaned yet. Typing and cleaning happen in silver.

bronze_b1 = (
    spark.read
         .option("header", True)
         .csv(f"{LANDING_SPARK}/orders/orders_batch_01.csv")
         .withColumn("_source_file", F.col("_metadata.file_path"))
         .withColumn("_ingested_at", F.current_timestamp())
)

# Still nothing has executed. Look at the plan:
bronze_b1.explain()
  • spark.read.option("header", True).csv(...) without inferSchema reads every column as a string. That is intentional: bronze must never fail or silently change a value because a column looked like a number.
  • _metadata.file_path is a hidden column that Spark adds when it reads files. Saving it as _source_file, together with current_timestamp() as _ingested_at, gives every row its lineage: which file it came from and when it was loaded.
  • Nothing has run yet. read, withColumn and select only build a plan, and explain() prints that plan without executing it. This is lazy evaluation in action.
# An action triggers the job
print("Rows in batch 1:", bronze_b1.count())

bronze_b1.write.mode("overwrite").saveAsTable(f"{P}bronze_orders")
display(spark.table(f"{P}bronze_orders"))

count() is an action, so it triggers the first real job. saveAsTable is another action: it writes the Delta table. display shows the result in the notebook.

Then silver starts with typing and shaping:

orders_typed = (
    spark.table(f"{P}bronze_orders")
         .select(
             F.col("OrderID").cast("int").alias("OrderID"),
             F.trim("CustomerID").alias("CustomerID"),
             F.trim("ProductID").alias("ProductID"),
             F.to_date("OrderDate", "yyyy-MM-dd").alias("OrderDate"),
             F.col("Qty").cast("int").alias("Qty"),
             F.col("UnitPrice").cast("decimal(10,2)").alias("UnitPrice"),
             F.col("Country"),
             F.col("Status"),
             F.to_timestamp("ModifiedAt").alias("ModifiedAt"),
         )
         .withColumn("LineAmount", F.col("Qty") * F.col("UnitPrice"))
         .withColumn("OrderSize",
                     F.when(F.col("Qty") >= 5, "Large")
                      .when(F.col("Qty") >= 2, "Medium")
                      .when(F.col("Qty").isNotNull(), "Small")
                      .otherwise("Unknown"))
)

orders_typed.printSchema()
display(orders_typed.where(F.col("Status") != "Cancelled").orderBy("OrderID"))
  • cast("int") turns the text "2" into the number 2. A value that cannot be converted, such as an empty string, becomes null in Spark.
  • to_date("OrderDate", "yyyy-MM-dd") needs the format that matches the text. cast("decimal(10,2)") keeps money exact instead of using a floating point type.
  • LineAmount is derived with withColumn. When Qty is null the product is null too, which is correct: we do not know the amount.
  • F.when(...).when(...).otherwise(...) is evaluated top to bottom and the first match wins, so the order of the conditions matters. A null Qty does not satisfy >= 5 or >= 2, so an explicit isNotNull() test is needed before otherwise("Unknown") to separate "Small" from "unknown".

union versus unionByName. This small test shows why the post recommends the second one:

a = spark.createDataFrame([(1, "India")], "OrderID INT, Country STRING")
b = spark.createDataFrame([("UK", 2)],    "Country STRING, OrderID INT")   # same columns, different order
c = spark.createDataFrame([(3, "USA", "Web")], "OrderID INT, Country STRING, Channel STRING")

print("union (positional): values land in the wrong columns")
a.union(b).show()

print("unionByName: matched by name")
a.unionByName(b).show()

print("unionByName(allowMissingColumns=True): Channel filled with null")
a.unionByName(c, allowMissingColumns=True).show()
  • a.union(b) stacks by position. Frame b has the same columns in a different order, so the text "UK" lands in the OrderID column and the number 2 in Country. Spark does not warn you.
  • a.unionByName(b) matches by name, so each value lands in the right column.
  • unionByName(c, allowMissingColumns=True) keeps going when one side has an extra column (Channel) and fills it with null for the rows that do not have it. You will need this for schema drift in section 4.

You can also mix in SQL. A %%sql cell, or spark.sql("..."), runs Spark SQL against the same tables:

%%sql
SELECT Category, SUM(Amount) AS Revenue
FROM gold_sales_wide
GROUP BY Category

From the demo: the same work in Spark SQL

A %%sql cell, or spark.sql(...), runs on the same Spark engine and sees the same tables. A temporary view created in PySpark is visible to SQL cells in the same session, which lets you move between languages in one notebook.

# Expose a clean batch to SQL
to_silver(spark.table(f"{P}bronze_orders")).createOrReplaceTempView("v_silver_candidates")

# spark.sql() from Python, e.g. to parameterise with f-strings
display(spark.sql(f"SELECT Status, COUNT(*) AS Orders FROM {P}silver_orders GROUP BY Status ORDER BY Status"))

The first line exposes the cleaned DataFrame as a view named v_silver_candidates. The second runs a SQL query from Python, where an f-string can parameterize the table name.

%%sql
WITH ranked AS (
    SELECT CAST(OrderID AS INT) AS OrderID, Status, ModifiedAt, _source_file,
           ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY CAST(ModifiedAt AS TIMESTAMP) DESC) AS rn
    FROM ep7_bronze_orders
)
SELECT * FROM ranked
WHERE OrderID IN (1002, 1013)
ORDER BY OrderID, rn

This is the latest-wins pattern as a common table expression: number the rows of each key by ModifiedAt descending. The bronze columns are still text, so ModifiedAt is cast to a timestamp inside the window. The query lists every row of orders 1002 and 1013 together with its rn, so you can see which row would survive: the one with rn = 1. Add WHERE rn = 1 to keep only that row. In a Fabric Warehouse you could write the filter directly with QUALIFY.

%%sql
MERGE INTO ep7_silver_orders AS t
USING v_silver_candidates AS s
   ON t.OrderID = s.OrderID
WHEN MATCHED AND s.ModifiedAt > t.ModifiedAt THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

This is the guarded upsert from section 4 in SQL. UPDATE SET * and INSERT * use every column of the source. The extra condition on the matched branch means an older or repeated row changes nothing.

%%sql
SELECT DATE_TRUNC('MONTH', OrderDate) AS Month,
       CategoryName,
       SUM(LineAmount)                AS Revenue,
       GROUPING_ID()                  AS GroupingLevel
FROM ep7_gold_sales_wide
WHERE Status <> 'Cancelled'
GROUP BY ROLLUP (DATE_TRUNC('MONTH', OrderDate), CategoryName)
ORDER BY Month NULLS LAST, CategoryName NULLS LAST

GROUP BY ROLLUP adds subtotal rows, and GROUPING_ID() returns a number that tells a subtotal row from a row where the column is genuinely null (0 means a detail row).

%%sql
DESCRIBE HISTORY ep7_silver_orders
first_merge_version = (spark.sql(f"DESCRIBE HISTORY {P}silver_orders")
                            .where("operation = 'MERGE'").agg(F.min("version")).first()[0])

display(spark.sql(f"""
    SELECT 'v{first_merge_version} (after batch 1)' AS Snapshot, OrderID, Status, ModifiedAt
    FROM {P}silver_orders VERSION AS OF {first_merge_version} WHERE OrderID = 1002
    UNION ALL
    SELECT 'current', OrderID, Status, ModifiedAt
    FROM {P}silver_orders WHERE OrderID = 1002
"""))

Every MERGE is a new version of the Delta table. DESCRIBE HISTORY lists the versions, and VERSION AS OF reads the table as it was. The last query shows order 1002 right after batch 1 (Shipped) next to its current state, which is useful when someone asks "what did this table say last week?".

T-SQL in the Warehouse

In the Warehouse you transform with set-based SQL. The usual tools are CTAS (CREATE TABLE ... AS SELECT), INSERT ... SELECT, UPDATE, DELETE, TRUNCATE TABLE, views, stored procedures and common table expressions (standard, sequential, and nested ones, which are in preview). MERGE is supported and generally available. Session-scoped #temp tables work too. Lakehouse tables in the same workspace can be read with a three-part name such as SilverLakehouse.dbo.orders.

INSERT INTO dbo.FactSales (OrderDate, ProductKey, Amount)
SELECT o.OrderDate, COALESCE(p.ProductKey, -1), o.Amount
FROM   SilverLakehouse.dbo.orders AS o
LEFT JOIN dbo.DimProduct AS p ON p.ProductId = o.ProductId;

Fabric supports some handy modern syntax: FROM-first queries, GROUP BY ALL and ORDER BY ALL without a column list, and QUALIFY to filter on a window function. It also has analytic functions such as MEDIAN, QUANTILE, APPROX_MEDIAN and APPROX_QUANTILE. Know the limits too. The following are not supported: triggers, materialized views, recursive queries, synonyms, CREATE USER and FOR XML. Primary key, unique and foreign key constraints can be added only as NOT ENFORCED, so they document intent but do not stop bad data. Column renames use sp_rename, and only a subset of ALTER TABLE is allowed.

From the demo: staging, typing traps and cross-database reads

The Warehouse script mirrors the notebook. It starts with a staging table where every column is VARCHAR, including empty strings and junk, which is the T-SQL version of bronze. In real life you would fill it with COPY INTO or OPENROWSET from OneLake. The demo inserts the same rows as the notebook's CSV file.

CREATE TABLE ep7.StagingOrders
(
    OrderID     VARCHAR(20),
    CustomerID  VARCHAR(20),
    ProductID   VARCHAR(20),
    OrderDate   VARCHAR(30),
    Qty         VARCHAR(20),
    UnitPrice   VARCHAR(20),
    Country     VARCHAR(50),
    Status      VARCHAR(20),
    ModifiedAt  VARCHAR(30),
    SourceFile  VARCHAR(100),
    LoadedAt    DATETIME2(6)
);
INSERT INTO ep7.StagingOrders
       (OrderID, CustomerID, ProductID, OrderDate, Qty, UnitPrice, Country, Status, ModifiedAt, SourceFile, LoadedAt)
VALUES ('1001','C001','P01','2026-10-01','2' ,'25.00' ,'India'   ,'Shipped'  ,'2026-10-01 09:00:00','orders_batch_01.csv',SYSUTCDATETIME()),
       ('1002','C002','P02','2026-10-01','1' ,'120.00','India'   ,'Pending'  ,'2026-10-01 10:00:00','orders_batch_01.csv',SYSUTCDATETIME()),
       ('1003','C003','P03','2026-10-02',''  ,'15.50' ,'USA'     ,'Shipped'  ,'2026-10-02 11:00:00','orders_batch_01.csv',SYSUTCDATETIME()),
       -- ... 12 more rows: 15 in total, the same as orders_batch_01.csv

A CTAS can also build small tables straight from a VALUES list, which is how the demo creates its source tables. The CAST calls fix the data types, because CTAS takes types from the query:

CREATE TABLE ep7.SrcCategory AS
SELECT CAST(CategoryKey AS INT) AS CategoryKey, CAST(CategoryName AS VARCHAR(50)) AS CategoryName
FROM (VALUES (1,'Electronics'),(2,'Home'),(3,'Office')) v(CategoryKey, CategoryName);

The typing trap. Spark turns an unparseable string into null. T-SQL does not always do that:

SELECT  TRY_CAST('' AS INT)                    AS EmptyToInt_Trap,      -- 0   (!)
        TRY_CAST(NULLIF(TRIM(''), '') AS INT)  AS EmptyToInt_Safe,      -- NULL
        TRY_CAST('N/A' AS INT)                 AS JunkToInt,            -- NULL
        TRY_CAST('' AS DATE)                   AS EmptyToDate_Trap;     -- 1900-01-01 (!)
  • TRY_CAST('' AS INT) returns 0, not null. An empty quantity would silently become a quantity of zero.
  • The same happens with dates: TRY_CAST('' AS DATE) returns 1900-01-01.
  • The safe pattern is NULLIF(TRIM(x), '') first, which turns an empty (or blank) string into NULL, and then TRY_CAST. A real junk value such as 'N/A' becomes NULL with TRY_CAST alone.

The typed copy of staging uses that pattern, with TRIM on the text columns:

CREATE TABLE ep7.OrdersTyped AS
SELECT  TRY_CAST(OrderID AS INT)                                   AS OrderID,
        TRIM(CustomerID)                                           AS CustomerID,
        TRIM(ProductID)                                            AS ProductID,
        TRY_CAST(OrderDate AS DATE)                                AS OrderDate,
        TRY_CAST(NULLIF(TRIM(Qty), '') AS INT)                     AS Qty,
        TRY_CAST(UnitPrice AS DECIMAL(10,2))                       AS UnitPrice,
        Country,
        Status,
        TRY_CAST(ModifiedAt AS DATETIME2(6))                       AS ModifiedAt
FROM ep7.StagingOrders;

Reading the lakehouse tables from the Warehouse. Add the lakehouse to the Warehouse explorer, and use a three-part name (lakehouse.schema.table). The script keeps this block commented out, because you must replace <YourLakehouse> with your own lakehouse name first:

SELECT 'Lakehouse (Spark)' AS Source, COUNT(*) AS [Rows], SUM(LineAmount) AS Revenue
FROM <YourLakehouse>.dbo.ep7_silver_orders
UNION ALL
SELECT 'Warehouse (T-SQL)', COUNT(*), SUM(LineAmount)
FROM ep7.SilverOrders;

SELECT OrderID, Status, ModifiedAt FROM <YourLakehouse>.dbo.ep7_silver_orders
EXCEPT
SELECT OrderID, Status, ModifiedAt FROM ep7.SilverOrders;          -- expect 0 rows

-- >>> EXPECTED ERROR <<<  The lakehouse SQL analytics endpoint is READ-ONLY.
-- Write with Spark, a pipeline, a dataflow, or into a Warehouse instead.
INSERT INTO <YourLakehouse>.dbo.ep7_silver_orders (OrderID) VALUES (9999);

The first query compares row count and revenue between the Spark table and the Warehouse table. The EXCEPT query should return no rows when both agree. The last statement is the expected error: the lakehouse SQL analytics endpoint is read-only, so an INSERT fails. Write with Spark, a pipeline or a dataflow, or into a Warehouse.

KQL in an Eventhouse

KQL is a pipeline language. You start with a table and pass the rows through operators with the | character: where filters, project picks columns, extend adds columns, summarize aggregates, join and lookup combine tables, union stacks them, parse and extract pull values out of text, and mv-expand turns an array into rows.

let since = ago(7d);
RawLogs
| where Timestamp > since and isnotempty(Level)
| parse Message with "user=" User " action=" Action
| summarize Events = count(), Users = dcount(User) by Action, bin(Timestamp, 1h)
| order by Timestamp desc

A query only reads. To store a result you run a command that takes a query, for example .set-or-append (add to a table, create it if missing), .set-or-replace or .append:

.set-or-append HourlyActions <|
    RawLogs
    | where Timestamp > ago(1h)
    | summarize Events = count() by Action, bin(Timestamp, 1h)

To transform data as it arrives, attach an update policy to the target table (you met these in Episode 5). To keep a running aggregate or a deduplicated copy, use a materialized view (below). Two KQL traps appear in this section:

  • The default join is innerunique, not inner. It first removes duplicates from the left table on the join key, then matches. A join of events to a lookup can silently return fewer rows than you expect. Say kind=inner or kind=leftouter when you mean it.
  • lookup is the dimension-enrichment operator. It is like a left outer join (the default kind) or an inner join, it does not repeat the key columns, and it assumes the left table is the large fact table and the right table is a small dimension. The right side is broadcast to every node, so if it is larger than several tens of megabytes the query fails.

From the demo: dimensions, an update policy and a join trap

In the Eventhouse you create dimensions with .set-or-replace. It creates the table if it does not exist and replaces its contents with the result of a query. A datatable supplies the rows inline. Customer C005 is missing on purpose:

.set-or-replace Customers <|
datatable(CustomerID:string, CustomerName:string, Segment:string, City:string)
[
    "C001", "Asha Traders",   "Retail",    "Pune",
    "C002", "Bright Office",  "Corporate", "Mumbai",
    "C003", "Campus Store",   "Education", "Nagpur",
    "C004", "Delta Supplies", "Retail",    "London"
]

Before any orders arrive, the demo creates the table and an update policy so every batch flows through it:

.create-merge table Orders (OrderID:int, CustomerID:string, ProductID:string, OrderDate:datetime, Qty:int, UnitPrice:real, Country:string, Status:string, ModifiedAt:datetime)
.create-merge table OrdersEnriched (OrderID:int, CustomerID:string, ProductID:string, OrderDate:datetime, Qty:int, UnitPrice:real, Country:string, Status:string, ModifiedAt:datetime, CustomerName:string, Segment:string)
.create-or-alter function with (folder="ep7", docstring="Enrich each new Orders batch with customer attributes") EnrichOrders()
{
    Orders
    | lookup kind=leftouter (Customers | project CustomerID, CustomerName, Segment) on CustomerID
    | project OrderID, CustomerID, ProductID, OrderDate, Qty, UnitPrice, Country, Status, ModifiedAt, CustomerName, Segment
}
.alter table OrdersEnriched policy update
@'[{"IsEnabled": true, "Source": "Orders", "Query": "EnrichOrders()", "IsTransactional": false}]'
  • .create-merge table creates the table, or adds any missing columns if it exists. OrdersEnriched is the target and has two extra columns for the customer.
  • EnrichOrders() is a stored function. Inside an update policy, the name Orders means only the batch being ingested, not the whole table. It uses lookup kind=leftouter to copy the customer attributes onto each new row.
  • The policy says: when data lands in Orders, run EnrichOrders() and store the result in OrdersEnriched. Create the policy before you ingest, or the first batches will not be enriched.

The join trap, measured. This query counts the rows from four ways of joining the latest orders to the customers:

union
    (OrdersLatest | join Customers on CustomerID                  | count | extend JoinKind = "1 default (innerunique)"),
    (OrdersLatest | join kind=inner Customers on CustomerID       | count | extend JoinKind = "2 kind=inner"),
    (OrdersLatest | join kind=leftouter Customers on CustomerID   | count | extend JoinKind = "3 kind=leftouter"),
    (OrdersLatest | lookup kind=leftouter Customers on CustomerID | count | extend JoinKind = "4 lookup leftouter")
| project JoinKind, Rows = Count
| order by JoinKind asc
  • The default join (innerunique) removes duplicate keys on the left side first. With orders on the left, it keeps only one order per customer, so the count is far lower than the real number of orders.
  • kind=inner keeps every matching order. kind=leftouter and lookup kind=leftouter also keep the orders whose customer is missing. Always write kind= explicitly.

2. Denormalize data

A normalized model stores each fact once and splits attributes across many tables, which is good for writing. A denormalized model copies attributes into the table that is read, which is good for reporting. In an analytics platform you normally normalize at the source and denormalize on the way to the gold layer.

Comparison of a normalized snowflake (Sales, Product, Subcategory, Category) with a flat denormalized dimension, plus what you gain, what it costs and where to do it

What it looks like

  • Flatten a snowflake dimension. Join Product, Subcategory and Category into one DimProduct table that holds all three levels. This is the most common exam example, and it is how a star schema is built from a snowflake. You can read the reasons in our PL-300 data model episode: fewer tables, a hierarchy in one table, shorter filter paths.
  • Build a wide table. Join facts to several dimensions once and store the result, so a report or a notebook reads one table with no joins.
  • Flatten nested data. JSON from an API or an event often has structs and arrays. Turn them into columns and rows before analysis.
  • Enrich events at ingestion. In an Eventhouse, copy dimension attributes onto each event with an update policy that uses lookup, so queries never need to join.

Code in PySpark, T-SQL and KQL that flattens Product, Subcategory and Category into one dimension and joins it to sales

From the demo: flatten the snowflake and build a wide table

The demo's source is a snowflake: Category, then Subcategory, then Product, plus Customer. The goal is a flat DimProduct with an Unknown member (key -1) for facts whose product does not exist.

PySpark

def build_dim_product():
    dim = (spark.table(f"{P}src_product")
           .join(spark.table(f"{P}src_subcategory"), "SubcategoryKey", "left")
           .join(spark.table(f"{P}src_category"), "CategoryKey", "left")
           .select("ProductKey", "ProductID", "ProductName", "SubcategoryName", "CategoryName", "ListPrice"))
    unknown = spark.createDataFrame([(-1, "N/A", "Unknown", "Unknown", "Unknown", None)], dim.schema)
    dim.unionByName(unknown).write.mode("overwrite").saveAsTable(f"{P}dim_product")


def build_dim_customer():
    dim = spark.table(f"{P}src_customer")
    unknown = spark.createDataFrame([(-1, "N/A", "Unknown", "Unknown", "Unknown")], dim.schema)
    dim.unionByName(unknown).write.mode("overwrite").saveAsTable(f"{P}dim_customer")


build_dim_product()
build_dim_customer()
display(spark.table(f"{P}dim_product").orderBy("ProductKey"))
  • Joining on the column name ("SubcategoryKey") keeps a single copy of the key column. "left" keeps every product even if a subcategory is missing.
  • The Unknown row is built with the schema of the dimension (dim.schema) and added with unionByName. mode("overwrite") rebuilds the table, so running it again after the source changes is safe.

T-SQL, with a CTAS that does the same in one statement:

CREATE TABLE ep7.DimProduct AS
SELECT  p.ProductKey, p.ProductID, p.ProductName, s.SubcategoryName, c.CategoryName, p.ListPrice
FROM ep7.SrcProduct p
LEFT JOIN ep7.SrcSubcategory s ON s.SubcategoryKey = p.SubcategoryKey
LEFT JOIN ep7.SrcCategory    c ON c.CategoryKey    = s.CategoryKey
UNION ALL
SELECT -1, 'N/A', 'Unknown', 'Unknown', 'Unknown', NULL;

KQL, where lookup is the dimension-enrichment join and a one-row datatable adds the Unknown member:

.set-or-replace DimProduct <|
Product
| lookup kind=leftouter Subcategory on SubcategoryKey
| lookup kind=leftouter Category on CategoryKey
| project ProductKey, ProductID, ProductName, SubcategoryName, CategoryName, ListPrice
| union (datatable(ProductKey:int, ProductID:string, ProductName:string, SubcategoryName:string, CategoryName:string, ListPrice:real)
         [ -1, "N/A", "Unknown", "Unknown", "Unknown", real(null) ])

The KQL comment in the demo says it plainly: lookup broadcasts the small right side. Always say kind=leftouter, so that a missing subcategory does not remove the product.

The row explosion trap, with a check you can run

If the dimension key is not unique, the join multiplies fact rows. The keys are not enforced, so the database will not stop it. The demo adds a second copy of product P01 and compares row counts:

bad_dim = spark.table(f"{P}dim_product").unionByName(
    spark.table(f"{P}dim_product").where("ProductID = 'P01'"))   # P01 now appears twice

silver = spark.table(f"{P}silver_orders")
print("Fact rows:", silver.count(),
      "| after join to bad dim:", silver.join(bad_dim, "ProductID", "left").count())

def assert_unique(df, key):
    dupes = df.groupBy(key).count().where("count > 1")
    n = dupes.count()
    if n:
        display(dupes)
        raise ValueError(f"{n} duplicate value(s) in key '{key}'")
    print(f"OK: '{key}' is unique")

assert_unique(spark.table(f"{P}dim_product"), "ProductID")
try:
    assert_unique(bad_dim, "ProductID")
except ValueError as e:
    print("Caught:", e)
WITH bad_dim AS
(
    SELECT * FROM ep7.DimProduct
    UNION ALL
    SELECT * FROM ep7.DimProduct WHERE ProductID = 'P01'   -- P01 now appears twice
)
SELECT  (SELECT COUNT(*) FROM ep7.SilverOrders)                                         AS FactRows,
        (SELECT COUNT(*) FROM ep7.SilverOrders f LEFT JOIN bad_dim d ON d.ProductID = f.ProductID) AS RowsAfterJoin;

-- Uniqueness check to run in ETL (keys are NOT ENFORCED): should return nothing
SELECT ProductID, COUNT(*) AS Cnt FROM ep7.DimProduct GROUP BY ProductID HAVING COUNT(*) > 1;
  • The "fact rows" count and the "rows after join" count should match. With the doubled P01, the second number is larger, and every revenue total that touches P01 is inflated.
  • assert_unique in the notebook, and the last GROUP BY ... HAVING COUNT(*) > 1 query in SQL, are the check to put in your ETL. It should return nothing. In the notebook it raises an error, which stops the pipeline before bad numbers reach a report.

The wide gold table

Now join the fact to the dimensions once, and store the result so reports read one table. In all three versions the rule is the same: start from the fact, use a left join, and map a missing dimension to -1 and "Unknown", so that no sale is ever dropped.

def build_gold_sales_wide():
    f = spark.table(f"{P}silver_orders").alias("f")
    p = spark.table(f"{P}dim_product").alias("p")
    c = spark.table(f"{P}dim_customer").alias("c")
    wide = (
        f.join(F.broadcast(p), F.col("f.ProductID") == F.col("p.ProductID"), "left")
         .join(F.broadcast(c), F.col("f.CustomerID") == F.col("c.CustomerID"), "left")
         .select(
             "f.OrderID", "f.OrderDate", "f.Status", "f.Country", "f.Qty", "f.UnitPrice", "f.LineAmount",
             F.coalesce("p.ProductKey", F.lit(-1)).alias("ProductKey"),
             F.coalesce("p.ProductName", F.lit("Unknown")).alias("ProductName"),
             F.coalesce("p.CategoryName", F.lit("Unknown")).alias("CategoryName"),
             F.coalesce("c.CustomerKey", F.lit(-1)).alias("CustomerKey"),
             F.coalesce("c.CustomerName", F.lit("Unknown")).alias("CustomerName"),
             F.coalesce("c.Segment", F.lit("Unknown")).alias("Segment"),
             F.col("f.CustomerID").alias("SourceCustomerID"),
             F.col("f.ProductID").alias("SourceProductID"),
         )
    )
    wide.write.mode("overwrite").option("overwriteSchema", True).saveAsTable(f"{P}gold_sales_wide")


build_gold_sales_wide()
gold = spark.table(f"{P}gold_sales_wide")
print("Silver rows:", spark.table(f"{P}silver_orders").count(), "| Gold rows:", gold.count())

print("Rows mapped to the Unknown member:")
display(gold.where("ProductKey = -1 OR CustomerKey = -1")
            .select("OrderID", "SourceCustomerID", "CustomerKey", "SourceProductID", "ProductKey"))
  • F.broadcast(p) and F.broadcast(c) tell Spark to send the small dimensions to every worker, so the big fact table is not shuffled.
  • F.coalesce("p.ProductKey", F.lit(-1)) returns the first non-null value: the real key if the join matched, otherwise -1.
  • The original keys are kept as SourceCustomerID and SourceProductID, so you can see why a row is Unknown and repair it later.

In T-SQL the demo wraps the build in a stored procedure, so it can be rerun after a late dimension arrives (section 4):

CREATE PROCEDURE ep7.usp_BuildGold
AS
BEGIN
    DROP TABLE IF EXISTS ep7.DimCustomer;
    CREATE TABLE ep7.DimCustomer AS
    SELECT CustomerKey, CustomerID, CustomerName, Segment, City FROM ep7.SrcCustomer
    UNION ALL
    SELECT -1, 'N/A', 'Unknown', 'Unknown', 'Unknown';

    DROP TABLE IF EXISTS ep7.FactSalesWide;
    CREATE TABLE ep7.FactSalesWide AS
    SELECT  f.OrderID, f.OrderDate, f.Status, f.Country, f.Qty, f.UnitPrice, f.LineAmount,
            COALESCE(p.ProductKey, -1)           AS ProductKey,
            COALESCE(p.ProductName, 'Unknown')   AS ProductName,
            COALESCE(p.CategoryName, 'Unknown')  AS CategoryName,
            COALESCE(c.CustomerKey, -1)          AS CustomerKey,
            COALESCE(c.CustomerName, 'Unknown')  AS CustomerName,
            COALESCE(c.Segment, 'Unknown')       AS Segment,
            f.CustomerID                         AS SourceCustomerID,
            f.ProductID                          AS SourceProductID
    FROM ep7.SilverOrders f
    LEFT JOIN ep7.DimProduct  p ON p.ProductID  = f.ProductID
    LEFT JOIN ep7.DimCustomer c ON c.CustomerID = f.CustomerID;
END;
GO
EXEC ep7.usp_BuildGold;

The procedure rebuilds both tables with DROP TABLE IF EXISTS and CTAS each time. The rows that end up on the Unknown member are order 1006 (customer C999), 1007 (product P99) and 1009 (customer C005, who has not arrived yet). You can list them with the check query in the script:

SELECT OrderID, SourceCustomerID, CustomerKey, SourceProductID, ProductKey
FROM ep7.FactSalesWide
WHERE CustomerKey = -1 OR ProductKey = -1
ORDER BY OrderID;

In KQL the equivalent is a stored function, so the join happens at query time:

.create-or-alter function with (folder="ep7", docstring="Wide sales: latest orders + product + customer") SalesWide()
{
    OrdersLatest
    | extend LineAmount = Qty * UnitPrice
    | lookup kind=leftouter (DimProduct | project ProductID, ProductKey, ProductName, CategoryName) on ProductID
    | lookup kind=leftouter (Customers | project CustomerID, CustomerName, Segment) on CustomerID
    | extend ProductKey   = coalesce(ProductKey, int(-1)),
             ProductName  = iff(isempty(ProductName),  "Unknown", ProductName),
             CategoryName = iff(isempty(CategoryName), "Unknown", CategoryName),
             CustomerName = iff(isempty(CustomerName), "Unknown", CustomerName),
             Segment      = iff(isempty(Segment),      "Unknown", Segment)
}
  • Left outer lookups return empty strings (not nulls) for text columns when there is no match. That is why the function uses iff(isempty(...), "Unknown", ...) for the strings.
  • For the integer ProductKey the missing value is a real null, so coalesce(ProductKey, int(-1)) works. This is the same trap you will meet again in the missing-data part: coalesce() does not replace empty strings.

Flatten nested JSON

In Spark use explode to turn an array into one row per element, and dot notation to reach into a struct. In KQL use mv-expand on a dynamic column.

orders = spark.read.json("Files/landing/orders_json/")
items = (orders
    .select("OrderId", F.col("Customer.Id").alias("CustomerId"), F.explode("Items").alias("item"))
    .select("OrderId", "CustomerId", "item.Sku", "item.Qty", "item.Price"))
Orders
| mv-expand item = Items
| project OrderId, CustomerId = tostring(Customer.Id), Sku = tostring(item.Sku),
          Qty = toint(item.Qty), Price = todouble(item.Price)

From the demo: JSON in all three engines

The demo's JSON has three orders. Order 2001 has two line items, order 2002 has one, and order 2003 has none. That last one shows the difference between dropping and keeping empty arrays.

PySpark

orders_j = spark.read.json(f"{LANDING_SPARK}/orders_json/")
orders_j.printSchema()

flat = (orders_j
        .select("OrderID",
                F.to_date("OrderDate").alias("OrderDate"),
                F.col("Customer.CustomerID").alias("CustomerID"),     # dot notation into a struct
                F.col("Customer.City").alias("City"),
                F.explode("Items").alias("item"))                     # one row per line item
        .select("*", "item.*").drop("item")
        .withColumn("LineAmount", F.col("Qty") * F.col("UnitPrice")))

display(flat)

print("explode rows:      ", orders_j.select(F.explode("Items")).count())
print("explode_outer rows:", orders_j.select(F.explode_outer("Items")).count(), "(keeps order 2003)")
  • F.col("Customer.CustomerID") uses dot notation to reach into a struct.
  • F.explode("Items") gives one row per element, and then select("*", "item.*") turns the element's fields into columns.
  • The two counts at the end show the difference: explode drops order 2003 because its array is empty, and explode_outer keeps it with nulls.

T-SQL uses OPENJSON to read the document and CROSS APPLY to expand the array:

DECLARE @orders VARCHAR(8000) = '[
  {"OrderID":2001,"OrderDate":"2026-11-01","Customer":{"CustomerID":"C001","City":"Pune"},
   "Items":[{"ProductID":"P01","Qty":2,"UnitPrice":25.0},{"ProductID":"P03","Qty":1,"UnitPrice":15.5}]},
  {"OrderID":2002,"OrderDate":"2026-11-02","Customer":{"CustomerID":"C002","City":"Mumbai"},
   "Items":[{"ProductID":"P02","Qty":1,"UnitPrice":120.0}]},
  {"OrderID":2003,"OrderDate":"2026-11-03","Customer":{"CustomerID":"C003","City":"Nagpur"},
   "Items":[]}
]';

SELECT  o.OrderID, o.OrderDate, o.CustomerID, o.City,
        i.ProductID, i.Qty, i.UnitPrice, i.Qty * i.UnitPrice AS LineAmount
FROM OPENJSON(@orders)
     WITH (OrderID    INT          '$.OrderID',
           OrderDate  DATE         '$.OrderDate',
           CustomerID VARCHAR(10)  '$.Customer.CustomerID',     -- "dot notation" into the struct
           City       VARCHAR(30)  '$.Customer.City',
           Items      NVARCHAR(MAX) '$.Items' AS JSON) o           -- AS JSON requires NVARCHAR(MAX)
CROSS APPLY OPENJSON(o.Items)
     WITH (ProductID VARCHAR(10), Qty INT, UnitPrice DECIMAL(10,2)) i;
SELECT 'CROSS APPLY' AS Mode, COUNT(*) AS [Rows]
FROM OPENJSON(@orders) WITH (Items NVARCHAR(MAX) '$.Items' AS JSON) o
CROSS APPLY OPENJSON(o.Items) i
UNION ALL
SELECT 'OUTER APPLY', COUNT(*)
FROM OPENJSON(@orders) WITH (Items NVARCHAR(MAX) '$.Items' AS JSON) o
OUTER APPLY OPENJSON(o.Items) i;                          -- 3 vs 4 rows
  • The WITH clause maps JSON paths to typed columns. '$.Customer.CustomerID' is the path syntax for reaching into an object.
  • Items NVARCHAR(MAX) '$.Items' AS JSON keeps the array as JSON text for the next step. The AS JSON option requires NVARCHAR(MAX).
  • CROSS APPLY OPENJSON(o.Items) behaves like explode (order 2003 disappears). OUTER APPLY behaves like explode_outer (order 2003 stays, with nulls). The final query counts the rows: 3 for CROSS APPLY and 4 for OUTER APPLY.
  • For a JSON file in OneLake, read it with OPENROWSET(BULK '<path to the .jsonl file>') and then use CROSS APPLY OPENJSON exactly as above.

KQL

.set-or-replace OrdersJson <|
datatable(OrderID:int, OrderDate:datetime, Customer:dynamic, Items:dynamic)
[
    2001, datetime(2026-11-01), dynamic({"CustomerID":"C001","City":"Pune"}),
          dynamic([{"ProductID":"P01","Qty":2,"UnitPrice":25.0},{"ProductID":"P03","Qty":1,"UnitPrice":15.5}]),
    2002, datetime(2026-11-02), dynamic({"CustomerID":"C002","City":"Mumbai"}),
          dynamic([{"ProductID":"P02","Qty":1,"UnitPrice":120.0}]),
    2003, datetime(2026-11-03), dynamic({"CustomerID":"C003","City":"Nagpur"}),
          dynamic([])
]
OrdersJson
| mv-expand Item = Items
| project OrderID, OrderDate,
          CustomerID = tostring(Customer.CustomerID),       // dot notation into the property bag
          City       = tostring(Customer.City),
          ProductID  = tostring(Item.ProductID),
          Qty        = toint(Item.Qty),
          UnitPrice  = todouble(Item.UnitPrice)
| extend LineAmount = Qty * UnitPrice
  • A dynamic column holds JSON. Customer.CustomerID is dot notation into the property bag, and you convert the value with tostring(), toint() or todouble().
  • mv-expand Item = Items gives one row per array element. Like explode, it drops the row whose array is empty.

To keep order 2003, as explode_outer would, turn the empty array into an array with one null before expanding it:

union
    (OrdersJson | mv-expand Items | count | extend Mode = "mv-expand (like explode)"),
    (OrdersJson | extend Items = iff(array_length(Items) == 0, dynamic([null]), Items)
                | mv-expand Items | count | extend Mode = "empty -> [null] (like explode_outer)")
| project Mode, Rows = Count

The same demo also shows how to turn log-like text into columns with parse and extract:

datatable(Line:string)
[
    "2026-11-10 09:00:00 ORDER 1002 status=Delivered channel=Web",
    "2026-11-10 09:05:00 ORDER 1010 status=Shipped channel=Store"
]
| parse Line with EventTime:datetime " ORDER " OrderID:int " status=" Status:string " channel=" Channel:string
| extend StatusViaExtract = extract(@"status=(\w+)", 1, Line)

parse reads the pieces between the literal words and gives them types (EventTime:datetime, OrderID:int). extract pulls one value with a regular expression, here the word after status=.

Trade-offs and traps

  • It is a trade. You gain simpler queries and faster reads, and you pay in storage, in update effort (change a category name and many rows change) and in the need to rebuild the flat table when a dimension changes.
  • A join can multiply rows. If the dimension has two rows for one key, every matching fact row appears twice and your totals double. Check that the dimension key is unique and compare row counts before and after the join. Fabric does not enforce keys for you.
  • Use a left join from the fact. It keeps every fact row. Where the dimension is missing, fill the key with the Unknown member (-1), as you learned in Episode 5.
  • Mind history. For a slowly changing dimension (type 2), a denormalized fact should carry the attribute that was true at the time of the event. Join on the key and on the validity range:
SELECT f.OrderId, f.Amount, d.Segment
FROM   dbo.FactSales AS f
JOIN   dbo.DimCustomer AS d
  ON   d.CustomerId = f.CustomerId
 AND   f.OrderDate >= d.ValidFrom AND f.OrderDate < d.ValidTo;
  • Do not denormalize the source system. Transactional databases stay normalized. Denormalize in the layer built for reading.
  • Update policies need the dimension to be there. If the dimension row arrives after the event, the lookup at ingestion finds nothing. More on this in section 4.

3. Group and aggregate data

To aggregate is to collapse many rows into fewer, with a measure for each group. The first decision is the grain: what does one output row represent? Here it is one row per month and category. Once the grain is clear, the syntax is straightforward in all three engines.

Code in PySpark, T-SQL and KQL that computes monthly revenue and distinct customers per category, excluding cancelled orders

PySpark

Use groupBy(...).agg(...) with functions such as F.sum, F.count, F.countDistinct, F.avg, F.min, F.max, F.collect_list and F.approx_count_distinct. Add rollup and cube for subtotals, and pivot to turn values into columns. Window functions keep every row and add a calculation across related rows: ranks, running totals, previous and next values.

from pyspark.sql import Window

sales.rollup("Category", "Subcategory").agg(F.sum("Amount").alias("Revenue"))
sales.groupBy("Category").pivot("Year", [2025, 2026]).sum("Amount")

w = (Window.partitionBy("Category").orderBy("Month")
           .rowsBetween(Window.unboundedPreceding, Window.currentRow))
monthly.withColumn("RunningRevenue", F.sum("Revenue").over(w))

From the demo: aggregates in PySpark

The grain is one row per month and category, and cancelled orders are excluded first. The demo uses the wide gold table from section 2:

sales = (spark.table(f"{P}gold_sales_wide")
              .where("Status <> 'Cancelled'")
              .withColumn("Month", F.trunc("OrderDate", "month")))

monthly = (sales.groupBy("Month", "CategoryName")
                .agg(F.sum("LineAmount").alias("Revenue"),
                     F.count("*").alias("Rows"),
                     F.count("Qty").alias("RowsWithQty"),           # count(col) skips nulls
                     F.countDistinct("OrderID").alias("Orders"),
                     F.approx_count_distinct("CustomerKey").alias("ApproxCustomers"),
                     F.avg("LineAmount").alias("AvgLine"),
                     F.min("OrderDate").alias("FirstOrder"),
                     F.max("OrderDate").alias("LastOrder"),
                     F.collect_set("ProductName").alias("Products"))
                .orderBy("Month", "CategoryName"))

display(monthly)
  • F.trunc("OrderDate", "month") turns a date into the first day of its month, which becomes the grouping column.
  • F.count("*") counts rows. F.count("Qty") counts only rows where Qty is not null. Putting both side by side (Rows and RowsWithQty) makes the missing quantity visible.
  • countDistinct is exact. approx_count_distinct is faster and uses less memory, with a small error. collect_set gathers the distinct product names into an array.

Subtotals and pivots. rollup adds a subtotal per month and a grand total. cube adds every combination of the columns. grouping_id() says which level a row is:

display(sales.rollup("Month", "CategoryName")
             .agg(F.sum("LineAmount").alias("Revenue"), F.grouping_id().alias("GroupingLevel"))
             .orderBy(F.col("Month").asc_nulls_last(), F.col("CategoryName").asc_nulls_last()))
display(sales.cube("Month", "Segment")
             .agg(F.sum("LineAmount").alias("Revenue"))
             .orderBy(F.col("Month").asc_nulls_last(), F.col("Segment").asc_nulls_last()))

pivot turns the category values into columns. Listing the values yourself saves Spark an extra scan to discover them:

categories = ["Electronics", "Home", "Office", "Unknown"]   # listing values avoids an extra scan
display(sales.groupBy("Month").pivot("CategoryName", categories).agg(F.sum("LineAmount")).orderBy("Month"))

Window functions keep every row and add a calculation over related rows, unlike groupBy, which collapses them:

daily = sales.groupBy("OrderDate").agg(F.sum("LineAmount").alias("Revenue"))

w_running = Window.orderBy("OrderDate").rowsBetween(Window.unboundedPreceding, Window.currentRow)
w_moving3 = Window.orderBy("OrderDate").rowsBetween(-2, Window.currentRow)

display(daily.withColumn("RunningRevenue", F.sum("Revenue").over(w_running))
             .withColumn("Moving3DayAvg", F.round(F.avg("Revenue").over(w_moving3), 2))
             .orderBy("OrderDate"))
w_rank = Window.partitionBy("CategoryName").orderBy(F.col("Revenue").desc())

display(sales.groupBy("CategoryName", "ProductName").agg(F.sum("LineAmount").alias("Revenue"))
             .withColumn("RankInCategory", F.dense_rank().over(w_rank))
             .orderBy("CategoryName", "RankInCategory"))
  • rowsBetween(Window.unboundedPreceding, Window.currentRow) sums from the first day up to the current one, which is a running total. rowsBetween(-2, Window.currentRow) averages the current day and the two before it, which is a 3-day moving average.
  • dense_rank() numbers products by revenue within each category and does not skip numbers when there are ties (rank() would).

Empty input. This pitfall shows up when a filter removes every row:

empty = sales.where("1 = 0")
print("agg without groupBy:", empty.agg(F.sum("LineAmount")).collect())
print("agg with groupBy:   ", empty.groupBy("Month").agg(F.sum("LineAmount")).collect())

The aggregate without groupBy returns one row holding a null. With groupBy it returns no rows. A downstream step that expects at least one row works in the first case and breaks in the second.

T-SQL

Use GROUP BY, with HAVING to filter groups after they are built (WHERE filters rows before). In the Warehouse, GROUP BY ALL groups by every non-aggregated column in the select list. Window functions use OVER (PARTITION BY ... ORDER BY ...).

SELECT Category, [Month], Revenue,
       SUM(Revenue) OVER (PARTITION BY Category ORDER BY [Month]
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningRevenue
FROM   dbo.MonthlyRevenue;

From the demo: aggregates in the Warehouse

The same grain, built with a CTAS so the result is stored as a gold table:

CREATE TABLE ep7.MonthlyRevenue AS
SELECT  DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS [Month],
        CategoryName,
        SUM(LineAmount)               AS Revenue,
        COUNT(*)                      AS [Rows],
        COUNT(Qty)                    AS RowsWithQty,
        COUNT(DISTINCT OrderID)       AS Orders,
        COUNT(DISTINCT CustomerKey)   AS Customers,
        AVG(LineAmount)               AS AvgLine
FROM ep7.FactSalesWide
WHERE Status <> 'Cancelled'                      -- WHERE filters rows BEFORE grouping
GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1), CategoryName;
  • WHERE removes the cancelled rows before grouping. COUNT(*) counts rows and COUNT(Qty) skips nulls, exactly as in Spark.
  • The grouping expression is repeated in GROUP BY, because the alias [Month] is not available there.

HAVING filters the groups after aggregation:

SELECT CategoryName, SUM(LineAmount) AS Revenue
FROM ep7.FactSalesWide
WHERE Status <> 'Cancelled'
GROUP BY CategoryName
HAVING SUM(LineAmount) > 150;

Subtotals with ROLLUP. GROUPING_ID tells a subtotal row apart from a row where the category is really null:

WITH sales AS
(
    SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS [Month], CategoryName, LineAmount
    FROM ep7.FactSalesWide
    WHERE Status <> 'Cancelled'
)
SELECT  [Month], CategoryName,
        SUM(LineAmount)                   AS Revenue,
        GROUPING_ID([Month], CategoryName) AS GroupingLevel     -- 0 detail, 1 month subtotal, 3 grand total
FROM sales
GROUP BY ROLLUP ([Month], CategoryName)
ORDER BY GroupingLevel, [Month], CategoryName;

The grouping level is 0 for a detail row, 1 for a month subtotal and 3 for the grand total, so the ORDER BY GroupingLevel puts details first and the grand total last.

Window functions: a running total, a 3-day moving average and a rank:

WITH daily AS
(
    SELECT OrderDate, SUM(LineAmount) AS Revenue
    FROM ep7.FactSalesWide
    WHERE Status <> 'Cancelled'
    GROUP BY OrderDate
)
SELECT  OrderDate, Revenue,
        SUM(Revenue) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING)              AS RunningRevenue,
        AVG(Revenue) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Moving3DayAvg
FROM daily
ORDER BY OrderDate;

SELECT  CategoryName, ProductName, SUM(LineAmount) AS Revenue,
        DENSE_RANK() OVER (PARTITION BY CategoryName ORDER BY SUM(LineAmount) DESC) AS RankInCategory
FROM ep7.FactSalesWide
WHERE Status <> 'Cancelled'
GROUP BY CategoryName, ProductName
ORDER BY CategoryName, RankInCategory;

DENSE_RANK() OVER (PARTITION BY CategoryName ORDER BY SUM(LineAmount) DESC) ranks the products inside each category. Note that the window function is evaluated after the grouping, which is why it can use SUM(LineAmount).

Percentiles, approximate and exact:

SELECT  CategoryName,
        APPROX_PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY LineAmount) AS ApproxMedianLine
FROM ep7.FactSalesWide
WHERE Status <> 'Cancelled'
GROUP BY CategoryName;

SELECT DISTINCT CategoryName,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY LineAmount) OVER (PARTITION BY CategoryName) AS ExactMedianLine
FROM ep7.FactSalesWide
WHERE Status <> 'Cancelled';
  • APPROX_PERCENTILE_CONT is an aggregate function, fast and suitable for big tables. Use it when a small error is acceptable.
  • PERCENTILE_CONT ... OVER (PARTITION BY ...) is exact, but it is a window function, so it repeats the value on every row and needs SELECT DISTINCT to give one row per category.

Empty input: no GROUP BY returns one row with null, and with GROUP BY it returns zero rows, the same as in Spark.

SELECT SUM(LineAmount) AS NoGroupBy FROM ep7.FactSalesWide WHERE 1 = 0;
SELECT CategoryName, SUM(LineAmount) AS WithGroupBy FROM ep7.FactSalesWide WHERE 1 = 0 GROUP BY CategoryName;

KQL

summarize is the aggregation operator. Group by a column, or by a time bucket made with bin(). Useful aggregates are count(), countif(), dcount(), count_distinct(), sum(), sumif(), avg(), percentile(), make_list(), make_set(), arg_max() and take_any(). For a running total, sort the rows and use row_cumsum():

MonthlyRevenue
| sort by Category asc, Month asc
| extend RunningRevenue = row_cumsum(Revenue, Category != prev(Category))

Table of aggregate functions in PySpark, T-SQL and KQL: count, count non-null, distinct count exact and approximate, sum, median, conditional, collect list, latest value, time buckets, filter on aggregate and subtotals

From the demo: aggregates in KQL

First, one summarize with the common aggregation functions:

SalesWide
| where Status != "Cancelled"
| summarize Revenue     = sum(LineAmount),
            Rows        = count(),
            RowsWithQty = countif(isnotnull(Qty)),
            Orders      = dcount(OrderID),           // approximate (HyperLogLog)
            OrdersExact = count_distinct(OrderID),   // exact, more expensive
            AvgLine     = avg(LineAmount),
            P50Line     = percentile(LineAmount, 50),
            Products    = make_set(ProductName)
         by Month = startofmonth(OrderDate), CategoryName
| order by Month asc, CategoryName asc
  • Orders = dcount(OrderID) is approximate (HyperLogLog), and OrdersExact = count_distinct(OrderID) is exact but more expensive.
  • Rows = count() counts rows. RowsWithQty = countif(isnotnull(Qty)) counts non-null values, the KQL way of writing COUNT(Qty).
  • percentile(LineAmount, 50) is the median. make_set collects the distinct product names. startofmonth(OrderDate) builds the month.

Conditional aggregates compute several measures in one pass, instead of filtering the table repeatedly:

SalesWide
| summarize ShippedRevenue = sumif(LineAmount, Status in ("Shipped", "Delivered")),
            PendingOrders  = countif(Status == "Pending"),
            CancelledRows  = countif(Status == "Cancelled")
         by CategoryName

Daily totals, a running total and a day-over-day change. bin(OrderDate, 1d) rounds to the day. The rows must be sorted before row_cumsum() and prev() work as expected:

SalesWide
| where Status != "Cancelled"
| summarize Revenue = sum(LineAmount) by Day = bin(OrderDate, 1d)
| order by Day asc
| extend RunningRevenue = row_cumsum(Revenue),
         DeltaVsPrevDay = Revenue - prev(Revenue)

Top product per category. arg_max(Revenue, ProductName) returns the product name from the row with the highest revenue in each category:

SalesWide
| where Status != "Cancelled"
| summarize Revenue = sum(LineAmount) by CategoryName, ProductName
| summarize arg_max(Revenue, ProductName) by CategoryName

High-cardinality groups. When there are very many groups, hint.strategy=shuffle spreads the aggregation across nodes:

Orders
| summarize hint.strategy=shuffle Rows = count() by CustomerID

Pre-aggregate with a materialized view. The view built in section 4 aggregates across batches. Read it like a table:

ChangesPerDay
| order by Day asc

A query only reads, so to store a gold aggregate you use a command:

.set-or-replace GoldDailyRevenue <|
SalesWide
| where Status != "Cancelled"
| summarize Revenue = sum(LineAmount) by OrderDate = bin(OrderDate, 1d)

Things that trip people up

  • Nulls. SUM, AVG, MIN and MAX skip null values. COUNT(col) in SQL and Spark counts non-null values, but count() in KQL counts rows, so count non-null values with countif(isnotnull(col)).
  • Empty input. With no group-by, an aggregate over no rows returns one row (counts and sums are 0, other results are null). With a group-by it returns no rows.
  • Approximate versus exact. dcount() in KQL and approx_count_distinct in Spark use sketches: fast, cheap, small error. Use count_distinct(), countDistinct or COUNT(DISTINCT) when the number must be exact.
  • High-cardinality groups. In KQL, a summarize with a very large number of groups can be spread across nodes with the shuffle hint, summarize hint.strategy=shuffle .... Spark does the equivalent shuffle for you on a groupBy.
  • Pre-aggregate what you read often. A gold aggregate table, or a KQL materialized view, answers common questions without scanning the raw data every time. A materialized view always returns fresh results: it combines the already-aggregated part with the newest records that are not yet processed. Query the materialized part only, with materialized_view("Name"), when you accept a little staleness for speed.
  • Update policy or materialized view? Update policies run on each ingestion batch, so they can only aggregate within one batch. For real aggregation across batches, use a materialized view.

4. Handle duplicate, missing and late-arriving data

Real data is messy. Sources resend records, leave gaps, and deliver things after you thought the day was finished. The exam tests whether you can name the problem and choose the pattern. Start with duplicates.

Duplicates

There are three kinds, and they need different fixes:

  • Exact copies. Every column is the same, for example because a file was loaded twice. Remove them with DISTINCT, dropDuplicates() or distinct *.
  • Several versions of one key. The same order arrives again with a newer ModifiedAt. You want the latest. A plain distinct will not remove them, because the rows differ.
  • Repeated deliveries of an event. A stream or API delivers each event at least once, so the same event ID can appear twice. Keep one.

First find them. Group by the key and keep groups with more than one row:

df.groupBy("OrderId").count().where("count > 1")

SELECT OrderId, COUNT(*) AS Copies FROM dbo.StagingOrders GROUP BY OrderId HAVING COUNT(*) > 1;

Orders | summarize Copies = count() by OrderId | where Copies > 1

Then remove them:

Code in PySpark, T-SQL and KQL that keeps the latest version of each order using row_number, QUALIFY and arg_max

  • Latest per key uses a window function. Number the rows in each key by ModifiedAt descending and keep number 1: row_number() in Spark, ROW_NUMBER() with QUALIFY (or a CTE) in T-SQL, and summarize arg_max(ModifiedAt, *) by OrderId in KQL.
  • dropDuplicates(["OrderId"]) keeps an arbitrary row. Spark does not promise which one survives, so it is fine for exact copies and wrong when the newest version must win. distinct() compares all columns, while dropDuplicates(subset) compares only the columns you list.
  • Constraints will not save you. In the Warehouse, primary and unique keys are NOT ENFORCED, and a Delta table has no enforced key either. Deduplication is your ETL's job.

From the demo: find and remove duplicates

The orders file has 15 rows: 1005 twice (exact copies), and two versions of 1002. Work through the same steps in each engine.

PySpark: detect, then see what each tool does:

display(
    orders_typed.groupBy("OrderID")
                .agg(F.count("*").alias("Copies"),
                     F.countDistinct("ModifiedAt").alias("Versions"))
                .where("Copies > 1")
)
print("Rows:", orders_typed.count(), "| after distinct():", orders_typed.distinct().count())
display(orders_typed.distinct().where("OrderID IN (1002, 1005)").orderBy("OrderID", "ModifiedAt"))
  • The first query lists keys that occur more than once, with the number of copies and distinct versions. 1005 has 2 copies and 1 version, and 1002 has 2 copies and 2 versions.
  • distinct() compares all columns, so 15 rows become 14: the identical 1005 collapses, and both versions of 1002 survive.
display(orders_typed.dropDuplicates(["OrderID"]).where("OrderID = 1002")
                    .select("OrderID", "Status", "ModifiedAt"))

This is the dropDuplicates trap. Keeping "one row per OrderID" without an ordering returns an arbitrary row, so the stale Pending version of 1002 can survive. Even when it happens to pick the right one, that is luck.

w_latest = Window.partitionBy("OrderID").orderBy(F.col("ModifiedAt").desc())

orders_dedup = (orders_typed
                .withColumn("rn", F.row_number().over(w_latest))
                .where("rn = 1")
                .drop("rn"))

print("Rows after latest-wins dedup:", orders_dedup.count())
display(orders_dedup.where("OrderID IN (1002, 1005)").select("OrderID", "Status", "ModifiedAt"))

The window version is deterministic: partition by the business key, order by ModifiedAt descending, keep rn = 1. The result has 13 rows, and order 1002 is Shipped.

T-SQL: the same four steps. Detect uses HAVING, because the filter is on a group:

SELECT  OrderID,
        COUNT(*)                   AS Copies,
        COUNT(DISTINCT ModifiedAt) AS Versions
FROM ep7.OrdersTyped
GROUP BY OrderID
HAVING COUNT(*) > 1;
SELECT DISTINCT * FROM ep7.OrdersTyped WHERE OrderID IN (1002, 1005) ORDER BY OrderID, ModifiedAt;
WITH ranked AS
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ModifiedAt DESC) AS rn
    FROM ep7.OrdersTyped
)
SELECT OrderID, Status, ModifiedAt
FROM ranked
WHERE rn = 1 AND OrderID IN (1002, 1005);

The CTE with ROW_NUMBER() works everywhere. The version with QUALIFY filters on the window function without a CTE:

SELECT OrderID, Status, ModifiedAt
FROM ep7.OrdersTyped
WHERE OrderID IN (1002, 1005)
QUALIFY ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ModifiedAt DESC) = 1;

The script notes that if your Warehouse returns a syntax error on QUALIFY, the feature is not enabled for you yet, and the CTE version is what the rest of the script relies on.

KQL: the demo loads the file twice on purpose, to simulate a pipeline retry (the protected form, with an ingest-by tag, comes in the upsert part below). The four sources then show four different row counts:

union
    (Orders                              | summarize Rows = count() | extend Source = "1 Orders (raw)"),
    (OrdersDedup                         | summarize Rows = count() | extend Source = "2 OrdersDedup (take_any)"),
    (materialized_view("OrdersDedup")    | summarize Rows = count() | extend Source = "3 materialized part only"),
    (OrdersLatest                        | summarize Rows = count() | extend Source = "4 OrdersLatest (arg_max)")
| project Source, Rows
| order by Source asc
  • Orders (raw) has 30 rows: 15 twice. The deduplicated view OrdersDedup has 14, and OrdersLatest has 13, one per order.
  • Querying a materialized view by name returns the materialized part plus the records not yet processed, so it is always fresh. materialized_view("OrdersDedup") reads only the materialized part, which is faster but can lag right after an ingestion.

Query-time versions of detect and dedup:

Orders
| summarize Copies = count(), Versions = dcount(ModifiedAt) by OrderID
| where Copies > 1
| order by OrderID asc
Orders
| summarize arg_max(ModifiedAt, *) by OrderID
| where OrderID in (1002, 1005)
| project OrderID, Status, ModifiedAt
Orders
| distinct *
| where OrderID in (1002, 1005)
| project OrderID, Status, ModifiedAt
| order by OrderID asc, ModifiedAt asc

arg_max(ModifiedAt, *) is KQL's row_number() = 1: it returns the whole row with the latest ModifiedAt for each OrderID. distinct * compares all columns, so both versions of 1002 stay, as in the other engines.

Keep it clean with an upsert

A better design is to make the load idempotent: running it twice gives the same result as running it once. The standard tool is MERGE, which updates rows that match the key and inserts rows that do not.

Code in PySpark Delta MERGE, T-SQL MERGE and a KQL materialized view with arg_max that upsert the latest order per key and ignore older late rows

  • The source must have one row per key. If several source rows match one target row, the merge is ambiguous and Delta can fail. Deduplicate the source first, as in the previous step.
  • Guard against old rows. Adding s.ModifiedAt > t.ModifiedAt to the matched clause stops a late, older version from overwriting a newer one.
  • Insert-only merge removes repeated events. With only a "when not matched, insert" clause, a record that already exists is ignored. Add a date-range condition so Delta does not scan the whole table:
(tgt.alias("t")
    .merge(src.alias("s"),
           "t.EventId = s.EventId AND t.EventDate >= current_date() - INTERVAL 7 DAYS")
    .whenNotMatchedInsertAll()
    .execute())
  • KQL has no MERGE. To keep the latest row per key, use a materialized view with arg_max. To drop repeated deliveries, use a materialized view with take_any(*) grouped by the event ID, and give it a lookback so it deduplicates only against records ingested in a recent window, for example 6 hours:
.create materialized-view with (lookback=6h) EventsDedup on table Events
{
    Events | summarize take_any(*) by EventId
}

KQL can also stop duplicate loads at ingestion: tag an ingestion with an ingest-by: tag and use the ingestIfNotExists property, so a batch that was already ingested is skipped. And a materialized view can be created over another materialized view when the source one is a deduplication view (take_any(*)).

From the demo: build silver with a guarded upsert

PySpark. The cleanup steps are packaged into one reusable function, to_silver(), and the upsert into upsert_silver():

def to_silver(bronze_df):
    """Type, standardize, dedup (latest wins) and flag missing values."""
    w = Window.partitionBy("OrderID").orderBy(F.col("ModifiedAt").desc())
    return (
        bronze_df.select(
            F.col("OrderID").cast("int").alias("OrderID"),
            F.trim("CustomerID").alias("CustomerID"),
            F.trim("ProductID").alias("ProductID"),
            F.to_date("OrderDate", "yyyy-MM-dd").alias("OrderDate"),
            F.col("Qty").cast("int").alias("Qty"),
            F.col("UnitPrice").cast("decimal(10,2)").alias("UnitPrice"),
            F.when(F.upper(F.trim("Country")).isin(PLACEHOLDERS), F.lit(None))
             .when(F.length(F.trim("Country")) <= 3, F.upper(F.trim("Country")))
             .otherwise(F.initcap(F.trim("Country"))).alias("Country"),
            F.trim("Status").alias("Status"),
            F.to_timestamp("ModifiedAt").alias("ModifiedAt"),
        )
        .withColumn("rn", F.row_number().over(w)).where("rn = 1").drop("rn")
        .withColumn("LineAmount", F.col("Qty") * F.col("UnitPrice"))
        .withColumn("QtyMissing", F.col("Qty").isNull())
        .fillna({"Country": "Unknown"})
    )


def upsert_silver(source_df):
    target = DeltaTable.forName(spark, f"{P}silver_orders")
    (target.alias("t")
           .merge(source_df.alias("s"), "t.OrderID = s.OrderID")
           .whenMatchedUpdateAll(condition="s.ModifiedAt > t.ModifiedAt")
           .whenNotMatchedInsertAll()
           .execute())


def fingerprint(table):
    """Row count + order-independent checksum, used to prove idempotency."""
    df = spark.table(table)
    return df.select(F.count("*").alias("rows"),
                     F.sum(F.xxhash64(*df.columns).cast("decimal(38,0)")).alias("checksum")).first()


def last_merge_metrics(table):
    return (DeltaTable.forName(spark, table).history(1)
            .select("version", "operation", "operationMetrics.numSourceRows",
                    "operationMetrics.numTargetRowsInserted", "operationMetrics.numTargetRowsUpdated"))
  • to_silver() types, standardizes, deduplicates (latest wins) and flags missing values. Because it is a function, batch 2 later goes through exactly the same logic.
  • upsert_silver() is a Delta MERGE. The condition s.ModifiedAt > t.ModifiedAt on the matched branch is the guard: an older or repeated row can never overwrite a newer one. That guard is what makes the load idempotent.
  • fingerprint() returns the row count and an order-independent checksum of all rows (xxhash64 summed). Two equal fingerprints mean the table did not change. last_merge_metrics() reads the numbers of rows inserted and updated by the last merge from the Delta history.
silver_b1 = to_silver(spark.table(f"{P}bronze_orders"))

# Create the empty silver table with the right schema, then load it via MERGE
if not spark.catalog.tableExists(f"{P}silver_orders"):
    silver_b1.limit(0).write.saveAsTable(f"{P}silver_orders")

upsert_silver(silver_b1)

display(last_merge_metrics(f"{P}silver_orders"))
display(spark.table(f"{P}silver_orders").orderBy("OrderID"))

The silver table is created empty with the right schema, then loaded by the same MERGE that later loads will use. The first run inserts 13 rows.

The failing merge. This block merges the un-deduplicated rows, with no guard, into a scratch copy:

spark.table(f"{P}silver_orders").write.mode("overwrite").saveAsTable(f"{P}scratch_merge_demo")

dirty_source = orders_typed.withColumn("QtyMissing", F.col("Qty").isNull()).drop("OrderSize")

try:
    (DeltaTable.forName(spark, f"{P}scratch_merge_demo").alias("t")
        .merge(dirty_source.alias("s"), "t.OrderID = s.OrderID")
        .whenMatchedUpdateAll()          # no guard, no dedup
        .whenNotMatchedInsertAll()
        .execute())
    print("MERGE succeeded (unexpected)")
except Exception as e:
    print("MERGE failed as expected:\n", str(e).splitlines()[0][:300])

It fails, because several source rows (two 1002s and two 1005s) match one target row and Delta cannot tell which should win. The fix is the one the function already applies: deduplicate the source to one row per key first.

Prove idempotency. Run the same load again and compare:

before = fingerprint(f"{P}silver_orders")
upsert_silver(to_silver(spark.table(f"{P}bronze_orders")))   # rerun the same batch
after = fingerprint(f"{P}silver_orders")

print("Before:", before)
print("After: ", after)
print("Idempotent:", before == after)
display(last_merge_metrics(f"{P}silver_orders"))

If the fingerprints are equal, the second run changed nothing, and the merge metrics show 0 rows inserted and 0 updated.

T-SQL. The twin of to_silver() is a view:

CREATE VIEW ep7.vw_SilverCandidates AS
WITH typed AS
(
    SELECT  TRY_CAST(OrderID AS INT)                         AS OrderID,
            TRIM(CustomerID)                                 AS CustomerID,
            TRIM(ProductID)                                  AS ProductID,
            TRY_CAST(OrderDate AS DATE)                      AS OrderDate,
            TRY_CAST(NULLIF(TRIM(Qty), '') AS INT)           AS Qty,
            TRY_CAST(UnitPrice AS DECIMAL(10,2))             AS UnitPrice,
            CASE WHEN Country IS NULL
                   OR UPPER(TRIM(Country)) IN ('', 'N/A', 'NA', 'NULL', '-', 'UNKNOWN') THEN 'Unknown'
                 WHEN LEN(TRIM(Country)) <= 3 THEN UPPER(TRIM(Country))
                 ELSE UPPER(LEFT(TRIM(Country), 1)) + LOWER(SUBSTRING(TRIM(Country), 2, 100))
            END                                              AS Country,
            TRIM(Status)                                     AS Status,
            TRY_CAST(ModifiedAt AS DATETIME2(6))             AS ModifiedAt
    FROM ep7.StagingOrders
),
ranked AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ModifiedAt DESC) AS rn
    FROM typed
)
SELECT  OrderID, CustomerID, ProductID, OrderDate, Qty, UnitPrice, Country, Status, ModifiedAt,
        CAST(Qty * UnitPrice AS DECIMAL(18,2))               AS LineAmount,
        CAST(CASE WHEN Qty IS NULL THEN 1 ELSE 0 END AS BIT) AS QtyMissing
FROM ranked
WHERE rn = 1;

It types the columns (with NULLIF(TRIM(...), '') for the quantity), standardizes the country, keeps the latest row per order, and adds LineAmount and a QtyMissing flag. Then the silver table, with a primary key that can only be NOT ENFORCED:

CREATE TABLE ep7.SilverOrders
(
    OrderID     INT           NOT NULL,
    CustomerID  VARCHAR(20),
    ProductID   VARCHAR(20),
    OrderDate   DATE,
    Qty         INT,
    UnitPrice   DECIMAL(10,2),
    Country     VARCHAR(50),
    Status      VARCHAR(20),
    ModifiedAt  DATETIME2(6),
    LineAmount  DECIMAL(18,2),
    QtyMissing  BIT
);
ALTER TABLE ep7.SilverOrders ADD CONSTRAINT PK_SilverOrders PRIMARY KEY NONCLUSTERED (OrderID) NOT ENFORCED;

The demo proves that NOT ENFORCED means not enforced: duplicate keys go straight in.

CREATE TABLE ep7.PkDemo (OrderID INT NOT NULL, Note VARCHAR(20));
ALTER TABLE ep7.PkDemo ADD CONSTRAINT PK_PkDemo PRIMARY KEY NONCLUSTERED (OrderID) NOT ENFORCED;
INSERT INTO ep7.PkDemo VALUES (1, 'first'), (1, 'duplicate!');
SELECT * FROM ep7.PkDemo;          -- 2 rows with the same "primary key"

The last query returns two rows with the same "primary key". Keep this in mind for the exam: the constraint documents intent and helps the optimizer, but your ETL must check uniqueness.

The guarded upsert, which should leave 13 rows with 1002 = Shipped:

MERGE ep7.SilverOrders AS t
USING ep7.vw_SilverCandidates AS s
   ON t.OrderID = s.OrderID
WHEN MATCHED AND s.ModifiedAt > t.ModifiedAt THEN
    UPDATE SET CustomerID = s.CustomerID, ProductID = s.ProductID, OrderDate = s.OrderDate,
               Qty = s.Qty, UnitPrice = s.UnitPrice, Country = s.Country, Status = s.Status,
               ModifiedAt = s.ModifiedAt, LineAmount = s.LineAmount, QtyMissing = s.QtyMissing
WHEN NOT MATCHED BY TARGET THEN
    INSERT (OrderID, CustomerID, ProductID, OrderDate, Qty, UnitPrice, Country, Status, ModifiedAt, LineAmount, QtyMissing)
    VALUES (s.OrderID, s.CustomerID, s.ProductID, s.OrderDate, s.Qty, s.UnitPrice, s.Country, s.Status,
            s.ModifiedAt, s.LineAmount, s.QtyMissing);

The failing merge in T-SQL uses the same recipe, a scratch copy and an unguarded update from the un-deduplicated table (run these two on their own):

CREATE TABLE ep7.ScratchMergeDemo AS SELECT * FROM ep7.SilverOrders;
MERGE ep7.ScratchMergeDemo AS t
USING ep7.OrdersTyped AS s
   ON t.OrderID = s.OrderID
WHEN MATCHED THEN
    UPDATE SET Status = s.Status, ModifiedAt = s.ModifiedAt;

Idempotency: take a snapshot, rerun the same merge, and compare in both directions with EXCEPT:

CREATE TABLE ep7.SilverSnapshot AS SELECT * FROM ep7.SilverOrders;
SELECT  (SELECT COUNT(*) FROM (SELECT * FROM ep7.SilverOrders   EXCEPT SELECT * FROM ep7.SilverSnapshot) a) AS RowsAdded,
        (SELECT COUNT(*) FROM (SELECT * FROM ep7.SilverSnapshot EXCEPT SELECT * FROM ep7.SilverOrders)   b) AS RowsLost;

The first query counts rows that are new in the table, and the second counts rows that disappeared. Both 0 means the load is idempotent. (The merge itself is the one from 5.3, shown above.)

Finally, the whole load is packaged as a stored procedure with a watermark and a lookback window:

CREATE PROCEDURE ep7.usp_LoadSilver
    @LookbackDays INT = 14
AS
BEGIN
    DECLARE @Watermark DATETIME2(6) =
        (SELECT ISNULL(MAX(ModifiedAt), '1900-01-01') FROM ep7.SilverOrders);
    DECLARE @From DATETIME2(6) = DATEADD(DAY, -@LookbackDays, @Watermark);

    MERGE ep7.SilverOrders AS t
    USING (SELECT * FROM ep7.vw_SilverCandidates WHERE ModifiedAt >= @From) AS s
       ON t.OrderID = s.OrderID
    WHEN MATCHED AND s.ModifiedAt > t.ModifiedAt THEN
        UPDATE SET CustomerID = s.CustomerID, ProductID = s.ProductID, OrderDate = s.OrderDate,
                   Qty = s.Qty, UnitPrice = s.UnitPrice, Country = s.Country, Status = s.Status,
                   ModifiedAt = s.ModifiedAt, LineAmount = s.LineAmount, QtyMissing = s.QtyMissing
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (OrderID, CustomerID, ProductID, OrderDate, Qty, UnitPrice, Country, Status, ModifiedAt, LineAmount, QtyMissing)
        VALUES (s.OrderID, s.CustomerID, s.ProductID, s.OrderDate, s.Qty, s.UnitPrice, s.Country, s.Status,
                s.ModifiedAt, s.LineAmount, s.QtyMissing);

    SELECT @Watermark AS WatermarkUsed, @From AS LookbackFrom;
END;
  • The watermark is the largest ModifiedAt already in silver. The procedure reads from watermark - @LookbackDays, so rows that arrive a little late are picked up again.
  • Overlapping loads are harmless because of the guard in the matched branch.
  • The final SELECT returns the watermark and the lookback start the procedure used, which is handy for logging.

KQL. There is no MERGE, so the Eventhouse uses materialized views for deduplication. Create them before ingesting, so every batch flows through them:

.create materialized-view with (lookback=7d, autoUpdateSchema=true, folder="ep7") OrdersDedup on table Orders
{
    Orders
    | summarize take_any(*) by OrderID, ModifiedAt
}
.create materialized-view with (autoUpdateSchema=true, folder="ep7") OrdersLatest on table Orders
{
    Orders
    | summarize arg_max(ModifiedAt, *) by OrderID
}
.create materialized-view with (folder="ep7") ChangesPerDay on materialized-view OrdersDedup
{
    OrdersDedup
    | summarize Changes = count(), Orders = dcount(OrderID) by Day = bin(ModifiedAt, 1d)
}
  • OrdersDedup uses take_any(*) by OrderID, ModifiedAt: repeated deliveries of the same version collapse into one row. lookback=7d limits the comparison to records ingested in the last 7 days, so repeats that arrive later are missed.
  • OrdersLatest uses arg_max(ModifiedAt, *) by OrderID: one row per order, newest version wins. This is KQL's upsert.
  • ChangesPerDay is a view on top of the first view. KQL allows a view on another view only when the source view is a take_any(*) deduplication view.
  • autoUpdateSchema=true keeps the view in step when columns are added to the table (schema drift, later).

Now ingest batch 1. The ingest-by tag is the label that protects against retries:

.ingest inline into table Orders with (format="csv", tags='["ingest-by:orders_batch_01"]') <|
1001,C001,P01,2026-10-01,2,25.00,India,Shipped,2026-10-01 09:00:00
1002,C002,P02,2026-10-01,1,120.00,India,Pending,2026-10-01 10:00:00
1003,C003,P03,2026-10-02,,15.50,USA,Shipped,2026-10-02 11:00:00
1004,C001,P01,2026-10-02,3,25.00,N/A,Shipped,2026-10-02 12:00:00
1005,C004,P04,2026-10-03,5,8.75,UK,Shipped,2026-10-03 09:30:00
1005,C004,P04,2026-10-03,5,8.75,UK,Shipped,2026-10-03 09:30:00
1006,C999,P02,2026-10-04,2,120.00,  india ,Pending,2026-10-04 10:00:00
1007,C002,P99,2026-10-05,1,60.00,USA,Shipped,2026-10-05 15:00:00
1008,C003,P03,2026-10-06,4,15.50,,Shipped,2026-10-06 16:00:00
1009,C005,P05,2026-10-15,2,45.00,Germany,Shipped,2026-10-15 10:00:00
1011,C001,P02,2026-10-20,1,120.00,india,Shipped,2026-10-20 13:00:00
1012,C004,P05,2026-10-28,3,45.00,UK,Cancelled,2026-10-28 09:00:00
1013,C002,P01,2026-11-02,6,25.00,India,Shipped,2026-11-02 10:00:00
1014,C003,P04,2026-11-05,10,8.75,USA,Shipped,2026-11-05 11:00:00
1002,C002,P02,2026-10-01,1,120.00,India,Shipped,2026-10-02 08:00:00

.ingest inline is for demos and tests only, not production. The demo then runs the same data again without protection (a "retry"), which doubles the table to 30 rows. The protected form skips the load when a batch with the same tag already exists:

.ingest inline into table Orders with (format="csv", tags='["ingest-by:orders_batch_01"]', ingestIfNotExists='["orders_batch_01"]') <|
1001,C001,P01,2026-10-01,2,25.00,India,Shipped,2026-10-01 09:00:00
Orders
| count

.show table Orders extents
| project ExtentId, RowCount, Tags, MinCreatedOn

ingestIfNotExists='["orders_batch_01"]' checks for an extent tagged ingest-by:orders_batch_01. One exists, so nothing is ingested and the row count stays at 30. The .show table Orders extents command lists the extents with their tags and row counts, so you can see what protects you.

Missing data

A gap can be a NULL, an empty string, a placeholder such as "N/A" or -1, a missing row, or a missing dimension member. First standardize the placeholders, then decide what each gap means:

  • Drop the row when it is unusable, for example when the key is missing. df.na.drop(subset=["OrderId"]), WHERE OrderId IS NOT NULL, where isnotempty(OrderId).
  • Replace with a default when a default is meaningful. fillna, ISNULL, COALESCE (the first non-null of several values), coalesce() and iff() in KQL.
  • Impute (for example, with an average or the previous value) only with a rule you can explain, because it changes the statistics.
  • Flag it. Keep a column such as Country_missing so analysts can tell real values from filled ones.

Table of ways to handle missing values in PySpark, T-SQL and KQL: drop, replace with a default, coalesce, empty string to null, test for missing, flag, missing dimension member, fill forward and missing time buckets

Some details matter:

  • fillna is type-aware. A string default fills only string columns and a number fills only numeric ones. df.fillna({"Country": "Unknown", "Qty": 0}) sets each column's own default.
  • Strings in KQL are never null. A missing string is an empty string, so use isempty() and isnotempty() for text. Use isnull() for numbers, dates and booleans.
  • NULLIF(c, '') turns an empty string into null in T-SQL, which makes COALESCE and aggregates behave.
  • Do not use 0 for a missing average input. Replacing nulls with 0 lowers the average. Leave them null, which the aggregate skips, or impute deliberately.
  • Missing dimension member. Left join and set the key to the Unknown member (-1), as in Episode 5. Never drop the fact row.
  • Fill forward. For sensor-like data, carry the last known value forward. In Spark use last(..., ignorenulls=True) over a window. In KQL use make-series and series_fill_forward(). make-series also creates the rows for time buckets that have no data, with a default value.
from pyspark.sql import Window

w = (Window.partitionBy("DeviceId").orderBy("Ts")
           .rowsBetween(Window.unboundedPreceding, Window.currentRow))
filled = readings.withColumn("Temp", F.last("Temp", ignorenulls=True).over(w))
Readings
| make-series Temp = avg(Temp) default=real(null)
              on Ts from datetime(2026-11-01) to datetime(2026-11-02) step 1h by DeviceId
| extend Temp = series_fill_forward(Temp)

From the demo: profile, standardize and fill

The order is always profile, standardize placeholders, then decide. Counting only nulls hides most of the junk.

PySpark. First count the nulls in every column, then standardize the text:

display(orders_dedup.select([F.sum(F.col(c).isNull().cast("int")).alias(c)
                             for c in orders_dedup.columns]))

Only one Country shows as null, but N/A, " india " and india are problems too.

PLACEHOLDERS = ["", "N/A", "NA", "NULL", "-", "UNKNOWN"]

orders_std = orders_dedup.withColumn(
    "Country",
    F.when(F.upper(F.trim("Country")).isin(PLACEHOLDERS), F.lit(None))
     .when(F.length(F.trim("Country")) <= 3, F.upper(F.trim("Country")))   # codes: UK, USA
     .otherwise(F.initcap(F.trim("Country")))                              # names: India
)

display(orders_std.groupBy("Country").count().orderBy("Country"))
  • Placeholders become real nulls: isin(PLACEHOLDERS) on the trimmed, upper-cased country catches "", N/A, NA, NULL, - and UNKNOWN in any case.
  • Short values (3 characters or fewer) are treated as codes and upper-cased (UK, USA). Longer values are names and get initcap (India). Now " india ", india and India are one value.

Filling with 0 distorts averages. Compare the average quantity with the nulls ignored against the one after filling them with 0:

filled_zero = orders_std.fillna({"Country": "Unknown", "Qty": 0})

display(
    orders_std.agg(F.avg("Qty").alias("AvgQty_nulls_ignored"))
    .crossJoin(filled_zero.agg(F.avg("Qty").alias("AvgQty_after_fill_0")))
)

The second average is lower, because the missing quantity now counts as a real zero. The demo's decision for silver is to fill Country with "Unknown", keep Qty as null, and add a flag so the gap stays visible:

orders_clean = (orders_std
                .withColumn("QtyMissing", F.col("Qty").isNull())
                .fillna({"Country": "Unknown"}))

display(orders_clean.orderBy("OrderID"))

T-SQL. The profile counts nulls and placeholders, and trailing spaces:

SELECT  SUM(CASE WHEN Qty IS NULL THEN 1 ELSE 0 END)                                     AS QtyNull,
        SUM(CASE WHEN Country IS NULL THEN 1 ELSE 0 END)                                 AS CountryNull,
        SUM(CASE WHEN UPPER(TRIM(Country)) IN ('', 'N/A', 'NA', 'NULL', '-', 'UNKNOWN')
                 THEN 1 ELSE 0 END)                                                      AS CountryPlaceholder,
        SUM(CASE WHEN Country <> TRIM(Country) THEN 1 ELSE 0 END)                        AS CountryNeedsTrim
FROM ep7.OrdersTyped;

Standardizing needs more code than in Spark, because T-SQL has no INITCAP:

SELECT  Country                                                         AS CountryRaw,
        CASE WHEN Country IS NULL
               OR UPPER(TRIM(Country)) IN ('', 'N/A', 'NA', 'NULL', '-', 'UNKNOWN') THEN NULL
             WHEN LEN(TRIM(Country)) <= 3 THEN UPPER(TRIM(Country))                       -- UK, USA
             ELSE UPPER(LEFT(TRIM(Country), 1)) + LOWER(SUBSTRING(TRIM(Country), 2, 100)) -- India
        END                                                             AS CountryClean,
        COUNT(*)                                                        AS Rows
FROM ep7.OrdersTyped
GROUP BY Country;

The CASE expression makes placeholders NULL, upper-cases short codes, and builds the capitalized name by hand from UPPER(LEFT(...)) + LOWER(SUBSTRING(...)). The same average trap, in SQL:

SELECT  AVG(CAST(Qty AS DECIMAL(10,4)))               AS AvgQty_NullsIgnored,
        AVG(CAST(ISNULL(Qty, 0) AS DECIMAL(10,4)))    AS AvgQty_After_ISNULL_0
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ModifiedAt DESC) AS rn
      FROM ep7.OrdersTyped) t
WHERE rn = 1;

AVG ignores nulls, and ISNULL(Qty, 0) lowers the average. The ROW_NUMBER() subquery keeps only the latest version of each order first.

Fill forward has no one-word function in T-SQL, but there is a neat trick:

WITH prices AS
(
    SELECT * FROM (VALUES ('P01', CAST('2026-11-01' AS DATE), CAST(25.0  AS DECIMAL(10,2))),
                          ('P01', CAST('2026-11-02' AS DATE), CAST(NULL  AS DECIMAL(10,2))),
                          ('P01', CAST('2026-11-03' AS DATE), CAST(NULL  AS DECIMAL(10,2))),
                          ('P01', CAST('2026-11-04' AS DATE), CAST(26.0  AS DECIMAL(10,2))),
                          ('P02', CAST('2026-11-01' AS DATE), CAST(120.0 AS DECIMAL(10,2))),
                          ('P02', CAST('2026-11-02' AS DATE), CAST(NULL  AS DECIMAL(10,2)))) v(ProductID, PriceDate, Price)
),
grouped AS
(
    SELECT *, COUNT(Price) OVER (PARTITION BY ProductID ORDER BY PriceDate ROWS UNBOUNDED PRECEDING) AS grp
    FROM prices
)
SELECT ProductID, PriceDate, Price,
       MAX(Price) OVER (PARTITION BY ProductID, grp) AS PriceFilled
FROM grouped
ORDER BY ProductID, PriceDate;

COUNT(Price) OVER (...) counts only non-null prices, so the number grows only on rows that have a price. Each null row therefore shares the group number of the last known price, and MAX(Price) OVER (PARTITION BY ProductID, grp) copies that price to every row of the group.

KQL. Strings are empty, never null, so the profile must use both tests:

OrdersLatest
| summarize QtyIsNull     = countif(isnull(Qty)),
            CountryIsNull = countif(isnull(Country)),
            CountryEmpty  = countif(isempty(Country)),
            Rows          = count(),            // count() counts ROWS, not non-null values
            RowsWithQty   = countif(isnotnull(Qty))

CountryIsNull is 0 even though one country is missing, because that value is an empty string. CountryEmpty finds it. Standardizing placeholders, and then the trap:

OrdersLatest
| extend c = trim(@"\s+", Country)
| extend CountryClean = case(toupper(c) in ("", "N/A", "NA", "NULL", "-", "UNKNOWN"), "Unknown",
                             strlen(c) <= 3, toupper(c),
                             strcat(toupper(substring(c, 0, 1)), tolower(substring(c, 1))))
| summarize Rows = count() by CountryRaw = Country, CountryClean
OrdersLatest
| where OrderID == 1008
| project OrderID, Country,
          WithCoalesce = coalesce(Country, "Unknown"),
          WithIff      = iff(isempty(Country), "Unknown", Country)

For order 1008, the country is an empty string. coalesce(Country, "Unknown") returns the empty string, not "Unknown", because an empty string is not null. iff(isempty(Country), "Unknown", Country) is the correct way for text. Averages behave as elsewhere:

OrdersLatest
| summarize AvgQty_NullsIgnored = avg(Qty),
            AvgQty_Fill0        = avg(coalesce(Qty, int(0)))

And the KQL fill forward, make-series to create the missing days and series_fill_forward() to carry the last value into them:

datatable(ProductID:string, PriceDate:datetime, Price:real)
[
    "P01", datetime(2026-11-01), 25.0,
    "P01", datetime(2026-11-04), 26.0,
    "P02", datetime(2026-11-01), 120.0
]
| make-series Price = avg(Price) default = real(null)
    on PriceDate from datetime(2026-11-01) to datetime(2026-11-05) step 1d
    by ProductID
| extend PriceFilled = series_fill_forward(Price)
| mv-expand PriceDate to typeof(datetime), Price to typeof(real), PriceFilled to typeof(real)
| order by ProductID asc, PriceDate asc

Product P01 has prices on 1 and 4 November only. make-series creates a row for every day with a null where there is no price, series_fill_forward fills the nulls, and mv-expand turns the arrays back into rows.

Late-arriving data

Every record has two times: event time (when it happened) and arrival time (when it reached Fabric). Data is late when it arrives after the load that should have included it. Batch pipelines have this problem too, not just streams.

Timeline showing three orders with event time and arrival time, one on time, one late by hours and one late by days, with a nightly load line, and three cards: late fact, late dimension and late in an Eventhouse, each with how to handle it

Late-arriving facts

An order from Monday shows up on Thursday. If your incremental load selects rows by event date (OrderDate = yesterday), it never sees that row. There are three fixes:

  • Filter on arrival or modified time. A watermark on ModifiedAt or IngestedAt catches late rows, as in the watermark pipeline from Episode 5.
  • Reprocess a lookback window. Every run re-reads, say, the last three days from the source, and replaces or merges that window. Late rows inside the window are caught.
  • Make the load idempotent. Use MERGE, or replace the slice with Delta's replaceWhere option so rerunning gives the same table:
window_start = "2026-11-01"      # re-read the last N days on every run
fresh = spark.table("bronze_orders").where(f"OrderDate >= '{window_start}'")
(fresh.write.format("delta").mode("overwrite")
      .option("replaceWhere", f"OrderDate >= '{window_start}'")
      .saveAsTable("silver_orders"))

Late facts also change aggregates you already published. Decide whether to restate history or to lock periods, and tell your users which.

Late-arriving dimensions

A sale arrives before the customer record. The lookup finds no match. Do not drop the row and do not leave a null key. Insert an inferred member (a placeholder dimension row with the business key and unknown attributes) or use the Unknown member (-1), then update the dimension and repair the fact rows when the real data arrives. For a type 2 dimension, a late change also means closing and opening versions with corrected dates and pointing the affected facts at the right version.

Late data in an Eventhouse

  • ingestion_time() returns approximately when a record was ingested, in UTC. It returns null if the IngestionTime policy was not enabled when the data was ingested, and null when used outside a table or materialized view. It is approximate, and it is measured at the end of ingestion, so you cannot use it to order records. Keep the timestamp from the source system for ordering.
  • To process each record exactly once, use database cursors, not a time range.
  • A materialized view includes late rows in its aggregate. A deduplication view with a lookback only compares against recently ingested records, so a repeat that arrives after the window is not caught.
  • Update policies and joins. Update policies and materialized views can use joins, but only if the matching data is available when they run. If the dimension row is ingested after the fact, the policy can miss it. Load dimensions first, or enrich later at query time with lookup.
Orders
| extend IngestedAt = ingestion_time()
| where IngestedAt > ago(1h)        // arrived in the last hour, even if the event is old
| summarize Arrived = count(), OldestEvent = min(Timestamp)

Streaming engines handle lateness with watermarks: a threshold such as "accept events up to 10 minutes late". We cover them in the streaming episodes. For batch, lookback windows and idempotent loads do the same job.

From the demo: batch 2 arrives

Batch 2 lands on 2026-11-10: an update to 1002 (Delivered), a late fact 1010 (an October order), a new order 1015 from the not-yet-known customer C005, a repeat of 1013, and a new Channel column. Every engine handles the same five steps.

1. Schema drift. A new column arrives in the file. Let the table grow instead of failing:

orders_batch_02 = """OrderID,CustomerID,ProductID,OrderDate,Qty,UnitPrice,Country,Status,ModifiedAt,Channel
1002,C002,P02,2026-10-01,1,120.00,India,Delivered,2026-11-10 09:00:00,Web
1010,C001,P03,2026-10-03,2,15.50,India,Shipped,2026-11-10 09:05:00,Store
1015,C005,P01,2026-11-09,2,25.00,Germany,Shipped,2026-11-10 09:10:00,Web
1013,C002,P01,2026-11-02,6,25.00,India,Shipped,2026-11-02 10:00:00,Web
"""
write_text(f"{LANDING_FS}/orders/orders_batch_02.csv", orders_batch_02)

bronze_b2 = (spark.read.option("header", True)
                  .csv(f"{LANDING_SPARK}/orders/orders_batch_02.csv")
                  .withColumn("_source_file", F.col("_metadata.file_path"))
                  .withColumn("_ingested_at", F.current_timestamp()))

# Schema drift: append the new Channel column with mergeSchema (old rows get null)
(bronze_b2.write.mode("append").option("mergeSchema", True).saveAsTable(f"{P}bronze_orders"))

spark.table(f"{P}bronze_orders").printSchema()
display(spark.table(f"{P}bronze_orders").groupBy("_source_file").count())

In Spark, option("mergeSchema", True) adds the new column to the Delta table, and old rows get null in it. In T-SQL you add a nullable column and then insert the new rows:

ALTER TABLE ep7.StagingOrders ADD Channel VARCHAR(20) NULL;
INSERT INTO ep7.StagingOrders
       (OrderID, CustomerID, ProductID, OrderDate, Qty, UnitPrice, Country, Status, ModifiedAt, Channel, SourceFile, LoadedAt)
VALUES ('1002','C002','P02','2026-10-01','1','120.00','India'  ,'Delivered','2026-11-10 09:00:00','Web'  ,'orders_batch_02.csv',SYSUTCDATETIME()),
       ('1010','C001','P03','2026-10-03','2','15.50' ,'India'  ,'Shipped'  ,'2026-11-10 09:05:00','Store','orders_batch_02.csv',SYSUTCDATETIME()),
       ('1015','C005','P01','2026-11-09','2','25.00' ,'Germany','Shipped'  ,'2026-11-10 09:10:00','Web'  ,'orders_batch_02.csv',SYSUTCDATETIME()),
       ('1013','C002','P01','2026-11-02','6','25.00' ,'India'  ,'Shipped'  ,'2026-11-02 10:00:00','Web'  ,'orders_batch_02.csv',SYSUTCDATETIME());

In KQL, .alter-merge adds the column, and the materialized views follow because of autoUpdateSchema=true. The batch is tagged and protected with ingestIfNotExists:

.alter-merge table Orders (Channel:string)

.ingest inline into table Orders with (format="csv", tags='["ingest-by:orders_batch_02"]', ingestIfNotExists='["orders_batch_02"]') <|
1002,C002,P02,2026-10-01,1,120.00,India,Delivered,2026-11-10 09:00:00,Web
1010,C001,P03,2026-10-03,2,15.50,India,Shipped,2026-11-10 09:05:00,Store
1015,C005,P01,2026-11-09,2,25.00,Germany,Shipped,2026-11-10 09:10:00,Web
1013,C002,P01,2026-11-02,6,25.00,India,Shipped,2026-11-02 10:00:00,Web

2. Event date versus watermark. The naive load takes rows with an OrderDate later than the last loaded date. The correct load takes rows whose ModifiedAt is later than the watermark:

silver_now = spark.table(f"{P}silver_orders")
last_event_date = silver_now.agg(F.max("OrderDate")).first()[0]
watermark       = silver_now.agg(F.max("ModifiedAt")).first()[0]
print("Last OrderDate in silver:", last_event_date, "| Watermark (max ModifiedAt):", watermark)

bronze_typed = to_silver(spark.table(f"{P}bronze_orders"))

naive = bronze_typed.where(F.col("OrderDate") > F.lit(last_event_date))
incremental = bronze_typed.where(F.col("ModifiedAt") > F.lit(watermark))

print("Naive event-date load picks up:", [r.OrderID for r in naive.collect()])
print("Watermark load picks up:       ", sorted(r.OrderID for r in incremental.collect()))

The naive load finds only 1015, so it misses the late order 1010 and the update to 1002. The watermark load finds 1002, 1010 and 1015, and correctly skips the repeat 1013, whose ModifiedAt is not newer. The same comparison in T-SQL and KQL:

DECLARE @LastEventDate DATE         = (SELECT MAX(OrderDate)  FROM ep7.SilverOrders);
DECLARE @Watermark     DATETIME2(6) = (SELECT MAX(ModifiedAt) FROM ep7.SilverOrders);

SELECT 'Naive: OrderDate > last loaded date' AS Approach, OrderID, OrderDate, ModifiedAt, Status
FROM ep7.vw_SilverCandidates WHERE OrderDate > @LastEventDate          -- only 1015: misses 1002 + 1010
UNION ALL
SELECT 'Watermark: ModifiedAt > max(ModifiedAt)', OrderID, OrderDate, ModifiedAt, Status
FROM ep7.vw_SilverCandidates WHERE ModifiedAt > @Watermark             -- 1002, 1010, 1015
ORDER BY Approach, OrderID;
let LastEventDate = datetime(2026-11-05);
let Watermark     = datetime(2026-11-05 11:00:00);
union
    (OrdersLatest | where OrderDate  > LastEventDate | extend Approach = "Naive: OrderDate > last loaded date"),
    (OrdersLatest | where ModifiedAt > Watermark     | extend Approach = "Watermark: ModifiedAt > max(ModifiedAt)")
| project Approach, OrderID, OrderDate, ModifiedAt, Status
| order by Approach asc, OrderID asc

3. Load with a lookback window, then run it again. A lookback re-reads everything modified in the last N days. The overlap is deliberate, and the guard makes it harmless:

LOOKBACK_DAYS = 14
lookback = bronze_typed.where(F.col("ModifiedAt") >= F.lit(watermark) - F.expr(f"INTERVAL {LOOKBACK_DAYS} DAYS"))
print("Lookback picks up:", sorted(r.OrderID for r in lookback.collect()))

upsert_silver(lookback)
display(last_merge_metrics(f"{P}silver_orders"))
display(spark.table(f"{P}silver_orders").where("OrderID IN (1002, 1010, 1013, 1015)").orderBy("OrderID"))
# Run batch 2 again: nothing should change
before = fingerprint(f"{P}silver_orders")
upsert_silver(lookback)
print("Idempotent on rerun:", before == fingerprint(f"{P}silver_orders"))

The rerun check prints True when the table is unchanged. In T-SQL, call the procedure from earlier twice, and compare with the snapshot:

EXEC ep7.usp_LoadSilver @LookbackDays = 14;
DROP TABLE IF EXISTS ep7.SilverSnapshot;
CREATE TABLE ep7.SilverSnapshot AS SELECT * FROM ep7.SilverOrders;
GO
EXEC ep7.usp_LoadSilver @LookbackDays = 14;
GO
SELECT  (SELECT COUNT(*) FROM (SELECT * FROM ep7.SilverOrders   EXCEPT SELECT * FROM ep7.SilverSnapshot) a) AS RowsAdded,
        (SELECT COUNT(*) FROM (SELECT * FROM ep7.SilverSnapshot EXCEPT SELECT * FROM ep7.SilverOrders)   b) AS RowsLost;

In the Eventhouse, the materialized views take care of this on their own. After batch 2, OrdersLatest shows 1002 as Delivered and includes the late 1010, and OrdersDedup counts the repeated 1013 once:

OrdersLatest
| where OrderID in (1002, 1010, 1013, 1015)
| project OrderID, OrderDate, Status, ModifiedAt, Channel
| order by OrderID asc

OrdersDedup
| where OrderID == 1013
| count

4. Arrival time in an Eventhouse. ingestion_time() returns the (approximate) arrival time. Every row of a batch shares almost the same value, so it is good for finding "what is new" but not for ordering events:

Orders
| extend IngestedAt = ingestion_time()
| summarize Rows = count(), DistinctIngestTimes = dcount(IngestedAt), FirstIngest = min(IngestedAt), LastIngest = max(IngestedAt)

For exactly-once processing downstream, use a database cursor: save cursor_current() after each run and, next time, read only Orders | where cursor_after('<saved cursor>').

print CurrentCursor = cursor_current()

5. The late dimension. Customer C005 finally arrives. In the lakehouse and the Warehouse, gold is rebuilt from silver, so adding the row and rebuilding repairs orders 1009 and 1015. Nothing needs backfilling, because the fact rows were never dropped:

print("Before: rows on Unknown customer")
display(spark.table(f"{P}gold_sales_wide").where("CustomerKey = -1").select("OrderID", "SourceCustomerID", "CustomerKey"))

spark.createDataFrame([(5, "C005", "Eagle GmbH", "Corporate", "Berlin")],
                      spark.table(f"{P}src_customer").schema) \
     .write.mode("append").saveAsTable(f"{P}src_customer")

build_dim_customer()
build_gold_sales_wide()

print("After: only the customer that never existed (C999) is still Unknown")
display(spark.table(f"{P}gold_sales_wide").where("CustomerKey = -1 OR SourceCustomerID = 'C005'")
                                          .select("OrderID", "SourceCustomerID", "CustomerKey", "CustomerName"))
INSERT INTO ep7.SrcCustomer VALUES (5, 'C005', 'Eagle GmbH', 'Corporate', 'Berlin');
EXEC ep7.usp_BuildGold;
SELECT OrderID, SourceCustomerID, CustomerKey, CustomerName
FROM ep7.FactSalesWide
WHERE CustomerKey = -1 OR SourceCustomerID = 'C005'
ORDER BY OrderID;                         -- only C999 stays on -1

After the rebuild, only customer C999, who never existed, stays on the Unknown member.

The Eventhouse behaves differently, and this is a classic exam question. The update policy enriched 1009 and 1015 before C005 existed, so their customer name is empty:

OrdersEnriched
| where CustomerID == "C005"
| project OrderID, CustomerID, CustomerName
.append Customers <|
datatable(CustomerID:string, CustomerName:string, Segment:string, City:string)
[ "C005", "Eagle GmbH", "Corporate", "Berlin" ]

After C005 is appended, query the enriched table again. The names are still empty, because an update policy runs once, at ingestion, and never reruns. The query-time function is correct immediately:

SalesWide
| where CustomerID == "C005"
| project OrderID, CustomerID, CustomerName

To repair the ingestion-time table, backfill: rerun the enrichment over the whole table. Outside the policy, EnrichOrders() reads all of Orders, not just one batch:

.set-or-replace OrdersEnriched <|
EnrichOrders()

So there are three answers to "enrichment is null for a late dimension": load dimensions first, enrich at query time, or backfill once the dimension arrives.

6. Restate only the affected dates. Instead of overwriting the whole daily table, recompute only the date range that this batch touched. In Delta this is replaceWhere:

affected = lookback.agg(F.min("OrderDate").alias("lo"), F.max("OrderDate").alias("hi")).first()
predicate = f"OrderDate >= '{affected.lo}' AND OrderDate <= '{affected.hi}'"
print("Replacing slice:", predicate)

restated = (spark.table(f"{P}gold_sales_wide")
                 .where("Status <> 'Cancelled'")
                 .where(predicate)
                 .groupBy("OrderDate").agg(F.sum("LineAmount").alias("Revenue")))

(restated.write.mode("overwrite")
         .option("replaceWhere", predicate)
         .saveAsTable(f"{P}gold_daily_sales"))

display(spark.table(f"{P}gold_daily_sales").orderBy("OrderDate"))
  • The predicate is built from the smallest and largest OrderDate in the batch. replaceWhere replaces only the rows that match the predicate and fails if the new data falls outside it.
  • Running it again gives the same result, so it is idempotent, and it is much cheaper than rewriting a big table.

The Warehouse has no replaceWhere, so it restates the slice with a transaction: delete the range and insert it again. If anything fails, the transaction rolls back, and the table never shows a half-restated state:

DECLARE @lo DATE = (SELECT MIN(TRY_CAST(OrderDate AS DATE)) FROM ep7.StagingOrders WHERE SourceFile = 'orders_batch_02.csv');
DECLARE @hi DATE = (SELECT MAX(TRY_CAST(OrderDate AS DATE)) FROM ep7.StagingOrders WHERE SourceFile = 'orders_batch_02.csv');

BEGIN TRANSACTION;
    DELETE FROM ep7.DailyRevenue WHERE OrderDate BETWEEN @lo AND @hi;

    INSERT INTO ep7.DailyRevenue (OrderDate, Revenue)
    SELECT OrderDate, SUM(LineAmount)
    FROM ep7.FactSalesWide
    WHERE Status <> 'Cancelled' AND OrderDate BETWEEN @lo AND @hi
    GROUP BY OrderDate;
COMMIT TRANSACTION;

SELECT @lo AS RestatedFrom, @hi AS RestatedTo;
SELECT * FROM ep7.DailyRevenue ORDER BY OrderDate;

Choosing the right pattern

Match the symptom to the fix. The first yes is your answer.

Decision flow: the latest version per key uses row_number or arg_max; the same event delivered twice uses dropDuplicates, insert-only MERGE or a take_any materialized view; rows days late use a lookback and upsert; a fact before its dimension uses an inferred member; late Eventhouse events use cursors and a materialized view

When an exam question describes a need, find it on the left and pick the answer on the right.

Cheat sheet matching common transformation, aggregation, duplicate, missing-data and late-data needs to the right PySpark, T-SQL or KQL feature

Common mix-ups

  • "You can INSERT or UPDATE through the lakehouse SQL analytics endpoint." It is read-only. Write with Spark, a pipeline or a dataflow, or use the Warehouse for T-SQL writes.
  • "The Warehouse supports triggers, recursive queries and materialized views." It does not. KQL has materialized views, and the Warehouse does not.
  • "A primary key stops duplicates in the Warehouse." Keys are accepted only as NOT ENFORCED. Your ETL must check them.
  • "PySpark runs each line as you type it." Transformations are lazy and run when an action such as write or count is called.
  • "dropDuplicates(key) keeps the newest row." It keeps an arbitrary one. Use a window function when the newest must win.
  • "distinct() and dropDuplicates(subset) are the same." distinct() compares every column.
  • "MERGE quietly picks one of several matching source rows." It fails or is ambiguous. Deduplicate the source to one row per key.
  • "The default KQL join is an inner join." It is innerunique, which removes duplicate left keys first.
  • "lookup works with a big right-hand table." The right side is broadcast and should be small. A few tens of MB is the limit.
  • "A missing string in KQL is null." It is empty. Use isempty().
  • "KQL count() skips nulls." It counts rows. Use countif(isnotnull(x)).
  • "dcount() is exact." It is approximate. Use count_distinct() for exact.
  • "An aggregate over an empty table with no group-by returns no rows." It returns one row.
  • "An update policy can aggregate across batches." It sees one ingestion batch. Use a materialized view.
  • "Materialized views run during ingestion." They materialize in the background, but queries still see fresh data because the view adds the unprocessed records at query time.
  • "A left join never changes the number of fact rows." It can multiply them if the dimension key is not unique.
  • "Replace missing numbers with 0 for averages." That drags the average down. Leave nulls or impute with a rule.
  • "fillna(0) fills text columns too." A number fills only numeric columns.
  • "A load by event date catches late rows." It never revisits old dates. Load by arrival time or reprocess a lookback window.
  • "Late data is only a streaming problem." Batch loads have it too.
  • "Drop a fact row when its dimension is missing." Use the Unknown member and repair it later.
  • "ingestion_time() can order records." It cannot. It is approximate, and it is null if the policy was off at ingestion.
  • "An empty string cast to INT in T-SQL gives null." TRY_CAST('' AS INT) returns 0, and an empty string cast to a date returns 1900-01-01. Wrap the value with NULLIF(TRIM(x), '') first.
  • "coalesce(Country, "Unknown") fixes a missing country in KQL." A missing string is empty, not null, so coalesce does nothing. Use iff(isempty(Country), "Unknown", Country).
  • "Loading the same batch twice is harmless in an Eventhouse." It doubles the rows. Use an ingest-by tag with ingestIfNotExists, or a deduplication view.
  • "A primary key in the Warehouse rejects a duplicate." The demo inserts two rows with the same key without an error, because the key is NOT ENFORCED.
  • "Adding the late customer repairs the Eventhouse enrichment automatically." An update policy runs once, at ingestion. Backfill the table, or enrich at query time.
  • "explode, CROSS APPLY and mv-expand keep orders with no items." They drop them. Use explode_outer, OUTER APPLY or turn the empty array into [null] first.
  • "union in PySpark matches columns by name." It matches by position. Use unionByName.

Try these scenarios

Ten exam-style questions. Cover the answers and test yourself.

1. The slow product hierarchy

Every report joins Sales to Product, Subcategory and Category. The model is slow and report authors are confused. What do you change?

Answer: Denormalize. In the silver to gold step, join the three tables into one flat DimProduct with Product, Subcategory and Category (a CTAS in T-SQL, or joins in a notebook), and point the fact table at it.

2. The join that lost rows

A KQL query joins Events to Devices on DeviceId, and the result has fewer rows than Events. Why?

Answer: The default join kind is innerunique, which removes duplicate keys from the left table first, and it drops unmatched rows. Use kind=leftouter, or the lookup operator for a small dimension.

3. Writing from the SQL endpoint

A developer runs an INSERT against a lakehouse table through its SQL analytics endpoint and gets an error. What are the options?

Answer: The endpoint is read-only. Write the data with Spark (a notebook), a pipeline or a dataflow, or load it into a Warehouse, where T-SQL writes are supported.

4. The wrong order wins

A notebook uses dropDuplicates(["OrderId"]) on a bronze table that holds several versions of each order. Sometimes an old version survives. How do you fix it?

Answer: dropDuplicates keeps an arbitrary row. Use a window function: row_number() over partitionBy("OrderId") ordered by ModifiedAt descending, and keep row 1.

5. The failing merge

A Delta MERGE into silver_orders fails because several source rows match the same target row. What is the cause and the fix?

Answer: The source has duplicate keys. Deduplicate the source to one row per OrderId (latest by ModifiedAt) before the merge, and keep a s.ModifiedAt > t.ModifiedAt condition so a late older row never wins.

6. Events delivered twice

An eventstream delivers each event at least once. Analysts query an Eventhouse table and see duplicates, but only for repeats that arrive within a few hours. What is a low-effort fix?

Answer: A materialized view that groups by EventId with take_any(*) and a lookback of a few hours. Query the view instead of the raw table.

7. Orders that show up days late

A nightly load copies yesterday's rows by OrderDate. Finance reports that some orders are missing, and they appear in the source days later. What do you change?

Answer: Stop selecting by event date. Use a watermark on the modified or arrival time, or reprocess a lookback window (for example the last 3 days) on every run, and write it with MERGE or replaceWhere so the load is idempotent.

8. The enrichment that returns nulls

An update policy enriches telemetry with device details using lookup. For new devices the enriched columns are empty, even after the device record is loaded a minute later. Why?

Answer: The update policy ran at ingestion, when the device row did not exist yet, and it does not rerun. Load the dimension before the facts, or enrich at query time with lookup, or backfill the affected rows once the dimension arrives.

9. The zero that was never ordered

A Warehouse load casts an empty Qty column with TRY_CAST(Qty AS INT). An analyst notices orders with a quantity of 0, and the average quantity is too low. What happened, and what is the fix?

Answer: In T-SQL, casting an empty string gives 0, not null. Use TRY_CAST(NULLIF(TRIM(Qty), '') AS INT), so an empty value becomes NULL, which AVG then skips.

10. The retry that doubled the table

A pipeline ingests a file into an Eventhouse table. The activity fails after the load, the pipeline retries, and now every row exists twice. How do you stop the retry from duplicating data?

Answer: Tag the ingestion with an ingest-by: tag and set ingestIfNotExists to the same value. When an extent with that tag already exists, the retry is skipped. A take_any(*) materialized view with a lookback is the second line of defense for repeats the tag does not catch.

Where to go next

Keep reading

DP-700FabricSpark Structured StreamingKQLWindowingReal-Time Intelligence

DP-700 Episode 11: Streaming II, Spark & KQL

DP-700 study guide told as a story: run Spark Structured Streaming in production, process data with KQL, and write tumbling, hopping, sliding and session windows.

potphodepraful ·

DP-700FabricEventstreamT-SQLShortcutsOptimization

DP-700 Episode 9: Resolving Errors II & Optimization I

DP-700 study guide: fix Eventstream, T-SQL and OneLake shortcut errors, then tune Lakehouse tables and pipeline Copy activities in Microsoft Fabric.

potphodepraful ·

DP-700FabricEventstreamReal-Time IntelligenceStreamingEventhouse

DP-700 Episode 10: Streaming I, Engines & Eventstream

DP-700 study guide told as a story: pick a streaming engine, choose native tables or shortcuts, decide on query acceleration, and build an Eventstream.

potphodepraful ·