Informatica Transformations and Sessions Quiz

Return ports are available in Lookup and Stored Procedure transformations to pass values back to the mapping. Filter and Rank transformations do not have return ports.

Unconnected Lookup transformations use static cache, not dynamic cache. The cache is built at the beginning of the session and does not change during runtime.

Rank transformation supports all port types: Input (I) for incoming data, Output (O) for ranked results, Variable (V) for temporary calculations, and Return (R) ports.

Stored Procedure transformations can be Passive, Connected, or Unconnected. They are not classified as Active because they execute procedures directly in the database.

Router transformation has a default group that captures all rows not matching any user-defined group condition. Aggregator does not have a default group.

Deleting lookup ports that are used in the mapping will cause the session to fail because the transformation cannot resolve required field dependencies.

Tables should first be imported or replaced in the Source Analyzer, and then copied to the Target Designer. This maintains proper metadata relationships and object hierarchy.

In Informatica, when a Lookup transformation uses caching (the default setting), the lookup source data is read once at session start and cached in memory for subsequent lookups throughout the session. This avoids repeated database queries and improves performance.

An unconnected sequence generator in Informatica is not part of the main data flow and is called using the :LKP(.) expression in another transformation like Expression. It can indeed be used to generate sequence values instead of a connected sequence generator when the sequence generation needs to be conditional or separate from the main pipeline.

The position of a filter transformation in ETL mappings significantly affects performance. Filtering out unwanted rows as early as possible reduces the volume of data flowing through downstream transformations.

When Informatica server cannot write to required directories (session log, bad files, cache files), the session initiates but fails during execution when it attempts to write these files. The initialization succeeds, but the runtime fails due to permission issues.

When creating a new session in Informatica and configuring connections, the first connection object in the list is selected by default for relational connections. This is standard Informatica behavior in the Workflow Manager.

In Informatica, moving data between transformations does incur overhead because the Integration Service creates temporary disk files for data caches (e.g., for Aggregator, Joiner, Rank, Sorter transformations). Each active transformation that processes data typically requires caching, which adds I/O overhead.

Transaction Control transformation is strictly an active/connected transformation - it must be connected to the data flow and cannot be used unconnected. It generates transaction control events (commit, rollback) based on conditions and changes the transaction boundary. In contrast, Lookup can be connected or unconnected, Expression is passive and connected, Stored Procedure can be connected or unconnected.

The Rank transformation in Informatica requires: minimum one input port (I), one output port (O), one variable port (V) is optional for internal calculations, and exactly one rank port (R) which specifies the column to rank on. The statement says 'strictly only one return port' which refers to the rank port - this is correct.

Union transformation in Informatica is a passive transformation that merges data from multiple pipelines. It does not generate transaction boundaries (commit points) - it simply unions the data. Option A is incorrect because Union has multiple input groups but only one output group. Option B is incorrect because matching ports are required across input groups. Option D is incorrect because precision, datatype, and scale must match across groups for union compatibility.

An Aggregator (which groups and reduces rows) and Update Strategy (which flags rows for insert/update/delete) can be placed in either order in the pipeline. The order depends on business logic - you might aggregate first then flag the aggregated rows, or flag individual rows then aggregate. Both orders are valid depending on the use case.

In most standard database and ETL filtering contexts, string comparisons in filter conditions are case sensitive unless specified otherwise using lower or upper conversion functions.

Joiner transformation has specific restrictions: (1) It cannot be used if either input pipeline contains an Update Strategy transformation because Joiner needs stable input and Update Strategy flags rows for database operations which conflicts. (2) A Sequence Generator connected directly before Joiner causes issues because Sequence Generator is active and generates rows, potentially disrupting the sorted input requirement for Joiner. Both statements are true.

Deleting a lookup port from a relational lookup table does not cause a session failure if the mapping does not reference or use that specific lookup port, because unused ports do not break port-matching or data flow dependencies.