Home › Blog › Informatica Interview Questions (2026): 30 Questions on PowerCenter and IDMC, Answered

Informatica Interview Questions (2026): 30 Questions on PowerCenter and IDMC, Answered

Informatica interviews cover two products: PowerCenter, the on-premises ETL tool, and Cloud Data Integration on Informatica's Intelligent Data Management Cloud (IDMC). Expect architecture, active and passive transformations, Lookup caches, Joiner and Aggregator tuning, Update Strategy, SCD mappings, parameter files, partitioning, pushdown optimization and migration to the cloud.

Informatica interviews cover two products: PowerCenter, the on-premises ETL tool, and Cloud Data Integration on Informatica's Intelligent Data Management Cloud (IDMC). Expect architecture, active and passive transformations, Lookup caches, Joiner and Aggregator tuning, Update Strategy, SCD mappings, parameter files, partitioning, pushdown optimization and migration to the cloud. For context, Salesforce completed its acquisition of Informatica on November 18, 2025, per Salesforce's newsroom, and Informatica's PowerCenter product page now promotes moving PowerCenter workloads to IDMC (checked November 17, 2026). Below are 30 questions by level, five scenarios and how interviews test the tool.

Active and passive transformations

An active transformation can change the number of rows, the row type or transaction boundaries; a passive one cannot.

TransformationTypeUsed for
Source QualifierActiveReading relational and flat-file sources; SQL override, filter, join
ExpressionPassiveRow-level calculations
Filter / RouterActiveDropping rows / splitting rows into groups
AggregatorActiveGroup-by aggregates
JoinerActiveJoining heterogeneous sources
LookupPassive by default; active when set to return all matching rowsLooking up reference values
Sorter / RankActiveSorting and distinct / top-N per group
Sequence GeneratorPassiveSurrogate keys
Update StrategyActiveFlagging rows for insert, update, delete or reject
Normalizer / UnionActivePivoting repeating groups / merging pipelines

Fundamentals

  1. Describe the PowerCenter architecture. Client tools (Designer, Workflow Manager, Workflow Monitor, Repository Manager) talk to a domain whose Repository Service manages metadata in a repository database and whose Integration Service runs workflows.
  2. Mapping, session, workflow, worklet, mapplet? A mapping defines data flow from sources through transformations to targets; a session runs a mapping with connection and runtime settings; a workflow orders sessions and tasks; a worklet is a reusable group of tasks; a mapplet is a reusable set of transformations.
  3. What makes a transformation active? It can change the number of rows passing through, the row type or the transaction boundary, as Filter, Aggregator and Update Strategy do. Expression and Sequence Generator are passive.
  4. When do you use a Source Qualifier SQL override? To push filters, joins of same-database tables or sorting into the source query. Keep the port order matching the select list, and document it, because overrides hide logic from the mapping.
  5. Filter or Router? A Filter passes rows meeting one condition and drops the rest. A Router tests several conditions in one pass and sends rows to multiple groups, with a default group for the rest.
  6. How does the Joiner work? It joins two pipelines with normal, master outer, detail outer or full outer joins. Make the smaller source the master, because the master is cached, and use sorted input on large data.
  7. Connected or unconnected Lookup? A connected Lookup sits in the pipeline and can return several ports. An unconnected Lookup is called from an expression, such as :LKP.lkp_customer(in_customer_id), and returns one value, which suits conditional lookups.
  8. Which Lookup caches exist? Static (the default, read-only during the session), dynamic (inserted or updated as rows pass, with a NewLookupRow port), persistent (kept between runs), shared, and uncached lookups that query per row.
  9. How does the Aggregator perform best? With sorted input on the group-by ports, which lets it release groups as it goes instead of caching everything. Incremental aggregation reuses earlier results for append-only data.
  10. What does Update Strategy do? It flags each row with DD_INSERT (0), DD_UPDATE (1), DD_DELETE (2) or DD_REJECT (3), for example IIF(ISNULL(lkp_id), DD_INSERT, DD_UPDATE). The session must treat source rows as data driven.
  11. What does the Sequence Generator provide? NEXTVAL and CURRVAL ports for surrogate keys, with start value, increment and cycle options. A reusable generator shares one sequence across mappings.

Intermediate

  1. How do you build an SCD Type 2 mapping? Look up the current target row by natural key, compare tracked attributes (often through an MD5 of the columns), route new keys to insert, changed keys to insert a new version and expire the old one, and leave unchanged rows alone.
  2. Mapping parameters, variables and parameter files? Parameters such as $$LoadDate are fixed for a run; variables can change and persist their final value in the repository. Session parameters such as $DBConnection_Src switch connections. A parameter file supplies values per environment.
  3. How do you implement incremental extraction? Filter the source on a mapping variable holding the last extracted timestamp, and update it with SETMAXVARIABLE($$LastRunTs, updated_at) so the next run starts where this one stopped.
  4. Target load order or constraint-based loading? Target load order sets which Source Qualifier pipeline loads first; constraint-based loading orders targets in one pipeline by primary and foreign keys so parents load before children.
  5. Which session partition types exist? Pass-through, round-robin, hash auto-keys, hash user keys, key range and database partitioning. Choose by whether rows need to group together, as for aggregations.
  6. What is pushdown optimization? Converting transformation logic into SQL that runs in the source or target database: source-side, target-side or full pushdown. It suits large volumes in a capable database and moves processing away from the Integration Service.
  7. How do you handle errors? Reject files for target rejects, row error logging to tables, session settings such as stop on errors, and the ERROR() and ABORT() functions for rule violations.
  8. How do you tune a slow session? Read the thread statistics in the session log to see whether the reader, transformation or writer thread is busiest, then fix that stage: filter in the source, sort input, trim Lookup SQL, drop indexes for bulk loads, adjust commit interval and partition.
  9. Which workflow tasks do you use? Session, Command, Email, Decision, Assignment, Timer, Event-Wait, Event-Raise and Control tasks, connected by links with conditions.
  10. How do you deploy between environments? Deployment groups or exported XML, with labels and versioning in the repository, plus parameter files so the same objects run against each environment's connections.

Experienced and senior

  1. What are the main IDMC Cloud Data Integration concepts? Mappings designed in the browser, mapping tasks that run them with parameters, taskflows for orchestration, connections, and a runtime environment: a Secure Agent on your own infrastructure or Informatica-hosted serverless runtime.
  2. How do PowerCenter and IDMC differ for a developer? IDMC is browser-based, runs on Secure Agents or serverless runtime, and is metered in Informatica Processing Units. PowerCenter uses desktop clients and a domain you operate. Many core transformation concepts carry over.
  3. How would you approach a PowerCenter-to-cloud migration? Inventory and rationalize workflows, convert with Informatica's tooling or rebuild, then parallel-run and reconcile counts, sums and hashes per target before cutover.
  4. How do you handle a Lookup on a very large table? Don't cache the whole table: filter the lookup SQL, use a Joiner with sorted input, or push the join into the database.
  5. How do you remove duplicates? A Sorter with distinct, an Aggregator on the key, a Rank for the latest row per key, or Expression variable ports comparing each row with the previous one after sorting. Ports evaluate in order: input, then variable, then output.
  6. How do you design for restartability? Make loads idempotent with batch ids or delete-and-reload windows, enable workflow recovery where appropriate, and keep the last successful watermark separate from the current run.
  7. How do you debug a mapping? Use the Debugger with breakpoints on sample data, raise the tracing level to Verbose Data for a short run, and read session logs and reject files.
  8. When do mapplets and reusable transformations hurt? When a shared object changes and silently alters every mapping that uses it. Version them, test impact before changing them, and keep them small.
  9. What should you know about Informatica's ownership change? Salesforce completed its acquisition of Informatica on November 18, 2025. In interviews, stick to what the product does today and ask the team which platform it runs and plans to run.

Five scenarios interviewers give

How interviews test Informatica

Employers don't publish question banks, but ETL developer candidates commonly report walking through a mapping they built, designing an SCD Type 2 mapping on a whiteboard, a SQL round and a performance-tuning story. Testing knowledge helps: see our ETL testing interview questions, plus data warehouse interview questions for modelling.

Where CoPilot Interview fits

CoPilot Interview is an AI interview assistant whose free AI mock interview builds Informatica and ETL development questions from your résumé and the job description, reads them aloud and scores each spoken answer out of 10. The browser demo at /try has 145 practice questions in 10 tracks, scored out of 10, with no signup. The free plan gives 15 minutes a day of live help in sessions of up to 15 minutes, with AI mock interviews included; Standard costs $24.99 a month and Pro $39.99. For data roles, see our guide to practicing data engineer interviews with AI. Live coding tasks, take-homes and online assessments usually forbid outside help, so prepare for those; in any live interview, follow your employer's rules.

Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly.

Frequently asked questions

Is Informatica still in demand in 2026?

Many enterprises still run PowerCenter and Informatica's cloud services, now under Salesforce, which completed its acquisition on November 18, 2025. Informatica steers PowerCenter customers toward IDMC, so expect IDMC questions alongside PowerCenter ones.

Should I learn PowerCenter or IDMC?

Learn the shared concepts, then IDMC Cloud Data Integration, which is where Informatica directs PowerCenter customers. Keep PowerCenter for roles that maintain existing workflows.

What is the difference between an active and a passive transformation?

Active transformations can change row count, row type or transaction boundaries, as Filter and Aggregator do; passive ones, such as Expression, pass every row through.

Connected or unconnected Lookup: which is faster?

Neither by itself. Unconnected lookups help when you only need the value for some rows, since they run only when called.

How do I prepare for an Informatica interview?

Rehearse one SCD Type 2 mapping, one tuning story and the transformation table above, and practice the SQL; our <a href="/blog/sql-interview-questions">SQL interview questions</a> cover the usual round.

Practice this interview before the real one

The AI mock interview builds questions from your resume and the job description, reads them aloud and scores each spoken answer out of 10. Included on the free plan.

Start a mock interview