2.6 — Transform Data with PySpark, SQL and KQL
DP-700 Domain 2 · Skill 2.6
What You Need to Know
The exam expects you to read transformation code in three languages and choose the line that does the job. The same five tasks come up again and again: denormalize, aggregate, remove duplicates, handle missing values, handle late-arriving data.
Denormalize (flatten related tables)
Join the normalized tables into one wide table, which suits dimensions and reporting.
# PySpark: store -> district -> region, keeping only stores that are open
dim_store = (stores.filter(F.col("Status") == "Open")
.join(districts, "DistrictID", "left")
.join(regions, "RegionID", "left"))
- A left join keeps every store, even one whose district is missing (its district columns become null). An inner join keeps only rows that match on both sides, so it would drop that store, and it also drops districts that have no stores.
- T-SQL:
SELECT ... FROM Store s LEFT JOIN District d ON ... LEFT JOIN Region r ON .... In KQL:| join kind=leftouter (District) on DistrictID.
Group and aggregate
| Language | Example |
|---|---|
| PySpark | df.groupBy("Region").agg(F.sum("Amount").alias("Total")) |
| T-SQL | SELECT Region, SUM(Amount) FROM Sales GROUP BY Region; |
| T-SQL subtotals | GROUP BY ROLLUP(Year, Product) adds a total per year and a grand total. CUBE adds every combination. GROUPING SETS lists the exact combinations you want |
| KQL | Sales | summarize Total = sum(Amount) by Region, bin(Timestamp, 1h) |
Remove duplicates
| Language | Exact duplicates | Keep the latest row per key |
|---|---|---|
| PySpark | df.dropDuplicates() |
dropDuplicates(["OrderID"]), or a Window with row_number() ordered by ModifiedAt desc, keeping rn = 1 |
| T-SQL | SELECT DISTINCT |
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ModifiedAt DESC) in a CTE, keeping rn = 1 |
| KQL | distinct * |
summarize arg_max(ModifiedAt, *) by OrderID |
dropDuplicates(["key"]) keeps an arbitrary row for each key. When "keep the latest" matters, order explicitly with a window function.
Handle missing values
- PySpark:
df.na.drop(subset=["CustomerID"])drops rows missing a required key.df.na.fill({"Country": "Unknown", "Qty": 0})fills defaults.F.coalesce(col1, col2)takes the first value that isn't null. - T-SQL:
COALESCE(Country, 'Unknown'),ISNULL(Qty, 0), andWHERE CustomerID IS NOT NULL. - KQL:
isnull(),isempty()(for empty strings),coalesce()andiff(). - To standardize text, use
F.initcap(),F.upper()orF.trim()in Spark, andUPPER()orTRIM()in T-SQL.
Handle late-arriving data
| Situation | Approach |
|---|---|
| A late fact (an event arrives after its period was loaded) | Load incrementally by ingestion time rather than event time, and MERGE by business key so reprocessing is safe. Recalculate the affected aggregates |
| A late dimension (a fact arrives before its dimension member) | Insert an inferred member that has only the business key, or point the fact at Unknown (−1). Update the member when its details arrive |
| Late events in a stream | A watermark (withWatermark("eventTime", "10 minutes")) tells Spark how long to wait before it closes a window. Events later than that are dropped |
Key Concept: Learn the matching code in each language: dropDuplicates / ROW_NUMBER / arg_max for duplicates, na.fill / COALESCE / coalesce() for missing values, and groupBy / GROUP BY / summarize for aggregation.
Exam Traps
- Using
dropDuplicates(["key"])when the requirement says to keep the most recent version - Using an inner join when rows without a match must be kept (or a left join when they must be dropped)
- Replacing missing keys with defaults (rows with missing keys should usually be dropped or quarantined; missing attributes get filled)
- Writing a separate query and a UNION for subtotals, when
ROLLUP,CUBEorGROUPING SETSproduce them in one pass
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Transform data with Apache Spark and query with SQL, Microsoft Learn
- Dimensional modeling: load tables (inferred members), Microsoft Learn
- GROUP BY (Transact-SQL): ROLLUP, CUBE, GROUPING SETS, Microsoft Learn
- arg_max() aggregation function (KQL), Microsoft Learn
- Structured Streaming: handling late data and watermarking, Apache Spark documentation