Hey, what's up Gurus.
In this lesson,
we're going to be talking about how to deal
with duplicate and NULL data within data flow.
Pretty cool lesson,
so, let's get started.
So, in this lesson,
we will start off by talking
about how to handle duplicate and NULL data,
which by the way,
is not just a job for data flow,
but we're going to focus on Data Flow
because of the DP-203.
We're also going to take a look
at some of the basic steps
that you need to complete
in order to deal with duplicate or NULL data
in Data Flow.
And then finally,
of course, we'll jump in and I will show you
how it all works in the portal.
So, let's start by talking
about why this is not just a job for Data Flow.
There's an old saying,
if all you have is a hammer,
everything looks like a nail.
That's very much the case here.
If all you understand is Data Factory,
that's the only tool you've got,
and hey, that's going to be the solution
for everything.
If you've broadened your horizons,
you might say,
oh, Databricks might be a solution in some cases,
or Power BI might be a solution in some cases.
There's a lot of different ways
to look and solve problems
in Azure with data engineering,
so, make sure that you're broadening
your technology base,
so that you can look at the best solution,
not just the only one you know.
Next, complexity.
So, we've talked about Data Factory
and Data Factory Flow,
and being able to do transformations there,
but we've also talked about that being
for generally more simple use cases.
If you have very complex needs,
a lot of times you're going to look at something
like Databricks.
So, understanding your level of complexity,
how much data you're pushing through your pipelines,
will help you to understand the solution
that you should be using for the job as well.
Finally, accompanying solutions.
So, if you think about your pipeline,
if pretty much all you're using is Data Factory,
hey, you might want to do everything in Data Factory,
including data flow,
but if you're already using Databricks
for example,
well, you might want to take a look
at doing your transformations in Databricks,
and then just using Data Factory
to move things along the pipeline.
So, understanding the other solutions
and other steps in your pipelines,
or other solutions in your data engineering jobs,
will help you to understand the best solution
for the task at hand.
So, just be considering all of those things,
not just looking at data flow.
Having said that,
these are the basic steps
to deal with duplicate and missing data.
Step 1,
we're going to create data flow in Data Factory.
Step 2,
we're going to select our source.
Then we're going to choose our modifier,
and I'm going to show you
what that means here in just a second,
but you're going to choose the modifier
to accomplish the task that you're looking to do.
And then 4,
you're going to choose your destination,
which, if you remember in Data Factory,
is the sink.
So, you're going to choose that as well.
So, this is how we're going to choose our modifier
As I go through the demo here.
I'm going to throw this link as well in your lesson,
but make sure that you're looking at that,
because that's going to have a ton of helpful information.
With that,
let's jump over and see how this works
in the portal.
So, let's take a look at removing duplicate rows.
And I want to show you
that Data Flow script link from Microsoft,
so let's jump over and take a look at that.
This is a very cool section
that has a ton of little sample scripts
that you can use
to solve all sorts of different problems.
So, in this case,
let's go ahead and grab this code snippet here,
which is going to help us
with deduping our data.
And so, we're going to copy this snippet,
jump back over here in our data flow,
and we're going to look at accessing the code now
instead of just pulling modules in.
So, over here on the right,
you'll see that we can use Properties.
We can look at our code as well
by clicking on that link.
And we can also take a look at this,
which is our scripts.
Now, since we pulled the script
from that Microsoft link,
we're going to go ahead and use this.
And I'm just going to paste in the script snippet
that we just grabbed.
So, I'll click on OK,
and you can see that it now created
a DistinctRows module for you.
And so, what this is going to do
is it's going to go through all of your code.
It's going to pull out the first instance of each data,
make that unique,
and then anything else that's a duplicate,
it's going to not pass through to our output.
So, let's go ahead and grab this.
For our incoming stream,
we'll choose source1,
and we'll put that in line with our data flow.
So, next,
we want to talk about how do we get rid
of NULL values.
So, we'll jump back over to our script here,
and we'll grab our check for NULL script.
So this is going to separate out
all of our NULL values for us.
So, we'll copy that script snippet,
come back over here,
go to our script section again,
and we'll just paste that in as well.
So, you can see that it now has created
a hasNULLs for us.
So let's go ahead and put this in line
with our data flow as well.
So, we'll come down to our incoming stream.
We're not going to choose source1.
We're actually going to choose DistinctRows,
because we want to have this happen after.
And so, once we do that,
you can see that we now have DistinctRows.
After it's separated out that,
it's going to take a look for NULLs,
and it's going to separate that out
into 2 different flows,
so we can decide what we want to do with the data
that has NULL values in it,
and the data that doesn't have any NULL values in it.
And so on and so forth.
So we can go with the rest of the way through,
building our transformations out
if we so desired.
Once we're all the way done,
and there again,
once we're done with everything,
we would just grab a sink
and we would drag that over
to finish out our output.
And then we could go through
and put that into our data pipeline,
and we would have a pretty cool snippet
that is going to go through,
separate out all of our distinct rows,
and then separate out NULL and non-NULL values.
So, with that,
let's jump back over
and take a look at our review.
Keep in mind,
duplicate data,
NULL data, that is not just for data flow,
so be thinking about alternate solutions
as you move through.
Also, understand the basic data flow steps.
So, step 1,
create a data flow.
Step 2,
you're going to create your source.
Step 3,
you're going to use that modifier,
and you're going to grab those script snippets
from that link,
and then you're going to paste those in where applicable.
And then step 4,
you're going to create your sink
and then attach that into your Data Factory pipeline.
If you remember that,
then you are good to go
and you know what you need to know
on the DP-203 to handle duplicate
and NULL data in data flow.