Changelog

dbt adapter: unit tests are here! 必

May 27, 2024

dbt has brought data work closer to software engineering practices, but one workflow has remained a challenge for SQL users: unit testing. While dbt data tests allow you to validate the quality of your data, they cant really help guarantee that changes to your logic will produce the results you expect when faced with specific input scenarios.

With the dbt-materialize v1.8.0 release, you can now use dbt unit tests to validate your SQL logic without fully materializing a model, and future-proof it against edge cases!

How does it work?

Lets assume you have the following models in dbt:

sql
-- models/my_model_a.sql
SELECT
  1 AS a,
  1 AS id,
  2 AS not_testing,
  'a' AS string_a,
  DATE '2020-01-02' AS date_a
sql
-- models/my_model_b.sql
SELECT
  2 as b,
  1 as id,
  2 as c,
  'b' as string_b
sql
-- models/my_model.sql
SELECT
  a+b AS c,
  CONCAT(string_a, string_b) AS string_c,
  not_testing,
  date_a
FROM {{ ref('my_model_a')}} my_model_a
JOIN {{ ref('my_model_b' )}} my_model_b
ON my_model_a.id = my_model_b.id

Unit tests are defined in (youve guessed it) a YAML file under the /models directory, and allow you to specify a set of input rows (given), and the corresponding expected output rows (expect):

yml
unit_tests:
  - name: test_my_model
    model: my_model
    given:
      - input: ref('my_model_a')
        rows:
          - {id: 1, a: 1}
      - input: ref('my_model_b')
        rows:
          - {id: 1, b: 2}
          - {id: 2, b: 2}
    expect:
      rows:
        - {c: 2}
important

Note: The tests property has been deprecated in favour of data_tests (to accomodate the new unit_tests property) and will be removed in a future dbt release. You should adapt your data tests to use the new nomenclature!

You can then run your unit tests using dbt test, or implicitly as part of dbt build (when the upstream dependencies of the model being tested dont yet exist in the database):

bash
dbt build --select "+my_model.sql"

11:53:30  Running with dbt=1.8.0
11:53:30  Registered adapter: materialize=1.8.0
...
11:53:33  2 of 12 START sql view model public.my_model_a ................................. [RUN]
11:53:34  2 of 12 OK created sql view model public.my_model_a ............................ [CREATE VIEW in 0.49s]
11:53:34  3 of 12 START sql view model public.my_model_b ................................. [RUN]
11:53:34  3 of 12 OK created sql view model public.my_model_b ............................ [CREATE VIEW in 0.45s]
...
11:53:35  11 of 12 START unit_test my_model::test_my_model ............................... [RUN]
11:53:36  11 of 12 FAIL 1 my_model::test_my_model ........................................ [FAIL 1 in 0.84s]
11:53:36  Failure in unit_test test_my_model (models/models/unit_tests.yml)
11:53:36

actual differs from expected:

@@ ,c
+++,3
---,2

Unit tests can be a quicker way to iterate on model development in comparison to re-running the models, since you dont need to wait for a model to hydrate before you can validate that it produces the expected results.

How can you upgrade?

To upgrade to the latest version of dbt-materialize, run:

bash
pip install --upgrade dbt-materialize

Its important to note that the latest dbt release included a hefty restructure of its underlying package structure, so its possible that you run into errors like ModuleNotFoundError: No module named 'dbt.adapters.factory' after running the upgrade command. If thats the case, try running:

bash
pip install --force-reinstall dbt-adapters

, and refer to dbt-core #10135 for additional troubleshooting and support!

Back to the Changelog

Try Materialize Free