Need for ETL tool – As explained to Undergraduate Students (Part 1)

UniversityYesterday I went to an University Campus to deliver a talk on ‘Data Cleansing’ . One of the challenges that I faced was to explain these students the need for an ETL tool in a way they can relate to. So I created a hypothetical story so that they can appreciate the need of such a tool and I am narrating it again to get feedback of the readers.

In the world of analytics, data is a resource or asset to make business decisions. For example a CEO of a Bank wants to know what are the good locations for opening a new center? One of the way this decision can be made is to find out the area where there are more number of customers concentrated. Say near Bank Location A there are 25000 customers residing but typically the bank can serve only 10,000 so that makes a case to expand the branch or open another branch.

So the bank hires some college interns to get the count of customers per location of the branch. Note the individual branch does not have this information as this information resides in a central database of the bank. So the interns need to get the list of the customers and divide them into different cities and different location and get the count of the customers in each location. Sounds like a day’s job for someone who knows how to key in proper SQL statements (Select , Group by, Count etc.). Correct? Let’s see…

On Day 1 of the job, they speak to the DB Administrator  to get access to the database where they can give these queries. The Database Administrator has some concerns…

1. Which database they want an access to. The Loan customer’s data reside in Oracle, the debit Card customer’s data resides in the DB2, The Credit Card customer’s data reside in Netezza and so on…
2. Moreover recently the bank had some acquisition and the data from the acquired bank still uses the Informix database.
3. To add to all this, the Loan account, Debit Card account, Credit Card account are considered different accounts. If a single person has all these accounts from the perspective of bank they are three different individuals. Or in technical terms the data contains duplicates. Not to forget that a bank was recently acquired and some of the customers would have account in both of them, so they should also be considered one person.
4. As if it was not enough, the Database Administrator added that these complex queries cannot be run on up and running live databases. Customers are making transactions and bank will suffer huge loss in case of down time. But if required he can give read access to one of the mirror database for queries during night from 11:00pm to 4:00 am.

So now what looked like a day’s job turned out to be something that would take them months to accomplish. And what if more complexities are added in between (like more data sources or the window to move data is not sufficient)? So what is the solution? Stay tuned…

3 thoughts on “Need for ETL tool – As explained to Undergraduate Students (Part 1)

  1. Your explination inspired me alot sir . It was so clear and informative sir but it was sad that it was only one day workshop looking forward for such great and enlightened workshops sir.
    – GITAM university student .

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s