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?