In this lesson,
we are going to be talking about upserting data
using data flow.
Little aside, as I built this lesson,
autocorrect desperately wants to use upsetting,
rather than upserting,
so no matter how upsetting autocorrect finds it,
we are going to talk about upserting in this lesson,
starting with some definitions and terms
that you need to understand.
Of course,
we're going to look at the steps that you need to do
in order to use upserting in Data Factory,
and then finally, we're going to see all of this in action.
So with that, let's get started.
First, the definition of upserting.
The definition is an operation that allows you
to either insert rows into a database table
if they don't exist, or update them if they do.
So the key here is alteration.
We, instead of replacing the entire database
every time we renew or update something,
what we're going to do is we're going to intelligently look,
and if we have new records,
we're going to add them,
or we're going to change them if they already exist.
And again, this is going to be dependent upon the code
that we insert into Data Factory.
So along with that, if your only tool is a hammer,
every problem looks like a nail.
So as we look at the different types of updates
to a database, the first is upload.
If we upload,
we're just simply going in and we're adding a few records.
Next, we can do full.
And if we do full,
we're literally replacing the entire database.
We can do incremental,
which means we look at where we stopped,
and we just simply add from there,
or, like we talked about, we can do upserting,
which is editing the rows where changes are,
and adding new data where it doesn't exist.
So these are the 4 different types of updates
to database records,
upload, full, incremental and upsert.
It's important that you understand what those are
and that you understand what the differences are as well.
So when we talk about the basic steps of upserting,
here's what we're doing.
First, we're going to go into Data Flow.
Then we're going to pick our source.
We're going to choose and add an AlterRow module.
We're going to create our Upsert If conditions,
and then we're going to finish
with our destination or our sink.
So these are the basic steps that we would do
in order to create upserting in Data Factory.
Create our data flow,
add our source, always,
choose AlterRow,
which is what we're going to use
to do our upsert conditions,
then we're going to add those upsert conditions,
and then, finally, we always finish with a sink.
So with that,
let's go ahead and jump over into the portal,
and I'll show you what that looks like.
So here we find ourselves in Data Factory,
and I've opened up a data flow.
So I've already added my source,
and I just created a simple source.
Now, one of the things when you create your source,
you want to make sure that you've allowed schema drift.
This is not always going to be the case,
but normally if you're going to use upserting,
you'll probably want to have schema drift on,
unless there's a specific reason
that you know your database isn't going to change.
So then we're going to go ahead
and choose this little plus button,
scroll all the way down to the bottom,
and we are going to choose the AlterRow module.
So with our AlterRow on,
going to come down here,
and you can see we've got our incoming stream
set as our source number 1,
and then we're going to choose our AlterRow conditions,
and this is where we can do our insert, update,
delete, or upsert, which is what we want to do.
Once we have that,
we'll just go in here to our expression builder,
and we can add in whatever conditions we have,
and the conditions aren't really all that important,
because it's going to be very specific to your circumstance.
So it's not something that you're probably going
to have to do on the DP-203,
as in, I'm going to add in expressions,
or see questions about that.
I think that's very unlikely.
So you would just add in something like, isNull,
and then we have our column name here,
and then we could have false or,
again, whatever your expression is.
We would just add all of that in here.
We would click on Save and Finish,
and then now we have our upsert condition.
And then if you remember,
the last step to this is always adding in our sink.
So we'll just scroll down and choose a destination sink.
Again, we want to allow schema drift in the source
and the sink.
If we're going to do one, we need to do the other.
And then once this is done,
we would just simply publish this data flow,
then go into a pipeline,
and we could add this data flow into our pipeline,
which would then be looking to do upsert
based upon the source.
So those are the steps that you would need to do
in order to do upserting in Data Factory.
So with that, let's jump in and finish out this lesson.
So what are the options for data loading?
Make sure that you know the different options,
whether that's full or incremental or upsert,
just make sure you're aware of alternate solutions.
Make sure that you understand the basic steps
for upserting in Data Factory.
So we have to have a source,
we're going to add the AlterRow,
we're going to add in our conditions,
then we're going to look to add in our destination,
so just our sink.
Make sure that you know those basic steps.
And remember that the key to upserting is alteration.
We're altering rows if they exist,
or we're adding new rows if they don't exist.
You got all that done,
then you should be good to go on to the next lesson.
That's it for this one, I'll see you in the next.