Compare Flat File with Table using iceDQ | ETL Testing | Data Warehouse Testing
Learn about iceDQ, an integrated DataOps solution for Data Testing, Monitoring, and Observability.
For ETL Testing Concept - https://bit.ly/44Yd8L2
In this video, we explore the comparison between a flat file and a database table using reconciliation in iceDQ. Learn how to set up the reconciliation rule, preview and map data, handle data type discrepancies, and identify mismatches. Watch how we validate flat file "customer.csv" against the database table "Customer" in iceDQ projects and maintaining data integrity. Compare Flat File with Table to ensure accuracy and consistency in your data validation process.
Data quality is of utmost importance, as it directly impacts the reliability and effectiveness of any data-driven project. By employing iceDQ's data validation capabilities, you can gain confidence in your data and make informed decisions based on accurate information.
Whether you're a data analyst, business professional, or simply interested in data quality, this video provides valuable insights into conducting a data validation test using iceDQ. Join us on this journey to ensure the integrity of your data and optimize your decision-making processes.
Subscribe to our channel for more informative content, and hit the notification bell to stay updated with our latest uploads. Thank you for watching.
-------------------------------------------------
Website: https://bit.ly/44Yd8L2
LinkedIn: / icedq-toranainc
Facebook: / icedq.toranainc
Twitter: / icedq_toranainc
-------------------------------------------------
Chapters:
0:00 Introduction
0:26 Create a Reconciliation Rule
2:07 Select a Diff Join Condition
2:48 Adding Checks in the Rule
3:04 Fixing Data Type Discrepancies in the Checks
4:29 Execute Rule and Verify Results
-------------------------------------------------
Transcript:
Hello and welcome to today's video. Today, we will be discussing the comparison between a flat file and a database table using reconciliation in iceDQ. Let's begin by creating a reconciliation rule. First, select a folder and create a rule. Give the rule an appropriate name.
Now, let's set up our source data set. Choose the connection type as a “File”, Connection as “Flat File Native”. The file type as “Delimited”. Locate the desired file, in this case, "customer.csv". Next, let's preview the data. Our first row is the header row, and we want to skip the header row. We don't want to trim the data. Instead of null we are using blank space. These changes are done to imitate our ETL process behavior. Refresh the schema and data to view the file contents, which include customer information such as names, email IDs, phone numbers, and addresses.
Moving on, let's set up our target data connection. Choose the connection type as “Database”, Connection as “AdventureWorks2019”. Select the schema as “dbo” and table name as “Customer”. Now, let's preview the data in the target. We should have the same data as in the file, including names, phone numbers, email ID’s, addresses, and more.
Next, let's set up Diffjoin. The column names are the same in both the source and target. One point to note is that the data type for source is “Text” since our Source is a Flat File. Whereas we have an appropriate data type in Target since our target is a database. Now, select “Auto Map by Column Name” to Map the columns. Let’s Pin our Primary Key “CustomerID” And add rest of all the columns as our checks.
Let's take a look at the checks that have been created. Each column from the source is now part of the checks. However, there is a discrepancy in the data types between the source and the target as we can see in Diffjoin. For example, customertype has datatype as text in source since it is populated from flat file whereas it has datatype as tinyint in target since it is populated from database. The same issue is present for columns Date Of Birth, Zip Code and Modified Date. To resolve this, we can use the "tostring" function to convert the data types of the necessary columns. Let’s update the check for Date Of birth.
Adding tostring. Update the Check. Let’s update the check for Zip Code. Adding tostring. Update the Check. Let’s update the check for Modified Date. Adding tostring. Update the Check.
Let's publish and run the rule. Refresh the Runs. We can see that out of 100 records in the source and target, we are getting mismatches for all 100 records. Let’s Investigate further by clicking on the instance ID. We can see the modified date is the culprit. This discrepancy is due to format issues between the source and target.
Continuing the investigation, we observe that customer type, title, and first name don't have any issues. However, middle names are replaced by the character 'A'.Last names and email addresses are fine. Phone numbers also have some mismatches. Few Phone numbers have value 1234567890. The date of birth column has similar issues.