hey everyone on this video i will show you how to write some custom schema tests
as well as how to actually override some of the existing out-of-the-box tests that dvt ships with
so here they give you an example of exactly how you can do that and in the simplest way to explain
it essentially it's a macro all tests are macros and in order to add a new one you need to create
a macro with the prefix test underscore and then it's just your sql script from there and that's it
just as they say here so here's an example where they're testing if a field is even or odd and
giving a result and since we're working with the schema test the output is going to be a count
and essentially it's looking for where count is zero for it to pass if there's a count of anything
greater than zero that test will fail so in this case if you get anything that's an odd field that
will return a record and then the count will be greater than zero and this would fail in our
scenario we'll do something a little different but we can copy this for now and then start and
start with this script so as background on this scenario in one of the previous videos we created
a team details model that just has some basic information about nba teams city state team name
nothing crazy so in this case all we'll do is create some test scenarios around the cities
the combination of city and team and just just to show you how it works
so as we mentioned everything is a macro so let's create a new file we'll call this test underscore
and in our case we'll just say is la team dot sql now we're going to paste that original test
that we found from the dbt site but we'll change this name so this is really what's important this
right here is what the test will be called so we'll call this instead of is even we'll say is
la team and again we're just going to test things around the name of the city here it's going to
understand what is the column where the test is on so depending on what column we put this on
it'll know what it is and then the model it will recognize what model it's falling under
as well so now instead of calling this even field we can just call this in our case we'll
say city doesn't really matter it's you know it's how you want to reference it i'll replace
this here you can get rid of this comment and here we'll say where city equals los angeles
and then it will count the results of this cte and if it's greater than zero it will fail if
it's zero it'll pass now how do we actually use this so if we go back to our yaml file our schema
definition let's add this test so let's add this on the city column so we'll say tess is la team
and what this will do is when we test this model it'll look to see if there is a macro
with the prefix test called is l18 which we have here it'll go into here and run this query
now as i said the column name is automatically put in there and that's going to take
this column name it's going to understand that this is where it's being tested
and the model is part of this model here so it'll know it's this model this column it's going to
look where that column is los angeles so let's see what happens dbt test example team details
now think about what you would expect to happen here okay so we got an error
it compiled it ran so what happened so now just like in the past where we've gone
over our compiled sql we can do the same here for this test so if we go to target
example is l8 is la team we can see the compiled sql and we can copy
and paste this and see you know what happened so we're getting two errors it's greater than
zero so it errored let's get rid of the count and see what's returned obviously there's two records
if we if we were to look at our actual data we know that there's two records clippers and lakers
that's why it failed there's two results it's not zero um and even if it was one it would fail
kind of a weird test but i just wanted to show you how it actually runs and compiles but let's
let's adjust this a little bit let's try to get this to pass first we'll add the column team here
now we know that this is going to only be on this model so we can put this hard-coded in
here but you know if we were to reuse this or on a different model we wouldn't have this team
column available to us obviously but but we know that's how it's going to compile in this
case so just bear with me for the sake of of the example so we'll say where city is los angeles
and team not in lakers clippers because we know that those are the only two options here so
essentially it's going to test that there is not another option and it will fail if there is so we
would expect this to pass because we would expect zero results that pass this scenario so let's try
again and here we can see it did pass this time so now if we go back to the compiled sql here
it's now a little bit different
and we can see the count is zero because there are no values that actually meet that condition
so that's how it would pass okay so that's how you create your own custom schema test from scratch
without anything else involved but now let's say you wanted to actually override something
that already exists for example the not null test unique relationships you know any of these default
tests how would you do that and as it says here you can find the source code for these tests here
so let's open this up now dbt being a completely open source project gives you full access to these
and these are all just macros just like what we just made so you can override it so let's go
let's say not null for example here we can see exactly what the not null test is running whenever
you come into your actual file here and say tests not null really what it's doing is it's reading
from the core dbt files and and running this macro but you can override it you could copy this paste
it into a new macro let's call this tests test not null again you have to have the prefix test
test not null and drop this in here so now what will happen is it will recognize that this exists
um on your in your project and override anything that might be uh defaulted already so in this case
anything else we wanted to add here you know if you wanted to add some more conditions here
whatever you wanted it would then run that instead of the default compiled code for not know and you
can do that for any of these other ones here that come out of the box so that's pretty cool
gives you some flexibility and you can tweak it as you want the last thing i'll mention is a lot
of these common scenarios where you might have a couple conditions or want to do something else
is already covered for the most part in this dbt utils package which if you've watched some of
the other videos we briefly touched on that how you can get to that and add it to your project
let's open this up again if you're not familiar with this take a look at one of the other videos
and we can see here that they give some macros already available that you can use
schema tests so you know quality expression is true and you would use it something like this
so we can look at the source and again it's just a test just like everything else test underscore
and whatever they're doing so you can look through here and maybe find some ones that meet the actual
scenario that you're trying to customize and maybe they've already done it and you can just use what
they've pre-built so a lot of options here that you can use one of the great aspects of of again
of an open source project is this flexibility and customization let me know if there's any questions
on this hope you found this helpful thank you for watching and i'll see you at the next video