In this lesson we will:
- Introduce the concept of singular tests;
- Show how to run singular tests.
Singular Tests
Singular tests are the simplest type of tests in dbt. They involve writing a sql query, which if it returns any rows, would represent a failing test.
For instance, if it is against our business rules to have an order with a negative value, we could define a test with the following query:
select * from {{ ref('orders' ) }} where value < 0
If the query were to return at least one row, then the test fails and this would be reported during the dbt test phase of our run.
Of course, our tests are likely to be much more complex than the above example, and could involve joins and complex queries to assert data correctness rules.
## Creating A Singular Test
Singular tests are written as plain SQL and stored in your tests folder. Again, we could use the following SQL:
select * from {{ ref('orders' ) }} where value < 0
And store it in the file tests/orders_should_not_be_negative.sql. The filename will be used by dbt as the name of the test.:
Running Singular Tests
In order to execute our test, we would simply run a dbt test at the command line:
16:35:11 1 of 8 ERROR accepted_values_average_trip_distance_average_trip_distance__placed__shipped__completed__returned [ERROR in 0.09s]
16:35:11 2 of 8 START test not_null_average_trip_distance_average_trip_distance ......... [RUN]
16:35:11 2 of 8 PASS not_null_average_trip_distance_average_trip_distance ............... [PASS in 0.05s]
16:35:11 3 of 8 START test not_null_payments_by_type_cnt ................................ [RUN]
16:35:11 3 of 8 PASS not_null_payments_by_type_cnt ...................................... [PASS in 0.04s]
16:35:11 4 of 8 START test not_null_payments_by_type_payment_type ....................... [RUN]
16:35:11 4 of 8 PASS not_null_payments_by_type_payment_type ............................. [PASS in 0.05s]
16:35:11 5 of 8 START test not_null_payments_by_type_very_large_constant_currency ....... [RUN]
16:35:11 5 of 8 PASS not_null_payments_by_type_very_large_constant_currency ............. [PASS in 0.05s]
16:35:11 6 of 8 START test not_null_pickups_by_location_count_cnt ....................... [RUN]
16:35:11 6 of 8 PASS not_null_pickups_by_location_count_cnt ............................. [PASS in 0.04s]
16:35:11 7 of 8 START test not_null_pickups_by_location_count_pickup_ntaname ............ [RUN]
16:35:11 7 of 8 PASS not_null_pickups_by_location_count_pickup_ntaname .................. [PASS in 0.04s]
16:35:11 8 of 8 START test unique_payments_by_type_payment_type ......................... [RUN]
16:35:11 8 of 8 PASS unique_payments_by_type_payment_type ............................... [PASS in 0.06s]