Skip to main content

How to Verify Transformation Logic using Concat Expression?

In this use case, we will demonstrate how to test transformations using iceDQ. Our scenario involves validating data transformations between a source table and a target table. The source table contains three columns: "First Name," "Middle Name," and "Last Name." Meanwhile, the target table has a single column called "Name," which is the concatenated form of the three columns. Let's explore how to validate this transformation scenario.

Steps​

Here are the steps outlined in the video:

Create Rule​

  1. Start by creating a new validation rule. Provide an appropriate name for the rule.

Set Up Source Data Connection​

  1. Configure a source data connection to the source database. In this case, the source database is "Adventure Works," and the schema is "HR." The source table we'll be using is called "Employee Name." Preview the data to ensure it includes "First Name," "Middle Name," and "Last Name."

Set Up Target Data Connection​

  1. Configure a target data connection to the Adventure Works data warehouse. The schema for the target is "DBO," and the target table is "Dim Employee Name." Preview the data in the target table, particularly the concatenated column called "Name."

Define Join Condition​

  1. Set up the join condition, typically using the employee ID to connect source and target records.

Configure Transformation Check​

  1. Add a custom transformation check. Concatenate "First Name," "Middle Name," and "Last Name" from the source using the plus symbol and compare it with the "Name" column in the target. Take into account that the middle name might be missing, and if so, replace it with a blank character.

Save, Publish, and Execute​

  1. Save the transformation check configuration and proceed to publish and execute the rule.

Review Results​

  1. After execution, review the results. Pay attention to any warnings or records with issues. In this example, there were 10 records with problems. Investigate further by clicking on the instance ID. Analyze the differences between the source and target data. For example, some target records might be null, or the middle name may be missing in certain instances.

Video: How to Verify Transformation Logic using Concat Expression?​

Conclusion​

By following this use case, you have learned how to test data transformations using iceDQ. This process allows you to validate the accuracy of transformations between a source table and a target table, ensuring that the concatenated "Name" column aligns with the expected format. Identifying and addressing transformation issues enhances data quality and reliability in your projects.

Last updated on