Roadmap › ETL & Data Warehousing Concepts › Module: Fundamentals
Lessons in this course (12)
4. Common transformations
BasicMediumLesson 4 of 12
- Cleansing — trim, fix case, standardise formats (dates, phone numbers), handle NULLs/defaults.
- Validation — reject or flag rows breaking rules (missing keys, invalid dates).
- Deduplication —
ROW_NUMBER() OVER (PARTITION BY businessKey ORDER BY ModifiedAt DESC). - Lookups — map business keys to surrogate keys of dimensions.
- Derivations — calculated columns (Margin = Revenue − Cost).
- Data type conversion — string → date/decimal (with error handling).
- Aggregation, pivot/unpivot, split/merge columns, joins/merges across sources.
- Conforming — same meaning and codes across sources (e.g. Gender 'M'/'Male'/1 → 'M').