1. Introduction in IICS
The Joiner Transformation in Informatica Intelligent Cloud Services (IICS) is used to combine data from two different sources based on a common field.
It is useful when the source systems cannot be joined directly at the database level.
For example, customer information may come from one source and sales information from another source. The Joiner Transformation can combine both datasets using Customer_ID.
2. How Joiner Works in IICS
A typical process is:
Customer Source + Sales Source → Joiner → Target
The Joiner compares records from two input pipelines using a specified join condition.
Example:
Customer.Customer_ID = Sales.Customer_ID
3. Master and Detail Sources
A Joiner uses two input groups:
Master
The Master source is the primary input used during the join operation.
Detail
The Detail source is matched against the master records.
Choosing the appropriate master source can improve performance, especially when working with large datasets.
4. Types of Joins
IICS Joiner supports common join types such as:
- Normal Join – Includes only rows where the join conditions match perfectly in both pipelines. Unmatched rows from both sides are discarded.
- Master Outer Join – Keeps all rows from the Detail pipeline and only matching rows from the Master pipeline. Unmatched Master rows are dropped.
- Detail Outer Join – Keeps all rows from the Master pipeline and only matching rows from the Detail pipeline. Unmatched Detail rows are dropped.
- Full Outer Join – Keeps all incoming rows from both the Master and Detail pipelines, regardless of whether they match the join condition.
5. Example
| Customer_ID | Customer_Name |
|---|---|
| 101 | Rahul |
| 102 | Priya |
| 103 | Amit |
| Customer_ID | Sales |
|---|---|
| 101 | 5000 |
| 102 | 7500 |
| 104 | 3000 |
Customer.Customer_ID = Sales.Customer_ID
A Normal Join produces:
| Customer_ID | Customer_Name | Sales |
|---|---|---|
| 101 | Rahul | 5000 |
| 102 | Priya | 7500 |
Customer 103 and sales record 104 are excluded because there is no matching record.
6. Real-World Use Case
A common enterprise ETL pipeline can use a Joiner to combine data from different systems:
CRM Data + ERP Sales Data → Joiner → Transformation → Snowflake
For example, customer information from a CRM system can be combined with sales information from an ERP system using Customer_ID.
7. Performance Considerations
Joiner performance depends on factors such as:
- Number of records
- Join condition
- Master/detail selection
- Data volume
- Available memory
- Source data distribution
For large datasets, it is important to filter unnecessary records before the Joiner and select the appropriate master source.
8. Best Practices
- Use appropriate join conditions.
- Filter unnecessary records before joining.
- Select the smaller dataset as the master where appropriate.
- Avoid unnecessary columns in the Joiner.
- Handle unmatched records according to business requirements.
- Validate the output after the join.
- Monitor mapping performance for large datasets.
Conclusion
The Joiner Transformation is an important IICS transformation for combining data from two different pipelines or sources.
It supports multiple join types and is especially useful when data resides in different systems. Proper join conditions, master/detail selection, filtering, and performance optimization help create efficient and reliable IICS data pipelines.