20 Informatica interview questions with interview-ready answers first, then the PowerCenter and IICS logic behind them — including workflows, lookups.
Knowing the definition of a transformation and being able to explain it under pressure are two completely different skills. Most candidates preparing for informatica interview questions have the definitions covered — they've read the docs, they've skimmed a few guides — but they freeze when the interviewer says "okay, walk me through how you'd actually build that" or "what would you check first when the session fails?" That gap, between recalling a term and explaining the real setup logic behind it, is what this guide is designed to close. Every question below comes with a short spoken answer you can use in the room, followed by the deeper technical reasoning you'll need when the interviewer pushes.
This is not a glossary. It's an answer-first playbook built around the questions that actually come up in junior-to-mid ETL developer rounds, covering PowerCenter fundamentals, transformation logic, performance tuning, and the PowerCenter-to-IICS comparison that's showing up in almost every cloud-adjacent role.
The Informatica questions interviewers ask when they want to see if you actually worked in ETL
What is Informatica, and where does it fit in an ETL stack?
Short answer: Informatica is a data integration platform. In an ETL stack, it sits between your source systems and your target — it extracts data from places like CRM databases or flat files, applies transformation logic, and loads the result into a warehouse or data mart.
The longer version matters because interviewers want to know you've seen a real pipeline, not just a diagram. A typical use case: a sales team's CRM generates transactional records throughout the day, and a nightly Informatica job extracts those records, joins them to a customer dimension, applies business rules like currency conversion or null handling, and loads the result into a Snowflake or Oracle warehouse for reporting. Informatica's role is to own that middle layer reliably — handling failures, logging row counts, and making the whole process repeatable without manual intervention.
What are the main components of PowerCenter?
Short answer: PowerCenter has five main components — the Repository, the Designer, the Workflow Manager, the Workflow Monitor, and the Integration Service.
When an interviewer asks this, they're usually about to follow up with "what does each one actually do?" The Repository stores all metadata — mappings, workflows, sessions, and connection objects. The Designer is where you build and modify mappings. The Workflow Manager is where you assemble workflows, schedule them, and configure sessions. The Workflow Monitor shows you real-time and historical run status. The Integration Service is the runtime engine — it reads the workflow instructions and physically moves data from source to target. Knowing which component owns which responsibility is what separates someone who has used the tool from someone who has read about it.
How do you explain a mapping, a workflow, and a session without sounding vague?
Short answer: A mapping defines the data flow — sources, transformations, and targets. A session is a runtime instance of that mapping with specific connection and configuration settings. A workflow is the container that runs one or more sessions in sequence or in parallel.
The concrete build looks like this: you create a mapping in the Designer that reads from an Oracle source, applies an Expression transformation to calculate a derived field, and writes to a target table. You then create a session in the Workflow Manager that points to that mapping and specifies the database connections, commit intervals, and error thresholds. Finally, you wrap that session in a workflow that can be scheduled, triggered by an event, or chained with other sessions. One mapping, one session, one workflow — that's the minimal working unit a junior candidate should be able to describe clearly.
What is the difference between a source qualifier and a lookup?
Short answer: A source qualifier filters and shapes the rows coming out of a relational source. A lookup enriches rows mid-pipeline by fetching a value from a separate table or flat file.
The technical split is easier to see with an example. Say you're loading customer orders. The source qualifier on your orders table is where you add a WHERE clause to exclude cancelled orders — it controls what rows enter the pipeline. Later in the same mapping, you use a lookup against a product dimension table to pull in the product category for each order line. The source qualifier is about what comes in; the lookup is about what gets added as the row moves through. Interviewers care about this distinction because conflating the two usually means the candidate hasn't built a real mapping.
Informatica interview questions about PowerCenter architecture that sound simple until follow-ups start
How does PowerCenter architecture work end to end?
Short answer: Client tools connect to the Repository Service to store and retrieve metadata. The Workflow Manager sends workflow instructions to the Integration Service, which executes sessions and moves data between sources and targets at runtime.
The 30-second spoken answer is enough to pass the initial question. The follow-up — "walk me through it" — is where you need the next layer. The Repository Service manages all metadata persistence and handles concurrent client connections. Client tools like the Designer and Workflow Manager communicate with it to read and write mappings and workflows. When you run a workflow, the Integration Service takes over: it reads the session configuration, establishes source and target connections, applies the transformation logic defined in the mapping, and writes output to the target. Logs, row counts, and error records are all generated by the Integration Service during this execution phase.
What does the repository actually store, and why do interviewers care?
Short answer: The repository stores metadata — mappings, workflows, sessions, transformations, connection objects, and version history. It does not store actual data records.
The metadata-versus-data distinction trips up candidates who haven't thought about deployment. When a team promotes a mapping from development to production, they're migrating repository objects — not copying data. Version control in PowerCenter works through the repository: you can check objects in and out, compare versions, and roll back a broken mapping to a prior state. Interviewers ask about the repository because it's the foundation of team collaboration and deployment, and a candidate who doesn't understand what it stores will struggle to explain how changes move between environments.
How do you describe Workflow Manager and Workflow Monitor in an interview?
Short answer: Workflow Manager is where you build and configure workflows. Workflow Monitor is where you watch them run and diagnose failures.
The real monitoring habit is more specific than "check if it ran." In the Workflow Monitor, you look at task-level status first — which session inside the workflow failed, and at what point. Then you open the session log for that task, which shows you the exact error message, the row counts processed before failure, and the last successful commit. Guessing at the cause before checking the log is the single most common mistake junior developers make in production, and interviewers who have managed ETL teams know it.
What is the integration service responsible for during execution?
Short answer: The Integration Service reads workflow and session instructions, establishes source and target connections, executes the mapping logic, and writes data to the target.
The practical implication: if a workflow starts successfully but nothing lands in the target table, the Integration Service is still the right place to start your investigation. Check whether the session completed or errored, then look at the session log for connection failures, transformation errors, or commit rollbacks. A workflow showing "Succeeded" at the workflow level can still have a session that wrote zero rows due to a rejected record threshold being hit — the Integration Service log will tell you exactly what happened at the row level.
Informatica interview questions on transformations, where the real judgment starts showing
When should you use an Expression transformation instead of pushing logic into SQL?
Short answer: Use an Expression transformation when you need the logic visible and maintainable inside the mapping. Push logic into SQL when performance is the priority and the source database can handle the computation efficiently.
The real tradeoff is readability versus performance. An Expression transformation keeps derived field logic — date formatting, string concatenation, conditional flags — inside the PowerCenter mapping where any developer on the team can see and modify it without touching the source query. Pushdown optimization, by contrast, sends that logic to the database engine, which is faster for large volumes but harder to audit when something breaks. The interview-safe answer is: default to Expression for maintainability, use pushdown when you have a documented performance problem and the source system can absorb the load.
Connected or unconnected lookup — which one sounds smarter in a real interview?
Short answer: Connected lookups are part of the data flow and return a value for every row. Unconnected lookups are called explicitly using the :LKP expression and return a single value on demand.
Neither is universally smarter — the right answer depends on the use case. A connected lookup makes sense when you're enriching every row in the pipeline with a value from a reference table, like pulling a region code for each customer record. An unconnected lookup is the better call when you only need the lookup for specific rows, or when you want to reuse the same lookup logic across multiple transformations without duplicating the cache. The cache behavior matters here: both types can use a static or dynamic cache, but unconnected lookups are often more efficient when the lookup is called conditionally rather than for every row passing through.
How do you explain Joiner, Aggregator, Sorter, and Sequence Generator without turning it into a glossary?
Short answer: Joiner combines two heterogeneous sources mid-pipeline. Aggregator groups and summarizes rows. Sorter orders rows before a downstream transformation that requires sorted input. Sequence Generator produces unique key values for surrogate key assignment.
The clearest way to make these stick in an interview is to anchor them in one example. Take a sales summary mapping: you use a Joiner to combine the orders stream with the customer stream on customer ID. You pass the joined rows into an Aggregator grouped by region and month to calculate total revenue. Before the Aggregator, you add a Sorter on the group-by keys because sorted input dramatically reduces the memory the Aggregator needs. Finally, you use a Sequence Generator to assign a surrogate key to each summary row before it lands in the fact table. One mapping, four transformations, each with a clear job.
What does Update Strategy actually do in a load pattern?
Short answer: Update Strategy determines whether each row entering the target should be inserted, updated, deleted, or rejected, based on a condition you define.
The spoken answer is simple. The real value shows up when the target table is being maintained rather than just appended to. In a customer dimension load, for example, you might flag rows as DD_INSERT when the customer ID doesn't exist in the target, and DD_UPDATE when it does. The Update Strategy transformation applies that flag, and the session's target properties honor it by issuing INSERT or UPDATE statements accordingly. If you need to handle deletes — removing records that no longer appear in the source — you flag them as DD_DELETE. Rows that violate constraints or don't match any condition get flagged as DD_REJECT and written to a reject file for review. Understanding the full four-way split is what separates a candidate who has done maintenance loads from one who has only done initial loads.
How to explain performance tuning without sounding like you memorized a checklist
How do you tune an Informatica mapping for performance?
Short answer: Start with pushdown optimization to move work to the database, then look at partitioning to parallelize the pipeline, then check whether sorted input can reduce Aggregator memory usage, and finally remove any transformations that aren't doing real work.
The highest-value knobs in order: pushdown optimization is often the single biggest win because it lets the source database do filtering and joining before rows ever enter the Integration Service. Pipeline partitioning splits the data stream so multiple threads process different row ranges simultaneously — effective when the source volume is large and the target can handle concurrent writes. Sorted input for Aggregator and Joiner transformations reduces memory pressure significantly because the engine doesn't need to hold the full dataset in cache to group rows. Finally, audit the mapping for pass-through transformations — Expression transformations that calculate nothing, or Routers with a single output group — and remove them. Each unnecessary transformation adds processing overhead.
When does a lookup become a performance problem?
Short answer: When the lookup cache is too small for the reference table, or when the lookup is running uncached against a large source on every row.
The symptom an interviewer expects you to recognize is a session that runs fine in development on a small dataset and degrades badly in production. The usual cause is cache pressure: the lookup cache is sized to fit in memory, but in production the reference table has grown beyond that threshold, forcing the Integration Service to spill to disk or re-query the source. The fix is either to increase the cache size in the session properties, switch to a persistent cache if the reference data is stable, or pre-filter the lookup source to reduce the rows it needs to hold. Dynamic cache is appropriate when the reference table is being updated during the session run — but it carries its own overhead, so use it only when the data genuinely changes mid-load.
What changes when incremental loading or incremental aggregation is part of the design?
Short answer: Incremental loading means you only process rows that changed since the last run. Incremental aggregation means the Aggregator transformation updates its cached result set rather than recalculating from scratch each time.
The production logic behind incremental loading usually involves a high-water mark — a timestamp or sequence number stored in a control table that records the last successful load boundary. Each run reads from the source where the record's modified timestamp is greater than the stored high-water mark, processes only those rows, and updates the control table on success. Without this pattern, a job that runs nightly ends up reprocessing the entire source history every time, which is the most common cause of ETL jobs that worked fine at launch and become unacceptably slow six months later. Incremental aggregation in PowerCenter works differently — it uses a cached aggregate file from the prior run and applies only the delta rows, which is useful for rolling summary tables where a full recalculation would be expensive.
PowerCenter vs IICS: the keyword interview question that tells them whether you can move with the platform
How is PowerCenter different from IICS?
Short answer: PowerCenter is an on-premises ETL platform managed on your own servers. IICS — Informatica Intelligent Cloud Services — is the cloud-native version, delivered as a SaaS platform with browser-based design tools and a Secure Agent handling local execution.
The interviewer's real question is whether you can work in both environments without getting confused. The architectural difference that matters most: in PowerCenter, the Integration Service runs on a server you manage. In IICS, the Secure Agent runs locally or in your cloud environment and handles data movement, while the design, scheduling, and monitoring happen in the cloud console. The mapping concepts carry over — sources, targets, transformations — but the tooling, deployment model, and monitoring interface are different enough that assuming they're interchangeable will trip you up in a cloud role.
What should you study first if the job mentions Secure Agent and cloud tasks?
Short answer: Understand what the Secure Agent does, how task flows replace PowerCenter workflows, and where to monitor job runs in the IICS console.
The Secure Agent is a lightweight process that runs in your network and executes data integration tasks defined in the cloud. It's the bridge between your on-premises or cloud data sources and the IICS platform. Task flows in IICS are the functional equivalent of PowerCenter workflows — they sequence and orchestrate individual tasks like Mapping Tasks, Synchronization Tasks, and Data Masking Tasks. If you've only worked in PowerCenter, the concepts translate, but the configuration interface and the way you handle errors and retries are different. Start with Secure Agent setup, then walk through a simple Mapping Task end to end in the IICS console.
How do you talk about PowerCenter-to-IICS migration without sounding like you've only read a sales deck?
Short answer: Acknowledge that mappings often migrate with reasonable fidelity, but the operational differences — agent setup, task configuration, scheduling, and monitoring — are where teams consistently underestimate the work.
A practical migration story looks like this: the mapping logic itself usually converts through Informatica's migration tools, but teams discover quickly that PowerCenter session parameters, pre- and post-session commands, and complex workflow logic with decision tasks don't map one-to-one to IICS task flows. Connection objects need to be recreated in the IICS console. Scheduling moves from the Workflow Manager to the IICS scheduler or an external orchestrator. Monitoring shifts from the Workflow Monitor to the cloud console, which has a different set of log details and alert configurations. The teams that struggle are the ones who assumed migration was a lift-and-shift. The ones who succeed treat it as a partial redesign of the operational layer.
Scenario-based Informatica questions recruiters use to separate theory from actual ETL work
How would you load only changed records from a source table?
Short answer: Use a high-water mark stored in a control table, filter the source in the source qualifier using a WHERE clause on the modified timestamp, and update the high-water mark on successful completion.
The real implementation path depends on what the source system gives you. If the source has a reliable `last_modified` timestamp column, the high-water mark approach is the cleanest option. If the source doesn't have a timestamp but does have a change flag or a CDC mechanism, you filter on that instead. If neither exists, you fall back to a full extract with a hash comparison or a minus query to identify changed rows — which is more expensive but sometimes unavoidable. The key point interviewers want to hear: you don't assume the source is always well-structured, and you have a fallback for each scenario.
How do you handle duplicate rows before they hit the target?
Short answer: Deduplicate in the mapping using a Sorter followed by logic in an Expression or Router transformation, or push the deduplication to the source qualifier using DISTINCT or a GROUP BY in the override query.
The tradeoff is between where you catch the duplicates and what it costs you. Source-side deduplication via a SQL override is efficient because it reduces the row volume before data enters the pipeline — but it only works if the source is a relational database and the deduplication logic is simple enough to express in SQL. For more complex deduplication — where you need to choose which duplicate to keep based on a recency flag or a priority rule — you sort the rows in the pipeline on the key and the tiebreaker column, then use an Expression transformation to flag the first occurrence of each key as the keeper. Target constraints are a last line of defense, not a strategy.
How would you build a Type 1 or Type 2 SCD in Informatica?
Short answer: Type 1 overwrites the existing record when an attribute changes. Type 2 preserves the history by inserting a new row with a new effective date and closing out the old row.
The concrete dimension example: a customer dimension stores address information. With Type 1, when a customer moves, you update the existing row — simple, but history is lost. With Type 2, you set an end date on the existing row, insert a new row with the updated address and a new effective start date, and assign a new surrogate key to the new version. In Informatica, Type 1 is handled with Update Strategy flagging existing rows as DD_UPDATE. Type 2 requires a lookup to detect changed attributes, an Expression to generate new surrogate keys via Sequence Generator, and separate insert streams for new and changed rows. The interviewer wants to know you understand when history matters — not just how to configure the mapping.
What do you check first when a session fails but the workflow still starts?
Short answer: Open the session log in Workflow Monitor, find the first ERROR line, and check whether the failure is a connection issue, a transformation error, or a row-level rejection that exceeded the error threshold.
The real debugging sequence has three steps. First, look at the session's task status in the Workflow Monitor — a session can show "Failed" while the parent workflow shows "Running" if other tasks are still executing. Second, open the session log and search for the first ERROR entry, not the last one — the root cause is usually the first failure, and subsequent errors are often cascading consequences of it. Third, check the row counts: if the session processed rows but the target shows nothing, look for a commit failure or a rollback caused by a target constraint violation. A session that wrote zero rows to the target but shows no errors usually means the source query returned no rows — check the source qualifier filter conditions before assuming the mapping is broken.
How Verve AI Can Help You Prepare for Your ETL Developer Job Interview
The hardest part of an ETL developer interview isn't the concepts — it's translating what you know into a clear, confident answer in real time, especially when the follow-up comes faster than you expected. That's where Verve AI Interview Copilot changes the dynamic. During a live interview on Zoom, Google Meet, or Teams, the Interview Copilot listens in real-time and helps you structure your answer as the conversation unfolds — so when an interviewer pivots from "what is a lookup?" to "walk me through a cache pressure problem you've seen," you have a clear path forward instead of a blank moment. On the desktop app, the Interview Copilot stays invisible during screen share, so your support is there without changing how the interview looks to the other side. Before the real thing, the separate Mock Interviews feature lets you run practice rounds on Informatica-specific scenarios — PowerCenter architecture, transformation logic, SCD patterns — so your spoken answers are tight before the day that counts.
Conclusion
The pattern across every section of this guide is the same: a short, clean answer that holds up in the room, backed by the technical reasoning that survives follow-ups. You don't need a memorized script for every question. You need to understand what the platform is actually doing — why the Integration Service owns the execution layer, why lookup cache size matters at scale, why a Type 2 SCD needs a separate insert stream — so that when the interviewer goes off-script, you can reconstruct the answer from first principles rather than scrambling to remember a definition.
Start with the spoken answer. Say it out loud. Then work through the deeper version until you can explain the setup logic, the failure mode, and the tradeoff without looking at notes. That two-layer preparation is what separates candidates who pass the screen from the ones who get the offer.
Jason Miller
Career Coach
