Transformation

Raw data answers almost no business question by itself. Transformation fixes types (a price arriving as the text "RM 1,200.50" becomes the number 1200.50), removes duplicates, standardizes codes and time zones, joins records from different sources, applies business rules, and aggregates the result into the shape people query. The order matters: clean first, then join, then aggregate, so a bad record is caught before it is summed into a total.

Here is the whole idea at BookNest scale. The raw events from Generation carry only a book_id; the genre lives in the catalog. One DuckDB 61,228 query joins the two and aggregates revenue per genre:

Turning raw order events and the catalog into revenue per genre

-- Raw order events plus the catalog become revenue per genre.
WITH books AS (
  SELECT b.id, b.genre
  FROM (SELECT unnest(books) AS b FROM read_json('books.json'))
)
SELECT books.genre,
       count(*)                       AS orders,
       sum(e.qty)                     AS copies,
       round(sum(e.qty * e.unit_price), 2) AS revenue_usd
FROM read_json('order-events.jsonl') AS e
JOIN books ON books.id = e.book_id
GROUP BY books.genre
ORDER BY revenue_usd DESC;

Output

┌─────────────────┬────────┬────────┬─────────────┐
│      genre      │ orders │ copies │ revenue_usd │
│     varchar     │ int64  │ int128 │   double    │
├─────────────────┼────────┼────────┼─────────────┤
│ Technology      │      2 │      4 │       158.0 │
│ Science Fiction │      2 │      5 │        81.0 │
│ Travel          │      2 │      4 │        75.0 │
│ Cooking         │      1 │      2 │        48.0 │
│ Home and Garden │      1 │      1 │        21.3 │
└─────────────────┴────────┴────────┴─────────────┘

The same logic scales from eight rows to eight billion by changing the engine, not the idea: Analytical SQL and Data Warehouses writes it as tested dbt 37,942 models, Batch Processing with Apache Spark runs it in Spark 129 , and Apache Kafka and Managed Cloud Kafka computes it continuously over a stream. Whether you transform before loading (ETL) or after loading into the warehouse (ELT) is a choice Pipeline Foundations weighs; most new platforms load first and transform inside the warehouse or lakehouse, where compute is elastic.

MENU