in Technology by

How do you remove Duplicate records in Informatica? And how many ways are there to do it?

1 Answer

0 votes
by

There are several ways to remove duplicates.

  1. If the source is DBMS, you can use the property in Source Qualifier to select the distinct records.Edit-transformations-informatica- interview-questionsOr you can also use the SQL Override to perform the same.SQL-override-informatica-interview-questions
  2. You can use, Aggregator and select all the ports as key to get the distinct values. After you pass all the required ports to the Aggregator, select all those ports , those you need to select for de-duplication. If you want to find the duplicates based on the entire columns, select all the ports as group by key.
    group-by-key-informatica-interview-questionsThe Mapping will look like this.
    mapping-informatica-interview-questions
  3. You can use Sorter and use the Sort Distinct Property to get the distinct values. Configure the sorter in the following way to enable this.
    Configure-sorter-informatica-interview-questions
  4. You can use, Expression and Filter transformation, to identify and remove duplicate if your data is sorted. If your data is not sorted, then, you may first use a sorter to sort the data and then apply this logic:
  • Bring the source into the Mapping designer.
  • Let’s assume the data is not sorted. We are using a sorter to sort the data. The Key for sorting would be Employee_ID.
    sorter-informatica-interview-questionsConfigure the Sorter as mentioned below.
    Edit-transformations-informatica-interview-questions
  • Use one expression transformation to flag the duplicates. We will use the variable ports to identify the duplicate entries, based on Employee_ID.
    Flag-duplicates-informatica-interview-questions
  • Use a filter transformation, only to pass IS_DUP = 0. As from the previous expression transformation, we will have IS_DUP =0 attached to only records, which are unique. If IS_DUP > 0, that means, those are duplicate entries.
    Filter-transformations-informatica-interview-questions
  • Add the ports to the target. The entire mapping should look like this.
    ports-informatica-interview-questions

v. When you change the property of the Lookup transformation to use the Dynamic Cache, a new port is added to the transformation. NewLookupRow.

The Dynamic Cache can update the cache, as and when it is reading the data.

If the source has duplicate records, you can also use Dynamic Lookup cache and then router to select only the distinct one.

Related questions

0 votes
    How do you load first and last records into target table? How many ways are there to do it? Explain through mapping flows...
asked Mar 28, 2021 in Technology by JackTerrance
0 votes
    What are the different ways to implement parallel processing in Informatica?...
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    What are the different ways to filter rows using Informatica transformations?...
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    In control applications, how many ways are there to control a plant? (a) 1 (b) 2 (c) 4 (d) infinite Please answer the above question....
asked Aug 23, 2022 in Education by JackTerrance
0 votes
    How many primary ways are there for detecting buffer-overflow? (a) 6 (b) 3 (c) 2 (d) 5 I have been ... bank, Cyber Security questions and answers pdf, mcq on Cyber Security pdf,...
asked Nov 4, 2021 in Education by JackTerrance
0 votes
    Different circumstance which drives Informatica server to expel records?...
asked Jun 8, 2021 in Technology by JackTerrance
0 votes
    How do you load more than 1 Max Sal in each Department through Informatica or write sql query in oracle?...
asked Mar 28, 2021 in Technology by JackTerrance
0 votes
    I've got an extremely long XML file, like context1 test1 context1 context2 test2 context2 ........ ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 4, 2022 in Education by JackTerrance
0 votes
    In how many different ways can it be done ? 10 men and 8 women out of which 5men are teachers, 3 men doctors and ... 150 C. 214 D. 20 Select the correct answer from above options...
asked Nov 13, 2021 in Education by JackTerrance
0 votes
    I am trying to use change tracking to copy data incrementally from a SQL Server to an Azure SQL Database. I ... change tracking table? Select the correct answer from above options...
asked Feb 4, 2022 in Education by JackTerrance
0 votes
    A number of cats got together and decided to kill between them 999919 rats. Every cat killed an equal number of rats. Each cat ... cat killed ? A) 1009 B) 991 C) 2000 D) 1000...
asked Feb 12, 2021 in Education by JackTerrance
...