# Custom test output compiled sql which has something wrong

**URL:** <https://discourse.getdbt.com/t/custom-test-output-compiled-sql-which-has-something-wrong/6819>\
**Category:** Help\
**Tags:** testing\
**Created:** [February 16, 2023, 2:23pm UTC](https://discourse.getdbt.com/t/custom-test-output-compiled-sql-which-has-something-wrong/6819 "2023-02-16T14:23:56Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![kent0304](https://avatars.discourse-cdn.com/v4/letter/k/a5b964/32.png) [@kent0304](https://discourse.getdbt.com/u/kent0304)\
**Post date:** [February 16, 2023, 2:23pm UTC](https://discourse.getdbt.com/t/custom-test-output-compiled-sql-which-has-something-wrong/6819/1 "2023-02-16T14:23:56Z")

</div>

## 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`.

```auto
{% 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.

```auto
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

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [February 16, 2023, 2:35pm UTC](https://discourse.getdbt.com/t/custom-test-output-compiled-sql-which-has-something-wrong/6819/2 "2023-02-16T14:35:53Z")

</div>

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

Note: `@Owen` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1676558146060339?thread_ts=1676557465.572529&cid=CBSQTAPLG). It might not have transferred perfectly.