To implement data quality tests, we need to have a tool that can perform these tests.
One tool that has gained more popularity over the last few years is soda, which is a data quality framework.
We will use Soda Core, which is the open source Python based library, and using Soda Core we can define
the data quality checks in YAML format.
Now to have soda available we will need to install it using Pip.
We can do this by going back to the requirements.txt file that we introduced in the beginning of this
course, and adding two additional packages that will be part of our updated Docker image.
These are the following two.
The first one is for data quality, and this is the one that we're currently discussing.
And the other one is Pytest, which we will go more into once we do the functional testing part of this
course.
Notice also how for Soda Core, I had to specify Postgres since our data is inside the Postgres database,
we need to use the so-called process connector.
If we were using another database like redshift for example, we would need to use the appropriate connector.
Now that we have updated and saved the requirements.txt to update the Docker image, there are some
additional steps which we need to take.
So first we need to build the Docker image with updated tag.
Remember that the first version tag was 1.0.0, and we can remember this by going to the Docker Hub
repository and seeing 1.0.0.
So let's build the YouTube API image using the 1.0.1 tag.
Next we will need to push the image to Docker Hub.
So first we log in.
We are prompted for a for passwords.
Make sure you have the saved somewhere and it is easily accessible.
Once you get login succeeded, we can now push the updated image with the new tag.
So from here, let's go briefly on Docker Hub and see if the tag was updated.
As you can see, we just pushed the new image with the new tag less than a minute ago.
As a final step, run docker compose pull to use the latest image that we just pushed and rebuild and
restart the containers using the dash dash force dash recreate option of Docker compose.
So this will look as follows.
First, run the docker compose pull and then run docker compose up minus d dash dash force dash recreate.
Let's run docker PS to make sure that we are using the new tag.
And as you can see, we are using the new 1.0.1 tag.
If for some reason the image tag is not updated, try closing vs code and rerun the docker compose up
command and the docker PS command as well.
Now to check whether we have soda code installed, we go inside one of the containers.
Let's say we go inside the airflow worker.
So we use the docker exec command.
And now that we're inside the container, we type the pip show and the package in our case soda core
Postgres.
Progress.
And as you can see, the package was found with this particular version that we have.
Great.
So at this point you confirm that we have Postgres installed.
Now that we have it installed we can also run.
So dash dash help.
Here we get the commands that we can run.
The next thing we should do is to test the connection to our database.
As you can see, one of the options that we have here is to test connection.
This tests the connection that soda has with Postgres as per the soda docs for Postgres connector.
To test the connection, we need to create a YAML file called configuration.yaml, and the YAML file
will contain all of these parameters for the connection to the Postgres database.
Now, as we have said in one of the previous sections, we will store this YAML file under the include
directory as per airflow best practices.
And we will also create a subfolder which we will would call shoulder.
So let's do that right now.
First we create a sub folder soda.
And in here we will have the configuration.yaml.
The information for the configuration where you have in the env.
So we don't need to stay hard coding these values.
And the schema will be dynamic variable depending on whether we are working with the staging or the
core layer.
So if we have to look at what parameters we have in the documentation and change them according to our
parameters, we will have the following.
Just to go over this briefly, the one thing that we will define is the data source name, which I have
named as PG underscore data source.
The type of connection is with the Postgres connector.
So we will use the Postgres.
And here these we will all get from the env.
As we said the schema is a dynamic variable which can either be core or staging.
With the configuration set up we can now test the that's the connection.
As you can see, the test connection command is given here.
If we had to adjust for our scope, the command would look as follows.
So here we're defining the soda test connection command.
As we see in the soda shell we need to define using the dash D the data source which we said we would
define as PG underscore data source.
And here we define the part of the configuration file.
Also we use dash capital V for a verbose output.
So let's run this command and we should get a valid response.
So I didn't save this.
So let me first save the configuration.
And let's run this again.
And there we have it.
The connection PG underscore data source is valid.
So that's great.
We have now verified the connection is working fine.
At this point.
We can now define the tests that we would like to apply in a separate YAML file which should be called
YAML.
So let me create it right now.
In terms of checks for that come to mind for both staging and core layers are the following.
We first need to check if there are null values for the video ID column.
There shouldn't be any because this is a primary key.
We should also check if there are duplicates, which again should not be the case for video ID column.
We should also ensure that for each video, the views count is less than or equal to the number of likes,
and likewise the views count is less than or equal to the number of comments.
This is because, as you can imagine, the number of views has to be greater than or equal to the number
of likes and comments as to like and comment a video, you would first have to actually see the video
going back to the soda docs.
In order to run these data quality checks.
We have examples here of how we can do this.
So we should always follow the standard of using checks for.
And then here you can specify the table name.
In our case this will be YouTube underscore API.
So let's go back to VS code and start running our checks.
To check for nulls soda has an inbuilt check which is called missing count.
What you need to do is to define the missing count check and also specify the column that we will be
checking here.
We also specify the counts that we expect.
In our case we expect zero null values.
We can also do the same thing for the duplicates.
Again, the check is inbuilt in soda and we can define the duplicate underscore count with the column
in question.
And now for the last two checks we have to build a custom query which we can as follows.
So first we define the result variable of the query.
And we set it to a value which we want which in our case will be would be zero.
I will soon explain where the zero is coming from.
The variable name will be as follows.
So the likes count greater than video views is zero.
This is I guess, self-explanatory, but what I want to show here is that the likes count.
There are no instances where this is greater than the video views.
We can now define the name parameter, and the name is basically a description of what the query will
do.
So we can write something as follows.
Check for likes count column greater than video views.
From here, what is left is to write the actual SQL query for the code.
To do this, we copy the variable.
Again, we write the keyword query, and from here we set up the SQL query.
The query will look as such.
So again, I will reiterate what this query is doing is that we are checking for the count of videos
that has the likes.
Count is greater than the video views.
Again this should be zero which is what we defined here.
So we expect that this count is zero and no rows will be returned for this check.
Now the same thing can be done for the comments count.
So I will simply copy the check I have here, but this time for the comments.
Now that we have both the Configuration.yaml and the HTML, we can run a pseudo scan command to see
if our tests pass.
The command will look as follows and I will explain what it does.
So again we're using the soda scan.
This is done for the checks the data quality checks.
We are defining the data source, the path of the configuration.
The dash V.
The small dash V specifies the schema, which in our case we're going to run.
This checks for the core layer.
And here we're also defining the location of the checks.
Let's run this.
So it seems I have a couple of errors here.
The first thing that I'm noticing is that I misspelled missing count.
This should be with another I duplicate count.
Okay, let's try this again.
So from what we can see here, there is some error with these last two checks.
It could be because of the indentation that is caused with the colon.
So let me just do this.
Let's save and let's run again.
Finally success.
As you can see, four on four checks have passed.
You also get this command that says it all is good.
No failures, warnings or errors this time.
And let's say we want to test our logic.
And let's say we will set this to one.
In this case, we will get a failure because we cannot have any videos with the comments count greater
than the video views.
So let's run this again and see what the output is.
We might also want to include a minus v, a capital V for a verbose output.
And as you can see, the test that we manipulated failed.
And as you can see, the actual value should have been zero as it was before.
So let's change this back.
And we are done with the tests using soda.
At this point, we know that we have a valid connection and that the data quality tests are working
as expected.
The final step is to integrate all of this in airflow, which is what we will do in the next lecture.