Tuesday, November 06, 2007

Purpose of Parameters in ETL

For those of you who don't know, I am now doing software consulting full time. I'll write another post about that, but today I experienced something I wanted to share.

One of my clients has ETL jobs created to populate a data warehouse. Well, for all intents and purposes it's technically a data store, but I digress. I was asked to take a look at some Data Stage ETL jobs that needed to be modified because the database user id had changed. WHAT? You mean people are actually hard coding critical values and passwords in ETL jobs? Why yes they are. I spoke with the ETL architect about the practice and friendly advised him that is a data warehouse worst practice. He was aware of the issue and but a decision was made back in the past to 'get it done now', but thankfully it was an easy sell to put all the user information in variables, but they still wanted variables hardcoded in the jobs, so the battle is only half-way won. I did get a committment to move to a dynamic configuration and I am putting finishing touches on my prototype, but the moral of the story is to not save your user id's and passwords hard coded in ETL jobs. It simply exponentially increases the maintenance costs at a later time.

No comments: