ETL Tools versus "home grown ETL"

Well I have mixed views of fancy vendor based ETL and Business Intelligence Tools "BI" vendors such as Informatica and Cognos. In theory, these commercial off the shelf "COTS" based data warehouse tools sound like a panacea for busy development and production data warehouse teams who have the daunting task of building and deploying a potent data warehouse in crunch times from mixed data sources and technologies. Lets suppose that we have 200 servers with Oracle, Sybase, and Microsoft SQL Server databases running on Windows, UNIX and main frame based servers and 20 different data source feeds. To build the ideal home grown ETL environment is labor intensive and requires senior level technical staff. In the modern buzz words that software vendors mesmerize executive management professionals, the Informatica and Cognos tools sound like a perfect fix. "Oh we can build and deploy a full data warehouse with complex ETL logic in a few months!". Several million dollars later and months of delays and broken ETL pipelines, the costs of expensive ETL consultants has frustrated many DBA and developer staff not to mention inciting the wrath of IT managers.

Here is a good link to a relevant discussion on commercial ETL tools and home grown solutions:

ETL Tools Roundtable

Recap of home grown ETL versus commercial ETL tools and vendors




In fact many industry experts argue the case against ETL tool vendors and favor a custom home grown design with SQL and PL/SQL scripts for Oracle instead of the head aches of expensive tools such as Informatica. My beef with Informatica and other ETL vendors is that the tool hides the workings of the database and ETL processes. It requires expensive learning curve and delays. And even worse- the whole tool is a third party propiety method that is not based on a portable industry standard such as ANSI SQL. Heck, its bad enough that I must port Transact SQL code to Oracle PL/SQL. Lest not burden the poor overworked DBA with yet another messy tool!