Custom test output compiled sql which has something wrong

The problem I’m having

Hi, I’m trying to use custom test.
I put this code under macros and name test_is_between.sql.

{% macro test_is_between(model, column_name, bottom_number, top_number) %}

with validation as (
        {{ column_name }} as field_to_test
    from {{ model }}
validation_errors as (
    from validation
    where field_to_test > {{ top_number }} or field_to_test < {{ bottom_number }}

select count(*)
from validation_errors

{% endmacro %}

Then I prepare yaml file like this.

version: 2
  - name: debug
    database: bq-hoge-project
    schema: bq-fuga-dataset
      - name: hourly_check
          - name:hoge_column
              - is_between:
                  bottom_number: -180
                  top_number: 180

Finally when I run dbt test, it output compiled sql. But the output file name is source_is_between_debug_hourly_09e34fb4230ff5d8c8d0a11364f72e47.sql.

What’s is going on ? I don’t think it is normal. How can I get compiled file correctly?

The context of why I’m trying to do this

I just want to check test results, after I run dbt test --store-failures.
But the file names has some problems so that I couldn’t get correct name of the table in audit dataset.

Thank you

For starters, I think your test is defined incorrectly. It should be test instead of macro (details here:

And, slightly related, the test you’re attempting to write already exists in the dbt-expectations package (along with _ma_ny other very useful tests). Check it out here:

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.