Bad Record Exception Mappings
When you create a mapping that identifies bad record exceptions, you configure the mapping to write records to one or more database targets based on the quality of the data in the records.
The following figure shows an example Bad Record Exception mapping:
The mapping contains the following objects:
- Data source
- An Employees data source that contains the records to analyze for data quality.
- Mapplet
- The Bad_Records_Mapplet contains transformations that check for and add quality issues and record scores to source records. Rules are transformations that analyze the data and find the quality issues. For example, you can include a Labeler transformation to compare input data to reference tables. Depending on the results, you can configure the Labeler transformation to return quality issues as additional columns in the rows. You can configure a Decision transformation that uses IF, THEN, ELSE statements to examine the data and apply quality issues and record scores to the input data.
- Exception transformation
- The Exception transformation determines which records to write to the data targets including the bad records table and the issues table.
- Good record table
- The Exception transformation writes all good-quality records to the target_Employees table.
- Bad Record table
- The Exception transformation writes all bad-quality records to the target_EmployeeBadRecords table. Bad records require manual review.
- Issues table
- The Exception transformation writes quality issues to the target_EmployeeBadRecords_ISSUES table. When you view the bad records in the Analyst tool, the user interface links the quality issues to the bad records.
Optionally, the Exception transformation can write rejected records to a rejected records table. You must choose to create a separate output group for rejected records on the Configuration view of the transformation.
Bad Record Exception Quality Issues
Quality issues are text strings that describe the type of data quality problem that caused a low record score. The Bad Record Exception transformation receives quality issues associated with each source row that contains a low record score. You can configure different types of transformations that determine quality issues and record scores.
For example, you might create a Decision transformation that examines the phone number. The Decision transformation generates the record score and the quality issues for phone number.
The following decision strategy identifies phone numbers of incorrect length in a Decision transformation:
IF LENGTH(Phone_Number) > 10 THEN
Score:=50
Phone_Quality_Issue:='Phone num too long'
ELSEIF LENGTH(Phone_Number) < 10 THEN
Score:=50
Phone_Quality_Issue:=' Phone num too short’
ELSE
Score:=90
ENDIF
When you configure the Exception transformation, you must associate Phone_Quality_Issue with the Phone_Number port. The ports are from different input groups.
The Exception transformation reads the scores generated by the Decision transformation and assigns records with a score of "50" to the bad records group of output ports. It writes the Phone_Quality_Issue to the Issues group of output ports.
Human Tasks
When you configure a workflow that contains an Exception transformation, you include the mapping in a Mapping task. You add a Human task to the same workflow. The Human task requires one or more users to correct the exception records in the Analyst tool.
The Mapping task identifies the records in the source data that contain unresolved data quality issues. Data analysts use the Analyst tool to resolve the issues and to update the data quality status of each record.
When you configure a Human task, you create one or more task instances and one or more task steps. A task instance represents the data set that a user must work on. A task step represents the type of work that a user performs on the records in his or her task instance. You can create multiple task instances so that different users work on different parts of the data in the Analyst tool.
A user can update the status of the bad records in the Analyst tool in one of the following ways:
- •If a record is valid, the user updates the table metadata to confirm the record for persistent storage in the database.
- •If a record is not valid, the user updates the table metadata to remove the record from the database at a later stage in the workflow.
- •If the record status is not confirmed, the user updates the table metadata so that the record returns to the workflow for further processing in a Mapping task.
For more information about Human tasks, see the Informatica Developer Workflow Guide.
