Calling DBT tests like a macro (plus unit tests for data tests)

Help
, ,
1

Note: there is an existing thread here, but it has no resolution other than using dbt build (and it is now closed).

Solution

Edit: The solution is very simple… just scroll to the bottom to see it.

The problem I’m having

We would like to call our tests in the exact same way we can call macros today.

There is a workaround, but it would be so much simpler without a workaround.

The context of why I’m trying to do this

For many reasons.

One of the main ones would be to test our tests (i.e. create “unit tests” for our tests, or test the SQL that they generate). And sometimes we would like to use the generated SQL code in contexts other than dbt tests, for example in the context of multi-step models (models which execute multiple queries against the same table), and we would like to execute some tests in between those steps. Breaking the model up into multiple separate models is not a great workaround as it becomes more challenging to control the order of execution in combination with downstream dependencies which depend on the completion of the entire process (but only need/reference that one table), which adds unnecessary overhead.

Unfortunately, right now it doesn’t seem possible. So we must test by hand using the target folder (or query history), which is awful. We have many complex tests with large sets of parameters, and making changes to them can get very tricky without any kind of automated testing. There are too many possible combinations of parameters to possibly test everything by hand every time.

What I’ve already tried

Calling a test like a macro simply results in this error: 'test' is undefined. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with "dbt deps".

Some example code or error messages

{{ test-something(model, 'column', param=value, ...) }}
{{ something(model, 'column', param=value, ...) }}

Possible Workaround

Defining all of our tests as actual macros, and then wrapping the actual tests around those macro (like a proxy/facade).

But that’s pretty repetitive as it would require us to repeat the entire definition twice (so that we can call the macro in the exact same way that we can call/configure the test), and then also repeat all of the parameters again for the proxy call.

Something like this:

{% macro _test_something(model, column_name, long="", list=[], of=True, params=[], please="help", DRY=False, ...) %}
SELECT 'the test is actually defined here';
{% endmacro %}

{% test something(model, column_name, long="", list=[], of=True, params=[], please="help", DRY=False, ...) %}
  {{ _test_something(model, column_name, long, list, of, params, please, DRY, ...) }}
{% endtest %}

Note that the macro cannot be called test_something (hence the leading underscore), as that would interfere with the name of the test:

Compilation Error
  dbt found two macros named "test_something" in the project "wow".
   To fix this error, rename or remove one of the following macros:
      - macros/tests/test-something.sql
      - macros/tests/test-something.sql
2

Well… nevermind!

We can actually call tests as macros already!

The key is to prefix the name of the test with test_ (with an underscore), like {{ test_unique(...) }}.

:man_facepalming:

3

Bonus: this is really, really great to unit test data tests! :raised_hands:

Here’s a extremely simplified example. You just need to create a dummy model/table, and then:

INSERT OVERWRITE INTO {{ this }}
(VALUES (1), (2)) AS t (id);

WITH test AS (
  {{ test_unique(this, 'id') }}
)
SELECT COUNT(*) AS c
FROM test
HAVING c != 0
  AND 'UNIT TEST FAILED: Expected no duplicates to be found, but some where found.';

INSERT OVERWRITE INTO {{ this }}
(VALUES (1), (1)) AS t (id);

WITH test AS (
  {{ test_unique(this, 'id') }}
)
SELECT COUNT(*) AS c
FROM test
HAVING c != 1
  AND 'UNIT TEST FAILED: Expected one duplicates to be found, but ' || c || ' where found.';

It’s even possible to test data tests that should produce errors by leveraging Snowflake scripting to catch the expected errors:

DECLARE
  test_failed EXCEPTION(-20001, 'TEST IS SUPPOSED TO FAIL!');
BEGIN
  {{ test_unique(this, 'column_that_does_not_exist') }};
      
  RAISE test_failed;
EXCEPTION
  WHEN statement_error THEN
    RETURN 'TEST FAILED AS EXPECTED';
END;