[Feature] Support ref + custom macros in test config rendering context

[glenn-wawo]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

I have a test within a model that has the following syntax:

name: column name
description: description
tests:
 - not_null:
     config:
       where: "field < (SELECT max(field) FROM {{ ref('model') }} )"

When you run the test, the model reference within the subquery in the config is not translated into an actual table and the compiled SQL subquery in the test config looks like:

select *
from (
select 
  *   
from 
  schema.table where field < (
     SELECT max(field)FROM ref('model')
 ) dbt_subquery
where 
  column_name is null

Expected Behavior

expecting dbt to recognise the model reference in the test config & the subquery to be compiled into

select *
from (
select 
  *   
from 
  schema.table where field < (
     SELECT max(field)FROM schema.model
 ) dbt_subquery
where 
  column_name is null

Steps To Reproduce

• Install dbt 0.21.0 (I have also tried with dbt 0.20.1 and 0.20.2, with the same issues)
• configure the test as described above using out of the box test from dbt
• Run dbt test
• See error

Relevant log output

Invalid test config given in models/core/some_model.yml:
        'ref' is undefined
        @: UnparsedNodeUpdate(original_file_path='model...ne)

Environment

• OS: MacOS Big Sur
• Python: 3.8.6
• dbt: 0.21.0

What database are you using dbt with?

snowflake

Additional Context

This test setup worked when we use dbt_utils.not_null_where functionality before it was deprecated with dbt 0.20

[jtcohen6]

@glenn-wawo Unfortunately, I think you will need to use a custom generic to achieve this behavior.

The issue is that test configs:

• need to be rendered + stored at parse time (like all configs), so that inheritance and selection can do their magic
• have to use a more-limited rendering context, relative to test arguments (which are only needed at runtime)

With the flexibility of supporting where as an out-of-the-box config for every generic test, there is also a reduction in its functional possibilities. So I think you'd be best served by defining a new custom generic test, with a test argument, custom_where, that cannot be set in dbt_project.yml, but which can include ref() and custom macros:

{% test not_null_custom_where(model, column_name, custom_where) %}

  select *
  from {{ model }}
  where {{ column_name }} is null
    and {{ custom_where }}

{% endtest %}

name: column_name
description: description
tests:
 - not_null_custom_where:
     # not a config, an argument!
     custom_where: "field < (SELECT max(field) FROM {{ ref('model') }} )"

This is the same issue reported in #3580 (comment), and I do take the reduction in functionality seriously. There are a few ways we could think about supporting this:

• Take an approach similar to the "nested-curlies" for hooks. The where config would be rendered at parse time, just to capture ref/source/config; and then re-rendered at execution time, with the full context then available. This requires pretty ugly syntax, and it's quite confusing for users, so it doesn't feel like a solid foundation on which to expand.
• Add ref, source, and custom macros to the rendering context for test configs—that is, to the parsing context used for all .yml files. This is something I eventually want to do! I don't think it's doable today, however; we don't have the right guardrails in place to do it right, and I think there would be serious performance implications if we were to try to brute-force it. (cc @gshank for visibility)

[glenn-wawo]

thanks @jtcohen6. I actually did the same workaround solution that you suggested :)

Good to know where you guys stand on this issue currently. I'll be watching the space in dbt's future update

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest; add a comment to notify the maintainers.