Informatica PowerCenter is still one of the most common ETL tools on banking, insurance and healthcare projects in Indian IT service companies, and Informatica Intelligent Data Management Cloud (IDMC) is growing. These Informatica interview questions and answers cover the basics, transformations, performance and scenario-based questions asked of developers and ETL testers.
Informatica Basics (Q1–Q8)
1. What is Informatica PowerCenter?
An enterprise ETL tool for extracting data from sources, transforming it with reusable logic and loading it into targets such as data warehouses.
2. What are the main PowerCenter client tools?
Designer (build mappings), Workflow Manager (sessions and workflows), Workflow Monitor (run status and logs) and Repository Manager (objects, folders, permissions).
3. What is a mapping?
A set of source definitions, transformations and target definitions connected by links that describes how data flows.
4. Mapping vs mapplet?
A mapplet is reusable transformation logic that can be used inside several mappings.
5. What is a session and a workflow?
A session is a task that runs a mapping with connection and run settings; a workflow is a set of tasks (sessions, commands, emails) with links and conditions.
6. What is the repository?
The metadata database that stores mappings, sessions, workflows and run history.
7. Active vs passive transformation?
Active transformations can change the number of rows (Filter, Router, Aggregator, Joiner, Rank, Sorter with distinct); passive ones do not (Expression, Sequence Generator, and Lookup by default).
8. Connected vs unconnected transformation?
Connected ones sit in the data flow; unconnected ones are called from an expression and return one value, such as an unconnected Lookup.
Transformation Questions (Q9–Q18)
9. Source Qualifier: what does it do?
Represents rows read from relational or flat-file sources; can apply SQL override, filter, join of same-database tables, sorted ports and select distinct.
10. Filter vs Router?
Filter passes rows meeting one condition and drops the rest; Router splits rows into multiple groups by conditions, with a default group.
11. Joiner: which source should be master?
Usually the smaller source, because the Joiner caches the master rows.
12. Joiner vs Lookup?
Joiner joins two pipelines (including heterogeneous sources); Lookup looks up values from a table or file and can be connected or unconnected.
13. Lookup cache types?
Static (read-only during the session), dynamic (updated as rows are inserted, used for de-duplication and SCD), and persistent (saved and reused across runs).
14. What does the Aggregator do and how do you improve it?
Performs group-by calculations; sorted input reduces cache size and improves performance.
15. Update Strategy transformation?
Flags rows for insert, update, delete or reject with DD_INSERT, DD_UPDATE, DD_DELETE and DD_REJECT.
16. Sequence Generator?
Generates unique numbers, commonly for surrogate keys.
17. Normalizer?
Converts repeating columns into rows (and reads COBOL sources).
18. Rank transformation?
Returns top or bottom N rows per group.
Sessions, Workflows and Parameters (Q19–Q24)
19. What is a parameter file?
A text file supplying values for mapping parameters/variables ($$) and session parameters ($) at run time, so the same workflow runs in different environments.
20. Mapping parameter vs mapping variable?
A parameter stays constant during a run; a variable can change and its final value is saved to the repository, useful for incremental loads.
21. How do you implement incremental load?
Store the last extracted timestamp in a mapping variable (SETMAXVARIABLE) or control table and filter the source on it.
22. What is target load plan?
The order in which target load order groups are loaded in a mapping.
23. What are session logs used for?
Rows read, written and rejected, errors and timing; testers use them to reconcile counts.
24. What is a worklet?
A reusable group of workflow tasks.
Performance Tuning (Q25–Q29)
25. How do you find a bottleneck?
Check thread statistics in the session log to see whether the reader, transformation or writer thread is busiest.
26. Common tuning techniques?
Filter early, use sorted input for Aggregator and Joiner, cache only needed lookup columns, avoid unnecessary data type conversions, and tune commit interval.
27. What is pushdown optimisation?
Pushing transformation logic to the source or target database as SQL, so the database does the work.
28. What is session partitioning?
Splitting a session into parallel pipelines to process data faster.
29. How do you handle large lookups?
Use persistent or shared cache, filter the lookup SQL, or replace with a Joiner when appropriate.
Scenario-Based Informatica Questions (Q30–Q36)
30. How do you load only distinct rows?
Use Select Distinct in the Source Qualifier (relational) or a Sorter with distinct, or an Aggregator grouped by all ports.
31. How do you implement SCD Type 2?
Look up the target by business key, compare attributes; for changes expire the old row (update end date/flag) and insert a new row with a new surrogate key. Testing it: SCD testing.
32. How do you send first half of rows to one target and the rest to another?
Count rows (for example with an Aggregator or a pre-calculated count), number rows with a Sequence Generator or Expression, then use a Router.
33. How do you load the last N records of a file?
Number the rows, get the total count, then filter rows with number greater than total minus N.
34. A session succeeded but the target has fewer rows than the source. What do you check?
Session log counts for rejected rows, filters, router default group, lookups returning nulls into not-null targets, and the bad (reject) file.
35. How do you avoid duplicates when a workflow reruns?
Use a dynamic lookup or target lookup with Update Strategy, or truncate-and-load for the batch, depending on design.
36. How would you test an Informatica mapping?
Validate counts against session logs, run SQL source-to-target checks per mapping rule, and check rejects. See Informatica ETL testing.
What to Expect by Experience Level
| Experience | Focus of questions |
|---|---|
| Freshers / 0–2 years | Tools, transformations, active vs passive, basic SQL |
| 3 years | Lookups and caches, Update Strategy, parameter files, SCD Type 2, debugging failed sessions |
| 5 years | Performance tuning, partitioning, pushdown, design choices, scenario questions |
| 8–10+ years | Architecture, migration to IDMC/cloud, standards, code review, leading teams |
ETL testers on Informatica projects are also asked SQL and data warehouse questions. Practise those with the ETL Testing Interview Questions & Answers – Practice Tests.
Prepare for your ETL testing interview: top 50 questions · in-depth answers · for experienced (3–5 years) · for 10 years experience · SQL query questions · scenario-based questions · data warehouse questions · questions for freshers · free mock test · database testing questions · Python ETL testing questions · SQL questions for freshers · DBMS questions · data validation questions · service-company questions
Data warehouse basics: data warehouse concepts · star vs snowflake schema · fact and dimension tables · SCD types · data reconciliation · data warehouse testing · data warehouse interview questions
Frequently Asked Questions
What are the most common Informatica interview questions?
Active vs passive transformations, Filter vs Router, Joiner vs Lookup, lookup cache types, Update Strategy, parameter files, incremental loads, SCD Type 2, performance tuning and scenario-based mapping questions.
How do I prepare for an Informatica interview with 3 years of experience?
Be ready to explain your project mappings, lookups and caches, SCD Type 2, parameter files and how you debug failed sessions, and practise SQL.
What are scenario-based Informatica questions?
Practical "how would you…" questions such as loading distinct rows, splitting rows between targets, implementing SCD Type 2 or explaining a count mismatch.
Is Informatica still in demand in 2026?
Yes, especially in large enterprises and service companies, while many teams are also moving to Informatica IDMC and cloud tools like Azure Data Factory.
Do ETL testers need Informatica knowledge?
Basic knowledge helps testers read mappings and session logs, but they mainly validate data with SQL.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.