Chapter 8 – Business Intelligence – Do you know how to Make Cofee? Then you know (E)xtract T(ransform) & L(oad)

Posted on by Asik Ali Peermuhammed Syed Ziaudeen

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 😦 .

Image

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 😦 he added and ready for (reporting) studying.

Now how ETL works ?

Image

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.

Image

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

12 thoughts on “Chapter 8 – Business Intelligence – Do you know how to Make Cofee? Then you know (E)xtract T(ransform) & L(oad)”

  1. Hahahaa…
    For a non IT person & someone who does it daily, you make it sound like a tough series of trial and error…. you can almost make a flowchart 🙂

    Reply
  3. Pingback: Chapter 14 – A Sample ETL Project – Customer Management in a Hospital « Business Intelligence Testing - Learners Guide

  4. Hi Asik
    I am a Manual tester learning ETL testing now. Thanks for posting valuable information it is very helpfull for those who are n ew to ETL testing

    1.If I use informatica as ETL tool, as a tester how I need to verify at the staging that data has transformed as per the business logic
    2.How to validate the data is loaded successfully into target table(do I need to write sql queriis in target database and check)

    Reply

    1. Hello Srinivas,

      It doesnt matter what tool is used to load the data into warehouse, we need to verify the data as per the transformation logic.

      You need to write SQL query against Source Tables with all the rules and get the result set from source then simply select the Target tables and verify the data that you got from Source Query and the data loaded into Target tables are same

      Reply

  6. Need some clarification here ..1.Do we need to test source table also as per the transformation logic or only the target table
    2.If my source is a flat file then how to verify it the source table (we cannot writ the queries riight?)

    Reply

    1. What you said is correct !!!! Please load the flat file into a temp table and write queries to validate or manually eye ball the fields as per the specifications

      Reply

Leave a reply to Srinivas Cancel reply

Published by Asik Ali Peermuhammed Syed Ziaudeen

I am Asik, basically from India graduated from Amrita Deemed University in Polymer Science. Started working in Information and Technology Organization in Software Testing field. Interested in learning new things by visualizing it. Interested in Social Economic issues. Recently set a hobby of writing posts about my experience. Like to build and maintain communities in Personal as well as in Professional. Always going through people's profile and wonder can I learn what others are working in? Always asking questions about my capability to perform much better in my professional career. I would like to travel and accomplish the places in and around India, I am the lover of food especially meat and meat related food :-). Became a no-1 lazy fellow of the earth after reading the statement by Bill Gates "He prefer to hire a Lazy fellow than a Active one because Lazy fellow are keen to finish the work as soon as possible and they go back in rest :-)" Current location - St Louis - MO