Contents x
    Configuring a Test
    • 28 Jul 2023
    • 6 Minutes to read
    • PDF
    Contents

    Configuring a Test

    • Updated on 28 Jul 2023
    • 6 Minutes to read
    • PDF
    Article Summary

    Overview

    Configuring a Validatar test is simple. Here's what you need to know:


    There are two types of test creation methods in Validatar:

    • Standard Tests
    • Template Tests

    In this article, you'll learn about creating a standard test that is ideal for ad-hoc, one-time use cases. View the related articles to learn more about Template Tests.

    When to Use a Standard Test

    Here are a few examples of when you would choose to create a standard test.

    • For ad hoc test scenarios
    • Business rules only apply to one or a few tables
    • All object names are known and/or won't change

    Data Sources

    A user must have Create & Edit Tests permissions enabled in Data Source Permissions to select a data source. You will only see the list of Data Sources you have access to.

    Test Data Types

    A test data type is exactly as it sounds: you are choosing the type of data you want to test. The options are:

    Single Value SQL
    (String or Numeric)    		Write a SQL statement that returns a single string or numeric value. For example,
    select count(*) from dbo.Customers;

    Key-Value List SQL
    (String or Numeric)    		Write a SQL statement that returns two columns. The first column is a key and the second column can either be a string or numeric value. A Key-Value List SQL script usually includes a Group By clause when the second column returned is an aggregate.
    select CustomerID, count(*) from dbo.Customers
group by CustomerID;

    Profile ResultIf you've executed a profile set against a table or column, you can use that profile result in a Validatar test. You have to link the appropriate table or column to the test using the Metadata Links section before you can select a profile result.
    If Create a link to the metadata that contains profile data remains after you've linked table or column, go to Profile Set Executions and verify that a profile was run against the appropriate table or column.


    Target Data Type

    Target Data Type is where you choose the type of data you want to compare the previously selected data to. The options include Single Value SQL, Key-Value List SQL, and Profile Result as well as a few new options:

    Numeric ValueCompare your data to a single numeric value without having to write a SQL statement. You would choose this option when you expect a specific number in your data and don't plan to change it.
    Numeric RangeSee if your data lies between a range of numbers. The range is inclusive.
    String ValueCompare your data to a single string value. To search for patterns, check the Regular Expressions checkbox. Learn more about Regular Expressions syntax here.
    Previous Profile Result

    Compare your data to a previous profile result. You have to link the appropriate table or column to the test using the Metadata Links section before you can select a previous profile result.

    You can compare to either the average, minimum, or maximum of previous results for a selective amount of days, weeks, or executions.

    If Create a link to the metadata that contains profile data remains after you've linked table or column, go to Profile Set Executions and verify that a profile was run against the appropriate table or column.

    The available options in the Target Data Type field depends on what is selected in the Test Data Type above.

    Test Data TypeAvailable Target Data Types
    Single Value SQL (string)Single Value SQL (string), Profile Result, Previous Profile Result, String Value
    Single Value SQL (numeric)Single Value SQL (numeric), Profile Result, Previous Profile Result, Numeric Value, Numeric Range
    Key-Value List SQL (string)Key-Value List SQL (string), Single Value SQL (string), String Value
    Key-Value List SQL (numeric)Key-Value List SQL (numeric), Single Value SQL (numeric), Numeric Value, Numeric Range
    Profile ResultProfile Result, Previous Profile Result, Numeric Value, Numeric Range, Single Value SQL (numeric) 
    Test Data Type Notes

    Starting in Validatar 2021.3, the Multi-Value SQL test data type is now referred to as Key-Value List SQL.

    Metadata Links allow profile results that have been run against a table or column to be used in a Validatar test. Linking metadata is not required but is highly recommended to

    • Use data profile results in test cases
    • Create a relationship between metadata and their related test cases
    • Discover how comprehensive testing is

    Start typing the name of a table or column to search for the object and select it. You can add multiple metadata links to a test.

    Metadata Link Search.png

    Result Configuration

    The Result Configuration section is used to tell Validatar what it should consider as a failed test. You can also select options to increase execution efficiency when running the test. The contents of the result configuration section depend on the combination of test and target data types selected in the previous sections.


    Configuration SettingDescription
    Missing Key SettingsOne benefit of using Validatar is to identify missing values in a data set. When that scenario happens, you're able to tell Validatar how that missing value should be evaluated. The comparison can either

    • Pass: You're okay with the missing value
    • Fail: You want to identify a missing value
    • Be overridden: You want to override the missing value with another value, then make the comparison.
    Value Success CriteriaFor string tests: Choose if the test should pass or fail based on how the source value compares to the target value. When a regular expression is used, the only comparison option is for the source value to match the target value.

    Result/Record Success Criteria
    For numeric tests: Compares the two pieces of data from the test together and calculate the difference. There are various comparison methods
    • Exact Match Only: The two numeric values must be exactly the same.
    • Absolute Numeric Difference: Takes the absolute value of the difference. ABS(Test Value - Control Value)
    • Percent of Test Value: Takes the absolute value of the percentage of the test value. ABS(Test Value - Control Value *100)/Test Value
    • Percent of Control Value: Takes the absolute value of the percentage of the control value. ABS(Test Value - Control Value *100)/Control Value 
    • Custom Calculation: This allows you to use test variables (test value, control value, and key-value) to write arithmetic formulas. Use logic commands like TRUE, FALSE, AND, and IF and operators like Greater Than and NOT Equal To. For more complex comparisons, use some of the embedded functions like LOG(), ROUND(), and SQUARE().
    You can also set a numeric or percent tolerance value depending on the selected comparison method. 
    Overall Success CriteriaFor Key-Value List SQL tests: Since Key-Value List SQL results return multiple records that have been compared, the overall success criteria option allows the user to decide how many failed records account for a failed test. There are multiple comparison methods
    • All Rows Must Pass: All rows must pass for the entire test to pass.
    • Up to X Rows Can Fail: There's a maximum number of tests that can fail before the entire test fails.
    • Up to X Percent of Rows Can Fail: There's a maximum percentage of total records that can fail before the entire test fails.
    • Custom Calculation: This allows you to use test variables (passed row count, failed row count, total row count) to write arithmetic formulas. Use logic commands like TRUE, FALSE, AND, and IF and operators like Greater Than and NOT Equal To. For more complex comparisons, use some of the embedded functions like LOG(), ROUND(), and SQUARE(). 
    Only Keep FailuresChoose Yes if you only want to return failed records. This keeps your results repository from being overloaded.
    Abort Processing After Failures
    		Allows Validatar to abort the execution of a test after a certain number of record failures.
    Abort Processing After RowsAllows Validatar to abort the execution of a test after a certain number of rows have been returned. 
    Scripts Include Order ByChoose Yes if your SQL script includes an order by clause. This lets Validatar know that it does not have to sort your data, optimizing execution time.
    Purge Results After DaysAllows Validatar to clear the results of this test from the Validatar database after a certain number of days. Configuring this option overrides the default purge settings set at the global level.

    Access

    1. Click on Tests in the navigation pane or from the project home page.
    2. Select the New button.
    Was this article helpful?
    What's Next