Create a Standard Test

Prev Next

To create a standard test, open a folder in a project, choose New > New Standard Test, tell Validatar what data to test and what to compare it to, set the result criteria, then save and run it.

A standard test checks one thing: one table, one business rule, one source-to-target comparison. When the same check applies to many tables, build a template test instead.

Before you start

  • A project with the data sources you want to test added to it. See Create a Project and Create a Data Source.
  • The Create Tests permission on the project, and Create & Edit Tests on each data source the test reads. To run the test you also need Run Tests on those data sources. The data source list in a test shows only the sources that are in the project and that you can create tests against.
  • A query that returns the data you want to check. Try it in your own SQL tool first if you are unsure of it.

The example on this page

The Warehouse Reconciliation project checks that the Wholesale Distribution System (SQL Server) and its copy in the Enterprise Data Warehouse (Snowflake) agree. Nothing errors when a load quietly stops, so the team counts orders per month on both sides and compares them.

The test on this page is Wholesale Orders Reach the Warehouse by Month, in the folder Wholesale System to Warehouse. The warehouse copy of the orders table is the data under test. The wholesale system, where the orders were taken, is the control.

Start the test

  1. In the top navigation, click Projects and open Warehouse Reconciliation.
  2. Click Tests, then open the Wholesale System to Warehouse folder.
  3. Click the arrow on the New button and choose New Standard Test. Clicking New itself does the same thing.
  4. In Name, type Wholesale Orders Reach the Warehouse by Month.
  5. In Description, say what a failure means for the business, for example "A failure means orders taken in the wholesale system are missing from the warehouse for the months shown."
  6. In Quality Dimension, choose Completeness. See Quality Dimensions.
  7. In Severity Level, choose Critical. See Severity Levels.

The rest of the test is built on the Test Definition tab, which has three panels: What do you want to test?, What do you want to compare to? and Result Configuration.

Say what you want to test

The What do you want to test? panel is the test data set. For the full set of options, see Configuring Your Test Data Set.

  1. In Data Source, choose Enterprise Data Warehouse.
  2. In How to get the data, choose Script.
  3. Paste the query below into SQL Script.
  4. Click Preview & Initialize Options. Validatar runs the query (up to the number in Limit Records) and shows the result on the Preview tab.
  5. On the Data Set Options tab, set Process data as to Table.
  6. In the column grid, set the role of KEY to Key and the role of VALUE to Value.
-- Orders per calendar month in the warehouse copy, keyed by month as YYYY-MM.
-- Pass: every month has the same count as the wholesale system.
SELECT
    -- Dates landed as text, so the order date is read back as a date first.
    TO_CHAR(TO_DATE("OrderDate"), 'YYYY-MM') AS KEY,
    COUNT(*) AS VALUE
FROM LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS
GROUP BY 1

Process data as decides what Validatar does with the rows your query returns:

Option Offered when What gets compared
Single Value The query returns one value That value, for example a row count or a total
Table The query returns rows Each row, matched to the other side by its Key columns, with its Value columns compared
Column Aggregation The query returns rows One value computed from one column: Minimum, Maximum, Average, First, Last or Count Distinct
Row Count The query returns rows The number of rows the query returned

Row Count counts the rows that come back to Validatar, so for a large table, count in the database with COUNT(*) and use Single Value instead.

Say what to compare it to

The What do you want to compare to? panel is the control. Pick one of its three choices:

Choice Use it to Details
Another Data Set Compare with data from the same or another data source, such as source against target Compare Source and Target Data
Defined Standard or Rule Compare with a fixed value, a range, a string or pattern, or a date Comparing to a Defined Rule
Previous Runs Compare with this test's own earlier results or with earlier profile results Comparing to Previous Runs

For the example:

  1. Choose Another Data Set.
  2. In Data Source, choose Wholesale Distribution System.
  3. In How to define the control data, choose Script.
  4. Paste the query below into SQL Script.
  5. Click Preview & Initialize Options on the control panel.
  6. Set Process data as to Table, KEY to Key and VALUE to Value, the same as the test side.
-- Orders per calendar month in the wholesale system, keyed by month as YYYY-MM.
-- Pass: every month has the same count as the warehouse copy.
SELECT
    -- Style 120 writes a date as yyyy-mm-dd, so its first seven characters are the month.
    CONVERT(char(7), OrderDate, 120) AS "KEY",
    COUNT(*) AS "VALUE"
FROM Sales.Orders
GROUP BY CONVERT(char(7), OrderDate, 120)

When both sides run the same SQL against different data sources, tick Same as above on the control script instead of pasting it twice.

Set the result criteria

The Result Configuration panel decides what counts as a pass. Its rows change with the kind of data on each side. For every setting, see Configuring Result Criteria.

The example uses these settings:

Setting Value Why
Unmatched Keys: Test Only Fail A month in the warehouse that the system does not have is wrong
Unmatched Keys: Control Only Fail A month in the system that the warehouse does not have is missing data
Value Success Criteria Exact Match Only Order counts either match or they do not
Overall Success Criteria All Rows Must Pass One short month is enough to fail the test

Value Success Criteria compares each pair of values. Overall Success Criteria decides whether the whole test passes from the row results. The comparison methods work like this, where tolerance is the number or percent you enter:

Comparison method Passes when
Exact Match Only testNumericValue = controlNumericValue
Absolute Numeric Difference ABS(testNumericValue - controlNumericValue) <= tolerance
Percent of Test Value ABS(testNumericValue - controlNumericValue) * 100 / ABS(testNumericValue) <= tolerance
Percent of Control Value ABS(testNumericValue - controlNumericValue) * 100 / ABS(controlNumericValue) <= tolerance
All Rows Must Pass passedRowCount = totalRowCount
Up to X Rows Can Fail failedRowCount <= tolerance
Up to X Percent of Rows Can Fail (failedRowCount / totalRowCount) * 100 <= tolerance

Custom Calculation is also offered for both: you write the expression yourself.

Link the test to catalog objects

Metadata Links, under each data set's data source, connect the test to the tables and columns it checks. A link puts the test on that object's Tests tab in the Catalog, and a link marked with a filled star (Impacts Trust Score) counts the test toward that object's trust score. Click the star to switch it to Does Not Impact Trust Score.

  1. In Metadata Links on the test side, click Search for metadata and pick LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS.
  2. Do the same on the control side for Sales.Orders.

You can also let Validatar find them. When you save, it reads the table names in your SQL and, if the links differ, opens Suggested Metadata Links with links to add and links to remove. Tick the ones you want.

Save and run

  1. Click Save.
  2. Click Run Test.

Check that it worked

The result opens when the run finishes. For this test it fails, and the result says why. Every month from April 2024 on fails: the warehouse's last order was taken on 23 April 2024, so later months have orders in the system and none in the warehouse, and they show as Control Only rows. A few earlier months fail too, short by a few orders each, because a backorder keeps the date of the order it came from.

That is the point of the test: the load never errored, and the test caught it. To read a result in detail, see Run a Test and Read Its Results.

The test now appears in the folder, and on the Tests tab of each table it links to in the Catalog. To run it every night, add it to a job: see Create a Job.

Do it with Validatar AI (Validatar Cloud, beta). Open the AI panel and ask: "In the Warehouse Reconciliation project, folder Wholesale System to Warehouse, create a standard test that compares the number of orders per month in LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS on the Enterprise Data Warehouse with Sales.Orders on the Wholesale Distribution System, keyed by month as YYYY-MM, failing on any month that does not match." Review what it creates before you run it. You can undo the change from the chat history.

Troubleshooting

Symptom Cause Fix
The data source you need is not in Data Source It is not added to the project, or you lack Create & Edit Tests on it Ask a project admin to add the data source, or a data source admin to grant the permission
Process data as offers only Single Value The query returned a single value Return rows (for example add a GROUP BY) if you want a row-by-row comparison
Every row fails as Test Only and Control Only The keys differ in format between the two sides, for example 2024-04 against 2024-4 Make both queries return the key in the same format and type
No Run Test button The test has not been saved yet Click Save; the button appears
Run Test is greyed out You lack Run Tests on one of the test's data sources Ask a data source admin for the permission

For a test that runs but fails or errors in a way you did not expect, see Why Is My Test Failing or Erroring?.

Related