Launch Deal · 50% off 1st month ·

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

  1. Profile first (Power Query column quality, distribution and profile; df.describe(); COUNT(*) grouped by value) so you know what's wrong.
  2. Standardize: trim, fix case, map synonyms ("UK", "U.K.", "United Kingdom" → "United Kingdom").
  3. Validate: types, ranges and required keys. Send rejects to a quarantine table instead of losing them.
  4. Deduplicate on the business key, keeping the right version.
  5. 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_CONVERT return 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 DISTINCT to keep the latest version of a record
  • Using CAST where bad values must not fail the load (use TRY_CAST and 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.

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.