Day 6: dbt test¶
From Tests:
Tests are assertions you make about your models and other resources in your dbt project (e.g. sources, seeds and snapshots). When you run
dbt test, dbt will tell you if each test in your project passes or fails.tests are SQL queries.
select statements that seek to grab "failing" records
Examples of asserts:
- a column is unique in a model
- a column is never null
dbt test¶
$ dbt test
Running with dbt=1.1.1
Found 2 models, 4 tests, 0 snapshots, 0 analyses, 199 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
Concurrency: 1 threads (target='dev')
1 of 4 START test not_null_my_first_dbt_model_id ............................... [RUN]
1 of 4 FAIL 1 not_null_my_first_dbt_model_id ................................... [FAIL 1 in 0.72s]
2 of 4 START test not_null_my_second_dbt_model_id .............................. [RUN]
2 of 4 PASS not_null_my_second_dbt_model_id .................................... [PASS in 0.31s]
3 of 4 START test unique_my_first_dbt_model_id ................................. [RUN]
3 of 4 PASS unique_my_first_dbt_model_id ....................................... [PASS in 0.54s]
4 of 4 START test unique_my_second_dbt_model_id ................................ [RUN]
4 of 4 PASS unique_my_second_dbt_model_id ...................................... [PASS in 0.37s]
Finished running 4 tests in 2.28s.
Completed with 1 error and 0 warnings:
Failure in test not_null_my_first_dbt_model_id (models/example/schema.yml)
Got 1 result, configured to fail if != 0
compiled SQL at target/compiled/hello_dbt_spark/models/example/schema.yml/not_null_my_first_dbt_model_id.sql
Done. PASS=3 WARN=0 ERROR=1 SKIP=0 TOTAL=4
store-failures¶
Spark Thrift Server uses 5 threads to handle requests so I changed ~/.dbt/profiles.yml to match it.
~/.dbt/profiles.yml
local_spark_thrift_server:
outputs:
dev:
host: localhost
method: thrift
port: 10000
schema: analytics
threads: 5
type: spark
target: dev
$ dbt test --store-failures
Running with dbt=1.1.1
Found 2 models, 4 tests, 0 snapshots, 0 analyses, 199 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
Concurrency: 5 threads (target='dev')
1 of 4 START test not_null_my_first_dbt_model_id ............................... [RUN]
2 of 4 START test not_null_my_second_dbt_model_id .............................. [RUN]
3 of 4 START test unique_my_first_dbt_model_id ................................. [RUN]
4 of 4 START test unique_my_second_dbt_model_id ................................ [RUN]
4 of 4 PASS unique_my_second_dbt_model_id ...................................... [PASS in 1.97s]
1 of 4 FAIL 1 not_null_my_first_dbt_model_id ................................... [FAIL 1 in 1.98s]
2 of 4 PASS not_null_my_second_dbt_model_id .................................... [PASS in 2.13s]
3 of 4 PASS unique_my_first_dbt_model_id ....................................... [PASS in 2.15s]
Finished running 4 tests in 2.76s.
Completed with 1 error and 0 warnings:
Failure in test not_null_my_first_dbt_model_id (models/example/schema.yml)
Got 1 result, configured to fail if != 0
compiled SQL at target/compiled/hello_dbt_spark/models/example/schema.yml/not_null_my_first_dbt_model_id.sql
See test failures:
----------------------------------------------------------------------
select * from analytics_dbt_test__audit.not_null_my_first_dbt_model_id
----------------------------------------------------------------------
Done. PASS=3 WARN=0 ERROR=1 SKIP=0 TOTAL=4
0: jdbc:hive2://localhost:10000/analytics> select * from analytics_dbt_test__audit.not_null_my_first_dbt_model_id;
+-------+
| id |
+-------+
| NULL |
+-------+
1 row selected (0.091 seconds)