Create Data Unit Tests

Introduction

Data Unit Tests are blocks of SQL code that run at some point in a Transform (usually at the end), creating records in another table, which indicates if the test passed or failed. Lots of types of test are available, Kleene currently has three types built in - Null in field, Uniqueness and Freshness.

They are useful to reliably monitor the health of data in the pipeline.

The UNIT TEST option only appears above a Transform that creates or replaces a table once it has been saved.

Create the Unit Test log table

Before you create any tests, you'll need to have somewhere to store the results. You can do this the first time the UNIT TEST button is used, by selecting CREATE REQUIRED PROCEDURES in the bottom left hand corner, this will generate a table in your warehouse called UNIT_TEST_LOG to store the result.

eg KLEENE.UNIT_TEST_LOG where KLEENE is the schema name.

This table can then be queried to check the results of the tests.

Create a Data Unit Test

Click the UNIT TEST button to open the window, where three tests are available to add to the current transform.

Null in field

Checks none of the entires in a specified column have a NULL entry.

1. Select the table to test, then
2. Select the column to check and
3. Click ADD UNIT TEST.

Repeat this process for as many columns as needed.

Uniqueness

Checks the distinct number of entries in a specified field equals the table row count

1. Select the table to test, then
2. Select the column to check and
3. Click ADD UNIT TEST.

Repeat this process for as many columns as needed.

Freshness

Checks the maximum entry of a specified DATETIME or TIMESTAMP field is within a specified time interval

1. Select the table to test, then
2. Select the column to check (should be DATE/TIME), then
3. Select the period and time frame eg 3 hours
4. Click ADD UNIT TEST.

Repeat this process for as many columns or time periods as needed.

SQL Code

When you close the window, you'll notice one or more lines of SQL have been added to the end of the transform.

If you need to delete a test, simple remove the relevant line of SQL.

Feature Requests

To suggest a new feature go into Resource Center and add a suggestion.