3.4.2.0 Data Validation Tests

Data conformity, uniqueness, structure, and relevance are everyday challenges users face when working with data. From ensuring data is stored in the correct format (such as email addresses or mobile phone numbers) to verifying that insured customer data is accurate and relevant (for example, a customer cannot be listed as holding a car insurance policy if they are under the legal driving age according to their recorded date of birth).

Financial and regulatory rules can be enforced and documented, while manual Excel file validations can be automated, saving significant time and effort.

The Data Validation test wizard is a powerful tool that enables any user to define critical business or technical rules on any Data Source through a user - friendly builder - no coding required.

Connect your raw data from any database, file, or supported Data Source, and apply any type of rule to ensure your data complies with critical requirements. You will be alerted when violations occur, and details of the failed data will be available for investigation and resolution.

Global rules can be applied by sharing your data rules in the library and reusing them across any relevant Data Source.

If our function library does not contain what you need, you can also develop your own custom functions and add them to the library using our JavaScript rule builder.

Please refer to the related detailed manual: Functions 

How to create a Data Validation Test

Step 1 - Connect your Data Set:

• First, create a new Test by clicking the "Add Test" button in the Tests' grid. For more please refer related detailed manual: Tests Intro 
• A Data Set may be private, for a single-use, or shared to the library to be reused and managed.
• If you already have a Shared Data Set => it should appear in the sidebar menu on the left in "Data Sets" area.
• To create a new Private Data Set for your Test in the "Data Sets" section, click "Add New".
• Click "+" icon and assign to your Data Set name, short description, choose needed Data Source and add relevant Tags if needed. You also can share it with the library by clicking "Add to the shared library" on the right side.
• Select the columns you'll use in your Data Set, change data types if needed and Validate the query to make sure that the Data Set query is correct, save it.

Step 2 - Create Test flow:

In the left menu switch to "Test Types" option and choose Data Validation: 2 new tiles will appear in the "Test Flow" area - Data Validation test type and Result:

Step 3 - Customize your Test:

Now, when the Test main flow is ready, customize your Test by adding the required rules:

Click the upper right circle in the Data Validation box - two options will be shown:

Edit(pencil icon)- allows to edit the Test and to add/edit/delete Rules.

Delete (trash icon) - allows to delete chosen test Type.

Click "Edit" option in order to proceed with the Rules:

Upon entering the Test's editing window, an automatic statistical table appears, even before running the Test itself.

 The statistical table includes information about each column:

• Number of unique values
• Percentage of unique values
• Number of NULL values
• Percentage of NULL values
• The minimum value
• The maximum value

Note: if there is an identical value for all the rows, the unique value will be 1/(total number of rows) and not 0 since the calculated formula is (number of different values)/(total number of rows).

If your Data Set is based on an RDBMS table => "SQL mode" check box will be checked by default:

In this case all the Rules will be created using SQL query format. Please see related detailed manual: Data Validation Tests - SQL Mode

Note: it's always preferable to use SQL mode because RDBMS Data Set(s) because it significantly improves performance!

For non RDBMS Data Set(s) only JavaScript mode is available. In this case all the Rules will be built using JavaScript functions. Please refer related detailed manual: Data Validation Tests - JavaScript mode

2 options to build a Rule are available:

1. Two basic Rules can be applied to the Data Set's columns automatically – Unique key enforcement by clicking the key icon or Not Null enforcement by clicking the check box:

The user can set the field(s) to be a Unique (Like a Primary Key) Or/And to be without null values.

For example:

To the column "id" is applied Unique rule and the column "first_name" will be checked for Null values by applying to it Not Null rule. Two rules ("Unique" and "first_name Null") will be created automaticly in the "Validation Rules" section:

2. By clicking "Add Rule", you will be able to build a simple formula that will return true or false. Depending on Data set type, the formula can be build by combining Data Set columns, Application library of functions, variables, calculated columns and operands. Fill in the following fields:

 

Step 5 - Analyze results

• You can define a threshold, relevant statuses (green - success, yellow - warning, red - fail) and ranges for each rule.

All The Rules that you defined, will appear at the bottom.

• Click Finish when all needed Rules are created.
• When you are back to the canvas flow, Run Test to get Test results.
• The results table will appear at the bottom of the canvas.