Using macros in dbt where

[Kronossos]

Hi!

I have problems with using macro inside of dbt where.

I know that when you try to use one you will get error:

Encountered an error:
Compilation Error
  Invalid generic test configuration given in ../../projects/xxx/dbt/models/xxx/schema.yaml: 
  The model_name.column_name column's "not_null" test references an undefined macro in its where configuration argument. The macro 'country_specific' is undefined.
  Please note that the generic test configuration parser currently does not support using custom macros to populate configuration values
        @: UnparsedNodeUpdate(original_file_path='../.....ne)

But WHY?

I do not see any logical explanation of such a behaviour.

Why I want to use macro in where?

Imagine I have view like this:

ID	value	country_cd
1	0.56	ES
2	0.98	ES
3	0.11	IT
4	0.54	IT
5	0.12	ES
6	null	IT

I have some ML pipeline that modifies\uses only part of the data that is annotated with given country code (this is dbt variable like ('ES','TW')). At the end of this pipeline I want to test if part of the data I was using is errorfree so I want to test if value is not_null:

- name: value
        tests:
          - not_null:
              where: "{% if var('country_cd', None) %} country_cd in {{ var('country_cd') }} {% else %} 1=1 {% endif %}"

So when I run two pipelines, one for IT and second for ES, IT will fail because of null value, but ES will be okay.

And now imagine that I have multiple models and multiple columns that look like this:

Nice idea would be to just write simple macro to avoid copypaste code:

tests:
          - not_null:
              where: "{{ country_specific() }}"

{% macro country_specific() %}
    {% if var('country_cd', None) %} 
        country_cd in {{ var('country_cd') }} 
    {% else %} 
        1=1 
    {% endif %}
{% endmacro %}

But when you do this I get error:

Encountered an error:
Compilation Error
  Invalid generic test configuration given in ../../projects/xxx/dbt/models/xxx/schema.yaml: 
  The model_name.column_name column's "not_null" test references an undefined macro in its where configuration argument. The macro 'country_specific' is undefined.
  Please note that the generic test configuration parser currently does not support using custom macros to populate configuration values
        @: UnparsedNodeUpdate(original_file_path='../.....ne)

I also do not want to rewrite default tests just to use country_code:

{% test column_not_null_for_country(model, column_name) %}

select *
from {{ model }}
where

{% if var('country_cd', None) %}
    country_cd in {{ var('country_cd') }}
    and
{% endif %}

{{ column_name }} is null

{% endtest %}

Is there any reason not to use macros in where? Do u know sane workaround for this?

[jtcohen6]

@Kronossos Thanks for opening the discussion, and sorry for the delay getting back to you!

This is a limitation of dbt-core today, whereby the rendering context for configs defined in .yml files is generally restricted to things that dbt can know statically at the start of parse time: var(), env_var(), and static inputs. I also completely get the frustration with having the repeat the same conditional config over and over.

I just opened a separate discussion (#6280) to imagine a pattern that would enable users to distinguish between pure macros ("snippets" with static inputs/outputs), versus macros that modify the DAG, or require database state as an input. We don't have a first-class way to distinguish between those in dbt today, and they're really quite different.

Workaround

In the meantime, there is a workaround for this, specific to the where config on generic tests. Because the where config passes through another built-in macro, get_where_subquery, when it's templated out into the test query at runtime, you can actually override this macro and add your own custom logic.

Following your example:

version: 2
models:
  - name: your_model
    columns:
      - name: value
        tests:
          - not_null:
              where: "__country_filter__"  # placeholder: static string for config value

{% macro get_where_subquery(relation) -%}
    {% set where = config.get('where', '') %}
    {% if where == "__country_specific__" %}
        {% set where %}
            {% if var('country_cd', []) %}
                country_cd in (
                    {% for ccd in var('country_cd') %}
                        '{{ ccd }}' {{ ',' if not loop.last }}
                    {% endfor %}
                )
            {% else %}
                1=1
            {% endif %}
        {% endset %}
    {% endif %}
    {% if where %}
        {%- set filtered -%}
            (select * from {{ relation }} where {{ where }}) dbt_subquery
        {%- endset -%}
        {% do return(filtered) %}
    {%- else -%}
        {% do return(relation) %}
    {%- endif -%}
{%- endmacro %}

Voila:

$ dbt build --vars 'country_cd: [CA]'

-- compiled code, with some manual whitespace cleanup
select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
select value
from (select * from "jerco"."dbt_jcohen"."your_model" where 
                country_cd in (
                       'USA' ,
                        'CA' 
                )
        ) dbt_subquery
where value is null
    ) dbt_internal_test

Next steps

I've opened a PR to update the docs (dbt-labs/docs.getdbt.com#2418), in the hopes of both:

• making clear the present limitation, around custom macros in the rendering context for .yml file configuration
• showing the present workaround, based on get_where_subquery

Let's also keep the conversation going in the other linked discussion, toward a more general-purpose solution for static code reuse in .yml file config.