Test Reference Data using Reconciliation Rule in 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/42XqIwR
In this video, we test the reference data using iceDQ to create a reconciliation statement. We compare the 'name' between the transactional table and the master reference data to identify discrepancies. The verification process ensures the accuracy and consistency of the reference data. We also set up a reconciliation rule, establish the source connection, and preview the data for a comprehensive reconciliation statement.
iceDQ provides a multitude of data validation capabilities to meet your specific requirements.
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.
Don't forget to like this video, 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/42XqIwR
LinkedIn: / icedq-toranainc
Facebook: / icedq.toranainc
Twitter: / icedq_toranainc
-------------------------------------------------
Chapters:
0:00 Introduction
0:20 Creating Reconciliation Rule in iceDQ
1:12 Setting the Join Conditions
1:57 Execute Rule and Verify Results
-------------------------------------------------
Transcript:
Hello everyone, in today's video, we will be testing whether the reference data generated by our ETL process in the database matches the Master reference data present in the reference database.
Let’s start by creating a reconciliation rule that allows us to compare the two sets of data. Let's name our role appropriately.
We begin by setting up the source connection. We select the source schema and the table “Phone Number Type". Let's preview the data. We have three types of phone numbers: cell phone, home phone, and work phone. Our goal is to verify that these phone types in the source database match those in our reference database.
Next, we set up the target database ReferenceDB. We select the table “Phone Number Type”. Since the column names in the source and target are the same, we can directly connect them. We will use the phone number type ID as the primary key for the join condition, and we will add the remaining two columns, name and modified date, as our checks.
With the checks added, we are comparing the name from our source data to the name in our reference data, and we are also comparing the modified date from our source to the modified date in our reference data.
Lets Publish and execute the rule. After refreshing the results, we find that we are getting a warning. To investigate further, we click on the instance ID. We can see that there are no issues with the name comparison. However, we have three mismatches in the modified date. This occurs because the modified date in our source data is different from the modified date in our reference data.
In summary, we have successfully verified that the names present in our reference data match the names generated by our ETL process. We accomplished this by creating a reconciliation rule and comparing the relevant columns between the source and target databases. This verification process helps ensure the accuracy and consistency of our reference data.