Hey, Cloud Gurus, welcome back.
In this lesson, we're talking about cleansing data.
To do that,
we'll start with looking at a data cleansing overview.
We'll talk about some of the available tools
for cleansing data,
jump in to Azure Data Factory and do a demo,
and then, wrap everything up with a review.
To overview data cleansing a little bit,
what we mean is the process of analyzing data
in order to correct, remove, de-duplicate,
reformat, or complete it.
This is especially important when pulling data
from many different sources.
It's probably not all stored in the same way.
Some of it could have been computer generated.
Some of it could have been entered by human hands.
And so, there is work that needs to be done
to bring it into a clean, consistent format.
And really, this just goes to ensure data quality
and completeness, allowing it to be in a more usable state
for consumption downstream.
There are many tools available for this type of process,
and we won't go over all of them here,
but some of the ones you may encounter are
data quality services.
And this is a component of SQL Server
that allows for computer-assisted data cleansing.
It uses a knowledge base to check against,
and then, a data steward can follow behind
and interactively approve or modify suggestions.
There is a clean missing data module
in Azure Machine Learning.
And this allows you to replace, remove,
and even infer values.
You can also use our old friend, mapping data flows
in Azure Data Factory.
These activities allow you to include data cleansing
as part of your pipeline.
In fact, let's jump over to Azure Data Factory
and take a look at this.
Here we are in Azure Data Factory Studio.
If we come back over to our friend, the author tab,
let's create a new data flow.
Within here, we can add our source.
For this example, I'm going to use a customer table,
Customer_Prod.
And let's just name that Customers.
Now, one of the ways that you can go about data cleansing
using a data flow is with a derived column.
So we can add that to our flow here.
And let's name it CleanData.
Within our derived column,
we can go column by column and do transformations,
or we can add column patterns to get everything of,
let's say, a specific type, like a string.
In this demo, we'll do a simple example.
So let's just grab a single column.
For this one, let's go down to the phone column.
And let's say that we want to make sure
that our phone numbers don't have any spaces in them.
As mentioned before, when you have humans entering data,
there can be lots of mistakes.
Let's say you have hundreds of call center representatives.
There's a good chance that not all of them
are entering the data exactly the same.
So let's jump over to our expression
and use expression builder.
Azure Data Factory is very helpful in giving you categories
to help you find exactly what you're looking for better.
I'm going to go ahead and search for trim,
because I know that's what I'm looking for,
and grab this one down at the bottom.
And you can see, it even gives you some text
to help explain how to use the function.
Let's click that.
We want to trim phone.
And what we want to trim is a space.
And so, we'll save that.
Save and finish.
And we're telling it to grab the phone column
and cleanse that data by removing any spaces.
Now, this is a very simple example,
but you can see how you can take it and extrapolate it
to more complex scenarios, transforming multiple fields,
such as addresses and names, or even column patterns,
grabbing all of the ones that have strings
and removing extra spaces from each of them.
Or dashes, exclamation marks,
whatever it is that you need to remove from your data
in order to mold it into a more usable state
for later consumption.
By way of review,
cleansing data is the process of analyzing data
in order to correct, remove, de-duplicate,
reformat, or complete it.
There are multiple tools available for this,
including Data Quality Services,
Azure Machine Learning modules,
and, as we saw, Azure Data Factory activities.
And the whole purpose behind this
is to prepare it for downstream use.
Your data comes in in a very raw format,
as we've discussed earlier with our different zones.
And this is the part of the process that you're using
to curate and cleanse that data to make it more usable
for reports, dashboards,
and many other functions down the road.
That's it for this video.
I hope you've enjoyed it.
When you were ready, I'll see you in the next one.