Data Quality Testing

Coalesce provides built-in testing capabilities to assess the quality of your data.

Node Tests

These tests consist of a user-provided SQL SELECT statement, which, if matched by the data in the node's columns, will fail the test. Let's go through a simple example with Snowflake sample data to demonstrate.

Example Node Test

The column C_NATIONKEY is an integer that corresponds to a specific country, and we know that this number must be positive. No countries in the data set should have a negative number, so we will test for this.

1. Click on Testing
2. Click on New Test in the Node section to create a new test
3. In the text box, type in SELECT * FROM {{ ref_no_link('TARGET','STG_CUSTOMER')}} WHERE C_NATIONKEY < 0
   but make sure to replace TARGET with the name of your storage mapping
4. Uncheck the Continue on Failure toggle

5. Run the node, it should succeed. You can confirm by opening the Results pane and seeing the different stages.

6. Try reversing the < to >, which will match all of our records (nation keys are all positive integers). This will cause the test to fail and the node's data will not be processed.

📘

Before or After?

Node tests can be run before or after the node's transformations, and this is user-configurable.

Column Tests

Column tests are meant to check for null and unique values, and are always run after transformations. This cannot be changed unless using a UDN.

Example Column Test

1. Delete the node test(s) you made earlier
2. Click on Testing → Column
3. Select both Unique and Null

4. Now click on Null next to C_NATIONKEY. This will check to ensure all entries for this column are populated (not null).
5. Run the node and it should succeed, as there's no null entries.

6. Now try also selecting Unique for the C_NATIONKEY column. This will check to ensure all entries for this column are unique.
7. Run the node and it will fail, as C_NATIONKEY has repeated entries. Notice that the data was still processed, as column tests will always continue on failure.