A Router transformation provides the most balanced solution. It ensures data integrity by sending only unique records to the primary target, while also meeting the auditing requirement by capturing all duplicate records in a separate reject file. This is maintainable and provides full visibility into the data issue.

For a large, slowly changing dimension (like a Type 2 SCD), a static cache is typically the most performant choice. The key performance issue is often the lookup condition itself. Ensuring it uses an indexed column from the database is the first and most critical check. A static cache is built once at the start of the session and reused, which is ideal for a large, stable reference table updated in batch.

Option 3 correctly handles NULLs by using DECODE to convert them to empty strings. It only adds the separating space after the first name if the first name is not NULL. Then it concatenates the last name (or empty string). This prevents results like ‘ John’ (if first name is NULL) or ‘John ‘ (if last name is NULL). Simple CONCAT or || operators will result in a NULL output if any input is NULL.

A Sequence Generator transformation within a mapping is initialized *per session*. If two workflow instances run the same mapping concurrently, each session will have its own Sequence Generator instance starting from the configured Start Value, leading to duplicate IDs. For true uniqueness across concurrent runs, a database sequence or a different orchestration-level mechanism should be used.

In a Joiner transformation, the master source is cached in memory. If the larger table is set as the master, it can exhaust available memory. The standard performance best practice is to always designate the *smaller* of the two input groups as the master. This minimizes the memory footprint of the cache and is often the most effective fix for memory-related Joiner errors.

The simplest and most direct way to enforce this dependency is to use an ‘On Success’ link from the Session task to the Email task. This is a core workflow design principle in Informatica: the link condition dictates the execution path. An ‘On Success’ link means the Email task will only start if the preceding Session task completes with a ‘Succeeded’ status.

The transformation best suited for row-level data manipulation is the Expression transformation. The logic `IIF(RTRIM(ProductCode)=”, NULL, ProductCode)` correctly checks if the trimmed value is an empty string (meaning it was all spaces) and returns NULL; otherwise, it passes the original value. This is performed within the data pipeline before loading to the target. A Filter would remove the rows entirely, which is not the requirement (treat as NULL, not discard).

The ‘Connection Retry Period’ defines the wait time (in seconds) between retry attempts, and the ‘Connection Retry Limit’ defines the maximum number of retry attempts. If the initial connection fails, the Integration Service will wait for the retry period (10 seconds) and try again. It will repeat this process until a connection is made or the retry limit (3) is reached, at which point the task fails.

The standard Informatica pattern for Type 2 SCD processing is: 1) **Lookup** on the dimension table to find the existing record. 2) An **Expression** transformation to compare lookup values (e.g., address) with incoming values and flag rows as ‘Insert’ or ‘Type 2 Update’. 3) A **Router** to split the flow based on this flag. 4) An **Update Strategy** transformation to apply the correct database operation (Insert for new row, Update to expire old row). While an Expression is implied in the logic, the core set is Lookup, Router, and Update Strategy.

A Router transformation provides the most balanced solution. It ensures data integrity by sending only unique records to the primary target, while also meeting the auditing requirement by capturing all duplicate records in a separate reject file. This is maintainable and provides full visibility into the data issue.0