Important Informatica MDM Interview Questions and Answers - Part III

Are you preparing for Informatica MDM interview? And are you looking for the interview questions and answers about Informatica MDM? If yes, then refer to this article. In this article, we are going discuss various questions and their answers which are normally asked in Informatica MDM interview. You can also refer the previous article - Important Informatica MDM Interview Questions and Answers - Part II

Q 1: Suppose you are running stage job with delta detection enabled. While running stage job delta detection is successful but a stage job failed to insert the records in Stage table. How do you handle this issue?

Answer: 

This is scenario based question which can be asked by the interviewer to check knowledge of the candidate.

In the case of full data load if stage job failed to process records then we can handle this situation in two ways-
1. Truncate PRL and reload:

• When we run stage job, the records from landing table get compared with _PRL table and delta is determined. 
• If we re-run stage job after its failure then no delta will be determined as the _PRL table will be same as the landing table. 
• To fix this we can truncate PRL table and re-run stage job. 
• There will be more time required to run stage job as it is going to process whole data set. 
• Only delta records will be updated or inserted as part of the load job.

2. Populate PRL table using _RAW table:

• If we have enabled RAW retention then this approach will be an efficient approach.
• First, we need to determine JOB_ROWID for the previous run using C_REPOS_JOB_CONTROL table.
• Using JOB_ROWID we can pull all records from the _RAW table and insert into the _PRL table.
• We need to re-run -stage job to process delta records.

The video below provides more insights about the stage and load jobs in Informatica MDM

Q 2: When PRL, OPL, RAW and REJ tables are created?

Answer:

When we configure the landing and staging tables the next is to create the mapping. Once mappings are created then Raw retention and delta detections properties get enabled. The mentioned below are the instances during which PRL, OPL, RAW and REJ tables are created.

a. _REJ table get created when we create the staging table

b. When we configure the staging table for Raw Retention, the _RAW table associated with the staging table is created.  

c. The _PRL and _OPL tables are created when we configure delta detection for the staging tables.

Q 3: What are the causes of record rejection?

Answer:
The _REJ table is associated with the staging table. e.g. If the staging table name is C_STG_CRM_PARTY then associated reject table name will be C_STG_CRM_PARTY_REJ.

Reason for Reject table creation:
1. The reject table is created to store rejected records during the stage job and the load job.
2. To increases performance by rejecting a record when it first encounters a reason to reject the record

Note: If there is more than one reason to reject a record, the reject table describes the first reason that  encounters.

There are several causes for the record to reject during MDM processes. The main reasons or causes for record rejections are as follows:

• The value of PKEY_SRC_OBJECT column is null 
• The duplicate value in PKEY_SRC_OBJECT column. One one record is processed successfully (One with highest SRC_ROWID). The other duplicate record/records are rejected
• The value in the LAST_UPDATE_DATE column contains a future date or null date.
• The value in the LAST_UPDATE_DATE column is less than 1900.
• The unique column contains duplicate values.
• The column HUB_STATE_IND contains values other than 1, -1, 0
• The column contains invalid referential integrity value.

Q 4: When PRL, REJ, STG and RAW table get cleared/truncated?

Answer:
This is another interesting question interviewer may ask to check how extensive candidate has worked with Informatica MDM tool.

Not all the system tables in the Informatica MDM are truncated. Some of the system tables are truncated during specific processes.
a. The _PRL table gets truncated during each stage job run
b. The _REJ table never gets truncated during stage or load job. However, we can manually truncate it or we can use Clean  SIF API on Base Object table to clean or truncate REJ table.
c. The _STG table is truncated during each stage job
d. The _RAW table never gets truncated during stage or load job. However, if the retention period is complete then the unique records are kept in the _RAW table from stage job prior to the retention period. The remaining records are deleted from the _RAW table. The _RAW table also get truncated when we call Clean SIF API on Base Object Table.

Read More: Learn more about how to handle rejected records.

Q 5: Have you used any data quality tool along with Informatica MDM such as Informatica Data Quality?

Answer:

In some projects, Data Quality tools are used. It is not mandatory to have knowledge or work experience in Data Quality tool. However, having knowledge about Data Quality tool will make your career profile strong.

So if you have Data Quality experience then mention about it. e.g. You can mention that you used Data Quality to perform data analysis and come with data standardization rules for Party and Address data.

You can refer the video below learn more about Informatica Data Quality