Wait, what?
Section 10 recap.
We have actually reached the end
of all of the content sections for this course.
Congratulations, this is a milestone.
Before we finish section 10 though,
let's go in and do a brief recap.
In this section review, as in the others,
we are going to be reviewing all the topics in section 10.
If something doesn't look familiar,
jump back in, watch the lessons.
It's going to be focused on the 203.
And since we're focused on the 203,
I've tried to pull out pieces of information
that I thought were the most important
for the certification in this lesson.
Finally, if you don't know something, review.
We have a lot of blended concepts here at this point.
So you might need to jump back,
even into a previous section, if something is unfamiliar.
All right, let's dive in.
Auto optimization review.
This was an Azure Databricks tool that we talked about
that lets us write to a Delta table.
What it does is it auto-compacts small files together.
If you remember, that's going to be very helpful
if you have lots and lots of small files.
This is the traditional write,
writing down into the partitions.
And if you remember, the auto optimization tool
puts in a separate step that compacts those files together,
so we have less files being written to our Delta tables.
We also talked about hash distribution.
We talked about distribution quite a bit, actually.
The hash function lets us use a map, essentially,
to map the data that's coming in
and put it into the individual partitions.
Essentially, it's a fancy math algorithm.
And if you remember, it kind of looks like this.
We've got our input data, it goes into our hash function,
and then it gets distributed into those different buckets
over there on the right.
Round-robin distribution, on the other hand,
is first random, then it's sequential.
It gets rid of that hash function,
that purple box over there on the right,
and allows us to move data into the system much faster.
However, it's not mapped,
so it's going to be a little harder to get it back out.
So round-robin: quick to load, slow to query.
We talked about setting shuffle partition sizes.
The challenge was finding that right shuffle
partition number, especially as that number might change
as we move through stages in a query.
And so we can use our adaptive query execution, or AQE,
to help solve for this problem.
We set the initial shuffle partition number,
AQE then can help to adjust that
as we move through the query.
So this is what you need to do in order to set
your initial shuffle partition number.
That was the syntax for it.
Then we talked about some optimization steps
that you can use for Synapse.
First was the query plan.
Don't forget to turn on statistics.
You need that on.
That's going to help you as you optimize your queries.
We also looked at reducers and combiners
as a solution for optimization.
The recursive reducer,
the short of that, that you need to remember
is parallel performance.
You do need to turn it on.
That's what the code looks like.
And you also have row-level combiners,
the short of that again, parallel performance.
This is the code for that, over there on the right.
Next, we talked about optimization tips
for your environment.
Remember, we talked about ARM templates,
used for replication and control.
We talked about not putting everything
in the same subscription. That's going to be helpful
for our defense in depth concepts.
It's also going to be helpful for compliance and management.
And we talked about Azure Advisor.
I actually jumped in and showed you where that was.
That's something that's going to be helpful
for you just in everyday work.
Jump in, make sure you're looking
at that Azure Advisor periodically.
It is a must-use.
We talked about the basics of result caching.
So, SQL pool auto-caches are query results,
and we can use that for repeated queries.
If you remember, this is a persisted cache
that helps us with our query performance,
and improved query performance means we're using
less compute and less cost.
But we got to turn it on,
and we can do that with a user database or with a session.
And remember, we don't cache UDFs, user-defined functions.
We don't cache row/column security.
We don't cache rows larger than 64 kilobytes
or databases over 10 gigabytes.
And we don't cache built-in functions
or runtime that isn't deterministic.
So we talked about all of that.
That's actually a pretty important concept
that you need to remember as well.
We talked about OLTP and OLAP.
Quick question for you:
which one of these is on the DP-203?
You should have said OLAP.
Online analytical processing is a part of Synapse,
and that is on the DP-203.
OLTP, online transaction processing,
is a part of SQL database,
or at least is used most frequently in SQL database,
which is not on the DP-203.
So I would assume that any questions you get
are probably going to be around OLAP,
not transaction processing.
But the difference is OLAP is for complex queries.
Slower loading than OLTP.
And remember, that's because of that schema.
We also talked about OLAP being used for business decisions.
We talked quite a bit about being able to look
at, like, customer profiles or things like that.
And we talked about OLAP
being more than 1 terabyte in size.
So keep those basics in mind for OLAP.
We talked about Ambari UI.
This is a good way to troubleshoot
when we're looking at HDInsight clusters.
We look at configuration settings,
cluster health, stack and version.
We talked about the log files that you need to look at.
We talked about configuration settings
that you may or may not have set, but is worth a look at.
And then we talked about reproducing
the error on a new cluster.
In addition, we talked about taking slow changes
as you move through, and starting simple
and moving more complex as you go.
And we talked about tracking applications
in the Spark UI using DAG visualizations.
So we can track jobs.
We can pull detailed information on our submitted jobs.
We can track executors.
We can break that down by ID,
get task information on what's going on,
and we can look at memory and shuffle usage.
And then we can look at those stages,
which is what you're seeing over there on the right.
So we can look at our shuffle read/write,
we can look at the duration and I/O,
and then we can see the DAG visualization
of each different stage.
What's DAG stand for?
Do you remember?
Directed acyclic graph.
In summary, know shuffling and distribution.
Review the round-robin and hash distribution specifically,
but you do need to understand shuffling and distribution.
It appears in quite a few places in the exam requirements.
You should review the code from this section.
I think that's actually very helpful.
Again, don't memorize.
But you should glance through the section
and be able to pick out the pieces that we're talking about
and the concepts that are important.
Don't forget the labs.
I've said that a couple of times.
You'll need those for the exam and the career.
With that, hey, congratulations again.
You have finished section 10.
On to the last couple of videos, where I give you some tips
and tricks for preparing for your exam
and your new certification.
All right, congratulations again.
I'll see you in the next section.