Hi All,
[I am using Civil Engineers and Coffee making scenarios for Better Understanding for Beginners]
Most widely spoken concept in Business Intelligence world is ETL – Extract Transform and Load. The persons who are in IT field already they call it as ETL Architecture
ETL Architecture :
Who use the word Architecture most commonly ? – Civil Engineers. They say Building Architecture so when you are constructing something you are using the word Architecture. You cant construct anything without prior planning. So a Civil engineer will draw a diagram that will tell how the building going to look like.
Likewise in Business Intelligence world, we have Solution Designers (Civil Engineers) who designs the ETL System and they are also called System Architectures!!!
So now you understood why we are calling it as ETL System Architect
Now let me explain how Extract Transform and Load process works.
How to make Coffee?
Asik in the below image is preparing coffee for his wife
In the first step, he is preparing decoction (extracting the essence of the coffee from Beans).
In the second step, he is transforming it to Coffee by adding correct proportion Milk,Sugar
In the third Step, he loaded the Coffee into Container for Drinking
After drinking the coffee (testing) he found (defects) he needs to add more sugar
Now how ETL works ?
Developers involved in designing the Extract Transform Load Jobs. What is a Job? to make it understand ! we can say it is a Function.
These jobs will be written using Unix Scripting.
Extract Job (decoction ready) will bring all the data into Staging tables – testers needs to verify all the data is moved from file to Staging table
Transform Job (added milk and sugar) will apply rules and filter the records , alters the records as per documents - testers needs to verify all the data transformed as expected
Load Jobs (coffee ready ) once the transform jobs ran successful then Load jobs will load the data into Intermediate tables (will be used for delta check in Day_02) – testers needs to verify the the transformed data is loaded into target tables
1.In Load job we have Insert as well as Delta
Hope you guys understood the ETL Logic!!!
In my next blog, I will explain about INSERTS , UPDATES , Logically DELETES
Cheers
Asik
Filed under: Uncategorized Tagged: Architecture, CIVIL, Coffee, Construction Testing, Design, ETL, ETL Testing, Extracts, LOADS, Software Testing, TRANSFORMS
