2.6 — Cleanse and Shape Data
DP-600 Domain 2 · Skill 2.6
What You Need to Know
DP-600 lists a set of shaping skills: cleanse, denormalize, aggregate or de-aggregate, merge or join, fix duplicates, missing values and nulls, convert types, filter. You should be able to do each one in SQL, PySpark and Power Query, and spot the right line of code.
A cleansing process
- Profile first (Power Query column quality, distribution and profile;
df.describe();COUNT(*)grouped by value) so you know what's wrong. - Standardize: trim, fix case, map synonyms ("UK", "U.K.", "United Kingdom" → "United Kingdom").
- Validate: types, ranges and required keys. Send rejects to a quarantine table instead of losing them.
- Deduplicate on the business key, keeping the right version.
- Conform: consistent codes and keys across sources.
Cheat sheet
| Task | T-SQL | PySpark | Power Query |
|---|---|---|---|
| Filter | WHERE Status = 'Active' |
df.filter(F.col("Status")=="Active") |
Filter rows |
| Join / merge | LEFT JOIN Customer c ON ... |
df.join(cust, "CustomerID", "left") |
Merge queries (Left outer) |
| Append | UNION ALL |
df1.unionByName(df2) |
Append queries |
| Remove exact duplicates | SELECT DISTINCT |
dropDuplicates() |
Remove duplicates |
| Keep latest per key | ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC) = 1 |
Window + row_number() |
Group by with Max, then merge back (or sort and remove duplicates) |
| Replace nulls | COALESCE(Region, 'Unknown') |
df.na.fill({"Region": "Unknown"}) |
Replace values (null) |
| Drop rows missing a key | WHERE CustomerID IS NOT NULL |
df.na.drop(subset=["CustomerID"]) |
Remove empty or filter |
| Convert type | CAST(Amount AS decimal(18,2)), TRY_CAST |
F.col("Amount").cast("decimal(18,2)") |
Change type |
| Aggregate | GROUP BY |
groupBy().agg() |
Group by |
| De-aggregate | Join to a calendar to spread totals | explode() on arrays or sequences |
Unpivot or expand |
| Denormalize | Join lookups into one wide table | Successive joins | Merge and expand columns |
Notes:
TRY_CAST/TRY_CONVERTreturn NULL instead of failing on bad values, so you can catch rejects.- De-aggregating means turning summary rows into finer grain: a monthly budget spread to days, or an array of items exploded into rows. You need it when the facts must match a finer-grained dimension.
- Unpivot turns month columns (Jan, Feb, ...) into rows (Month, Value). That's the shape models need.
Missing data versus null
- A null key usually means the row can't be linked: quarantine it, or map it to an Unknown member (−1).
- A null attribute often gets a default ("Unknown"), unless null has a meaning of its own.
- A missing row (a date with no sales) isn't null data. Handle it in the model or the measure, not by inventing rows.
Key Concept: Know each shaping task in all three languages. Profile, standardize, validate, deduplicate and conform, and keep the rejects you can't fix.
Exam Traps
- Using
DISTINCTto keep the latest version of a record - Using
CASTwhere bad values must not fail the load (useTRY_CASTand capture the nulls) - Using an inner join when unmatched rows must be kept
- Filling null business keys with a default value that turns them into a real member
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 17 DP-600 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
- Using the data profiling tools in Power Query, Microsoft Learn
- TRY_CAST (Transact-SQL), Microsoft Learn
- Unpivot columns in Power Query, Microsoft Learn