--- title: "Create a Standard Test" slug: "create-a-standard-test" description: "Create a standard test: open a project folder, choose New > New Standard Test, define the data to test and what to compare it to, then save and run." status: "new" updated: 2026-11-20T21:54:26Z published: 2026-11-20T21:49:52Z canonical: "docs.validatar.com/create-a-standard-test" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.validatar.com/llms.txt > Use this file to discover all available pages before exploring further. # Create a Standard Test 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](/docs/create-a-template-test) instead. ## Before you start - A project with the data sources you want to test added to it. See [Create a Project](/docs/create-a-project) and [Create a Data Source](/docs/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](/docs/quality-dimensions). 7. In **Severity Level**, choose **Critical**. See [Severity Levels](/docs/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](/docs/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](/docs/comparing-to-another-data-set) | | **Defined Standard or Rule** | Compare with a fixed value, a range, a string or pattern, or a date | [Comparing to a Defined Rule](/docs/comparing-to-a-defined-rule) | | **Previous Runs** | Compare with this test's own earlier results or with earlier profile results | [Comparing to Previous Runs](/docs/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](/docs/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](/docs/understanding-trust-scores). 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](/docs/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](/docs/job-details). > **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?](/docs/why-is-my-test-failing). ## Related - [Configuring Your Test Data Set](/docs/configuring-your-test-data-set) - [Compare Source and Target Data](/docs/comparing-to-another-data-set) - [Comparing to a Defined Rule](/docs/comparing-to-a-defined-rule) - [Comparing to Previous Runs](/docs/comparing-to-previous-runs) - [Configuring Result Criteria](/docs/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](/docs/create-a-template-test). - Engineers who build tests from a coding tool can use the [MCP server](/docs/connecting-to-the-validatar-mcp-server) and the [Validatar Agent Kits](/docs/validatar-agent-kits). ## Related - [Compare Source and Target Data](/comparing-to-another-data-set.md) - [Configuring Result Criteria](/configuring-result-criteria.md) - [Run a Test and Read Its Results](/run-a-test-and-read-its-results.md) - [Create a Template Test](/create-a-template-test.md) - [Test Repository](/test-repository.md)