Add Custom SQL Tests

Add a Custom Condition, SQL Metric, or Custom Test when you can express a business rule in SQL and no built-in test type checks it. To choose between the three, see Custom Tests.

Prerequisites

• A test suite for the table group that holds the data. See Manage Test Suites.
• Permission to manage test definitions. See User Access.
• Familiarity with the SQL dialect of the target database. TestGen does not translate your SQL to another dialect.

Add a custom SQL test

Custom ConditionSQL MetricCustom Test

A Custom Condition checks every record of one table against an expression that is true when the record breaks your rule. The test fails when more records break the rule than the threshold allows.

1. Select Test Suites from the menu.
2. On the test suite, click View N test definitions .
3. Click Add.
4. Select Custom Condition.
5. On the Parameters tab, enter the Table to evaluate.
6. Optional. Enter a Test Focus, a short name that distinguishes this test from other Custom Condition tests on the same table, such as Ship Date After Order Date.
7. Optional. Change the Threshold Failure Count, the number of records that can break the rule without failing the test. The default is 0.

8. In Custom SQL Expression (TRUE on failure), enter an expression that is true when a record breaks the rule. Refer to the table's columns without a schema or table name.

   For example, these expressions flag a shipment dated before its order, and a shipment that is larger than the order or has no quantity.

   ship_date < order_date
   

   quantity_shipped > quantity_ordered OR quantity_shipped IS NULL
   

   A record for which the expression evaluates to NULL is not counted as breaking the rule. In the first example, a record with no ship_date passes. To count missing values as breaking the rule, test for them explicitly, as the second example does.

9. Click Validate. TestGen runs the expression against the table and shows Validation is successful. or the error message from the database.

10. Click Add.

The test runs with the next run of the test suite. Source Data on a result shows sample records for which the expression is true.

A SQL Metric runs a query that returns one number, and checks that the number falls within a minimum, a maximum, or both.

1. Select Test Suites from the menu.
2. On the test suite, click View N test definitions .
3. Click Add.
4. Select SQL Metric.
5. On the Parameters tab, select the Table the metric is about.

6. In Metric Query, enter a query that returns exactly one row with one named numeric column. The query can read any table the connection can access. Write the schema of each table into the query, or use {DATA_SCHEMA}, which TestGen replaces with the schema of the table group.

   For example, this query counts the orders that have not shipped a week after they were placed.

   SELECT COUNT(*) AS late_orders
   FROM {DATA_SCHEMA}.orders
   WHERE shipped_date IS NULL
     AND order_date < CURRENT_DATE - 7
   

   TestGen runs your query inside another query, so the query must be valid as a subquery. Some databases restrict what a subquery can contain. For example, SQL Server does not accept ORDER BY without TOP, or a WITH clause, in a subquery. TestGen removes a trailing semicolon.

7. Enter a Minimum Value, a Maximum Value, or both. Leave a bound blank to check only the other one.

8. Click Validate. TestGen runs the query the same way a test run does, and shows Validation is successful. or the error message from the database.
9. Click Add.

The test runs with the next run of the test suite. It fails when the value is below the minimum or above the maximum. A query that returns more than one row or column, a value that is not numeric, or no value at all gives the test the Error status instead.

Source Data on a result shows the value the query returns now, next to the bounds.

A Custom Test runs a query that returns the records that break your rule. The test fails when the query returns more rows than the threshold allows.

1. Select Test Suites from the menu.
2. On the test suite, click View N test definitions .
3. Click Add.
4. Select Custom Test.
5. Optional. On the Parameters tab, enter the Table the rule is about.
6. Optional. Enter a Test Focus, a short name that distinguishes this test from other Custom Tests on the same table, such as Order Total Matches Line Items.

7. In Custom SQL Query Returning Failing Records, enter a query that returns one row for each record or group that breaks the rule, and no rows when the data is correct. The query can read any table the connection can access. Write the schema of each table into the query, or use {DATA_SCHEMA}, which TestGen replaces with the schema of the table group.

   A row-level rule returns each record that breaks it. For example, this query finds line items that have no matching order.

   SELECT i.order_id, i.item_number, i.product_id
   FROM {DATA_SCHEMA}.order_items i
   LEFT JOIN {DATA_SCHEMA}.orders o ON o.order_id = i.order_id
   WHERE o.order_id IS NULL
   

   An aggregate rule returns each group that breaks it. For example, this query finds orders whose total does not match the sum of their line items.

   SELECT o.order_id, o.order_total, SUM(i.item_amount) AS items_total
   FROM {DATA_SCHEMA}.orders o
   JOIN {DATA_SCHEMA}.order_items i ON i.order_id = o.order_id
   GROUP BY o.order_id, o.order_total
   HAVING o.order_total <> SUM(i.item_amount)
   

   Return the identifiers and the values that broke the rule, not only a key. Source Data shows these columns when the test fails.

   TestGen counts the rows by running your query inside another query, so the query must be valid as a subquery. Some databases restrict what a subquery can contain. For example, SQL Server does not accept ORDER BY without TOP, or a WITH clause, in a subquery. TestGen removes a trailing semicolon.

8. Optional. In PII Redactable Columns, enter a comma-separated list of the query's result columns that hold PII. Enterprise TestGen masks these values in Source Data for users whose role cannot view PII data. See User Access.

9. Optional. Change the Threshold Failure Count, the number of rows the query can return without failing the test. The default is 0.
10. Click Validate. TestGen runs the query the same way a test run does, and shows Validation is successful. or the error message from the database.
11. Click Add.

The test runs with the next run of the test suite. Source Data on a result runs the query again and shows up to 500 of the rows it returns.

Next steps

• Run Tests to run the test suite with your custom tests
• Investigate Test Results to review failures and their source data