Lesson 18: Informatica UNION Transformation | Sorter Distinct, Rank, Top 5 Logic | Oracle SQL

Welcome to Lesson 18 of the Informatica + Oracle SQL Tutorial Series!
In this session, we dive deep into one of the most powerful combinations in ETL development:

✅ **UNION Transformation (Informatica)

✅ Sorter Transformation (Distinct Logic)
✅ Rank Transformation (Top-N Logic)**

You will learn how to:
✔ Combine data from India, Australia, and England cricket player datasets (heterogeneous sources)
✔ Use UNION Transformation to merge multiple pipelines
✔ Remove duplicates with Sorter (Distinct)
✔ Extract Top 5 highest-paid players from all countries using Rank Transformation
✔ Build two target tables:
 • ALL_PLAYERS_DIM (full combined dataset)
 • TOP_PLAYERS_DIM (Top 5 salary players)
✔ Understand why UNION does NOT remove duplicates
✔ Learn the difference between UNION vs JOIN vs SET Operators
✔ Understand why Rank uses two caches (Index + Data cache)
✔ Real-time mapping design used in ETL projects

🔥 What you will gain from this lesson:

Real-time ETL design approach

Complete cricket-based business requirement

Heterogeneous file + table union logic

Sorter performance tips

Rank transformation interview questions

SQL equivalent queries for UNION, UNION ALL, RANK, DENSE RANK

📌 This tutorial is perfect for:

ETL Developers
Informatica Freshers
Data Engineering Students
Oracle SQL Learners
Anyone preparing for MNC ETL interviews

Drive link https://drive.google.com/drive/folder...