In this lesson,
we are going to talk about "Optimizing Pipelines
"for Analytical or Transactional Purposes".
Now, before we get started,
just like a couple of lessons ago,
this is going to be a little bit of a weird lesson.
And the reason it's going to be a weird lesson
is because there's not a whole lot of practical application.
We're talking a lot more theory here.
But it is on the DP-203,
so we need to make sure
that we cover it, at least at a high level.
So with that little caveat,
let's go ahead and get started.
In this lesson, we're going to talk about
online transaction processing
versus online analytical processing, OLTP versus OLAP.
Now, this should be familiar to you,
and this should be a little bit of just a basic refresh.
We are also going to do a slightly deeper dive
on solution choice between those 2.
In addition, I'm going to give you a quick recap
of basic optimization techniques, including Azure services,
schema selection, data cleanliness, and automation.
With that, let's start talking
about online transaction processing
versus online analytical processing.
So, the first thing to look at is queries.
Simple queries generally fall into transaction processing,
whereas complex queries generally fall
into analytical processing.
So if I'm just looking at sales by a product,
that's probably going to be transaction processing.
If I want to look at sales trends
over a range of products in stores,
that's probably analytical processing.
Transaction processing, generally faster to load.
Analytical processing, generally slower to load.
And this is due to your schema differences.
In addition, transaction processing, generally day-to-day,
versus business decisions are analytical processing.
And that kind of goes back
to that simple queries versus complex queries.
If I'm just doing day-to-day transactions,
that's going to be OLTP.
If I want to get business analysis,
that's going to be more complex queries,
analytical processing.
Transaction processing, typically less than 10 gigabytes.
Analytical processing, typically more than 1 terabyte.
And again, this is a guide, not a rule.
So this is going to help you to understand
the differences between OLTP and OLAP.
With that, let's go a little bit further
into analytical processing.
First, let's talk about a couple of scenarios.
The first reason I would use analytical processing
is read-heavy scenarios.
So again, this falls into your analytics.
If I'm putting data in, and then I'm pulling
that data out a lot in queries
to figure out different ways to look
at that data or slice it,
that's going to be more analytical processing.
In addition, analytical processing helps me
to look at patterns, trends, data exploration.
So for instance, if I'm a trader,
and I'm looking at technical analysis,
I'm going to be looking at lots of different data points
and trying to pull meaning
by building patterns and trends out of that data.
Very analytical. Same thing's true with customers.
If I just want to take a look at Sally, for instance,
and figure out that she bought something last week,
well, that's transaction processing.
If on the other hand, I want to understand
Sally as a customer profile,
and I want to look at multiple metrics to figure out
if Sally's spending is going up or down,
that's very analytical processing.
Synapse is generally the good fit
for analytical processing in Azure.
So when we talk about the DP-203,
and we talk about OLAP,
we're probably talking about Synapse.
This is because it has a columnar data format.
It also is very good at handling large data sources.
It has integration with Databricks and Power BI.
So again, from that analytical processing,
we can pull data in, we can transform it,
and we can sync in with Power BI
very easily using Synapse.
Finally, performance is very important,
because we're running complex queries.
Synapse is great because it has parallel processing here.
So that's going to help you when you're looking
at getting that peak performance.
When we talk about online transactional processing,
this is not likely to be on the exam.
This is because OLTP typically runs SQL database,
which, SQL database is not on the DP-203.
So OLAP is very much in the wheelhouse of the DP-203.
OLTP, not so much.
So let's talk about just some basic optimization techniques
and kind of recap that.
Azure services. Choosing the appropriate pipeline steps
is absolutely critical in choosing your Azure service.
This is true whether we're talking
about doing light transformations just in Power BI,
or whether we're doing much more
complex transformations in Databricks,
whether we can run everything
through a Data Factory or a Stream Analytics,
or whether we need to build this in our Azure Synapse,
and we need to sync several services there.
Choosing the appropriate pipeline steps
is very important to sit and think
through the different options that you could choose.
Next, schema selection.
So snowflake, star, are we doing something else?
Are we doing a galaxy schema?
Understanding your schema selection
is also going to be helpful,
and a lot of the schema selection comes down to:
how is the data going to be coming in?
How many times, if at all,
do you think the schema is going to change?
What types of queries do you think
you're going to be running off of that schema?
And how important is data size?
Third, data cleanliness.
And this goes over here to the picture on the right.
Don't make a data dump.
You need to make sure that you have proper storage.
You need to make sure that, as the data comes in,
that you are storing it in good places,
you don't have duplicate data,
you don't have errors in your data.
That you are transforming if you need to,
so this is the ETL versus ELT discussion.
So when we look at transformation,
are we going to transform after loading the data
or before loading the data?
How are we going to use the data, right?
So think about data cleanliness.
And then finally, automation.
You need to make sure that you have a test
and dev environment, or development environment.
And when you look at automation,
you want to make sure that you are testing your automation,
and that you're using automation
in order to optimize the environment itself
and the movement of data throughout your environment.
Some key points to remember from this lesson.
Take a holistic approach.
Pipelines are a combination of multiple activities.
You need to be thinking about security,
you need to be thinking about ingestion,
you need to be thinking about transformation,
data movement, automation, right?
All of those things fall into your pipeline.
So make sure that you're taking
a holistic approach to building pipelines.
Remember, SQL database is not on the DP-203.
OLAP is likely Synapse.
So just go for the basics here,
you don't need to dive into the depths of OLAP.
You should have a basic knowledge
and know that that generally ties to Synapse.
If you've done that, you're probably pretty good.
All right. That is it for this lesson.
I'll see you in the next.