Skip to main content

Use the Coalesce Test Stage Macro

Coalesce provides built-in data validation tests that can be configured via the Testing UI and metadata. However, sometimes more custom or complex tests are needed.

By leveraging the test_stage() built-in macro and SQL templates, you can create user-defined test nodes to validate data flows.

Test Stage Limitations​

The out-of-the-box testing functionality has some limitations:

  • Manual configuration of test logic required
  • Hard to reuse tests across nodes
  • Limited flexibility in SQL and controls

Creating Your Test​

You'll need to:

  • Author a macro with your test logic and name it test_<stg_name>. In the examples we use My Custom Test.
  • Invoke the Macro within a custom node’s Run Template.
  • Control failure handling behavior with a true/false clause in the stage initiation.

This tests for NULLs and does not continue the test on a failure and will fail the node execution. Using false is what will cause the failure.

{{ test_stage('My Custom Test', false) }} 

SELECT 1
FROM {{this}}
WHERE Col IS NULL
The results screen for the TEST_NATION node shows successful truncation of the stage table and insertion of data. A test failed, and the process halted because the Continue on Failure option was disabled. This indicates that the workflow stops immediately upon encountering a test failure.

If you set the conditional to true, the test will continue the node execution after the test failure.

{{ test_stage('My Custom Test', true) }} 

SELECT 1
FROM {{this}}
WHERE Col IS NULL
The results screen shows the execution steps for the TEST_NATION node, including truncating the stage table and inserting data into TEST_NATION. One of the tests failed, indicating that records matching the fail condition were returned.

What's Next​