How Will You Segregate Odd and Even Records into One Target?
What Transformations Will You Use and What Happens If GROUP BY Is Not Selected in an Aggregator?
Segregating odd and even records is a classic Informatica interview scenario, and it has been asked recently for mid to senior-level ETL roles. The question is intentionally designed to test row-level processing knowledge, transformation behavior, and a very common Aggregator transformation pitfall.
Interview Question Statement
“You have a source table and you need to segregate odd and even records into a single target table.
Which transformations will you use in Informatica?
Can you use an Aggregator?
What happens if you do not select GROUP BY in an Aggregator transformation?”
- Source contains multiple records
- No business column (like EMPNO) should be used for odd/even logic
- Segregation must be row-based
- All records should be loaded into one target
- Each record must be marked as ODD or EVEN
This is important because odd/even here refers to row position, not column values.
Informatica Transformations Involved
In Informatica PowerCenter / IDMC, the correct solution uses:
- Source Qualifier
- Sequence Generator
- Expression Transformation
- Target Definition
Step-by-Step Technical Solution
1️⃣ Source Qualifier Transformation
The Source Qualifier reads records from the source system and passes them into the Informatica pipeline.
Important Interview Note:
Row order is not guaranteed unless explicitly sorted. Therefore, we must generate our own row sequence.
2️⃣ Sequence Generator Transformation (Core Transformation)
This is the most critical transformation in this scenario.
Configuration:
- Start Value:
1 - Increment By:
1 - Generates one unique number per row
Output Example:
| EMPNO | NAME | SEQNOS |
|---|---|---|
| 101 | John | 1 |
| 102 | Rahul | 2 |
| 103 | Amit | 3 |
Why interviewers expect this:
- Sequence Generator works at row level
- It is independent of database behavior
- It guarantees deterministic row numbering
3️⃣ Expression Transformation (Odd–Even Logic)
In the Expression transformation, we apply MOD logic.
Expression:
REC_TYPE = IIF(MOD(SEQNOS, 2) = 0, 'EVEN', 'ODD')
Optional numeric flag:
REC_FLAG = MOD(SEQNOS, 2)
This ensures:
- Odd rows → ODD
- Even rows → EVEN
4️⃣ Loading into a Single Target
All records are loaded into one target table, but logically segregated.
Target Structure Example:
| SEQNOS | EMPNO | NAME | REC_TYPE |
|---|---|---|---|
| 1 | 101 | John | ODD |
| 2 | 102 | Rahul | EVEN |
| 3 | 103 | Amit | ODD |
This fully satisfies the requirement.
What Happens If You Don’t Select GROUP BY in Aggregator?
This is one of the most important Informatica concepts.
- If no GROUP BY port is selected:
- Aggregator treats the entire dataset as a single group
- Output will contain only one row
- All row-level detail is lost
Why This Happens
Aggregator is designed for:
- SUM
- COUNT
- MAX
- MIN
- AVG
Without GROUP BY, Informatica assumes:
“All records belong to one group.”
What About Non-Aggregated Columns?
This is where most candidates get confused.
- Non-aggregated, non-grouped columns return undefined values
- Often, it appears like the last record
- But Informatica does not guarantee last record behavior
- Output depends on execution order and pipeline behavior
“Without GROUP BY, the Aggregator produces a single row. Non-aggregated columns may display the last processed value, but this behavior is undefined and should not be relied upon.”
