Actually, before I click new
let me come back here and let's
actually rename the source to the
way we want the source named here.
And this source is going
to be called TripFare1
The other is trip data.
We're going to call this TripFare1.
I'm going to copy and paste.
Now let's click on a new data set.
OK, this is going to come from our Gen 2
storage that comes with the workspace,
so we're going to select that
we're going to click continue.
This is again delimited text.
And for this data set, we're going
to call this one as trip_fare_1,
so we're going to copy and paste that.
The link service is going to be
linked service that was generated
for you automatically. Linked
services within data integration,
data engineering, synapse analytics
define the connection and
defines the connection details,
including your authentication
to connect into your datastores.
So this is for the Gen 2
accounts within the workspace.
Alright, so I'm connected there and I'm
going to select on the file path here.
So click browse and this is the
same file path that we had in
the storage explorer earlier.
And we're going to click on taxi data.
Now for mine,
I believe I mistakenly created
an additional folder in taxi data.
You should probably have one taxi data
in yours. If you did it correctly.
Not like I did, but that's fine.
We're going to click on trip_fare_1.
Click OK. Import schema from connection
store and make sure again you turn on
1st rows header because we do have
headers again in this file.
This way Synapse can now pick up the
headers from that source file.
OK, so we're back on our data flow designer.
If you click on projection
you should see that again.
So there we go. So there are colonies.
Same thing here.
This is the limited text.
Let's click on detect data type
and let's ask synapse to
infer those data types
for us and there it goes.
Now we have those data types correctly set.
OK, so you should look like this.
I'm going to go back to the
document just to review.
We created two sources
on your data flow designer,
those are both from the CSV delimited text
files that we uploaded to our workspace.
And that is acquiring data
and integrating it
into your sense workspace.
That is one way to do it,
there's many other ways as well,
but this is the way we're
gonna do it for this tutorial.
Next, we're going to do is
we're going to do Step 3,
which is explored the data.
So we can make sure you have
your dataflow debug turned on.
You should look like something
like this within your
dataflow designer in Synapse.
You detect the data types.
And now we're going to
move on to this next step.
So the first thing we're
going to do is we're going to
explore the data.
So we're going to click on the
data preview tab for each of the
sources for the trip data source,
so we're going to look at these statistics
for rate code, and then for trip fare,
we're going to look at the
statistics for vendor ID.
So statistics is really looking at
the properties of specific columns,
the values that fall within those
columns in your data within Synapse,
state engineering and data integration.
So here in my dataflow I'm going to
take the TripData1, the first
source, going over the data preview.
Let's click on refresh.
Now what this is able to do is this
is able to take the data frames that
are created for you in the back end.
Remember this is a low code,
no code environment so you're
not writing any code here.
But this is using Synapse Spark,
and so this is giving you a
sneak peak of what is in the
spark cluster at this time for this data.
So this is a look at the data.
This is from the data frame and
we wanted to look at this case.
Let's see this suppose I believe rate
code is we want to look at this.
Let's click on rate code.
You can make this a larger screen if
you click up here and expand. Rate code,
and click on statistics where we going to get,
by the way,
you can hide this properties pane on
the right hand side if you click up here.
Hide properties.
So we can see the statistics from that
column and now this sampled 1000 rows.
You see the total rose right here.
And this will give you the details of
the values that fall within that column.
Then you can see that some by far the
most frequent rate code is 1 in there,
so this is numeration does not have any
specific value to it.
This is just a numeration,
but if you look at the rest of
these statistics you also see
that the maximum is 5,
so you have values from one to five in here.
Now the reason why it's sampled at
1000 is if you click on debug settings
up here you'll see that the default
value for the row limits is 1000.
Let's bump this up to 10,000 just
at a zero to the end of each
of these new debug settings.
Click on debug settings,
change the row limits.
I'm gonna change them to 10,000
on mine, click save and then go
and refresh your data preview.
Now what's Synapse is doing here
is going back to the spark cluster
and saying that give me 10,000 rows
to sample instead of just 1000.
So you see the total number
of rows now is 10,000.
If we click on statistics.
On break code this time.
We're gonna see a similar results,
but you'll see that now it's
sampling out of 10,000.
So again, the values are one to five,
so we did not grab any additional
values in that sampling.
But again,
we see that one is by far the
most frequent value within there.
Let's do the same thing for trip fare.
Let's go down the trip fare.
Click on data preview and refresh here.
So please feel free to explore this data in
any other way or means that you'd like to in
the different columns and different values.
I'm just giving you an example.
I believe for this case I said vendor
ID, yeah, vendor ID for this one, so we're
going to click on vendor ID and statistics.
Go and explore the data on your
own while I'm doing this.
This is just one example.
Let's see what vendor ID comes back with.
So again 10,000 rows.
We also said that
limit for this second source.
Each of the - you see there is no
variability in the length of the column.
Each is 3 characters long.
Far and away we have VTS is the
most common value and 1.7% is CMT.
OK, so I'll let you explore
some of that data on your own and
let's move on through the tutorial.
So that was what we want to do
now that we've explored the
date a little bit is,
we want to take out this CMT.
Only keep those VTS rows, so
the CMT we kind of treat that as
if because the values were so
minimal within that data set.
Let's say that it's treated as if it
wouldn't normally want to take those out too.
And I just maintain keep the vast majority,
which is CMT.
So we're gonna do that in the next step.
We should also observe that the
Medallion and Hack license will make a
primary key, foreign key for a Join.
So the reason you can see that and how
you can see that it's because they
are goods would look like a unique
identifiers for each of those.
These would make good candidates for
primary and foreign key relationships in
order to join these two data sets together.
So you should recognize that
also by exploring this data.
And with that,
this will let you do now is to combine
these based on these two columns.
So let's do that for this next step,
we're going to Step 4,
which is clean and prep the data.
And the first
action within this step is to join the data.
So the way you do that is you go in to take
the top source that the trip data source,
and then you're going to join the trip fare.
I'm gonna use an inner join.
They're going to use those medallion
and hack license unique columns
as a way to do that. So click on trip data,
click the plus button right next to that.
This is the construction paradigm that
you'll find in data flows within Synapse,
which allows you to construct your data
transformation logic in a visual manner.
So click on plus and if you like,
you can just search join,
but you can also look also peruse
the entire list of transformations.
We'll just click on join right here.
Going to make this an inner join.
They want to join that to trip
fare so under right stream
which is empty
right now, you're going to pick
the other source,
which is trick fair one.
So Synapse will draw the line
between the two transformations.
And you've defined it as a joint.
I need to find the joint conditions,
so we're going to say inner.
And we're going to join in those
two columns that we saw were
unique by exploring the data.
So left hand side is medallion,
right hand side is also medallion.
Click on plus to have a
secondary condition as well.
Also use Hack license.
So we're going to join on Medallion
and Hack license between these two
datasets. If you'd like to see the
results of this inner joining,
click on Data preview and
refresh here in the join.
And you'll see the joined data here,
so you'll get all the columns
that's going to result from this join.
Now. What we want to do for creating
the rest of the data analytics pipeline
and data flow is to after the joined
is to add a filter transformation.
But now we're going to filter
out the vendor ID CMT,
and we're going to use this
expression right here called Vent,
which is as vendor ID not equal to CMT,
so this is how we get rid
of that anomalous value.
Back on our dataflow, there you
see the results of the join.
After the join click plus again,
let's add a filter.
Filter transformation is a
real reducing transformation.
So what we're going to do is we're
going to put the condition right and I'm
going to paste that right from my Word document.
Just take out some is extra characters
and if you click on open expression
builder you see that there is a very
rich environments that you can use to
build your transformation logic and
you can use many different function
expressions that are here and if you
click on functions you'll see the whole
list of function, expression functions
here avialable to you.
You can add parameters.
The input schema coming in from this joint
is available to you as well to view here.