Sasi 📊📈

ELT vs ETL: What difference does the order make?

A common problem that organizations face is how to gather data from multiple sources, in multiple formats.

Extract, transform, and load (ETL) pipelines first collect data from various sources.  

It then transforms the data and loads the data into a destination data store.

it often involves using staging tables to temporarily hold data as it is being transformed and ultimately loaded to its destination.

Let's see an example to understand better:

Your organization has started to explore more about historical trends and patterns of the data.    

Currently, the organization only has a transactional database (OLTP) for the product. 

Running these heavy analytical queries runs the risk of breaking the database.

So, you replicate the data on a database designed specifically for analytics (OLAP) to power these heavy queries.  

You build an ETL pipeline to replicate data from the transactional database to the analytical database, including some data transformations to make it easier to use.

Extract, load, and transform (ELT) differs from ETL solely in where the transformation takes place.  

Instead of using a separate transformation engine, the processing capabilities of the target data store are used to transform data.  

This simplifies the architecture by removing the transformation engine from the pipeline.  

Now let's look at a Business Example:

Your organization purchases third-party data to supplement your organization's data.  

While this data is useful, the vendor provides extremely messy tables (Which is usually the scenario).  

Since this data is for R&D purposes, there is no defined business logic available.

You build an ELT pipeline that extracts the raw data from the third-party vendor and loads it into the data lake.    

The data science team then determines which parts of the data are valuable, and builds data transformations on top of the data in the data lake for the workflows.

---
 
That’s all for now!

- Smash the LIKE button and REPOST

- BOOKMARK because you’ll definitely want to revisit this again

- Follow me for more posts like this

My newsletter will:

Guide you as I wished I was guided when starting in data

Share invaluable tips from my experience

Teach the latest on prompt engineering and insights with ChatGPT

🔗 Subscribe now and stay ahead: http://sasi.beehiiv.com