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

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

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



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 πŸ™‚

  2. 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)

    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

  3. 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?)

Leave a Reply to Chapter 14 – A Sample ETL Project – Customer Management in a Hospital « Business Intelligence Testing - Learners Guide Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s