Bulk Data Validation

You are viewing archived documentation for Data Trust v2024.06 (previous version).
Go to the latest v7.6 documentation →

Bulk Data Validation

This topic describes how to do bulk data validation from different tables at the same time. Validating data in bulk can be a challenging task when data is distributed over multiple tables in an organisation. Moreover if there are many tables and many rules that need to be applied, creating different scenarios for each table and going through the ordeal of applying different rules on each of them separately becomes exhausting. Our tool helps you to overcome this challenge in an easy way. The bulk data validation can be done in the following way.

1. Configure the data to be validated.
2. Execute the configured data with all the required validation rules.
3. Analyse the results.

Configuring Bulk Data Validation

Configuring bulk data means selecting your profile and the list of tables, queries or views that you want to validate together. You can then set the validation rules in the entire dataset at one go.

Steps to Select Data To be Validated

1. Login to your profile
2. Click on the tab named Scenario Studio
3. Click on the button titled Bulk Data Validation and you will get the following screen.

1. Click on the dropdown button option besides the label Bulk Validation. This will show dropdown menu with the available database profiles from which you can select the profile you want to validate.

1. Once you select the profile, you will get the following screen.

1. In this step you have two options Object Selection and Query Selection. You can follow anyone according to your requirement.

Object Selection Procedure

• This option is used to select the tables or views. Once you select this option you will see two text boxes labelled Database and Object Names.
• In the Database dropdown you have to select the database from which you want the tables.
• In the Object names you can provide a pattern or list that you want to obtain.

• You can even import data or list from a file. To get that option you need to click on the white page like button besides the pattern list text box and a pop up will appear as follows.

In the above option you can click the Import File button to import the list or manually add the table names in the option given.

• Another option is to directly select tables from the list that gets populated by pressing the Get Metadata button. The tables will appear on the right side as shown below.

• From the populated list you can then select the required tables to be validated and proceed with the validation.

Query Selection Procedure

• • • You can also opt for query selection option which will help you validate data of your stored queries. Once you click on the query selection option you will see the list of stored queries corresponding to the selected profile as follows:

• You can then select the queries you want to validate and add to metadata.

• Then click on proceed and the validation screen will appear

• We also have an option to select both tables and queries together to validate. In this case you can first select the table/query and then select the remaining one. Each time you just need to keep adding to metadata. Finally the metadata will contain your selected list of tables and queries to be validated.

Steps For Validation

1. Once the tables for validation are selected, you need to select the columns of the respective tables that you want to validate. You can do this by clicking on the plus icon beside the table name and the list of tables will get populated on the left side as follows.

1. After you have selected the columns, only those columns will appear on the screen and then you can set validation rules for them.

1. The different set of validation rules are :-
   • • • • Null Check
         • Duplicate Check
         • Zero Value Check
         • Numeric Type
         • Date Format Contamity
         • Regex
         • List Value Check
         • Minimum Value
         • Maximum Value
         • Condition Check
         • Row Count

The last two rules are used to validate data on tables as a whole and the others can be applied on individual columns.

Null Check

• In this rule we can ensure that there is no null value in the selected columns. Like in the following example, in the dbo.employees table the selected columns employeeid, lastname, firstname, birthdate and hiredate are being validated for null values. So, in this case if anyone of these values is null the resultant record fails for that row and is highlighted in the results.

• The text box next to the orange tick allows you to set a threshold for errors. We can set the threshold value in percentage or in absolute by clicking on that option.

In this example the threshold for last name is set to 1%, so if 1% of lastname data has null value the validation will pass. Similarly only 2 records of birthdate with null value will pass.

• Another feature is the combination button. The combination button is a toggle switch present on the top right, used when you want to check rows where all of the selected columns together should not have null values. Thus, in this case if any one value is null then that record does not fail.

So, in the above example records where all values i.e. employeeid, firstname, lastname, birthdate and hiredate are null fails, but records where only one of the values is null passes.

Duplicate Check

• Duplicate checks are used to eliminate duplicate values. The combination button can be used well in this case to ensure correct duplicate values are eliminated.

In the above example by using the combination button we ensure that only if a combination of employeeid, firstname and lastname together is duplicated the record is false.

• Zero Value Check

Zero value check is used to ensure certain fields do not contain zero as a value.

In the above example the records in unitprice, quantity and discount having zero value will be shown in the failure result. Here too we can use the threshold and the combination button if needed.

Numeric Type

This validation rule is used to ensure the column contains only numeric data.

In the above example you can ensure the customerid and orderid has only numeric value by selecting it.

Date Format Contamity

This is the standard validation rule where you can set the desired date format you want to follow. You can set the date format in the following way.

Step 1

Click on the parenthesis highlighted

Step 2

You will see the following pop up window

Step 3

You can then either select an option from the custom drop down or set it in your own format in the given text box.

Step 4

The query will then get updated automatically and the validation rule will be set.

Step 5

Click on the Apply button and the date format will be updated for that column

Regex

This is a customised validation check which you can set. In this validation you can put in a specific regular expression to meet your requirements. You can use this validation rule to validate data in two ways. You can either allow values that are matching the expression or values not matching the expression.

In the above example we have set the rule that any country name starting with capital A-Z or Small a-z can pass. Similarly we have also set the rule that shipping address not starting with A-D or F-I should pass.

List Value Check

In this rule you can specify a list of values that are valid for the column. You can specify the values directly or you can import it from a file. To set the values click on the parenthesis and the following screen will appear.

Once you specify the values click on Submit. Like in the following example the validation rule is that only data having categoryid -1,2,3 or 4 will pass others will fail.

Minimum Value

This validation rule helps you specify the minimum value required for a particular field. In the following example you can set the minimum value of the quantity field to be 100.

Maximum Value

This validation Rule helps you specify the maximum value for a particular field. In the following example the maximum discount is 50%.

Condition Check

In this validation check we can give any specific condition that we want to fulfil. This rule can be applied to a table as a whole. You can do this in the following way.

• Click on the parenthesis corresponding to the column where you want to set the condition. The following screen will appear.

Drag and drop the fields - column name in orange, available operators and input in the text box as follows. You can then select the operators and set the input value. You can even write the query directly in the expression box.

• In following example we are setting the condition that records that have discount value less than 50 should only pass.

Row Count

In this validation check we can specify the number of records we want to validate. In the following example the row count is set for individual tables and only those records will be validated. This rule can be applied on the table directly.

Executing Bulk Data Validation Scenario

Once you have completed configuring the scenario and setting the validation rules, you now need to execute it. The main advantage in this feature is you can set just any rule with simple steps and the entire dataset and validation rules set picture is available to you in one screen. This clear picture makes it very easy to set the rules or to analyse the result and make any changes if needed.

Steps For Execution

• • • • • Click on save button available on the top right of the toolbar and the following screen will appear.

Give the scenario a name and select the folder where you want to save it. Click on Save.

Once it is saved click on the execute button on the left of the save button. The entire scenario will be executed and the results shown.

Result Analysis

Once you complete setting the validation rules and executing the scenario the results appear as follows. You can select to view the result in two ways:

Summary View

This exception summary highlights the following points:

• • The red cross on top right implies that the scenario has failed overall
  • The summary on the left gives an overview of the entire process.
    • The status shows that in total 3 data sources have been validated. The result shows that none of the data sources passed.
    • The field status shows that a total of 15 fields have been validated. The result shows 9 out of the 15 fields failed.
    • The rules status shows 19 rules have been validated in total. The results show 12 out of the 19 rules failed.
  • The table on the right gives you the detail of table names and the fields that have been validated.
    • The rules column shows the number of rules applied. To get further details you can click on the’ I’ symbol. For example by clicking on the ‘I’ symbol next to the dbo.order_details table rule we get the following

The above pop-up box shows the details of the rule applied, its status and the description specifying the conditions applied.

• • • The passed column shows the number of rules passed for that table or field.
    • The failed column shows the number of rules failed for that table or field.

Detailed View

In the detailed view you can get the same screen as the design environment as follows:

This view contains all the data sources. You can filter out any particular table or fields for view from the filter option in the toolbar. For Example we can filter out the dbo.order_details table as follows:

In this view

• The grey boxes near the table name highlight the total number of records.
• The green boxes imply that all records for that rule have passed.
• The red boxes highlight the number of records that has failed for that rule.

If you click on any of the failed records you will get a clearer picture of exactly which record failed. In the above example by clicking on the red box highlighted for zero value check in discount we get the following details.

This view displays all the fields that we have selected for being displayed in the output during the design phase (This is done by clicking the eye icon next to the field name). Getting the complete data helps us understand where exactly the rule failed. For the above example by displaying the orderid and productid for zero value check rule on discount, you can understand where exactly there is a flaw.

You also have an option to download both the basic summary and the detailed summary as an excel document for future reference.