Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Informatica Power Center Interview Questions and Answers
Basic Questions on Transformations
What is a transformation?
What is an active transformation?
What is a passive transformation?
What is a connected transformation?
What is an unconnected transformation?
What are multi-group transformations?
List out all the transformations which use cache?
What is blocking transformation?
What is a reusable transformation?
How do you promote a non-reusable transformation to reusable transformation?
How to create a non-reusable instance of reusable transformations?
Which transformation can be created only as reusable transformation but not as non-reusable transformation?
AGGREGATOR Transformation
What is an Aggregator transformation?
Explain the difference between Aggregator and Expression Transformations?
Does an Aggregator transformation support only Aggregate expressions?
What kind of transformation logic did you use in Aggregator, give examples for each?
How does aggregator transformation handle null values?
What are the performance considerations when working with aggregator transformation?
Explain about index and data cache?
Explain the difference between the Cache calculator and the Auto Index settings
Why Aggregator transformation performance is improved when sorted input option is enabled?
Under what conditions selecting sorted input in aggregator will still not boost session performance?
Under what condition selecting sorted input in aggregator may fail the session?
Suppose we do not group by on any ports of the aggregator what will be the output.
What is the expected value if the column in an aggregator transformation neither a group by nor an aggregate expression?
What is incremental aggregation?
Which transformation is required to use Incremental Aggregation?
Explain about Incremental Aggregation with a real time example which you have used in your project?
Explain the steps PowerCenter performs when executing sessions with Incremental Aggregation option enabled?
You have enabled Incremental Aggregation to process the records Incrementally, but due to some issue you need to recalculate all the calculations, can you perform this by deleting the aggregate cache files created by Informatica?
In which scenarios can you use Incremental Aggregation?
In which scenarios, it is recommended to not use the sorted input option for Aggregator Transformation?
How can you delete duplicate records using Aggregator transformation?
How can you capture duplicate records in Aggregator transformation?
How can you process source records in groups?
Explain how to Optimize the Aggregator transformation?
Explain about Sorted input option in Aggregator transformation?
Why does Aggregator transformation have a partition point by default?
Explain about couple of options which can be used at mapping level to optimize the Aggregator transformation performance?
EXPRESSION TRANSFORMATION
What is an Expression Transform?
How many types of Ports are there in Expression Transform?
What is the Execution order of the Ports in an Expression?
Describe the approach for the requirement. Suppose the input is:
How can we Implement Aggregation operation without using an Aggregator Transformation in Informatica?
FILTER TRANSFORMATION
What is a Filter Transformation and why it is an Active One?
What is the difference between Source Qualifier Transformations Source Filter option and Filter Transformation?
JOINER TRANSFORMATION
What is a Joiner Transformation and why it is an Active One?
State the limitations where we cannot use Joiner in the Mapping pipeline.
Out of the two input pipelines of a Joiner, which one will we set as the Master Pipeline?
What are the different types of Joins available in Joiner transformation?
Define the various join types of Joiner transformation.
Describe the impact of number of join conditions and join order in a Joiner.
How does Joiner transformation treat null value matching?
When we configure the Join condition, what are the guidelines we need to follow to maintain the Sort order?
What are the Transformations that cannot be placed between the Sort origin and the Joiner transformation so that we do not lose the input Sort order?
What is the use of Sorted input in Joiner transformation?
Can we join two tables based on a Join column having different data type?
LOOKUP TRANSFORMATION
What is a Lookup transform?
What are the differences between Connected and Unconnected Lookup?
What are the different Lookup cache(s)?
Is Lookup an Active or Passive transformation?
What is the difference between Static and Dynamic Lookup Cache?
What are the uses of Index and Data Caches?
What is Persistent Lookup Cache?
What type of Join does Lookup support?
Explain how Lookup transformation works like SQL left outer Join.
Where and why do we use Unconnected Lookup instead of Connected Lookup?
How can we identify Persistent Cache files in Informatica Server?
How to configure a lookup on a Flat File with Header?
What is the difference between Persistent cache and Shared cache?
Describe how to return multiple port values from Unconnected Lookup in Informatica.
How to make the Persistent Lookup cache in sync with Lookup Table?
If we use Persistent cache for a Dynamic Lookup, will the cache file be updated or inserted as required?
Is there anything wrong in sharing a persistent cache between Static and Dynamic Lookup?
What is the difference between the two update properties - update else insert, insert else update in Dynamic lookup cache?
If the default value for the lookup return port is not set, what will be the output when the lookup condition fails? 35
How can we ensure data is not duplicated in the target when the source has duplicate records, using Lookup transformation?
NORMALIZER TRANSFORMATION
What is a Normalizer Transformation?
What are levels in Normalizer Transformation?
What is the purpose of GCID and GK in a Normalizer Transformation?
RANK TRANSFORMATION
What is a Rank Transform?
How does a Rank transform differ from Aggregator transform functions max and min?
How does a Rank cache works?
What is a Rank port and Rankindex?
How can you get ranks based on different groups?
What happens if two rank values match?
What are the restrictions of Rank transformation?
How does rank transformation handle string values?
What is dense rank and does informatica supports dense rank?
How do we achieve Dense_Rank in Informatica?
Source table has 5 rows. Rank in Rank transformation is set to 10. How many rows the Rank transformation will output?
How you will load Unique record into target flat file from Source Flat Files has duplicate data?
ROUTER TRANSFORMATION
What is the difference between Router and Filter?
What is the minimum number of groups we can declare in a Router Transformation?
SEQUENCE GENERATOR TRANSFORMATION
What is a Sequence Generator Transformation?
Define the properties available in Sequence Generator Transformation in brief.
What are the changes we observe when we promote a Non-Reusable Sequence Generator to a Reusable one? And what happens if we set the number of cached values to 0 for a Reusable Transformation?
How Sequence Generator in the Mapping is handled when we Migrate the Mapping from one environment to another?
How do I get a Sequence Generator to "Pick up" where another "Left off"?
STORED PROCEDURE TRANSFORMATION
What is a Stored Procedure Transformation?
How many types of Stored Procedure Transformation are there?
How do we call an unconnected stored procedure transformation?
How do we set the execution order of pre-post load stored procedure?
How do we set the call text for Stored Procedure Transformation?
How do we receive output/return parameters from Unconnected Stored Procedure?
SORTER TRANSFORMATION
What is a Sorter Transformation?
Why is Sorter an Active Transformation?
How does Sorter handle case Sensitive Sorting?
How does sorter handle null values?
How does a sorter cache works?
How to delete duplicate records or rather to select distinct rows for Flat File Sources?
UNION TRANSFORMATION
What is a Union Transformation?
What are the restrictions of Union transformation?
How come Union Transformation is Active?
UPDATE STRATEGY TRANSFORMATION
What is Update Strategy Transform?
What are Update Strategy Constants?
How can we update a record in Target Table without using Update Strategy?
What is Data Driven?
What happens when DD_update is defined in Update Strategy and treat source rows as insert is selected in session?
What are the three areas where the rows can be flagged for particular treatment?
By default operation code for any row in Informatica without being altered is insert. Then state when do we need DD_Insert?
What is the difference between Update Strategy and following update options in Target?
What is the use of forward reject rows in Mapping?
SOURCE QUALIFIER TRANSFORMATION
What is a Source Qualifier? What are the tasks we can perform using a Source Qualifier and why it is an Active Transformation?
What happens to a mapping if we alter the data types between Source and its corresponding Source Qualifier?
Suppose we have used the select distinct and the number of sorted ports property in the source qualifier and then we add custom SQL Query. Explain what will happen.
Describe the situations where we will use the source filter, select distinct and number of sorted ports properties of Source Qualifier Transformation.
What will happen if the select list columns in the custom override SQL Query and the output ports order in Source Qualifier Transformation do not match?
Describe the Scenarios where we go for Joiner Transformation instead of Source Qualifier Transformation.
What is the maximum number we can use in number of sorted ports for sybase source system?
What is use of Source Qualifier in Informatica? Can we create a Mapping without a Source Qualifier?
Suppose we have two tables of same database type, residing in different database instance. If a database link is available, how can we join the two tables using a Source Qualifier in Informatica provided there are valid join columns.
What is the meaning of “Output is Deterministic” property in Source Qualifier Transformation?
MISCELLANEOUS
What are the new features of Informatica 9.x in Developer Level?
Name the transformations which converts one to many rows i.e. increases the i/p: o/p row count. Also what is the name of its Reverse Transformation?
How many ways we can Filter Records?
What are the transformations that use cache for performance?
What is the formula for calculation of Lookup/Rank/Aggregator Index & Data Caches?
What is the difference between Informatica Powercenter and Exchange and Mart?
How do we handle Delimiter Character as a part of the data in a Delimited Source File?
We have just received source files from UNIX. We want to stage that data to ETL Process. What are the points we need to look for?
What is the difference between Joiner and Lookup. Performance wise which one is better to use
What is the B2B in Informatica? How can we use it in Informatica?
What is CDC, SCD and MD5 in Informatica?
How can we implement an SCD type2 Mapping without using a Lookup Transformation?
How does Joiner and Lookup Transformation treat null value matching?
Does Microsoft SQL server supports bulk loading? If yes, what happens when you specify bulk mode and data driven for SQL Server Target
How can you utilize COM components in Informatica?
What is SQL transformation in Informatica?
What is a XML Source Qualifier?
What is the “Metadata Extensions” tab in Informatica?
Describe some of the ETL best practices
Is there a scope of Cloud Computing in Data Warehousing Technology?
MAPPING
What are Mapping Parameters and Variables?
Which type of variables or parameters can be declared in parameter file? $, $$, $$$ - Can all be declared or not.
What are the default values for Variables?
What does first column of Bad file (Rejected Rows) indicates?
Out of 100000 source rows some rows get discard at target, how will you trace them and where it gets loaded? 72
What is Reject Loading?
Why Informatica writer thread may reject a record?
Why target database can reject a record?
Describe various steps for loading reject file?
Variable V1 has values set as 5 in designer (default), 10 in parameter file, and 15 in Repository. While running session which value Informatica will read?
What are shortcuts? Where it can be used? What are the advantages?
Can we have an Informatica Mapping with two pipelines, where one flow is having a Transaction Control Transformation and another not. Explain why?
How can we implement reverse pivoting using Informatica Transformations?
Is it possible to update a target table without any key column in target?
MAPPLET
What is a Mapplet?
What is the difference between Reusable Transformation and Mapplet?
What are the Transformations that are not supported in Mapplet?
Is it possible to convert Reusable Transformation to a Non-Reusable one?
What is the use of Mapplet & Worklet in Project?
Is it possible to have a Mapplet within a Mapplet and Worklet within a Worklet?
SESSION
What is Session and Batches?
What are various session tracing levels?
Can we copy a session to new folder or new Repository?
Is it possible to store all the Informatica session log information in a database table? Normally the session log is stored as a Binary Compression .bin file in Sesslogs Directory. Can we store the same information in database tables for future analysis?
Can we call a Shell Script from Session Properties?
Can we change the Source and Target table names in session level?
How to write Flat File column names in Target?
What are the Error Tables present in Informatica?
What are the alternate ways to stop a session without using “stop on errors” option set to 1 in session properties?
Suppose a session fails after loading of 10,000 records in the target. How can we load the records from 10,001 when we run the session next time?
Define the types of commit intervals apart from user Defined?
Suppose session is configured with Commit Interval of 10,000 rows and source has 50,000 rows explain the commit points for source based Commit & Target based commit. Assume appropriate value wherever required?
How to capture performance statistics of Individual Transformation in the mapping and explain some important statistics that can be captured?
How can we parameterize success or failure email list?
Is it possible that a session failed but still the workflow status is showing success?
What is Busy Percentage?
Can we write a PL/SQL block in Pre and Post session or in Target Query override?
Whenever a session runs does the data gets overwritten in a Flat File Target? Is it possible to keep the existing data and add the new data to the Target File?
Can we use the same session to load a target table in different databases having same target definition?
How do you remove the cache files after the Transformation?
Why doesn't a running session quit when oracle or Sybase return Fatal Errors?
If we have written a source override query in source qualifier in mapping level but have modified the query in session level SQL override then how integration service behaves.
WORKFLOW
What is the difference between Stop and Abort options in Workflow?
Running Informatica Workflow continuously – how to run a workflow continuously until a certain condition is met?
How do we send emails from Informatica after the successful completion of one session? The email will contain the job name/ session start time and session end time in the message body.
How can we send two separate emails after a successful session run?
What is Cold Start in Informatica?
We know there are 3 options for Session Recovery Strategy - Restart task, Fail task and continue running the workflow, resume from last checkpoint whenever a session fails. How do we restart a workflow automatically without any manual intervention in the event of session failure?
What is the difference Real-Time and Continuous Workflows?
How do we send a session failure mail with the workflow or session log as Attachment?
Explain Deadlock in Informatica and how do we resolve it?
How can we pass a value from one workflow to another?
ADMINISTRATION
What is Load Manager?
What is DTM Process? How many threads it creates to process data, explain each thread in brief?
Can you create a Folder within Designer?
How do you take care of security using a Repository Manager?
What are the different uses of a Repository Manager?
What are 2 modes of Data Movement in Informatica Server?
What is Code Page used for?
What is Code Page Compatibility?
What is default Block Buffer Size?
What is default lm Shared Memory Size?
Define server concepts with respect to Memory Buffers
What are the two programs that communicate with the Informatica Server?
COMMAND LINE ARGUMENTS
What is PMCMD Commands?
What is PMREP Commands?
How do we start & stop session from PMCMD Command Line?
METADATA REPOSITORY
Is there any Metadata Query to find the list of Informatica folder name, workflow names which are migrated in a particular quarter?
How can I run Metadata Queries in Informatica PowerCenter?
Write a Metadata Query to identify the sessions having Truncate option enabled
Where can I find a History / Metrics of the load sessions that have occurred in Informatica?
How to extract the workflow monitor record Information from Informatica Metadata Repository?
REPOSITORY MANAGER
Describe the steps for Export and Import?
What are the various methods of Code Migration or which is the best way of Deployment?
What are the various options for ETL Code Migration
What is Labeling in Informatica?
Suppose having Informatica Version Control in place, can we revert back an object to a state of two previous version.
What do we mean by Team Based Development in Informatica?
SCENARIO QUESTIONS
Suppose we have ten source flat files of same structure. How can we load all the files in target database in a single batch run using a single mapping?
Suppose we have two Source Qualifier Transformations SQ1 and SQ2 connected to target tables TGT1 and TGT2 respectively. How do you ensure TGT2 is loaded after TGT1?
Suppose we have a Source Qualifier Transformation that populates two target tables. How do you ensure TGT2 is loaded after TGT1?
Suppose we have the EMP table as our source. In the target we want to view those employees whose salary are greater than or equal to the average salary for their departments. Describe your mapping approach.
How can we perform changed data capture based on load sequence number (integer) column present in the source table?
How can we load ‘X’ records (user defined record numbers) out of ‘N’ records from source dynamically, without using filter and Sequence Generator Transformation?
Suppose we have ‘n’ number of rows in the source and we have two target tables. How can we load ‘n/2’ i.e. first half the source data into one target and the remaining half into the next target?
Suppose we have a flat file which has a header record with ‘file creation date’, and detailed data records. Describe the approach to load the 'file creation date' column along with each and every detailed record.
Suppose we have a flat file which contains just a numeric value. We need to populate this value in one column of the Target Table for every source record. How can we achieve this?
How will you load a source flat file into a staging table when the file name is not fixed? The file name is like sales_2013_02_22.txt, i.e. date is appended at the end of the file as a part of file name.
Solve the below scenario using Informatica and Database SQL.
Suppose we have a column in source with values as below:
Can we pass the value of a Mapping Variable between 2 pipelines under the same mapping? If not how can we achieve this?
Implement Slowly Changing Dimension of type 2 which will load current record in current table and old data in log table.
PERFORMANCE TUNING
Which one is faster Connected or Unconnected Lookup?
How we can improve performance of Informatica Normalization Transformation.
How to improve the Session Performance?
How do you identify the Bottlenecks in Mappings?
How do you handle performance issues in Informatica? Where can you monitor the Performance?
What are Performance Counters?
How can we increase session performance?
Other common questions asked during the interviews
What is the difference between odbc.ini and tnsnames.ora?
What will happen if the select list columns in the custom override SQL Query and the output ports order in Source Qualifier Transformation do not match?
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock