Skip to main content

Testing Nodes

You’ll need to create a SQL SELECT statement to test nodes. If the results match any of the data in the node’s columns, the test will fail. Let's go through a simple example to demonstrate.

Node tests can be run before or after the Node's transformations. You can set the run option in the Node test.

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.

    Example Node Test

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

    Example successful node test results

  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.

Test Utility Package​

Coalesce offers a Node testing package that allows you to deploy test Great Expectations like testing directly in Coalesce. Take a look at the Test Utility Package for more.

Viewing Column Level Tests​

After Create and Run you Nodes, you can see if the test passed or failed in the Results view.

The following image shows an example of a passing and failing test.

  • The Node level test Length is less than 0 is failing.
  • The column level test N_NATIONKEY: UNIQUE is passing.
  • The column level test N_REGIONKEY: UNIQUE is failing.
Data management interface showing STG_NATION table with fields N_NATIONKEY, N_NAME, and N_REGIONKEY. Test validation results include failures for 'Length is less than 0' and uniqueness tests for N_NATIONKEY and N_REGIONKEY. The interface displays nation data defined by TPC-H with options to validate, create and run tests.
info

You can also see column and Node level test failures as part of Refresh Results.