§ 1.6section 6 of 25·~6 min
Transformation: raw to useful
Raw data is messy. Transformation turns it into tables people can trust. Common steps:
- Fix types:
" 499 "(text) becomes499(number). Dates in five formats become one. - Standardise:
"pune","Pune "and"PUNE"all become"Pune". - Remove duplicates: the same order sent twice is counted once.
- Validate: rows that break the rules (negative amounts, missing IDs) go to a quarantine table instead of the clean one.
- Join and enrich: add the customer's city to each order.
- Aggregate: total sales per city per day.
- Apply business logic: net revenue = gross amount − refunds − discounts.
| Raw row (all text) | After transformation |
|---|---|
"1", " 499 ", "pune" | order 1, 49900 paise, Pune |
"1", " 499 ", "pune" | duplicate of order 1: dropped |
"2", "N/A", "Delhi" | amount isn't a number: quarantined |
ETL or ELT
| ETL | ELT | |
|---|---|---|
| Order | Extract → Transform → Load | Extract → Load → Transform |
| Where transforms run | A separate engine before the warehouse | Inside the warehouse, usually in SQL |
| Raw data kept? | Often not | Yes, loaded first, so you can re-transform later |
| Typical tools | Informatica, Talend, custom Python | dbt on BigQuery, Snowflake, Redshift, Databricks |
Cheap cloud storage and fast warehouses made ELT the common default. ETL is still used when data must be cleaned or masked before it lands, for example removing personal data.