WIXLIB

Informatica Interview Questions and Answers [Scenario-Based]In our previous blog, we presented to you a list of frequently asked Informatica interviewquestions, which you should prepare for the Informatica interview round. However, whileinterviewing for data warehousing related jobs, you may be asked questions about Informaticabased scenarios. Here is a list of top Informatica Scenario-based interview questions to help youprepare for your upcoming interview.What are the Scenario-Based questions?In a Scenario-based interview, you will be first offered a scenario and then asked questionsrelated to it. Your response to Informatica scenario-based interview questions will show yourtechnical skills as well as your soft skills, such as problem-solving and critical thinking.Now that you are just one step away to land a job in your dream job, you must prepare well forall the likely interview questions. Keep in mind that every interview round is different,especially when scenario-based Information questions are asked.Top Informatica Scenario-Based Interview QuestionsQ1. How do you load the last N rows from a flat-file into a target table in Informatica?Ans. Considering that the source has data: ColABCDEFGHIJKLMNONow follow the below steps to load the last 3 rows into a target tableStep 1:

Assign the row numbers to each record by using expression transformation. Name therow to calculate as N calculate.Create a dummy output port and assign 1 to the port in the same expressiontransformation.This will return 1 for each row.Ports in Expression TransformationV calculate V calculatet 1N calculate V calculateN dummy 1Outputs in Expression Transformationcol, N calculate, N dummyABC, 1, 1DEF, 2, 1GHI, 3, 1JKL, 4, 1MNO, 5, 1Step 2: Pass expression transformation output to the aggregator transformationDo not specify condition ‘any group’Create a N total records output port in the aggregatorAssign the N calculatet port to it.By default, it will return the last row

It will contain DUMMY portNow it will hold the value as 1 and N total records port (it will keep the value of thetotal number of records available in the source)Ports in Aggregator TransformationN dummyN calculateN total records N calculateOutputs in Aggregator TransformationN total records, N dummy5, 1Step 3 Now pass the value of expression and aggregator transformation to the joinertransformationMerge the dummy portCheck the property sorted input in the joiner transformation to connect both expressionand aggregator transformationNow the join condition will be O dummy (port from aggregator transformation) O dummy (port from expression transformation)Outputs in Joiner Transformationcol, N calculate, N total recordsABC, 1, 5

DEF, 2, 5GHI, 3, 5JKL, 4, 5MNO, 5, 5Step 4 Pass the joiner transformation to filter transformationMention the filter condition as N total records (port from aggregator)-N calculate(portfrom expression) 2Thus, the filter condition in the filter transformation will be N total records –N calculate 2OutputOutputs in Filter Transformationcol N calculate, N total recordsGHI, 3, 5JKL, 4, 5MNO, 5, 5SummaryQ2. Solve the below situations if data has duplicate rows.Data

tSituation – Give steps to load all unique names in one table and duplicate names in anothertable.Solution 1 – We want solution tables as:Amazon and Flipkart in one tableAndWalmart, Walmart, Walmart, Snapdeal, and Snapdeal in another tableFollow the below steps Sort the name data by using a sorter transformationPass the sorted output to an expression transformationForm a dummy port N dummy and assign 1 to the portNow for each row, the Dummy output port will return 1Expression Transformation OutputName, N dummyAmazon, 1Walmart, 1Walmart, 1

Walmart, 1Snapdeal, 1Snapdeal, 1Flipkart, 1 Pass the acquired expression transformation output to aggregator transformationCheck ‘groupby’ on name portCreate an output port in aggregator N calculate of each name and write an expressioncalculate(name).Aggregator Transformation Outputname, N calculate of each nameAmazon, 1Walmart, 3Snapdeal, 2Flipkart, 1 Pass the expression and aggregator transformation output to joiner transformationJoin the name portsReview the property sorted input to connect both transformations to joinertransformationJoiner Transformation Output

name, N dummy, N calculate of each nameAmazon, 1, 1Walmart, 1, 3Walmart, 1, 3Walmart, 1, 3Snapdeal, 1, 2Snapdeal, 1, 2Flipkart, 1, 1 Move the joiner output to router transformationCreate one groupSpecify it as O dummy O count of each nameConnect the group to one tableConnect default output group to another tableYou will get separate tables for bothQ3. Situation 2 – Solve the below situations if data has duplicate rows.Data t

Situation – Load each name once in one table and duplicate products in another table.Ans.Solution 2 – We want the output as:Table 1AmazonWalmartSnapdealFlipkartTable 2WalmartWalmartSnapdealThe below steps will give the desired solution: Sort the name data by using a sorter transformationPass name output to expression transformationCreate a variable port,Z curr nameAssign the name port to variable portCreate Z calculate portWrite in the expression editor, IIF(Z curr name Z prev name, V calculate 1,1)Form another variable and call it as port Z prev portAssign the name port to this variableForm the output portN calculate portAssign Z calculate to this output port

Expression Transformation Name portZ curr name nameZ calculate IIF(Z curr name Z prev name, Z calculate 1, 1)N calculate Z calculateExpression Transformation OutputAmazon, 1Walmart, 1Walmart, 2Walmart, 3Snapdeal, 1Snapdeal, 2Flipkart, 1 Route the expression transformation to router transformationForm a groupSpecify condition as N calculate 1Merge the group to one tableMerge the default group output to another table

Q4. In Informatica, how do you use Normalizer Transformation for the below-mentionedconditionStateQuarter 1PurchaseQuarter 2PurchaseQuarter 3PurchaseQuarter 4PurchaseABC80859095DEF60657075Ans. If you want to transform a single row into multiple rows, Normalizer Transformation willhelp. Also, it is used for converting multiple rows into a single row to make data look organized.As per the above scenario-based Informatica interview question, we want the solution to lookas:State EF265DEF370DEF475

Follow the steps to achieve the desired solution by using normalizer transformation:Step 1 – Create a table “purchase source” and assign a target table as “purchase target”Import the table to informaticaCreate a mapping for both the tables having a source as “purchase source”“purchase target” respectivelyCreate a new transformation from the transformation menuEnter the name “xyz purchase”Select create optionSelect done (now the transformation is created)Step 2 – Double click on normalization transformationGo to normalizer tab and select itFrom the tab, click on the icon, this will create two columnsEnter the names of columnsFix number of occurrences to 4 for purchase and 0 for the state nameSelect OK4 columns will be generated and appear in the transformationStep 3 – In the mapping, link all four columns in source qualifier of the four Quarters to thenormalizerLink state name column to normalizer columnLink state name and purchase columns to target tableLink lkp purchase column to target tableCreate session and workflowSave the mapping and execute itYou will get the desired rearranged outputState NameQuarterPurchaseABC180

ABC285ABC390ABC495DEF160DEF265DEF370DEF475Q5. What to do when you get the below error?AA 10000 Normalizer Transformation: Initialization Error: [Cannot match AASid with BBTid.]Ans. Follow the below process – Remove all the unconnected input ports to the normalizer transformationIf OCCURS is present, check that the number of input ports is equal to the number ofOCCURSQ6. What are the steps to create, design, and implement SCD Type 1 mapping in Informaticausing the ETL tool?Ans. The SCD Type 1 mapping helps in the situation when you don’t want to store historicaldata in the Dimension table as this method overwrites the previous data with the latest data.The process to be followed: Identify new recordsInsert it into the dimension tableIdentify the changed record

Update it in the dimension tableFor example:If the source table looks like:CREATE TABLE Students (Student Id Number,Student Name Varchar2(60),Place Varchar2(60))Now we require using the SCD Type 1 method to load the data present in the source table intothe student dimension table.CREATE TABLE Students Dim (Stud Key Number,Student Id Number,Student Name Varchar2(60),Location Varchar2(60))Follow the steps to generate SCD Type 1 mapping in Informatica In the database, create source and dimension tablesCreate or import source definition in the mapping designer tool’s source analyzerImport the Target Definition from Warehouse designer or Target designerCreate a new mapping from the mapping designer tabDrag and drop the source

Select Create option from toolbar’s Transformation sectionSelect Lookup TransformationEnter the name and click on createFrom the window, select Student dimension table and click OKEdit lkp transformationAdd a new port In Student Id from the properties tabConnect the port to source qualifier transformation’ Student Id portFrom the lkp transformation’s condition tab, enter the Lookup condition as Student Id IN Student IdClick OKNow, connect source qualifier transformation’s student id port to lkp transformation’sIn Student Id portCreate expression transformation using the input port as Stud Key, Name, Location,Src Name, Src LocationCreate an output port as New Flag, Changes FlagIn the expression transformation’s output ports, enter the below-mentioned expressiono New Flag IIF(ISNULL(Stud Key),1,0)Changed Flag IIF(NOT ISNULL(Stud Key)AND (Name ! Src NameOR Location ! Src Location),1, 0 ) Connect lkp transformation port to expression transformation portAlso, connect source qualifier transformation port to expression transformation portForm a filter transformation and move the ports of source qualifier transformationEdit the filter transformation and set new Filter Condition as New Flag 1 from the editfilter transformation optionPress OKCreate an update strategy transformationConnect all filter transformation port just exclude except the New Flag portFrom the properties tab of update strategy, enter DD INSERT as the strategy expressionDrag target definition to mappingConnect relevant ports to target definition from update strategyCreate a sequence generator transformationConnect NEXTVAL port to target surrogate key port (stud key)Create a different filter transformation

In the filter transformation, drag lkp transformation’s port (Stud Key), source qualifiertransformation (Name, Location), expression transformation (changed flag) portsGo to the properties tab to edit the filter transformationMention the filter condition as Changed Flag 1Click OKCreate the update strategyConnect parts of filter transformation to update strategyFrom the update strategy properties tab, enter expressions DD UpdateIn this mapping, drag target definitionFrom the update strategy, connect all the appropriate ports to target definitionQ7. Give steps to use PMCMD Utility Command.Ans. There are 4 different built-in command-line programs: infacmdinfasetuppmcmdPmrepPMCMD command helps for the following functions: Start workflowsSchedule workflowsStart a workflow from a specific taskStop and abort workflows and sessionsBelow are the steps to use PMCMD command:1. Start workflowpmcmd startworkflow -service informatica-integration-Service -d domain-name -u user-name -ppassword -f folder-name -w workflow-name2. Scheduling the workflowpmcmd scheduleworkflow -service informatica-integration-Service -d domain-name -u username -p password -f folder-name -w workflow-name3. Start a workflow from a specific task