​

How to Write and Deploy No-Code Test Cases

Collate supports data quality tests at the table and column level on all of the supported database connectors. Collate supports both business-oriented tests as well as data engineering tests. The data engineering tests are more on the technical side to ascertain a sanity check on the data. It ensures that your data meets the technical definition of the data assets, like the columns are not null, columns are unique, etc. There is no need to fill a YAML file or a JSON config file to set up data quality tests in Collate. You can simply select the options and add in the details right from the UI to set up test cases. To create a test in Collate:

• Navigate to the table you would like to create a test for. Click on the Data Observability tab.
• Click on Add Test to select a Table or Column level test.

​

Table Level Test

To create a Table Level Test enter the following details:

• Name: Add a name that best defines your test case.
• Test Type: Based on the test type, you will have further fields to define your test.
• Description: Describe the test case. Click on Submit to set up a test.

Collate currently supports the following table level test types:

1. Table Column Count to be Between: Define the Min. and Max.
2. Table Column Count to Equal: Define a number.
3. Table Column Name to Exist: Define a column name.
4. Table Column Names to Match Set: Add comma separated column names to match. You can also verify if the column names are in order.
5. Custom SQL Query: Define a SQL expression. Select a strategy if it should apply for Rows or for Count. Define a threshold to determine if the test passes or fails.
6. Table Row Count to be Between: Define the Min. and Max.
7. Table Row Count to Equal: Define a number.
8. Table Row Inserted Count to be Between: Define the Min. and Max. row count. This test will work for columns whose values are of the type Timestamp, Date, and Date Time field. Specify the range type in terms of Hour, Day, Month, or Year. Define the interval based on the range type selected.
9. Compare 2 Tables for Differences: Compare 2 tables for differences. Allows a user to check for integrity.
10. Table Data to Be Fresh: Validate the freshness of a table’s data (Collate).

​

Column Level Test

To create a Column Level Test enter the following details:

• Column: Select a column. On the right hand side, you can view some context about that column.
• Name: Add a name that best defines your test case.
• Test Type: Based on the test type, you will have further fields to define your test.
• Description: Describe the test case. Click on Submit to set up a test.

Collate currently supports the following column level test types:

1. Column Value Lengths to be Between: Define the Min. and Max.
2. Column Value Max. to be Between: Define the Min. and Max.
3. Column Value Mean to be Between: Define the Min. and Max.
4. Column Value Median to be Between: Define the Min. and Max.
5. Column Value Min. to be Between: Define the Min. and Max.
6. Column Values Missing Count: Define the number of missing values. You can also match all null and empty values as missing. You can also configure additional missing strings like N/A.
7. Column Values Sum to be Between: Define the Min. and Max.
8. Column Value Std Dev to be Between: Define the Min. and Max.
9. Column Values to be Between: Define the Min. and Max.
10. Column Values to be in Set: Check for the presence of allowed values. Enable matchEnum to require every value in the column to be allowed.
11. Column Values to be Not in Set: You can add an array of forbidden values.
12. Column Values to be Not Null
13. Column Values to be Unique
14. Column Values to Match Regex Pattern: Define the regular expression that the column entries should match.
15. Column Values to Not Match Regex: Define the regular expression that the column entries should not match.

​

Set a Failure Threshold (Optional)

Many tests let you choose how many failures to tolerate before Collate marks the test as failed. By default Collate tolerates none, so a single failing row fails the test. The Add Test case form shows the Failure Threshold and Threshold Unit fields for the tests listed in Tests That Apply a Failure Threshold. To set a failure threshold:

1. In Select Test Type, select a test, for example Column Values To Be Not Null.
2. In Failure Threshold, enter the number of failures to tolerate. The value can’t be negative.
3. In Threshold Unit, select how Collate measures the threshold:
   • rows: A count of failing rows. In the API, this is ABSOLUTE.
   • % of rows: A share of the rows evaluated. For row-level tests, the value can’t be higher than 100. In the API, this is PERCENTAGE.
   
4. Check the Preview. It restates your choice, for example “Fail when more than 0 row(s) in column fail this test.” A second line shows what Collate measures the test on, for example “Measured on the 100% sample this table is profiled with.”
5. Complete the rest of the form and select Create.

Keep Show Hint on to read the description of each field in the Form Hint panel. If you enter a value that isn’t valid, such as a negative threshold or a percentage above 100 on a row-level test, Collate rejects the test case with an error when you save it.

​

Tests That Apply a Failure Threshold

How Collate applies the threshold depends on the test:

• Row-level tests: Collate counts the rows that break the rule. The test fails when that count, or its share of the rows evaluated, is above the threshold. These tests are Column Values to be Not Null, Column Values to be Unique, Column Values to be in Set, Column Values to be Not in Set, Column Values to Match Regex Pattern, Column Values to Not Match Regex, Column Values to be Between, and Column Value Lengths to be Between. For example, with a threshold of 5 and % of rows selected, a Column Values to be Not Null test on 1,000 evaluated rows tolerates up to 50 null rows.
• Statistical and count tests: Collate compares one measured value with the range or the expected value you set. The threshold widens the accepted range, or the allowed difference from the expected value, so small deviations don’t fail the test. These tests are Column Value Max., Min., Mean, Median, and Std Dev to be Between, Column Values Sum to be Between, Column Values Missing Count, Table Row Count to be Between, Table Row Count to Equal, Table Column Count to be Between, Table Column Count to Equal, and Table Row Inserted Count to be Between. A percentage applies to the bound you set, not to rows, so it can be higher than 100, and the unit names adapt to the test. For example, a Table Row Count to be Between test with a Min. of 100, a Max. of 200, and a threshold of 10 as a percentage accepts row counts from 90 to 220.

Some tests behave differently:

• Column Values to be in Set: Collate applies a threshold only when Match enum is on. With Match enum off, the test passes as soon as one allowed value occurs, so no rows fail and Collate rejects a threshold above 0.
• Custom SQL Query: This test keeps its own Threshold and Operator. Its Threshold is the value that Collate compares the query result with, not a number of failures to tolerate.
• Table Column Name to Exist, Table Column Names to Match Set, and Compare 2 Tables for Differences: These tests don’t have the failure threshold fields.

If the Preview says that a test doesn’t apply a failure threshold yet, Collate still evaluates that test with no tolerance. The threshold means something different for each test, behaves differently on sampled tables, and applies per group in dimension-level tests. For more information, see Failure Thresholds. Once the test has been created, you can view the test suite. The test case will be displayed in the Data Quality tab. You can also edit the Display Name and Description for the test.

​

Set Up a Pipeline

Set up a pipeline to run the tests at a regular cadence.

1. On the Data Quality page, select the test suite and click the Pipelines tab. Alternatively, open the table, go to Data Observability > Data Quality, and click the Pipelines tab.
2. Click Add Pipeline.
3. Set up the scheduler for the desired frequency. The time zone is UTC.
4. Click Submit.

The pipeline runs at the scheduled time. The results update in the Data Quality tab.

​

Run a Test Case On Demand

You don’t have to wait for the schedule. To run your test cases right away, run the pipeline manually:

1. Open the table that has the test case and go to Data Observability > Data Quality.
2. Select the Pipelines tab and find the pipeline that runs your test case.
3. Click the ⋮ menu next to the pipeline and select Run.
4. Check the Recent Runs column for the pipeline to follow the result. The test case results update in the Data Quality tab.

A manual run doesn’t change the pipeline’s schedule.

​

Handle Test Failures

When a test fails, Collate creates an incident for it. If the test case has an owner, the incident is assigned to the owner automatically, so its status shows Assigned in the Incident column. If the test case has no owner, the status shows New. To change the status of an incident:

1. In the Incident column, click the status of the failed test, for example Assigned.
2. Select a status: Ack, Assigned, New, or Resolved.
3. If you select Resolved, choose a Reason and enter a Comment. Both are required. The reasons are Duplicates, False Positive, Missing Data, Other, and Out Of Bounds.
4. Click the check mark to save.

Set up alerts to be notified when a test fails.