Unit testing with dbt

At Teads, our current BI architecture centers around utilizing dbt with BigQuery, exposed with Looker. This marks a substantial evolution from our prior setup, which utilized Spark with Parquet, pushed datamarts to Redshift, and exposed them in Looker.

I had the opportunity to migrate one of those Redshift + Spark pipelines to our new dbt + BQ stack. Consequently, I dived into the challenge of implementing unit tests with dbt.

Why write unit tests in dbt

At first glance, dbt comprises SQL queries housed in per-model folders. While not directly executable due to Jinja macros, these queries are just a dbt compile command away from becoming executable in the BigQuery console. Do we unit-test SQL queries? Not that often! So why would we unit-test dbt models?

From my perspective:

• the Spark pipeline I was phasing out had a robust unit testing framework. It would be a considerable regression not to uphold this practice in the context of dbt.
• in dbt, a SQL query is viewed as a transformation — a function within a pipeline. Functions, by convention, ought to be unit-tested.
• as a software developer in 2023, there is no way I don’t unit-test my code.

Let’s see how to write unit tests with dbt.

Unit testing with dbt_utils.equality

The preferred and well-documented method for unit testing in dbt is through the utilization of the package dbt-labs/dbt_utils. This is precisely what we have implemented at Teads thus far.

Writing a unit-test

It involves declaring a dbt_utils.equality in a schema.yml file alongside the respective model:

models:
  - name: demand_type_per_office
    tests:
      - dbt_utils.equality:
          tags: [ 'ci' ]
          compare_model: ref('demand_type_per_office_expected')

To mock your inputs when running your test, you need a macro:

macros/.../select_table.sql:

{% macro select_table(source_table, test_table) %}
    {% if (target.name in ['prod', 'sandbox']) %}
        {{ source_table }}
    {% else %}
        {{ test_table }}
    {% endif %}
{% endmacro %}

Then update the model to test by replacing your upstream inputs references leveraging the macro previously created:

models/.../demand_type_per_office.sql:

SELECT
    office_id,
    demand_type
FROM {{ select_table(source(production_log_table), ref(log_table_fixture)) }}
GROUP BY 1, 2

Ultimately, you’ll require both a fixture and an expected outcome. There are two methods to achieve this:

• using a seed (CSV file)

seeds/.../log_table_fixture.csv:

office_id,demand_type
7598,guaranteed
7598,guaranteed
10303,unguaranteed

• employing SQL model

models/.../ci/log_table_fixture.sql

{{
    config(
        materialized='table',
        tags = ["ci"],
    )
}}

SELECT 7598 as office_id, 'guaranteed' as demand_type UNION ALL
SELECT 7598 as office_id, 'guaranteed' as demand_type UNION ALL
SELECT 10303 as office_id, 'unguaranteed' as demand_type

Given the intricacies of the data structures in my tables, particularly those involving arrays, the CSV file approach is not compatible. Consequently, I’ve opted for the SQL method

Project structure

We tried different ways of organizing our project. One constraint to consider is that fixtures must be positioned upstream of the models in the DAG. Therefore, it is advisable to place fixtures alongside the models rather than in the tests folder. The tests folder is specifically reserved for ensuring the integrity of models and is consequently situated downstream.

So we end up with something like that:

Basically, create a “ci” folder which contains one folder per model you want to test. In each “ci/model” folder, add all your specific fixtures and your expected, together with the schema.yml declaring your unit test. Prefixing fixture names may be necessary if they are reused in other models. It’s crucial to keep fixtures minimal, focusing only on essential columns rather than recreating a complex table.

My feedback

• it’s only one fixture and one expected so that you need to unit-test every case in a single run.
• I had to write complex SQL fixtures because of arrays, json would have been much better.
• to optimize the tuning of production models, it is essential to utilize a macro. By overriding the source and reference macros to point to a standardized fixture name, one can effectively prevent the need for alterations in the production models.
• the expected model is not seen in the DAG and so a dbt run will not create it in my CI. I had to add a — depends_on: {{ ref(‘my_model’) }}. I would expect dbt to manage this automatically.

Despite having unit tests in place, I find the current implementation less ’than ideal.

Unit testing with dbt-unit-testing

Recognizing the community’s demand for an integrated unit testing framework, ongoing discussions have taken place here and here. Finally, dbt Labs has planned to incorporate unit testing in their roadmap: dbt 1.8 in spring 2024.

In the absence of this feature, a trial was conducted using an external package mentioned in the dbt discussions: dbt-unit-testing provided by EqualExperts. It leverages the “dbt test” command so you have to place your tests in the tests folder.

An example here:

tests/.../unit-test/demand_type_per_office_unit_test.sql:

{{ config(tags=['unit-test']) }}

{% call dbt_unit_testing.test ('demand_type_per_office','should group demand type per office') %}
    {% call dbt_unit_testing.mock_source('production_log_table') %}
SELECT 7598 as office_id, 'guaranteed' as demand_type UNION ALL
SELECT 7598 as office_id, 'guaranteed' as demand_type UNION ALL
SELECT 10303 as office_id, 'unguaranteed' as demand_type
    {% endcall %}

    {% call dbt_unit_testing.expect() %}
SELECT 7598 as office_id, 'guaranteed' as demand_type UNION ALL
SELECT 10303 as office_id, 'unguaranteed' as demand_type
    {% endcall %}

UNION ALL
-- another test

{% endcall %}

For your production models, an alternative reference and source macro is employed:

models/.../demand_type_per_office.sql:

SELECT
    office_id,
    demand_type
FROM {{ dbt_unit_testing.source(production_log_table) }}
GROUP BY 1, 2

Let’s have a look:

• the production model has been altered, but it no longer references the test fixture anymore. Moreover, we can override reference and source macros as explained here. In that case, no code change is required.
• the whole test is implemented in one model: fixtures, so the expected and declaration are together, and it is much easier to read.
• we can have several tests on the same model.

This is much better. However, the current limitation lies in its lack of support for complex structures such as ARRAY, STRUCT, or UDF, posing a hurdle for my specific use case.

What about macros

Leveraging macros within dbt proves to be immensely valuable in preventing unnecessary copy-pasting and enforcing consistent behavior across models. And because of that, it has to be unit-tested.

Despite the absence of macro unit tests at Teads currently (most macros were already in place when I joined), a dbt developer blog inspired me to explore this area.

There are three distinct uses of macros in our codebases:

• SQL code generation
• dbt command line variable capture
• functions using pure Jinja

Let’s dive into each of them.

Macros generating SQL code

It generates a string that is part of a more global string representing a SQL request. Let’s have an example:

{% macro compute_demand_type(id_demand_type) %}
    CASE
        WHEN {{ id_demand_type }} = 1 THEN 'guaranteed'
        ELSE 'unguaranteed'
    END
{% endmacro %}

Indeed, unit-testing the generated string might not provide meaningful insights, as the ultimate evaluation occurs within a SQL engine.

In that case, I would opt for an integration-test folder as described in this part of the blog. I would create a specific SQL model and test it with the unit test framework mentioned above.

Macros capturing dbt command line variable

At Teads, we are using a macro to capture the time we want to process for our incremental tables.

{% macro parse_hour(field_name = 'date', output_format = "'%Y-%m-%d %H:00:00'") -%}
  {% set d = var(field_name, default=past_hour_table_format()) %}
  {% set p = modules.datetime.datetime.strptime(d, '%Y%m%d%H') %}
  {% set o = modules.datetime.datetime.strftime(p, output_format) %}
  {{ return(o) }}
{%- endmacro %}

As outlined in dbt documentation, accessing variables with the var(...) function can only be set in the command line or in the dbt_project.yml. That’s not really test-friendly.

To address this, a potential approach involves rewriting the macro to isolate variable capture within one macro, which then calls a second macro to perform the actual job. In this scenario, testing would focus solely on the second macro. Alternatively, the integration-test folder, as suggested earlier, could be employed by adding variables with different cases to the dbt_project.yml and unit testing the macro.

Macros implementing pure Jinja functions

Pure Jinja functions are straightforward to test, as inputs can be passed and the output can be checked against an expected result.

{% macro convert_table_to_partition_format(partition_str) %}
  {% set date = modules.datetime.datetime.strptime(partition_str, "%Y%m%d") %}
  {{ return(date.strftime("%Y-%m-%d")) }}
{% endmacro %}

Writing a macro that tests a macro is easy:

{% macro test_convert_table_to_partition_format() %}
  {% set result = convert_table_to_partition_format('20231219') %}
  {% set expected = '2023-12-19' %}
  {% if result != expected %}
    {{ exceptions.raise_compiler_error('error when formatting date expecting ' + expected + ', got ' + result) }}
  {% endif %}
{% endmacro %}

But:

• macros cannot be tagged, making it challenging to identify which macros should be executed during the unit test step of your CI.
• macros can only be run using the dbt run-operation command, which requires an explicit macro name. This necessitates bundling all macros into a single extensive “execute_unit_test” macro for automation..
• it requires building a bit of tooling to standardize the assert behavior.

Conclusion

While writing unit tests in dbt is feasible and, in my opinion, necessary, the testing framework is in its early stages with several challenges. Dbt Labs is actively addressing these issues, and the community has proposed constructive ideas with dbt-unit-testing. There’s optimism that the testing framework will see significant improvements in the coming months.