>> Learn all about Change Tracking,
what it is, how to use it, how to get started,
how it's different from change data capture,
and how easily more importantly,
it is to get started.
This week on Data Exposed.
Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed.
Today I'm joined by Brian from the Azure SQL team.
Brian, thanks so much for joining us today.
Can you tell us a little bit about what you do?
>> Thank you, Anna. I'm Brian Spendolini.
I'm a Product Manager in the Azure SQL Database group.
I usually deal with application development,
but I'm also a PM in helping out in the replication technologies.
Again with the application development,
our main focus is to make everyone
aware that SQL is cool to develop on.
You've had previous videos with
the local SQL development environment docker.
The tight integration we have between
a VS Code and SQL extensions,
as well as the ability to use SQL with GitHub Actions.
So that's primarily my focus at this point.
It may change, but again,
for today it is Change Tracking and developer experiences.
>> Cool. Awesome. Well, it's great to have
you on the show and we're excited
to always be talking about developer related topics.
Those are some great topics that you mentioned
and I know change tagging is going to be great one too,
but not something we've talked about before on
Data Exposed so I'd love to kick it off by asking you,
what is Change Tracking and why would someone want to use it?
>> This is a question we hear a lot.
Change Tracking has been around for
a very long time in the SQL database.
I think it was first appeared in SQL 2008.
It is available in just about every version from
Edge all the way to the Cloud and everything in between.
Now, Change Tracking gives you the ability to track
changes that are in the database that
happen based upon maybe an insert,
an update, or delete.
Change Tracking is going to basically tell
you when or how a row has changed.
Is it an insert, an update, or delete?
Which columns were involved with that change?
And then again, which rows have changed over
a specified period of time.
Now, this is great because it's a little
different than another feature we have,
which is called change
data tracking or change data capture, I should say.
With change data capture,
we're also storing the actual data
itself and what has changed in the previous row,
but with Change Tracking,
we're just keeping a record of what
has changed and not actually the historical data.
Now, you may be wondering, well,
why would I use it and why would I want to use that?
We'll go into it really quick of all
these use cases we have for it because having
that reduced data or that reduced information actually
can be very beneficial in a few use cases which again,
as I said, we will discuss.
Now, why use Change Tracking,
we're getting back to that.
So we see a lot of
developers trying to implement
this type of functionality within their database,
but they're going to do it with custom codes.
By using Change Tracking,
you're going to reduce your development time.
You're not going to need the need
for triggers and separate tables and
timestamp columns and tracking columns and
tracking tables in cleanup processes.
All these are going to be built into the actual feature,
as I go into here,
all these features we have.
As I said, reduce development time,
there's no schema changes.
A lot of times if you're doing this yourself,
you're going to have to add columns or
move columns, things like that.
You don't have to do that if you've just enable Change Tracking.
It also is very low overhead.
With change data capture it
uses the SQL Agent or the scheduler in the Cloud,
with Change Tracking, there is no overhead like that.
We just base it on the committed transaction.
As soon as the transaction is committed,
we're going to take a record of that and put it into a side table.
Then with this feature we have
standard tools and standard features and
functions and procedures that you can use to extract
this information and use it within your use cases.
Now, going to use cases,
when and where would you use this?
One of the big use cases we have is
one-way and two-way synchronization of applications.
If you have an application such as
maybe a caching tier or an application that caches certain data,
this is great because you can link it to
Change Tracking and only pull out the changes
that you need rather than taking
all that table or refreshing a table every time.
For two-way synchronous applications,
may be like a disconnected application,
may be you have an application that
sits on someone's mobile device or
their phone and they're doing something maybe in a rural area,
or they're doing something like a field technician
or a field service technician and they're in places
where there is no cell coverage or they're deep in
some tunnel or a warehouse where there is no coverage,
they can use their application
and then once it comes back into range,
you could sync back and forth and
Change Tracking is a great way to do that on either side.
Data restore is another one.
If you lose data in a table,
you're doing a recovery and there's some data missing you can
quickly use Change Tracking, you catch that up.
Lastly, one of the big use cases we see is ETL,
the extract, transform, and load scenario.
In this, usually it's a batch process.
ETLs are batch processes.
They happen at night. They happen on very large sets of data.
Well, I can use Change Tracking.
I can run this more frequently.
It only pull over the data that has changed over
shorter periods of time to keep
that data warehouse refreshed at a greater interval,
which helps keep better analytics and
better reporting because instead of having
that nightly load I can do this multiple times throughout the day,
maybe every hour, maybe every half an hour just on
smaller chunks of data and be able
to get that into that warehouse.
>> Awesome. Thanks so much Brian.
I think it's a great explanation of what Change Tracking is,
and then I also like how you compared
it to change data capture because it was one of
the things that I was wondering as I was
thinking about this topic.
Some of the use cases are super interesting too.
I've played a little bit with Change Tracking.
I think one of the scenarios I had was like
a fitness application on mobile in where you
wouldn't want to pull all the data
every single time you just want to go
from a specific version and
that's something that Change Tracking enables.
>> Now that we have an understanding of
what it is when you would use it,
how it's different from CDC like could we take a look?
>> Yes, let's go and see this in action real quick here.
Here I have a database and we have Azure Data Studio here.
This is actually running in a hyperscale database,
which is a great database for
not only production but for development.
It's a smaller one but
what we're going to do is we're going to turn it on.
When we turn it on, we have two parameters here,
we have changed retention and auto cleanup.
Change retention basically says how long am I
going to keep these changes in the site tables?
Then auto cleanup is if you want those
automatically in a process to run on the road.
I can just run this and it's going to
enable it within my database.
Next, we're going to just enable it on two tables.
We have the Adventure Works sampled data loaded into this database
so I can just quickly run it on
these tables and now it is enabled.
We're ready to go, change traffic is turned on,
change tracking is turned on these tables
and we're done as easy as it is to turn on.
We also have two of these system tables
change tracking database consists, change tracking tables.
This is going to tell me,
maybe if I ran this in the master database,
but we will just run it on their own it
says that yes, it's turned on.
Auto clean up is on our retention period is two days
and then if we look actually at the tables were tracking,
we can run this and we can say, oh,
here are the two tables they're seeing versions will talk
about the versions in a minute but it is
column tracking on his again column tracking,
which is here it will add some information within
our side table to say
which columns were actually changed, not the data in them,
but which column were changed,
which allows you to make specific ETL queries a little
bit more precise in
that data load and we'll talk about that in a second.
Again, if we want to use this,
we're going to just quickly update
ListPrice of products 680 and we'll just run this.
Now that we've run this,
it's going to update that row and now if
we look at it and we're going to use
the built-in function change cable
and it's going to get that data from
our side table and we're going to pass and actually know
for our version number will get in the version numbers in a
second but here we can see that our first version numbers one,
because it's a sequence,
we'll just expand this a little bit so there's our change version.
It was an update here's the column and then every sign
table's going to also have the primary key at intuitive as well.
Now I can go in here and I can also,
if you see a column here that says sys change context.
I can use this column for actually adding
text about that particular update, insert and delete.
If I run this one really quickly and that's done,
and I go back and I look at this table,
that side table again,
I can see here it is
my converted context and I
can maybe put some information about that.
It's very easy to use and you see that it's already tracking
these two updates and it has
the product ID are primary key on those tables.
This also does it based on transaction id
so here are two transactions that I can run.
Now it's going to be separate.
But if we look at our table,
we're going to see that our version numbers
are actually two different version numbers.
But just an example of
how version numbers word here is
something that's going to be multiple columns,
but it's in the same transaction so if I
run this and we go back and look at our table again,
we're going to see that this particular row
or these particular updates all
share the same version number because
it was within the same transaction.
Now, skipping ahead real quick,
I just wanted to talk
about where he said we're tracking this particular columns.
This is important in an ETL type of situation.
I'm going to do an update and it's going to update
the size column in the weight column on
this particular product and it's done.
What I can do now is I can say,
oh, well, show me this,
but I'm going to use another built-in function
called change tracking is in
column mask and we're going to pass in
the size column and if we look at our results here,
we can see that this particular function we passed in is
returning a one because in the last update we did,
we have that column.
That's going to be important when we look at ETL scenarios.
Let's just, for sake of time here,
push into an ETL scenario on
how we would do this with change tracking.
Here is an ETL scenario and what I've done is I've
linked the actual changes to that table.
Let's say I wanted to do an ETL process
right now and I run this it's going to say,
oh, here are the Product ID, the name,
and the list prices of the changes that happened in
that database and you can see that this was a nightly batch,
obviously being might take the whole thing but
now I can run this on shorter periods of
time and just pull over only the rows that have changed.
Now going back to that column scenario, I can say, hey,
would've find that have a particular process
that I only want to get
out only when a particular column has changed.
Well, here again, I can use that column
trapping isn't that change tracking
is in column mask function and say,
okay, this particular process,
only once a node changes,
when the size column has changed,
I run this and there it is there's
that signs change because we know that,
that we're looking at back column that
was in the cyst change column.
Very powerful built-in features,
very powerful features overall for tracking changes
when you don't need
the historical data like you normally
have with change data capture.
>> Nice glue. This is super-interesting,
great demonstrations that really helped me
understand how it works and how I might use it in my applications.
I think our users probably found it useful too, as I close,
do you have any tips or tricks for folks who
are just getting started with change tracking?
>> Yes. Just to remember,
a change tracking will work in
any version of the SQL database all the way from edge to
the Cloud from 2008 and beyond and
it's very easy to get up and running it's too coming on.
We'll also have all of these strips that
we ran today we'll put them on GitHub for people to see.
We have some learn modules actually that we can link below in
the description that you can look at and go
run so that'll run you through doing change tracking.
Just to remember, it's very easy to use
it's great for our ETL processes, for apps,
sync applications, some data restores and there's
very low overhead it with absolute no custom coding.
Everything's built in the cleanup is automatic it just allows you
to track these changes and you're
off and running with two simple commands.
>> Awesome. Well thanks Brian again for coming on.
Data Exposed to our viewers thanks for tuning in.
If you like this episode, go ahead and give it a like,
leave us a comment and let us know what
you are going to use change tracking for,
check the description for links to learn more and
we hope to see you next time on Data Exposed.