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
- In the top navigation, click Projects and open Warehouse Reconciliation.
- Click Tests, then open the Wholesale System to Warehouse folder.
- Click the arrow on the New button and choose New Standard Test. Clicking New itself does the same thing.
- In Name, type
Wholesale Orders Reach the Warehouse by Month. - 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."
- In Quality Dimension, choose Completeness. See Quality Dimensions.
- 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.
- In Data Source, choose Enterprise Data Warehouse.
- In How to get the data, choose Script.
- Paste the query below into SQL Script.
- Click Preview & Initialize Options. Validatar runs the query (up to the number in Limit Records) and shows the result on the Preview tab.
- On the Data Set Options tab, set Process data as to Table.
- In the column grid, set the role of
KEYto Key and the role ofVALUEto 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:
- Choose Another Data Set.
- In Data Source, choose Wholesale Distribution System.
- In How to define the control data, choose Script.
- Paste the query below into SQL Script.
- Click Preview & Initialize Options on the control panel.
- Set Process data as to Table,
KEYto Key andVALUEto 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.
- In Metadata Links on the test side, click Search for metadata and pick
LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS. - 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
- Click Save.
- 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
- Configuring Your Test Data Set
- Compare Source and Target Data
- Comparing to a Defined Rule
- Comparing to Previous Runs
- Configuring Result Criteria
- A standard test that works can become a template: click Convert to Template Test on the test. See Create a Template Test.
- Engineers who build tests from a coding tool can use the MCP server and the Validatar Agent Kits.