I worked for ten years at a company that had 27 stove piped systems. I have lived in the ETL nightmare for a long time.

My current position was transformed into the ETL process when our company was bought out by a competitor. I don't actually have to do the Load portion but am responsible for getting the data out and transformed to their load specs. It has been interesting to find out how the end-users have abused the applications and databases.

The current system works so well that five employees can transform up to 80 companies per month. And that is hundreds of medical and financial records per facility.

And really that has happened with every ETL process I have worked.

But sometimes it the programmer that does the RBAR process as oppose to UPDATE statement that causes the slow-down. I ran into a one where there was flag for flood insurance (FI_Req) that was based on two other columns in the table. The programmer was going through 500K+ rows to set the flag one by one.

We changed it to a simple set of update statements, and cut 30 minutes out of the processing time.

So now I always look to see what portion of the process is taking the longest and see if I can cut down the processing time.