Welcome back.
In this lesson, I'm going to be talking to you
about how we design and implement incremental data loads.
So let's get started.
We'll start by digging deeper into Azure Data Factory.
And in this lesson,
we're going to be discussing
what incremental data loading is.
We're going to outline the importance
of incremental data loading and why you should use it.
We're going to talk about the concept of watermarks
and why that's important.
We're going to tie all of this together
by examining incremental data loads in Data Factory.
And then, of course,
we're going to see this in action in the Azure portal.
So let's start with the very first tip.
Don't do this.
This is a crazy car.
We'll talk a little bit more about what this means,
but let's start talking about full data loading
versus incremental data loading.
So full data loading is a dump of the entire dataset.
So if we're going to load something new in,
we're going to take our current dataset, dump it entirely,
and we're going to completely replace it.
That's it.
There's no additional requirements.
Dump what you have, completely start over, and rebuild.
Incremental data loading on the other hand
is more like this car over here on the right,
don't dump anything.
We're going to keep everything that we have,
and we're going to look at the new stuff
in comparison with what we currently have.
And we're only going to add the difference.
That's what we're going to be doing.
So we're just going to keep piling more stuff on,
but we're not going to replace all the stuff that we had.
So that in a nutshell is what incremental data loading is.
So why should we use incremental data loading
and why shouldn't we use it?
First, we should use it because it's faster.
It's much, much faster.
So think about just dumping everything and starting over
every single time we have that full load
versus looking at only adding a few rows
or a few percent of the total database size.
So why shouldn't we use incremental data loading?
Well, it's more complicated.
First off, it's more likely to create errors.
Because we're only loading part of a database,
there's a much greater chance that somewhere along the line,
we don't capture all of the new data, or we cross over
and we get duplicates, or it crashes in the middle.
And if any of those things happen,
it's very likely that we can't rerun
the incremental data load with errors.
So consider that if that happens
and it crashes in the middle,
there's a good chance you might have to just dump everything
and start back over with a complete rebuild.
And then finally, the timing can be troublesome
because we have to look at exactly what we have
and then the new stuff, figure out where that ends,
and load that new data load in.
Depending upon how fast the data is moving,
depending upon how accurate we are with our timing
and our timestamps, we could have issues there,
which again, timing is more troublesome, of course,
over just dumping everything and starting over.
So now let's transition and talk about watermarks.
And when we look at watermarks,
a watermark is just simply a way
that we can mark as our database changes.
So it could be a timestamp, it could be an ID column.
So we could look at the number of ID and as it increases,
we could use that as a reference point or a watermark
to determine what's changed for our incremental data loads,
or it could be something like a timestamp.
So every day we get a new entry,
and we could use that as a watermark
to determine what's changed for our incremental data loads.
So in this lesson, I'm actually going to show you
2 different ways to do incremental data loads.
The first way is this way, the watermark way.
And then the second one,
I'm actually going to show you in Azure Data Factory
how we can do incremental data loading using dates.
So just keep that in mind, and keep in mind the watermark,
and we'll talk about that more in this course,
as we go through several different Azure services,
but a watermark is just simply a reference point
that we can use to determine what's changing with our data.
So in this example,
we would start off by selecting a watermark.
So we would go into our SQL database
or whatever database we're using,
and we would select our watermark column.
For instance, in this case, we could say it's customer ID.
Then we would store that watermark value.
And we would use that stored watermark value
to figure out what's changed when we run our pipelines.
Okay.
So the process in Azure Data Factory would be,
let's create 2 lookup activities.
So we would have our last watermark over here on the left,
and then we would have our new watermark over here
on the left as well.
So the last watermark would pull up the last time
that we did an incremental data load,
and then the new watermark would look
and it would pull the most recent watermark that we have.
From that, we would create a copy activity,
and the copy activity would just simply look
at the difference between the last and the new watermark.
And it would load all of the data between those watermarks.
And then finally, we would create a stored procedure.
I talked about storing.
We would create a stored procedure activity
that would update our new, new watermark, right?
So we had our old one, which is the last.
We did some updates,
and now we want to store our new watermark.
So we would store that new watermark
so the next time we ran it,
we would be able to move forward
and figure out what had been changed
since this most current run.
So that is one way that we can do incremental data loads.
It's not wildly important
that you understand all of the intricacies
about how to create that copy activity at this point.
It's more important that you understand what a watermark is,
and you understand this process,
that this is a possible way
that we could do incremental data loads.
So with that, we are actually going to pause this lesson
because we are only about halfway through this journey.
And then in the next part, I'm going to pick up
and I'm going to walk you through the Azure portal
to show you an example of how you can do an incremental load
in the Azure portal.
So take a break and I will see you in the next lesson.