Launch Deal · 50% off 1st month ·

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), and WHERE CustomerID IS NOT NULL.
  • KQL: isnull(), isempty() (for empty strings), coalesce() and iff().
  • To standardize text, use F.initcap(), F.upper() or F.trim() in Spark, and UPPER() or TRIM() 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, CUBE or GROUPING SETS produce 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.

See plans

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.