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 can’t 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?
Let’s assume you have the following models in dbt:
-- 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-- models/my_model_b.sql
SELECT
2 as b,
1 as id,
2 as c,
'b' as string_b-- 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.idUnit tests are defined in (you’ve 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):
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 don’t yet
exist in the database):
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
---,2Unit tests can be a quicker way to iterate on model development in comparison to re-running the models, since you don’t 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:
pip install --upgrade dbt-materializeIt’s important to note that the latest dbt release included a hefty restructure
of its underlying package structure, so it’s possible that you run into errors
like ModuleNotFoundError: No module named 'dbt.adapters.factory' after
running the upgrade command. If that’s the case, try running:
pip install --force-reinstall dbt-adapters, and refer to dbt-core #10135
for additional troubleshooting and support!