Conditionally running dbt tests / Only running dbt tests in production

Archive
3

Great question! We can actually get dbt to do our bidding here in a slightly different way that doesn’t involve tags.

Prior knowledge for understanding why this works:

  1. If we create a local version of a macro in our own project, it overrides the default macro in dbt.
  2. Tests are implemented as macros – the code for the not_null test is here.
  3. Tests can take arguments
  4. Tests pass when you return a 0
  5. We can figure out which environment we’re in using the jinja object {{ target.name }}, assuming you have named your targets sensibly!

So, putting that all together, if you want to run, say, the not_null test only when in prod, you can add a file to your project in the macros directory: macros/test_not_null.sql that looks like this:

{% macro test_not_null(model) %}

  {% set column_name = kwargs.get('column_name', kwargs.get('arg')) %}

  {% set test_in_env = kwargs.get('env') %}
  
  {#-
  We should run this test when:
  * the environment has not been specified OR,
  * we are in the specified environment
  -#}
  {%- if test_in_env is none or target.name == test_in_env -%}

    select count(*)
    from {{ model }}
    where {{ column_name }} is null

  {#- 
  We should skip this test otherwise, which we do by returning 0
  -#}
  {%- else -%}

    select 0

  {%- endif -%}

{% endmacro %}

Then in our schema.yml files, we can specify environments by using our env key:

models:
  - name: orders
    columns:
        - name: order_id
          tests:
            # this test will run in all environments
            - not_null

        - name: customer_id
          tests:
            # this test will only run when target.name == prod
            - not_null:
                env: prod

This pattern can be extended to take a list of environments if required!

Credit to @michael-kaminsky for this pattern! I based it off a custom test he had written :slight_smile:

4 Likes