Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 36 ยท Ingestion and Batch Processing

Batch Transformations

Learn the core batch transformation toolkit: cleaning, deduplication, joins, aggregation and window functions, with pandas and SQL examples you can run.

Batch processing means working on a bounded chunk of data, such as yesterday's events or the whole customer table, and producing a result. Most data platforms run mostly batch jobs because they are simple, cheap and easy to retry. This lesson walks through the transformation operations you will use in almost every job, in pandas and in SQL.

The anatomy of a batch job

Every batch job has the same shape: read a bounded input, transform it with a deterministic function, write the output atomically. If the input is fixed and the transformation is pure, the output is reproducible, which is what makes rerunning and backfilling safe.

Core transformations

Clean Types, nulls, text.
Deduplicate One row per entity.
Join Combine sources.
Aggregate Group and summarize.

Window functions add ranking and running totals.

1 / 2

Cleaning

Cleaning makes values consistent: trimmed text, normalized case, correct types, explicit handling of missing values. Decide for each column whether a missing value should be rejected, defaulted or kept as null; do not leave it to chance.

Python Clean a messy customer table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The impossible date 2026-02-30 becomes NaT (not-a-time). You must decide what that means for the business, not hide it.

Deduplication

Duplicates come from retries, double exports and joins gone wrong. Define what "the same" means, then keep one row by a rule such as latest timestamp.

Python Keep the latest row per key

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The SQL equivalent uses a window function, which also works in every warehouse:

Python The same with ROW_NUMBER in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

ROW_NUMBER() ... WHERE rn = 1 is the standard "latest row per key" idiom; learn it by heart.

Joins

Joins combine sources. The classic mistake is a join that multiplies rows because the key is not unique on one side. Check key uniqueness before joining and row counts after.

Python A join that silently multiplies rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Revenue is inflated by the duplicate customer. validate="many_to_one" turns a silent bug into a loud error. In SQL, count rows before and after or assert uniqueness with COUNT(*) = COUNT(DISTINCT key).

Aggregation

Aggregation summarizes many rows into few: counts, sums, averages, distinct counts. Pick the grain (one row per what?) deliberately, and state it in the table name or documentation: daily_revenue_by_country tells the reader the grain.

Python Aggregate at a chosen grain

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Window functions

Window functions compute across related rows without collapsing them: running totals, rankings, "previous value", moving averages. They are the most valuable advanced SQL skill for data engineering.

Python Running total and day-over-day change

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Skew and scale: when pandas is not enough

pandas works on data that fits in one machine's memory. When it does not, the same operations run on distributed engines (Spark, Trino, a warehouse), which split data across workers. Two ideas to carry along: shuffles (moving rows between workers so equal keys meet) are the expensive step of joins and group-bys, and skew (one key holding most of the rows) makes one worker do most of the work. We cannot run a cluster here, but the logic is the one you just wrote.

Test your transformations

Because transformations are functions of their input, test them with tiny hand-made frames, including edge cases: empty input, nulls, duplicates and a boundary date. Treat a failing test as a failing pipeline.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the standard SQL idiom for keeping the latest row per key?

    Number the rows within each key from newest to oldest and keep number one.
  2. 2. Why can a join inflate revenue?

    If the lookup table has duplicate keys, each order matches several rows.
  3. 3. What does the grain of a table describe?

    Grain states what a single row stands for, for example one order or one day per country.
  4. 4. What expensive operation moves rows between workers in a distributed join?

    A shuffle redistributes data so rows with equal keys end up together.

Summary

  • A batch job reads bounded input, applies a pure transformation and writes atomically.
  • Core operations: clean, deduplicate, join, aggregate and window functions.
  • Check key uniqueness before joins and row counts after; pick and document the grain.
  • Test transformations on tiny data, including edge cases.

Next: Partitioning and Backfills