Data Transformation(ETL, ELT and ECL)

Data Transformation is the bread and butter of ETL developers and data engineers.

Data Transformation is the starting point of any KPI, one needs to pull and process data based on business requirements for decision makers.

E = Extract | T = Transform | L = Load

This is the most common interview question and only those who have architecturally worked on integration can answer.

The general definitions

• ETL: Extract, Transform and Load
• ELT: Extract, Load and Transform
• ECL: Extract, Convert and Load

BUT technically HOW? Ask this nerd kind of question with experienced ETL/ ELT developers and I am sure 90% won't be able to respond.

Let’s go a little deeper and understand.

1. ETL processing is done on the source system as 1) Extract part (select) runs on source system, 2) Transform part runs on source system and 3) Load part (push data to- target table) runs on source system. Means all the operations are happening on the source system.
2. ELT processing has three more operations i.e., its DDL-EL-ETL. In ELT 1) first, a temp table is created (DDL) in the target system, 2) Extract part (select) runs on the source system, 3) Load (one-to-one push to temp table) runs on the source system, 4) Again Extract part (select) from temp table runs on the target system, 5) Transform part runs on the target system and 6) Load part (push to the table) runs on the target system. This means, 2 out of 6 operations are run on the source system where 4 operations including the most resource-hungry operation i.e., TRANSFORM operation run in the target system.
3. ECL processing is majorly used in Text Analytics where ECL tools read text from mediums like word, PDF, text file etc., catalog it by giving tags and load it in target storage which can be anything. Please note, here we are not talking about converting unstructured data like video, audio, scan, image etc., into structured data. Here, we are talking about converting or tagging existing data which is already in text format.

Now, we know why ELT tools' Sales and Pre-Sales normally don't offer a Landing Area in target systems where ETL tools' Sales and Pre-Sales will always suggest having a Landing Area.