When processing data from source into data warehouse, there are various techniques that
can be used.
In this lesson, we will see several techniques for ETL into data warehouse.
Depends on the characteristic of data source, and data warehouse itself, the technique can
be used or modified.
This technique can be used when both the data source and data warehouse are using same RDBMS
product.
For example, both uses postgresql, or oracle.
Some database like postgresql or oracle provides database link, where basically we can have
two database servers.
Database administrator then create a database link, a connection from one database to the
other.
For example, a database link from data warehouse to data source.
Then from data warehouse, we can select data from data source, then extract into data warehouse
staging area, to be transformed and loaded into data warehouse storage.
This usually can be found at older, legacy data warehouse.
And the ETL pipeline might not actually a python script or such.
When using this approach, a stored procedure on database, or regular sql script can also
be used.
This approach can has performance benefit, since we uses the database layer for all process,
including the ETL, using scheduled stored procedure, or scheduled query script on database.
However, setting database link is sometimes not straightforward, or some RDBMS does not
support it.
So in newer data warehouse,using specific product, especially cloud, this approach might
not be found.
But on-premise data center might still uses this approach.
If the data source and data warehouse using a different product, or even multiple products,
we cannot use database link.
This will also the case if the data warehouse is not RDBMS.
In this case, we can have ETL pipeline using several alternative.
We can build our own script, using SQL or python for example.
Most data warehouse product provides python library for data load.
This kind of script will be needed if we need heavy, customized transformation.
If the data extraction is simple, where the data is relatively clean and transformation
required is minimum, we can use data loader tools like Logstash or Embulk.
Such tools is optimized for do extraction and some level of transformation, and also
load to database, or even data warehouse product.
In the market, there are also ETL products.
Some I know is powerful and easy enough to use, some kind of intuitive user interface.
Just keep in mind that those products might not come free.
Some, like talend, provides free trial when this video is recorded, but they might limited
and the provider might change the license later.
In some cases, the data source might not come from database.
For example, user can use form builder like google form, to gather data.
Assuming user use google form, the data will comes in google sheet.
Or user can use excel spreadsheets at his own laptop to process and provides daily data.
Such things happened in reality, since spreadsheet is a widely used tools.
In such case, a common approach is to convert the data from spreadsheets into comma separated
file.
CSV files is actually quite common as data source, that some tools provides built-in
support to retrieve data from CSV.
So the data from google sheet or microsoft excel saved as csv.
The csv files were put on storage server.
Commonly using ftp folder.
Or, if we talks cloud, this is commonly uses cloud storage.
I�m not talking about google drive or dropbox, but a common node for cloud storage is S3.
AWS S3, google cloud storage, alibaba OSS, all provides S3 as cheap storage.
And if we use data warehouse tool from same cloud provider, they might even provides built-in
support to connect and process data from the cloud storage.
In case such feature not provided, we can always use ETL pipeline to read from CSV,
transform as required, and load into the warehouse.
Of course, the ETL pipeline can also takes from other data source like we previously
seen.
ETL process is commonly happened in batch, like every 3 AM, every 6 hours, etc.
But nowadays, we can also extract the data on almost real time.
This usually happened if we use data streaming platform, which currently apache kafka is
the most popular.
When we use apache kafka, we can use Kafka ecosystem.
For example in this case, we can use Kafka Stream or KSQLDB as extract-transform tools.
They are specific for kafka, and will transform each data as soon as the data arrive on kafka
broker.
If you want to know more about apache kafka, including kafka stream, go to last lecture
of the course and get the discount code.
For the sake of knowledge, suppose we use kafka stream, where it takes original data
from input topic, and send the transformed data into different kafka topic.
We can then use another tools for kafka, which is kafka connect, to send the data into data
warehouse.
All of this might only take millisecond, or second, as soon as data arrives.