In business intelligence, big data is always transformed by ETL workflows with all kinds of transformation rules. How to verify the transformation rules are right or not on the ETL workflow, especially for the big data distributed in multi platform databases?
Apparently, we couldn’t do it with our naked eyes.
Toad for Data Analysts (following Toad as replaced) is a cross-platform query and data integration tool that simplifies data access, analysis, and provisioning for data management professionals. This data analysis tool provides nearly limitless data connectivity, desktop data integration, visual query building, and workflow automation.
Firstly, we need to connect to the test databases. Secondly, testers can create fake views based on the transformation rules as expected result. Of course, the views can share with others. Then we will start to do data comparison.
Following I would like to introduce how to create data comparison and analyze the compared results.
- Open the Data Compare Wizard. Right click on the source object or select it from Menu->Tool->Compare->Data compare
2. Specify the compared table or view and the key columns, fill in filters if needed, also check whether the filters are valid.
3. The comparison will be processed and the compared result will be displayed in detail.
The compare results can be exported as HTML/XML, that we can check the results conveniently. The total compare results, the information of different columns, missing columns, addition columns and conflict columns are included in this file.
Compared results in details:
- The compared results can be sorted by different status. For example: Different, Additional, Missing, Identical and Conflict.
- When we select any of the records, there are a cell viewer and a row viewer under compared result.
The row viewer could list the detail information for this column.
The cell viewer could list the detail information for this ID.
- Toad will highlight different columns if there are some different columns from source to target.
4. Save as file: Save our configuration data in a DCP (Data Compare Project File) file so that we can do regression test in the future.
Overall, Toad for Data Analysts is a tool with power function, especially it can support multi-platform databases and compared with key columns, but it is a commercial tool, and for those who want to make data comparison testing as part of their continuous build process, Toad couldn’t integrate it to your continuous integration build. So I will do more research on other tools especially for Open Source tools in the succeeding post of this series.
