Custom test output compiled sql which has something wrong

Help
1

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 (
    select
        {{ column_name }} as field_to_test
    from {{ model }}
),
validation_errors as (
    select
        field_to_test
    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
sources:
  - name: debug
    database: bq-hoge-project
    schema: bq-fuga-dataset
    tables:
      - name: hourly_check
        columns:
          - name:hoge_column
            tests:
              - 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

2

For starters, I think your test is defined incorrectly. It should be test instead of macro (details here: https://docs.getdbt.com/guides/best-practices/writing-custom-generic-tests)

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: https://github.com/calogica/dbt-expectations#expect_column_values_to_be_between

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

1 Like