Analytics Vidhya
Published in

Analytics Vidhya

Talking To My Nana About ETL/ELT

Smooth and light explanation about ETL/ELT

Photo by Paréj Richárd via Unsplash

What’s ETL/ELT?

Either ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform) are processing step to get data clean and rigorous from sources — it can be a structured SQL or unstructured NoSQL — before, perhaps, sending to business intelligence platform to derive more enrich data.

This article helps you to understand the main concept and how to share the idea with your granny.

Elements of ETL/ELT

ETL or ELT consists of three steps such as followings:

  • Extract: Extract refers to pulling sources from original database
  • Load: Load refers to sinking data into temporary staging or data lake
  • Transform: Transform refers to changing or altering — by combining, deleting or grouping — data

Let’s Visualize

ETL — Visualization

To simplify, imagine you’re running a retail store. On the left-hand side, data taken from point of sales (PoS)/cashier machines where all transaction recorded before pushing into staging area. Staging area is where all process of magic happen — slicing, combining, cleansing, aggregating — before sinking into data warehouse. Data warehouse stores all clean data that will be visualizing in business intelligence platform.

ELT — Visualization

Now, your store getting bigger than ever with multi-million dollar turnover. Thousand transactions recorded just in second and you can’t keep pace with it. You need something that can handle low latency then ELT is the answer. Unlike ETL, raw data extracted from PoS will be loaded completely without any prior changes into data warehouse like BigQuery. Next, you can transform anything as you wish before visualizing in business intelligence platform.

OK Nana,

Now explaining to your beloved nana.

ETL — Visualization II

Instead of retail store, think ETL like journey of making a jam. It all started as farmers harvesting ripe berries, where color will be bright and shiny with sweet aromatic scent. After that, transforming into jam consistency with sugar and splash of water. Then, load into clean and hygiene jar for storing longer time. Now jam is ready to accompany your scones and pairing with your favourite tea during afternoon tea time.

ELT — Visualization II

Unlike ETL which look similar in making jam, ELT more like journey of canned fruit. Yes, same at beginning where berries harvested. Then, that berries load into can without any “transformation” and ready to be delivered to groceries across country. The “transformation” of berries will be started later, once you make a puree from the canned berries. The puree has been transformed into soft and creamy paste and ready to be used.

Conclusion

ETL/ELT seems confusing and intimidating at beginning. After simplifying the concept, it is as easy as talking to nana. Who would have thought right?

--

--

--

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Recommended from Medium

Reinventing The Week — The Trident Calendar

What’s New In Pandas Version 0.25?

Data structures from scratch- Bot-up series #4[Prerequisites for data structures]

NASDAQ: Reacted Strongly From Elliott Wave Blue Box Area

Clean AND tidy data = good data

There is no question that some producers are succeeding in

Data is a Capital Asset

How to Transform Data Extracted from Wikipedia into a Map in Python

How to Transform Data Extracted from Wikipedia into a Map

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Smurpratomo

Smurpratomo

A Data Science learner || Jakarta, ID

More from Medium

Salary of a Data Scientist

Datamart, Datawarehouse, Database — Get your Facts right under 5 minutes

10 best practices you cannot do without in any data science team

So, you want to be a data <blank space>