Transformation Fundamentals

A transformation changes data from one table into another table. It is the T in ETL (extract, transform, load) and ELT (extract, load, transform), which chapter 4 explained.

A wrong transformation often does not fail. It gives numbers that look fine and are wrong.

This chapter uses the same shop as the SQL chapters. In this chapter you will learn:

1. The three jobs of a transformation, and why their order matters.
2. How to clean messy values and keep one row per key.
3. How to join tables without losing rows.
4. How to add rows up into a metric.
5. Why one SQL query beats a row-by-row loop.
6. Where a transformation can run.

A transformation is one step that reads a table and builds a new table. A pipeline runs many of these steps, one after another.

Almost every step does one of three jobs. This table gives each job, with an example from the shop:

To shape means to filter rows, and to join tables so each row gains new columns. To aggregate means to add many rows up into fewer rows, such as one per country. The next sections show each job.

The order matters. There are three reasons:

• Clean before shape. A join on a key with a stray space finds no match.
• Shape before aggregate. You cannot group by country until the country column is there.
• Clean before aggregate. In chapter 2, one copied row gave 160.00 instead of 80.00.

Chapter 2 also named the layers. Cleaning builds silver tables, and aggregating builds gold tables. Chapter 4 showed where each step runs.

Cleaning makes each value valid. A valid value has the right type, one standard spelling and a real meaning.

Real data is messy. Here is a made-up raw table with the six orders, written with common faults. The query cleans it:

SELECT
  order_id,
  customer_id,
  LOWER(TRIM(status)) AS status,
  CAST(NULLIF(TRIM(amount), '') AS DECIMAL(10, 2)) AS amount,
  TRY_CAST(order_date AS DATE) AS order_date
FROM raw_orders_text
ORDER BY order_id;

The query makes five fixes:

• TRIM removes spaces at the start and the end. Order 102 had a space after its amount.
• LOWER makes all letters small. Order 101 (Paid) and order 102 (PAID) now say paid.
• NULLIF(text, '') turns an empty text into NULL, which means missing. This fixes order 105.
• CAST changes the type, here from text to a number with two decimals.
• TRY_CAST changes text to a date, but gives NULL if the date is not real. In most warehouses, plain CAST stops the whole query with an error.

Do not guess a date for order 106. A reject table is a separate table that keeps the rows you could not fix.

Send the row there and count it. Then ask the source to fix it.

Clean each column once, in the silver layer. If three tables each clean the name, they will disagree one day.

The next sections use the clean table, silver_orders. It holds the six real orders. The source fixed order 106, so its date is 2026-01-21.

Chapters 2 and 5 used DISTINCT. It removes copies that are exactly the same. Copies are not always the same.

A key is a column that is different for each real thing. In orders, the key is order_id.

Here is a made-up raw table where order 103 was loaded twice. It was paid on 2026-01-11 and refunded on 2026-01-12. A count of paid orders shows the problem:

SELECT COUNT(*) AS paid_orders
FROM raw_order_changes
WHERE status = 'paid';

DISTINCT cannot help here, because the two copies of order 103 differ in status and updated_at. We need a rule: keep the newest row for each order_id.

ROW_NUMBER() gives each row a number inside its group. PARTITION BY order_id makes one group for each order. ORDER BY updated_at DESC puts the newest row first.

The newest row gets number 1, in a column called rn (short for row number):

SELECT order_id, status, updated_at,
  ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY updated_at DESC
  ) AS rn
FROM raw_order_changes
ORDER BY order_id, rn;

Every order has a row with rn = 1. For order 103, it is the refunded copy.

WHERE runs before ROW_NUMBER(), so WHERE cannot see rn. A subquery is a query inside round brackets. Put the numbering in a subquery, then filter outside it:

SELECT order_id, customer_id, status, amount, updated_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM raw_order_changes
) AS ranked
WHERE rn = 1
ORDER BY order_id;

Order 103 now says refunded. A count of paid orders on this table gives 4, which is correct.

Shaping gets the rows and columns you need. A filter keeps some rows. To enrich is to add columns from another table.

Joins and relationships are taught in Joins & Relationship Reasoning. In short, a join puts two tables side by side where a key matches.

Asha moved from IN to DE on 2026-01-08, so customers now says DE for her. IN is India and DE is Germany.

A LEFT JOIN keeps every order, even when no customer matches.

This query keeps the paid orders and adds the name and country of each customer:

SELECT o.order_id, c.name, c.country, o.amount
FROM silver_orders AS o
LEFT JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.order_id;

WHERE removed order 103 (refunded) and order 105 (pending). Order 106 keeps its row, but its country is NULL, because Dev has no country in customers.

This is not an error. Later you can decide what to show for a missing country.

A join can also lose rows. In this made-up case, the customer load missed Dev. An INNER JOIN keeps only rows that match:

SELECT o.order_id, c.name, o.amount
FROM silver_orders AS o
INNER JOIN customers_late AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.order_id;

Change INNER JOIN to LEFT JOIN. Order 106 returns with a NULL name, and the count stays at four.

A join can also add rows. If the other table has many rows for one key, the order repeats. Here we join paid orders to order_items:

SELECT SUM(o.amount) AS revenue
FROM silver_orders AS o
JOIN order_items AS i
  ON i.order_id = o.order_id
WHERE o.status = 'paid';

Sum amount from silver_orders alone, without the join. Joins & Relationship Reasoning shows this in "Fan-out: sums that double".

Aggregating adds many rows into fewer rows. Aggregation & Business Metrics teaches GROUP BY and SUM, so this is a short recap.

The grain of a result is what one row means. Here, the grain is one row for each country.

This query joins the paid orders to their customers, then adds up by country:

SELECT
  COALESCE(c.country, 'unknown') AS country,
  COUNT(*) AS orders,
  SUM(o.amount) AS revenue
FROM silver_orders AS o
LEFT JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY COALESCE(c.country, 'unknown')
ORDER BY revenue DESC;

COALESCE gives the first value that is not NULL. Dev has no country, so order 106 is counted under unknown. Without COALESCE, GROUP BY would put it in a group with a NULL country.

This query uses each customer’s country from the customers table. Order 101 was placed while Asha lived in IN, but it counts under DE. Chapter 12, Incremental Processing & Backfills, shows how to use the country from the day of the order.

Now the same orders by day. This query counts every order, and adds up the paid amounts:

SELECT
  order_date,
  COUNT(*) AS orders,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0.00 END) AS paid_revenue
FROM silver_orders
GROUP BY order_date
ORDER BY order_date;

The rule "revenue means paid orders only" is a business rule. Write it in one place, in the gold table. Then every report gives 160.50.

Row-by-row code visits one row at a time. Set-based code says what you want from all the rows together. The engine is the program that runs the query, such as the warehouse.

The goal is the total paid revenue of silver_orders. First, a Python loop. Here orders holds the six rows as groups of three values (Python calls them tuples): order_id, status and amount:

from decimal import Decimal

total = Decimal("0")
for order_id, status, amount in orders:
    if status == "paid":
        total += Decimal(amount)

print(total)

Now the same goal in one SQL query:

SELECT SUM(amount) AS revenue
FROM silver_orders
WHERE status = 'paid';

Both give 160.50. The loop says how to do the work, step by step. The query says what you want, and the engine decides how.

That freedom is the benefit. The engine can often split the rows across machines, or read only the columns it needs. A loop in your own code cannot be split without a rewrite.

Loops that call a database for each row are the worst case. To add the country to the 4 paid orders, a loop asks the database 4 times. The join earlier asked once and returned the same 4 rows.

Chapter 4 showed that in ELT, the transformation runs inside the warehouse. Here we look at what can run it. This table gives four choices:

The logic stays the same. Here is the keep-the-newest step from earlier as a dbt model. A model is one SELECT in a file, and dbt builds a table or a view from it:

-- models/latest_orders.sql
SELECT order_id, customer_id, status, amount, updated_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM {{ source('shop', 'raw_order_changes') }}
) AS ranked
WHERE rn = 1

Here is the same step in PySpark, the Python tool for Spark. It gives the same six rows:

from pyspark.sql import Window
from pyspark.sql import functions as F

newest_first = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())

latest_orders = (
    raw_order_changes
    .withColumn("rn", F.row_number().over(newest_first))
    .filter("rn = 1")
    .drop("rn")
)

Whatever the tool, keep three rules for the layers:

• Never edit raw. It is the copy you can rebuild from.
• Clean once, in silver. Do not clean the same column again in gold.
• Add up in gold. Put each business rule in one place.

Start with the simplest tool that fits.

You can now follow the three jobs of a transformation. Remember these points:

• The jobs are clean, shape and aggregate. The order matters, and a wrong order gives wrong numbers with no error.
• Cleaning fixes spaces, case, types and NULLs. Keep rows you cannot fix, and count them.
• To keep one row per key, number the rows with ROW_NUMBER() and keep number 1.
• A LEFT JOIN keeps every row of the left table. An INNER JOIN can drop rows without any error.
• Count the rows before and after every join.
• A metric is a rule. Write it in one place.
• Prefer one set-based query to a loop.

Try these on paper:

1. In raw_order_changes, order 104 is loaded a second time as refunded, one day later. After you keep the newest row for each order, what do the paid count and the paid revenue become? (Answer: 3 paid orders, and 145.00.)
2. Run the country query again, but remove order 106. Which rows remain? (Answer: DE with 65.00, and US with 15.50.)
3. Join paid orders to order_items, then COUNT(*). How many rows come back? (Answer: 6, because 101 and 106 have two items.)

These exercises use the pipeline canvas. In each one, the bug is in the transformation step.

Some need chapters that come later. Those are marked optional.