foreign
ETL pipeline using databricks
ETL for large Enterprises today is
extremely messy and complex
they tend to have a variety of different
data sources from streaming Cloud
objects to relational databases and
on-prem systems in multiple different
formats like unstructured
semi-structured and structured data
to get value out of all of this
information and to implement common use
cases in bi data science and machine
learning they end up having to stitch
together a multitude of different open
source and third-party tools and
softwares
this approach has several challenges
with it
companies end up running into Data
quality issues they struggle to perform
common DML operations like deletes and
merges struggle to govern all of this
data in the cloud and find difficulty in
switching between batch or streaming
modes based on what kind of requirement
a use case has
databrick simplifies the process of
building an ETL pipeline by bringing
everything in one lake house platform
using a medallion architecture
in this architecture in the first part
we land all of our raw data into into a
bronze layer now this data could be Json
files it could be csvs could be text
from any cloud storage or from any
on-prem system or data warehouses once
our data is there in our raw format we
would then apply some cleaning and
transformation data merges to create a
silver data set
we can now combine multiple data sets to
create a final gold gold layer data set
which can then be used for which is ml
ready it's clean and can be used for any
sort of dashboards or for machine
learning models now for the purposes of
this demo I'm going to be pulling into
data sets I'm going to pull in some
customer Json files that have customer
information like email and their names
and then I have some CSV files in a
separate location which has test band
information I'm going to combine all of
that data to create a clean data set
that can then be used to build a
dashboard
now the first step in this is loading
our data from a blob storage
so first step I'm going to create a an
empty data table where I'm going to land
all of the data that's that's going to
be ingested now to do that I can write a
SQL command and all I have to do is
include one keyword called using Delta
and some additional optional parameters
if I want to create an empty Delta table
I can then use something called
databricks Auto loader to start
ingesting all my files from the cloud
storage into this empty table that I
created now Auto late Auto loader lets
us efficiently scan a cloud storage
folder for when new data is arriving and
only ingest that new data and this can
be done in new real-time or it can be
triggered in batch mode so right now
I've just triggered it once so what I've
done here is I've written a python code
to to essentially bring in this data I
want to call out some some things here
that you can see so I just specified the
format in which my data is there so it's
in a Json file and I have my data
located on an S3 bucket in a particular
directory which I specified here and I
want to save all of that in my user
bronze table that I just created when I
run this command my data starts getting
pulled using databricks Auto loader from
the cloud storage into the table
which I can then efficiently query using
simple and common SQL commands
and we can now see that our Json files
are now available to us in a nice table
now the next step in our ETL process is
to clean and anonymize this data so I'm
going to use the same function that I
used earlier the databricks auto loader
and now I'm going to instead change a
few things here now I'm going to pull in
the Boolean data from the table that I
just created the user bronze table and
I'm going to do some Transformations on
the data so I'm going to encrypt the the
email ID using using an encryption
technique I'm going to change the date
formats and make sure all the date
fields have the right date formats and
then I'm going to store that in a user
silver table now once I do that I can
again now query that table using SQL and
now we can see that our email ID is
encrypted and our data has been
anonymized
now the next step is I want to now pull
in spend information of those customers
so like we discussed earlier I have
different CSV files where we have
information around what kind of age the
customer has and what can what kind of
income pattern they have so again I'm
going to use databricks Auto loader to
ingest these CSV files I want to call
out a couple things here now that we are
using CSV files all I have to do is
specify the CSV parameter here and
instead of you loading from an S3 bucket
now I'm going to be loading from the
databricks file system where I have the
spend CSV folder
and and then when I run this command I
can again get all my spend data
available here
now once I have these two data sets
available to me the user the user silver
table and this pen silver table I want
to create a final table where I can have
all the information around the users and
the spend in one place so I'm going to
use again now the two tables that I just
created and I'm going to join using Auto
bricks loader autoloader on the ID field
and then I'm going to save that as a
user gold table now once I do that I can
see from here that all my data regarding
my customers their email ID their first
names and their income and age are all
available in one place in the gold table
I can start running common DML
operations like deletes and merges on
all of this data now which is which is
stored on the cloud so I realized that I
had to delete data from a certain date
so what I did was I done a ran a common
SQL delete from command and I can see
that a bunch of rows were deleted
our data set is now ready to now be
consumed by SQL dashboards or ml models
now this step once you've created this
entire pipeline this notebook I want to
be able to automate it and run it let's
say every few hours or run it once a
week now how do I do that I will go and
click this create button on the left
panel and go and hit job
I can now start creating
a pipeline and the first step I'm going
to do here is I'm going to give it a
name so I'm going to call it a simple
ETL pipeline
version one
and because I wrote all my code in a
notebook that's the type that I'm using
but I could be using
or or building these pipelines based on
a python script on SQL or Java jar files
I could be chaining those together and I
could be pulling in information from a
git server or an external git repository
that I have so all all that flexibility
to architect your own pipeline is
available here because I used a notebook
I'm going to go and browse to my
notebook
and I'm going to make sure that I'm
using a cluster that I created earlier
and I'm going to go and hit create
now
when I do that we see a pipeline has
been created I'm going to go ahead and
hit run and that will trigger a run here
so now what I'm going to do here is you
can see that there's a way for us to now
chain in multiple notebooks in all sorts
of different ways to create really
complex pipelines all in one place using
any site any sort of data sources
let me go back to the jobs and let's see
what this pipeline is doing now
I can see that my pipeline is in the
green status it's currently running so
while it's running let me show you a
couple of features and capabilities that
databricks provides out of the box that
that are going to be extremely useful
so we can first things we can have an
external git repository where we want to
store all of this data provided by any
of the common git providers
I can schedule this pipeline so right
now I just ran it once but if if I want
to run it every day every few weeks
every month I have the ability to create
a different sort of schedule and anytime
the the pipeline fails or it succeeds if
I want a notification to be sent I can
do that and if I want to now collaborate
with other people and other other data
Engineers on my team I can give them
permissions and I can add them using the
simple interface itself
while I was showing you that
we can see that our pipeline has been
succeeded so the entire notebook that I
just created it ran again any of the new
data that was there in those directories
on a cloud storage would have now been
pulled in processed and stored in our
user goal table
and that's it that's a simple way of
creating and automating in ETL pipeline
using data breaks thank you and don't
forget to hit subscribe
foreign
[Applause]