What You'll Master Here
clean (make it valid), shape (combine it), aggregate (summarise it). Do them in that sequence and the result is trustworthy.
Transformation is the stage where raw, untrustworthy data becomes data people can build decisions on. It is the "T" in ETL/ELT and the part most people picture when they hear "data pipeline". This chapter teaches the fundamentals that apply no matter which tool (SQL, dbt, Spark) you use.
The core idea is that almost every transformation is one of three jobs, cleaning, shaping, or aggregating, applied in that order. Get the jobs and the order right and your transformations are correct and easy to reason about. Get them wrong, usually by aggregating before cleaning, and you produce confident, wrong numbers.
By the end you will be able to take a messy raw table and turn it into a trustworthy metric step by step, and explain why each step happens where it does. You will also understand why set-based thinking (one operation over all rows) beats row-by-row loops at the scales data engineers actually work at.
Transformation is three jobs in order: clean (make it valid), shape (combine it), aggregate (summarise it). Do them in that sequence and the result is trustworthy.
Transformation is where correctness is won or lost. Unlike a crash, a transformation bug produces no error, just wrong numbers that look fine and get acted on. Mastering the fundamentals is how you avoid being the source of "the dashboard is wrong".
- transformation
- Turning raw data into trustworthy data by cleaning, shaping, and aggregating it.
- cleaning
- Making data valid: dropping bad rows, fixing types, standardising, de-duplicating.
- shaping
- Combining data: filtering, joining, and enriching into one coherent structure.
- aggregating
- Summarising rows into metrics: counts, sums, and rollups.
Treating transformation as "just writing some SQL". You skip the disciplined order (clean → shape → aggregate) and bake bad rows into metrics nobody can untangle.
Classify every transform as cleaning, shaping, or aggregating.
Always clean before you aggregate.
Prefer set-based operations over row-by-row loops.
Clean, then shape, then aggregate. This order is not a style preference; doing it out of order is how wrong numbers are born. Hold the order and most transformation bugs disappear.
Read the three-jobs topic, then each job with its worked example, then the row-vs-set topic. Notice how each example cleans before it aggregates.
Transformation turns raw data into trustworthy data through three ordered jobs, clean, shape, aggregate; the order is the discipline that keeps metrics correct.
- Name the three transformation jobs and the order to apply them.
- Explain why aggregating before cleaning produces untrustworthy numbers.