hi everyone welcome back to another video on dbt in this video we will take a look at a critical
function of dbt which is tests and as it says here tests are assertions that you make about
your models and other resources in your project and i like to think of them almost like unit tests
in a way and this is really useful because you can test individual sources you can test models
and it really helps you get a head start on the qa process that usually in analytics can be kind of
tedious to take care of dbt makes this a little easier to work with and get around this problem
you'll generally have two different types schema tests and data tests and out of the box it ships
with a couple specific tests unique not knowledge of the values and relationships you can also
create your own custom tests if you want there's a lot of customization that you can do here
for the sake of this video we'll just take a look at how one of these tests work how it compiles it
under the hood and then you can get an idea of you know how all the other ones are working and start
to grow your test cases off of that so let's hop back into vs code into this example demo
dbt project that we've been working on throughout this series and if we look at our schema yaml file
we can see here that in the column descriptions we have tests unique not null here as well as here
and this is for this specific columns so in this case we're saying this column
must pass these two tests you can also have these tests on the whole model itself not just
an individual column along with a couple other places the way this is interpreted
is this is saying this column should when we run these tests
it should be unique and it should be not null so it's going to run a script to validate that
that is true and that there are no records that have you know a non-unique value or a non or that
there is a null value if any of those happen this test is going to fail the way that we run tests
is dbt test before if you remember we would do dbt run to actually run and deploy the models and now
dbt test will actually run these test cases not deploy the models so let's see what happens here
it recognizes the four tests one two three four
and we can see three of them passed but one failed
and it gives you the result here it says failure and test not null for the schema compiled here
so what this is saying is the not an old test failed meaning there must have been a null value
in this data set where we're saying that's not allowed so let's first let's look at the script
for this model my first dbt model here this is just one of the example models that comes with
dbt when you initialize it and you can see here we're saying select one and then union
with select null and this is really done on purpose i believe to show how a test can fail
you know they're saying here you can actually remove it so what's happening here is this null
value is being inserted into this table and then when we run the test it's saying actually
you can't have a null so that's why it's failing but how does it know that similar to how we looked
where models are compiled in a previous video dbt will also compile all of the test cases so
that you can go and review what happened and see what logic was actually run and you can either
you know control click on this here and it will bring up that file directly if you're
using visual studio code or you can traverse through these folders here get to target compiled
and you can see these test cases are are in here schema tests coming from this file
and the one we are looking at is this particular one so that's the same file and here's the
script that's running it's compiling this and saying count in this model where id is null
and the expectation is this should be zero but if there's anything other than zero then this fails
and you can see here got one result expected zero and we know just from looking at the script that
we are specifically putting in a null value but this is really useful when you have a larger data
set or you're working with more real-time data and you want to test to make sure consistently
that there is no null value let's say in this case and by running this test you can get ahead of that
and catch it you know maybe before it gets to the reporting layer or for some user to see
and this is helpful because it's an actual script that we can run in our data warehouse
so if i go to my data warehouse here in snowflake and drop in this script
we can see validation errors there's a one here and if we wanted to see what the actual value was
you can get rid of the count
and see what the actual value was which we know it should be null right now if we wanted to correct
this and we don't want this to fail there's two things we could do we could either get rid of
this test case and say okay we're allowing nulls or we filter this out here just like they suggest
and say let's only take it where it's not null so let's save this dbt run again so
that will redeploy all these models this deployed successfully and now we can do another dbt test
and we would expect these all to pass now because that null value is being kept out and they all
pass so that's great news we corrected the error and we can move forward that's it for this video
i just wanted to show you how test cases are defined here in the yaml files how they are
compiled under the target folder just like model queries are as well and how you can check for them
in the actual data warehouse itself to help debug the test case and be able to correct your errors
so thanks again for watching hope you found this useful and i'll see you at the next video