foreign
last few sessions today the next one is
Dan fan
um he's going to be joining us remotely
here we go by the power of Technology uh
so Dan's uh keen on applying data
engineering and machine learning tools
to solve real world business problems
and there's a open source contributor
and today he's going to be talking about
building trusted data pipelines the
dagster DBT and duck DB which are three
Technologies I'm personally super
interested in
um so would you give Dan a warm welcome
please
thank you everyone can you hear me
um
yes
we can thank you for coming to my talk
on building 3D tested data bylines with
lobster DBT and duckdb and the 3D here
is being uh the inductor the DBT and the
DB and um sorry I'm not be able to join
the conference face-to-face and also
sorry my voice is not good today
um other than that the in today's slide
and the code demo is uh available online
on this uh GitHub link that's you later
you can get and play around if you have
time and
um a bit of myself I'm currently working
as a senior data engineer at investment
data system team at hesta we are a super
fun and I'm also open source contributor
and loop camping
um previously I was a PhD candidate at
Monastery University doing machine
learning for transport and had been
working as a data analyst database
designers and and web development
and um
obvious and opinions uh in this talk on
my own
all right so in this talk
um first I will talk about the construct
of data engineering and why we we need
does the database and then it will uh I
saw on demo and how we can implement
this in
using the 3D stack
so
at the data Engineers our job is to
build highlight data pilot is it it is
it LT or ETL data file like the general
idea here is we move the data from
different sources and then we extract
and load it to a place it whether it is
a data like a tree or other storage and
then from that we can move it into a
data warehouse and in that we can uh you
different tune to transform our data to
a cleaned from draw data to clean data
and after we have the clean data we can
save it for the downstream application
like building bi dashboard or using for
our machine learning model
and when we deployed our data in
production we want to strengthen out
data pilot right because we know that in
the future
will likely to be leaked or even break
so does work
we really in this robust and reliable
reliable data pilots and we have
many interesting talks on these topics
on how to be a robust or reliable data
by like using either uh
aperture spark we have a previous talk
on buy Spot interesting top and
there are also other topics on the using
data break or ship Lab at Etc and this
is very interesting talk and I recommend
you to watch it later if you have time
um
but and the lesson learned from this
talk is that we need to test our data by
lines and this this is very important
and also there's we also need to apply
the depth of practices like code
versioning with GitHub for example and
address this CI CD
or and also we need to add the
infrastructure ad code like we can use
terraformation not for that and
using a container environment
Docker so we have a similar environment
for development and for production
and we also had HR thing like data Linux
or data contract and hand data contract
from the Upstream
um
but in my opinion the robust data pilot
is not enough
yes robot robust and reliable data is
we absolutely need that but in addition
to that we also need to have a control
our data quality
because at the end
what we building our data or data
warehouse is we building the data and we
serve the data for our consumer for
people like data analysts or bi
developers and data scientists
so just this data pilot is
in addition to being robust it also need
to focus on data quality
so this is the
an example like for when we talk about
robust pipeline we talk about
engineering Focus that we want to
strengthen our
pilot to avoid lick or break in the
future when we deploy it why does the
chapter pipeline we also need to uh
focus on the data consumer is if data is
like water we want to have a good or
clean water or clean data
so if
we work at a data Engineers often you
will
hear about the question from other
people from later other list from data
scientists about different data quality
questions
does anybody asked you a lot of
questions on data quality related things
like either stable
compared with the Upstream table
do we have to have this column always as
data come from or how how recently our
data arrived and or either column of
delivery is in expected rain or it is
summarize start statistics for that
column we may send
Etc and
this is really difficult for data
Engineers to answer the question if we
don't have a good data quality testing
and measurement in in place
so is it really critical to test and
measure our data qualities if we need to
have just we need to test our data
quality
so if we look at this modern data stack
I would say with this is a an example
but you can see that we will have a
injection layer using either python
spark or other tool like air by
functions and we use this injection tool
to move the data for different socks
here into a data warehouse data
warehouse and the data warehouse can be
maybe snowflake relative data break or
even other as was enough
and in when we move the data here we can
if tune like DVT for data transformation
and we can transform the data from The
Landing or the raw layer to different
kind of like sticking and mod layer and
the model will be the clean data
that can be served for VR tune like top
lower power bi or can be used for uh
data science or machine learning
building model in python or r
and we can also have some orchestration
tuned like airflow or Darkstar or some
observability like soda or Monte Carlo
here and but the core thing is here as
you can see that is the injection layer
generator Warehouse layer we where we
have the um for computation using
different engine here with the tool for
data transformation and then we have
some orchestration so in this Workshop
we're going to give us a small demo
using for ingestion we will use Python
to move the data from
Amazon F3 those uh a local folder and
then from that we import into a dark DB
database
and
then we will DBT to transform the raw
data to States in layer and then to mod
layer
and inside this one we will use dbth UT
and DBT expectation which is two DBT
packets that you for testing our data
qualities and in addition to that
um
we have uh the duster here for data
augmentation and the good thing for
adapter here is if you see in the DVD
here we do that information in SQL and
in ingestion layer we do uh
parallel in in Python and then the
doctor can work with both of that to to
convert python Pi lie and the
transformation by line in SQL here to
call software asset and later I will
show you uh that in detail
uh
so what is that the 3D we have here the
first one is the wp is in in process SQL
database management it is serverless so
if you know a SQL light it will be
similar to SQL it is serverless so we
don't need to build any kind of server
like progress SQL or MySQL and that is
great because that may be like vector
like column style so it will be fast and
very suitable for analytics workflow
and we can use that could be at a small
Data Warehouse in our case and sit on
top of the WB is DVT which we can use
for data transformation and from that we
if different DVT packet for control or
test our data quality
and we also have Daxter which is a
software defined asset which can have a
two uh manage and Converse all of this
one into a very nice list of data linear
and Global assets which I will show you
later
so all the testing data quality
um
from the high levels we want to transfer
either between the table we might need
to test the relationship between table
for example it is this customer by the
byproduct so
or the relationship between the table of
weekend test if the raw tables with the
clean table is if they have the same
shape or have relationship with that
within one table we can set the local
column cow column axis Etc and at column
level we can test it depends on
different kind of
data on on on that column if it is a
test we can test the pattern of that if
it categorical Text data we can test if
it have belong to a list of accepted
value if it numerically that we can test
if the mean the mass median or the
outlier as well and if it we have the
date in in the in the table we can test
the Regency the range of the other
Square
in in order to do that we can have
generic tests if this is the column in
North noon or is in the unique or not
so we can't use this Liberty definitely
packet to test all of this guy of uh
things in in
our data parolise
so
the first basic one in DVT is the ship
with the four genetic tests
value and relations so here is one
example how to uh to write a test
so the model here the name here is is
the other table and it is what we have
column
namely order ID and it needs to be
unique and not known for for that
primary key
and another column may cause status and
just stated here for the orders it
should be in one of four value here
and the on the third or here a customer
ID is good we have relation to the other
table of customer and in the ID column
so this is one example of the four
generic test in DBT
um
but if we want to test more complex
things we need other packet liability UT
and DBT expectation
so
to do that we inhibiting we just need to
include the packet into a bucket not the
move file and then we install it using
the DVD depth command and then we run
the test using DVD test
um
and for the Liberty UC this this year
Bucket from the Liberty team and they
ship quick
more different tests like
um we can have a generic that like equal
row car equality uh Outlet one of
constant and we can also have the
northern room proportion which is quite
useful
um and we can also have different uh
relations and evenly compilation tests
here
as you can find more information or the
link I put here and also either
reference letter as well
for DPT expectation this is the idea is
we are similar to
a great expectation bucket in in Python
and they divide it into different group
of tests like for table shape we can
test this
if the column exists in in the table we
can test the column cow Roka
Etc
and for the missing value unique value
we can also that they have a various
stats here as well
um for if it is a numeric column we can
test the value of the column is between
something as well
and for the string or the text column we
can test if it's a matching some kind of
pattern and they also have some
basic that thing on distribution like if
the column value within ends standard
deviation which can be used to test the
outliers in the in the numeric column as
well
so there is also a lot more information
in the the documentation and I recommend
you to look at it at a later time
so what we got out to be we're going to
view this by like where we track
the data from battery so the the data
here we have three tables the payment
the order and the customer and we use
Python to a chart that's one and input
into a raw table into that DB and from
that we will DVT to transform that three
table into a stapling layer and from
that sticks layer we transform it into
the the mod layer which is the order
stable and the customer stable at the
end
actually how it look in the data asset
in in Industry thus we can view both SQL
Pipeline and and python by light
together
and this is a closer view I will show
you later in the demo as well and you
can see that this one this part is from
DVD so it have a icon activity here
all right so now is time for terrible
so I will switch
to the
to the code
so
this code is available on GitHub and
found that you can file the link to the
slide as well and just a remote reminder
just either Pi like we're gonna build
using injection python when the shop
into lucky B and transform it and then
you deducted for quotation
so on the left here if you see that
um
we have to main folder one is for DBT
and one for Dexter and first let's go to
DVT so
so the data we have three table raw by
Monroe other customer after we download
it it will be inside this one just have
a quick look on this uh three table
um this one is that customer which ID
first name that name we have the order
for that the customer
which if the ID and then order ID or the
date and then we have the payment for
for this order
and inside the DPT project
um
we have the Define the profile which is
defined in the the profile here as you
can see it will be here and then it will
link to the uh the dot DB
um
database which is actually one file this
lead file which is this file JavaScript
db.db and later we can also have
different targets for production and we
can deploy it with the other Big Data
Warehouse like snowflake or redshift
and at mentioned before for the testing
our data quality we is so a couple of
packaged liability UT and DVT
expectation in the package.gmo
and let's go into the model
so we have the uh this texting where we
have that three sticks in uh table here
and or model and in this schema we can
Define
the testing for our quality test live
for example customer ID is need to be
unique and not known the code of ID in
Injustice in customer tables all right
and
like the stated for that
sticks in order table which should be in
in one of these five Value Place chip
completed
Etc here
similar in the market we can also
control our data quality for example
here is another test for the first name
of the customer table it it needs to be
not known so at least 98 so that is a
percent of that first name column of the
customer table is is it should be not
known
and
five others uh that's here like the
here I want to check if the that
customer table
here with a similar shape at the states
in customers so this one is have this
sorry a similar row count
I also want to check if the table have a
seven column
so now if we want to test it we just
individly we just run DVD test
and it will uh
run
all the tests in our just edify in our
schema here
and you can see that is we have 18 tests
here and it pass so
for example if I change this one from 7
to 10 column
and if I run DBT test
and I select
the customer table
it will test only a random test only for
just a customer table and you can see we
have six tests and we have one Arrow
here which is the
um the cow equals 10 here which actually
seven right so if I change it back to 7
3 test customer again
it should uh
success for the sick test
yep
and
another thing here is we think in the
good thing in DVD weekend see the
generate our uh
our documentation and graphs and
after you lose generate the
documentation we can serve the the dock
by the way all these command AI
already put it in the
um
in the readme here as well
so if you see that
you see on the browser let's
go I think
you're done can you hear me
if you click click on jet link it will
show us the documentation
and it will so it's the um
that the table with information here as
well like
another thing is it can also help us to
uh
items if we click on this one
and click on the graph we can solve the
the byline here
but as you can see this the pilot here
only is a sequel in DBT and we cannot
see the Upstream pipeline using python
right so that's what we add Dexter so
let's bite to the second folder here is
Daxter DVT zapper and in here
um let me go to
CD Dexter
and in here
um
we Define the software assets and
the main
code in here but before that let me run
the the dust darkness which will
run the the data server
and
inside the doctor here we we can Define
the asset and we have two main Thai
right the first one is the uh the DVT
which we can load is
by differences it's load asset from DVD
project and we can specify the the part
to the to the father of Liberty which is
this one
uh sorry what is this one the DVT is up
for software and then after that that's
the we will get the metadata from DPT
examples of project and and it put it at
an asset
um another part here we have the the
python which is uh this will load python
from F3 and import into docdb which it
defines In The Raw data here
and in this one you can see that we have
just
a simple code that I tracked the raw
data for example if if they roll it
order here we read the data from F3 and
then we save it in here and then after
that we will create
um
this router folder and insert it into
the uh the documents file
and
inductor we just need to add a decorator
called assets and it will load that and
consider all the pilot asset
so after we run the the dark is we can
view it in the website here
I think
and if we click on
Global asset
you can see that
we have
all our data pilot here with both Python
and SQL pilot in DVT and we can't
also materialize all this table which is
actually will run all the byline in in
this uh
in this one in this graph and then
we can see the run as well
also here if we click on different
tables they are start to materializing
and we can see all the detail and
metadata and other information here as
well and they also have the the rounding
which for different steps that we
um we have
and
last
is and why we're waiting for this one to
uh finish I think it will need a couple
of minutes for it to finish
let's back to the slide
so I just saw you just one example
a way to implement our data by line
uh so with both Python and SQL Pipeline
and add different tests using um DBT ift
and DVD expectation to control the data
quality
but at the end building trust is your
journey and
we will need to have a good roadmap and
especially the most important in my
opinion is very strong leadership with
the right data strategy and that as
previous speaker also mentioned about
the data contract and we need to buy in
of different data stakeholder and data
consumer
and in the technical perspective we also
need to have well-designed data schema
and we need to control the data quality
from Upstream on the way in the data
warehouse or data Lake and on Downstream
as well and a couple of differences that
I use for the talk and thank you very
much for uh for that and
by the way our team are hiring for data
and engineer position
thank you and let's
come back to thank you so much Dan I'm
more here to see if
the doctor already finished so if we
finish it will be buttered right here
yeah like
that's great thank you so much
these are all tools I've been really
intrigued
DBT especially has been one that I
haven't managed to look into as much as
I'd like to so thank you so much for
that that was great
um obviously we're out of time now so we
don't have time for questions but the
next talk will be at
um in 10 minutes at 10 past four thanks
a lot Dan and we'll hopefully see you in
person sometime soon see ya