How I Redesigned over 100 ETL into ELT Data Pipelines

By Nicholas Leong, Data Engineer, Writer

Image by Author

Something like that, but instead of water flowing through pipes, data flows through our pipelines.

Data Scientists build models and Data Analysts communicate data to stakeholders. So, what do we need Data Engineers for?

Little do they know, without Data Engineers, models won’t even exist. There won’t be any data to be communicated. Data Engineers build warehouses and pipelines to allow data to flow through the organization. We connect the dots.

 
Should You Become a Data Engineer in 2021?
 

Data Engineer is the fastest-growing job in 2019, growing by 50% YoY, which is higher than the job growth of Data Scientist, amounting to 32% YoY.

Hence, I’m here to shed some light on some of the day-to-day tasks a Data Engineer gets. Data Pipelines is just one of them.

ETL/ELT Pipelines

ETL — Extract, Transform, Load
ELT — Extract, Load, Transform

What do these mean and how are they different from each other?

In the data pipeline world, there is a source and a destination. In the simplest form, the source is where Data Engineers get the data from and the destination is where they want the data to be loaded into.

More often than not, there will need to be some processing of data somewhere in between. This can be due to numerous reasons which include but are not limited to —

• The difference in types of Data Storage
• Purpose of data
• Data governance/quality

Data Engineers label the processing of data as transformations. This is where they perform their magic to transform all kinds of data into the form they intend it to be.

In ETL Data Pipelines, Data Engineers perform transformations before loading data into the destination. If there are relational transformations between tables, these happen within the source itself. In my case, the source was a Postgres Database. Hence, we performed relational joins in the source to obtain the data required, then load it into the destination.

In ELT Data Pipelines, Data Engineers load data into the destination raw. They then perform any relational transformations within the destination itself.

In this article, we will be talking about how I transformed over 100+ ETL Pipelines in my organization into ELT Pipelines, we will also go through the reasons I did it.

How I Did It

 
 
Initially, the pipelines were ran using Linux cron jobs. Cron jobs are like your traditional task schedulers, they initialize using the Linux terminal. They are the most basic way of scheduling programs without any functionalities like —

• Setting dependencies
• Setting Dynamic Variables
• Building Connections

Image by Author

This was the first thing to go as it was causing way too many issues. We needed to scale. To do that, we had to set up a proper Workflow Management System.

We chose Apache Airflow. I wrote all about it here.

 
Data Engineering — Basics of Apache Airflow — Build Your First Pipeline
 

Airflow was originally built by the guys at Airbnb, made open source. It is also used by popular companies like Twitter as their Pipeline management system. You can read all about the benefits of Airflow above.

After that’s sorted out, we had to change the way we are extracting data. The team suggested redesigning our ETL pipelines into ELT pipelines. More on why did we do it later.

Image by Author

Here’s an example of the pipeline before it was redesigned. The source we were dealing with was a Postgres Database. Hence, to obtain data in the form intended, we had to perform joins in the source database.

Select 
a.user_id,
b.country,
a.revenue
from transactions a 
left join users b on
a.user_id = b.user_id

This is the query ran in the source database. Of course, I’ve simplified the examples to their dumbest form, the actual queries were over 400 lines of SQL.

The query results were saved in a CSV file and then uploaded to the destination, which is a Google Bigquery database in our case. Here’s how it looked like in Apache Airflow —

This is a simple example of an ETL pipeline. It was working as intended, but the team had realized the benefits of redesigning this into an ELT pipeline. More on that later.

Image by Author

Here’s an example of the pipeline after it was redesigned. Observed how the tables are brought into the destination as it is. After all the tables have been successfully extracted, we perform relational transformations in the destination.

--transactions
Select 
*
from transactions --
Select
*
from users

This is the query ran in the source database. Most of the extractions are using ‘Select *’ statements without any joins. For appending jobs, we include where conditions to properly segregate the data.

Similarly, the query results were saved in a CSV file and then uploaded into the Google Bigquery database. We then made a separate dag for transformation jobs by setting dependencies within Apache Airflow. This is to ensure that all the extraction jobs have been completed before running transformation jobs.

We set dependencies using Airflow Sensors. You can read about them here.

 
Data Engineering — How to Set Dependencies Between Data Pipelines in Apache Airflow
 

Why I Did it

Photo by Markus Winkler on Unsplash

Now that you understand how I did it, we move onto the why — Why exactly did we re-wrote all our ETL into ELT pipelines?

Cost

 
 
Running with our old Pipeline had cost our team resources, specifically time, effort, and money.

To understand the cost aspect of things, you have to understand that our source database (Postgres) was an ancient machine set up back in 2008. It was hosted on-prem. It was also running an old version of Postgres which makes things even complicated.

It wasn’t until recent years when the organization realize the need for a centralized data warehouse for Data Scientists and Analysts. This is when they started to build the old pipelines on cron jobs. As the number of jobs increase, it had drained resources on the machine.

The SQL joins written by the previous Data Analysts were also all over the place. There were over 20 joins in a single query in some pipelines, and we were approaching 100+ pipelines. Our tasks began running during midnight, it usually finished about 1–2 p.m., which amounted to about 12+ hours, which is absolutely unacceptable.

For those of you who don’t know, SQL joins are one of the most resource-intensive commands to run. It’ll increase the query’s runtime exponentially as the number of joins increases.

Image by Author

Since we were moving onto Google Cloud, the team understood that Google Bigquery is lightning fast in computing SQL queries. You can read all about it here.

 
How fast is BigQuery? | Google Cloud Blog
 

Hence, the whole point is to only run simple ‘Select *’ statements in the source and perform all the joins on Google Cloud.

This had more than doubled the efficiency and speed of our Data Pipelines.

Scalability

Photo by Quinten de Graaf on Unsplash

As businesses scale, so do their tools and technologies.

By moving onto Google Cloud, we can easily scale our machines and pipelines without worrying much.

Google Cloud utilizes Cloud Monitoring which is a tool that collects metrics, events, and metadata of your Google Cloud Technologies like Google Cloud Composer, Dataflow, Bigquery, and many more. You can monitor all sorts of data points which includes but are not limited to —

• Cost of Virtual Machines
• The cost of each query ran in Google Bigquery
• The size of each query ran in Google Bigquery
• Duration of Data Pipelines

This had made monitoring a breeze for us. Hence, by performing all transformations on Google Bigquery, we are able to accurately monitor our query size, duration, and cost as we scale.

Even as we increase our machine sizes, data warehouses, data pipelines, etc, we completely understand the costs and benefits that come with it and have full control of turning it on and off if needed.

This had and will save us from a lot of headaches.

Conclusion

Photo by Fernando Brasil on Unsplash

If you’ve read until this point, you must really have a thing for data.
You should!

We’ve already made ETLs and ELTs. Who knows what kind of pipelines we will be building in the future?

In this article, we talked about —

• What are ELT/ETL Data Pipelines?
• How I redesigned ETL to ELT Pipelines
• Why I did it

As usual, I end with a quote.

Data is the new science. Big Data holds the answers
—Pet Gelsinger

Subscribe to my newsletter to stay in touch.

 
You can also support me by signing up for a medium membership through my link. You will be able to read an unlimited amount of stories from me and other incredible writers!

I am working on more stories, writings, and guides in the data industry. You can absolutely expect more posts like this. In the meantime, feel free to check out my other articles to temporarily fill your hunger for data.

Thanks for reading! If you want to get in touch with me, feel free to reach me at nickmydata@gmail.com or my LinkedIn Profile. You can also view the code for previous write-ups in my Github.

 
Bio: Nicholas Leong is a data engineer, currently working in an online classifieds tech company. In his years of experience, Nicholas has fully designed batch and streaming pipelines, improved data warehousing solutions, and performed machine learning projects for the organization. During his free time, Nicholas likes to work on his own projects to improve his skills. He also write about his work, projects, and experiences to share them with the world. Visit my site to check out my work!

Original. Reposted with permission.

Related:

• Prefect: How to Write and Schedule Your First ETL Pipeline with Python
• Design Patterns for Machine Learning Pipelines
• ETL and ELT: A Guide and Market Analysis