Informatica Interview Questions and Answers (2026): PowerCenter to IICS

PowerCenter is Informatica's enterprise ETL platform: it extracts data from heterogeneous sources, transforms it, and loads targets in batch. Architecturally, the repository is a relational database holding all metadata — mappings, sessions, workflows, connections — managed by the Repository Service. The Integration Service executes workflows: it spawns a DTM (Data Transformation Manager) process per session that reads, transforms, and writes data. Everything sits inside a domain, the administrative unit spanning one or more nodes. The client tools are Designer (build mappings), Workflow Manager (build sessions and workflows), Workflow Monitor (track runs), and Repository Manager (folders, permissions, object management). In an interview, separating design-time objects (stored in the repository) from run-time execution (Integration Service and DTM) shows you understand the architecture rather than just the GUI.

A mapping is the design-time data flow: source definitions, transformations, and target definitions linked together in Designer. It defines what to do with data but nothing about when or where. A session is a task that makes one mapping executable: it binds the mapping to physical connections, file paths, memory settings, commit intervals, and error handling. A workflow is the orchestration layer built in Workflow Manager — sessions, command tasks, decision tasks, event waits, and email tasks connected by links, executed by the Integration Service on a schedule or on demand. A worklet groups tasks for reuse across workflows, the way a mapplet groups transformations for reuse across mappings. Interviewers ask this to confirm you separate logic (mapping), runtime configuration (session), and orchestration (workflow) cleanly.

An active transformation can change the number of rows passing through it, change transaction boundaries, or change row type. Filter, Router, Aggregator, Joiner, Rank, Sorter, Union, Normalizer, Update Strategy, and Source Qualifier are active. A passive transformation preserves row count — every input row produces exactly one output row: Expression, Sequence Generator, and Stored Procedure are passive. The classic follow-up is the Lookup: passive by default, but newer PowerCenter versions let it return all matching rows, which makes it active. The practical consequence of the classification: you cannot merge pipelines coming from different active transformations into the same downstream transformation, because the Integration Service cannot align rows whose counts may differ — merging branches requires a Union or a Joiner. Quoting that consequence, not just the definition, is what earns the marks.

Both are active and condition-driven, but they behave differently. A Filter has a single condition; rows that fail are silently dropped — they are not captured in any reject file. A Router evaluates multiple conditions in one pass, with one output group per condition plus a default group for rows matching none. A row satisfying several conditions goes to every matching group, and the input is processed only once. Use a Router where you would otherwise chain multiple Filters off the same pipeline: one Router is cheaper because the data is read once instead of once per Filter, and you keep the non-matching rows via the default group. Use a Filter when you genuinely want to discard rows early — and pushing that filter into the Source Qualifier as a filter override, so the database drops the rows, is a standard performance move.

A connected Lookup sits in the pipeline: it receives input through port links, participates in row-by-row flow, can return multiple columns, and supports a dynamic cache. On no match it returns default values for output ports. An unconnected Lookup sits outside the flow and is invoked from an Expression with the :LKP.lookup_name(args) syntax. It returns exactly one value through its designated return port, uses only a static cache, and returns NULL on no match. The key advantage of unconnected is conditional invocation: wrapped in an IIF, the lookup fires only for rows that need it, and one unconnected lookup can be called from multiple expressions. Choose connected when you need several columns back or a dynamic cache; choose unconnected when the lookup is needed rarely or shared across logic.

The Aggregator computes SUM, AVG, COUNT, MIN, MAX and similar functions over groups defined by group-by ports. It is active and cache-based: an index cache holds group keys and a data cache holds row data. Without sorted input, it must read the entire input before emitting anything — a new member of any group could arrive at any time — so cache grows with the number of groups. With Sorted Input enabled and the data genuinely sorted on the group-by ports (via a Sorter or an ORDER BY in the Source Qualifier), it processes one group at a time and releases each group when the keys change, which slashes cache size and improves throughput. Two traps worth naming: enabling Sorted Input on unsorted data fails the session or corrupts results, and ports without an aggregate function return the last row of each group.

The Joiner joins two pipelines, which may come from heterogeneous sources — a flat file joined to an Oracle table, say. One input is the master, the other the detail. The Integration Service caches master rows and streams detail rows against that cache, so you designate the source with fewer rows (or fewer duplicate keys) as master to keep the cache small. The four join types trip people up: a normal join keeps only matching rows; a master outer join keeps all detail rows and discards unmatched master rows; a detail outer join keeps all master rows and discards unmatched detail rows; a full outer join keeps everything. Sorted input on a Joiner enables group-by-group processing and better performance at volume. When both inputs are relational tables in the same database, prefer a database join in the Source Qualifier — the database usually joins faster.

SCD Type 2 preserves history by inserting a new dimension row when a tracked attribute changes, instead of overwriting. Standard mapping: the Source Qualifier feeds a Lookup on the target dimension keyed by the business key, returning the surrogate key and current attribute values. An Expression compares source against looked-up values and flags each row NEW, CHANGED, or UNCHANGED. A Router splits the flow: NEW rows take a surrogate key from a Sequence Generator through an Update Strategy with DD_INSERT; CHANGED rows go down two branches — one inserts the new version (new surrogate key, current flag 'Y', effective start date), the other updates the old row with DD_UPDATE to set its end date and flag 'N'. History can be tracked by version number, current-row flag, or effective date ranges — date ranges are most common because they support as-of queries. PowerCenter also ships an SCD wizard that generates this pattern.

Update Strategy tags each row with the operation the writer should perform: DD_INSERT (0), DD_UPDATE (1), DD_DELETE (2), or DD_REJECT (3), typically via an IIF or DECODE expression. For the flags to matter, the session property 'Treat source rows as' must be Data Driven — otherwise the session-level setting (Insert, Update, or Delete) applies uniformly and the transformation is ignored. Rejected rows go to the session reject file when Forward Rejected Rows is enabled. Target-level session properties interact too: insert, update as update, update as insert, update else insert, and delete decide what the writer may actually do. A common scenario question is upsert: either Update Strategy with a data-driven session, or 'update else insert' at the target — and the target definition needs key columns defined, because PowerCenter builds the UPDATE's WHERE clause from them.

By default a Lookup builds a static cache: the lookup source is read once at session start into index and data caches, and never changes during the run. A dynamic cache can be inserted into or updated as rows pass through — essential when the lookup table is also the target being loaded, so later rows in the same run see earlier inserts. A persistent cache is saved to cache files and reused by later sessions, skipping the rebuild — right for large, slowly changing reference tables; name it to share across mappings and recache when the reference data changes. Unnamed caches can be shared between compatible lookups within one mapping. The trade-off to articulate: caching costs memory and build time but avoids repeated database hits — for a huge table probed only a handful of times, an uncached lookup can actually be faster.

Pushdown optimization has the Integration Service translate transformation logic into SQL and push it to the database instead of processing rows itself. Source-side pushdown folds upstream logic into the SELECT against the source; target-side pushes downstream logic into the write; full pushdown — when source and target share a database — can turn the whole mapping into SQL executed in-database, so data never moves through the DTM. It shines when both ends sit on a powerful database: classic ELT. The limits: only logic with a SQL equivalent can be pushed, so transformations or functions the database cannot express (varies by database; sequence-heavy logic and some lookups are common blockers) produce a partial pushdown. The Pushdown Optimization Viewer shows exactly which pieces were pushed, and you can control behavior per session or dynamically through the $$PushdownConfig mapping parameter.

All three are reuse mechanisms at different levels. A reusable transformation, built in the Transformation Developer, is a single transformation — say a standard address-cleansing Expression — shared across mappings; edits propagate everywhere it is used. A mapplet, built in the Mapplet Designer, packages a set of transformations behind Mapplet Input and Output transformations, so a whole pattern — validate, standardize, lookup — drops into many mappings as one object. Mapplets carry restrictions: they cannot contain targets, other mapplets, or certain source types such as XML. A worklet is the workflow-level equivalent: a reusable group of tasks (sessions, commands, decisions) placed inside multiple workflows, useful when several nightly workflows share the same load-and-audit sequence. The crisp interview framing: reusable transformation = one object, mapplet = reusable mapping fragment, worklet = reusable workflow fragment.

Partitioning splits a pipeline into parallel threads at partition points, multiplying reader, transformation, and writer threads. Pass-through keeps rows in their current partition — used to add a stage boundary without redistributing data. Round-robin distributes rows evenly, useful after an unevenly split source. Hash auto-keys hashes on the grouping or sort keys — effectively required before an Aggregator, Rank, or Sorter so all rows of a group land in the same partition; hash user keys lets you choose the hash ports yourself. Key range routes rows by value ranges you define, typically aligned to target table partitions; database partitioning reads or writes according to the database's own partition scheme. The costs interviewers want to hear: every partition gets its own caches, so memory multiplies; skewed keys create hot partitions that erase the gains; and partitioning is a licensed option, so confirm it exists before designing around it.

Start with evidence: the session log's thread statistics show run time and busy percentage for reader, transformation, and writer threads — the highest busy percentage is the bottleneck. Then isolate. Point the mapping at a flat-file target: if runtime collapses, the target is the problem — fix with bulk loading, dropping indexes during the load, larger commit intervals, or an external loader. Put a Filter with condition FALSE right after the Source Qualifier: if it is still slow, the source is the problem — tune the source query, indexing, or network. Otherwise it is the mapping: cache and trim expensive lookups, enable sorted input on Aggregators and Joiners, replace chained Filters with one Router, and remove unnecessary datatype conversions. Session-level knobs come last — DTM buffer size, buffer block size, transformation cache sizes — and partitioning only after single-thread tuning is exhausted.

A dynamic lookup cache changes while the session runs: as each row passes, the Integration Service can insert it into the cache or update the cached row, keeping the cache synchronized with what the target will become. It solves a specific failure: with a static cache, if the same business key arrives twice in one run against an empty target, both rows miss the lookup and you insert duplicates. With a dynamic cache, the first row inserts into the cache and the second row finds it. The transformation exposes a NewLookupRow port — 0 (no change), 1 (inserted), 2 (updated) — and each lookup port pairs with an associated input port supplying the values written into the cache. Downstream, you route on NewLookupRow into Update Strategies so the target receives the same insert or update the cache recorded, and you mark volatile ports like timestamps as ignore-in-comparison to avoid spurious updates.

The standard pattern is a persisted mapping variable as a watermark. Declare $$LastExtractTime, filter the source on last_updated_ts > $$LastExtractTime inside the Source Qualifier so the database does the filtering, and call SETMAXVARIABLE($$LastExtractTime, last_updated_ts) in an Expression. The Integration Service persists the new value to the repository only when the session succeeds, which gives natural restartability: a failed run never advances the watermark, so the next run re-extracts the same window. Alternatives are a control table updated post-load (more visible to operations, portable across tools) or script-generated parameter files. Design points interviewers probe: the audit column must be trustworthy, or late-updated rows are silently missed; timestamp deltas never see deletes, so you need soft deletes or periodic reconciliation; and re-processed windows must not duplicate rows, so pair the delta with a dynamic lookup or upsert logic. True change capture including deletes is where CDC tooling and database logs come in.